Network Programming in .NET

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.