Home > Uncategorized > Writing a text file from SQL server

Writing a text file from SQL server

How to write a text file from SQL server:

1. Create the following Stored Procedure:

CREATE PROCEDURE usp_UseOA (
@File varchar(1000)
, @Str varchar(1000)
)
AS
DECLARE @FS int
, @OLEResult int
, @FileID int

EXECUTE @OLEResult = sp_OACreate
'Scripting.FileSystemObject'
, @FS OUT

IF @OLEResult <> 0

BEGIN
PRINT
'Error: Scripting.FileSystemObject'
END

-- Opens the file specified by the @File input parameter
execute @OLEResult = sp_OAMethod
@FS
, 'OpenTextFile'
, @FileID OUT
, @File
, 8
, 1
-- Prints error if non 0 return code during sp_OAMethod OpenTextFile execution
IF @OLEResult <> 0
BEGIN
PRINT 'Error: OpenTextFile'
END

-- Appends the string value line to the file specified by the @File input parameter
execute @OLEResult = sp_OAMethod
@FileID
, 'WriteLine'
, Null
, @Str
-- Prints error if non 0 return code during sp_OAMethod WriteLine execution
IF @OLEResult <> 0
BEGIN
PRINT 'Error : WriteLine'
END

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS


2. Allow OLE Automation on the database

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

3. Call the stored proc

usp_UseOA 'c:dfslog.txt','hello world'

NB: The SQL server account will have to have read/write access to the destination folder.

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: