I am trying to write to a file with Transact-SQL using a stored procedure that I can pass input to. However, every time I run the commands in SQL Server 2012 it displays Command(s) completed successfully.
, but I navigate to the text file and I don't see any text there. Any idea what could be going wrong?
My code:
-- GRANTS PERMISSION TO OLE AUTOMATION -- sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO DROP PROC WriteToGSQL; -- ENCAPSULATING THE SPECIFIC CODE THAT DOES THE ACTUAL WRITING TO THE TEXT FILE -- SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[WriteToGSQL] @Path VARCHAR(2000), @Text VARCHAR(2000) AS BEGIN DECLARE @Auto INT DECLARE @FileID INT EXECUTE sp_OACreate 'Scripting.FileSystemObject', @Auto OUT EXECUTE sp_OAMethod @Auto, 'OpenTextFile', @FileID OUT, @Path, 8, 1 EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, @Text EXECUTE sp_OADestroy @FileID EXECUTE sp_OADestroy @Auto END DECLARE @File VARCHAR(2000); DECLARE @Txt VARCHAR(2000); SET @File = 'C:\xxxxxxxx\xxxx\test.txt'; SET @Txt = 'Did it work?'; EXEC WriteToGSQL @File, @Txt;
@JeroenMostert deserves the credit for pointing you in the right direction, I'm just putting his words into SQL to help you along (and I'm doing it without an SSMS to hand so you might need to tweek it a little).
To re-iterate Jeroen's points, you need to make sure each sp_OA... call works by checking the return value, and you need to call the Close method on the file before destroying the object. Have a look at the MSDN docs for the FileSystemObject for further ideas.
DECLARE @hr int;
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject', @Auto OUT
IF @hr <> 0
BEGIN
RAISERROR('Error %d creating object.', 16, 1, @hr)
RETURN
END
EXECUTE @hr = sp_OAMethod @Auto, 'OpenTextFile', @FileID OUT, @Path, 8, 1
IF @hr <> 0
BEGIN
RAISERROR('Error %d opening file.', 16, 1, @hr)
RETURN
END
EXECUTE @hr = sp_OAMethod @FileID, 'WriteLine', Null, @Text
IF @hr <> 0
BEGIN
RAISERROR('Error %d writing line.', 16, 1, @hr)
RETURN
END
EXECUTE @hr = sp_OAMethod @FileID, 'Close', Null, Null
IF @hr <> 0
BEGIN
RAISERROR('Error %d closing file.', 16, 1, @hr)
RETURN
END
EXECUTE @hr = sp_OADestroy @FileID
EXECUTE @hr = sp_OADestroy @Auto