When I write to a text file using sp_OAMethod nothing shows up

Question

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;

Answer 1

@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