Creating a text file on local machine using SQL Server 2008

Question

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.

Any help is much appreciated..

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,'&','&'),'<' ,'<'),'>','>')
    EXEC @rc = sp_OAMethod @FileID , 'WriteLine' , Null , @text
    Exec @rc = [databasename].dbo.sp_OADestroy @FileID

    Declare @Append bit

    Select @Append = 0

    If @rc <> 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'

I take it that SSIS is not available to you? That's really a better tool for the job. — Ann L.
– Ann L., Commented Nov 15, 2011 at 20:04

Community · Accepted Answer · 2017-04-13 12:13:41Z

5

Create a share on your local machine and then use it like this:

EXEC USP_SaveFile 'Microsoft SQL Server 2008', '\\YourMachine\dir1\dir2\test.txt'

Please mind that SQL Server is a service so this: Network Service account accessing a folder share apply to it.

edited Apr 13, 2017 at 12:13

CommunityBot

11 silver badge

answered Nov 15, 2011 at 7:24

Michał Powaga

23.4k8 gold badges55 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

masif Over a year ago

+1 for the answer, @Michał Powaga is there any other method to achieve the same as i am not having the permissions of sharing the folder.

Michał Powaga Over a year ago

No, sorry. The only thing I think about is client side application that executes procedure and saves returned result set (or output parameter). But it won't happen on server side as you want to.

Collectives™ on Stack Overflow

Creating a text file on local machine using SQL Server 2008

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related