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
'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.
Categories: Uncategorized
Comments (0)
Trackbacks (0)
Leave a comment
Trackback