title: Creating a text file on local machine using SQL Server 2008
body: <p>I would like to know how to create a text file and save it on my local machine, below is the stored procedure I am using. It creates the text file on server but I want it to be created on my local machine.</p>
  
  <p>Any help is much appreciated..</p>
  
  <pre><code>alter Procedure [dbo].[USP_SaveFile]
      (@text as NVarchar(Max), @Filename Varchar(200)) AS
  Begin
      declare
          @Object int,
          @rc int,
          @FileID Int
  
      EXEC @rc = sp_OACreate 'Scripting.FileSystemObject', @Object OUT
      EXEC @rc = sp_OAMethod @Object , 'OpenTextFile' , @FileID OUT , @Filename , 2 , 1
      Set @text = Replace(Replace(Replace(@text,'&amp;','&amp;'),'&lt;' ,'&lt;'),'&gt;','&gt;')
      EXEC @rc = sp_OAMethod @FileID , 'WriteLine' , Null , @text
      Exec @rc = [databasename].dbo.sp_OADestroy @FileID
  
      Declare @Append bit
  
      Select @Append = 0
  
      If @rc &lt;&gt; 0
      Begin
          Exec @rc = [databasename].dbo.sp_OAMethod @Object, 'SaveFile',null,@text ,@Filename,@Append
      End
  
      Exec @rc = [databasename].dbo.sp_OADestroy @Object
  End
  
  --  Use [databasename]
  --  EXEC sp_configure 'show advanced options', 1
  --  GO
  --  EXEC sp_configure 'Ole Automation Procedures', 1;
  --  GO
  --  RECONFIGURE;
  --  GO
  --  EXEC USP_SaveFile 'Microsoft SQL Server 2008', 'D:\sqlfiles\test.txt'
  </code></pre>