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;

You're not checking the return values of any call to sp_OACreate and sp_OAMethod, so there's no way to see if anything's going wrong. Use the return value in conjunction with sp_OAGetErrorInfo. See MSDN for a sample. — Jeroen Mostert
One thing that looks obviously "wrong" is that you don't call Close on the text file. Simply calling sp_OADestroy gets rid of the object and may prevent the stream from flushing its contents. (I have no experience actually using it, so this is speculation.) — Jeroen Mostert
@JeroenMostert thank you for the response. I apologize, I am new to TSQL so I'm not very familiar with the sp_OA functionalities. I looked at the MSDN example, but no luck, is it possible that you could show me what you mean? — M. Barbieri

Rhys Jones Rhys Jones · Accepted Answer · 2016-06-17T21:42:52

@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

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

1 Answers