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](https://msdn.microsoft.com/library/ms174984) for a sample. — Jeroen Mostert, Jun 17 '16 at 14:34
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, Jun 17 '16 at 14:40
@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, Jun 17 '16 at 16:36

score 12 · Answer 1 · answered Jun 17 '16 at 21:42

@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

I tried this, but I get the error "Error -2146828218 opening file.", any idea what that's about? — M. Barbieri, Jun 20 '16 at 19:21
-2146828218 is 800A0046 in hex, which (according to Google) is probably a permission denied response. Remember this is running on the SQL Server computer, not the client computer, so the @Path is on the server and the SQL Server service account needs permission to create a file that path. — Rhys Jones, Jun 20 '16 at 21:07
Thanks for the response! Yeah I figured that it would be a permission error :/. How, then would I give SQL Server service account to create a file? — M. Barbieri, Jun 20 '16 at 22:13
Well, not to muddy the waters, but this code failed for me. The close method specifically gave error 0xC0000005 (EXCEPTION_ACCESS_VIOLATION) and I ended up using EXECUTE @@hr = sp_OADestroy @@FileID without the close line. — Dan, Mar 16 '18 at 23:31

Mark Davich · Answer 2 · 2022-12-20T00:10:09.640

I was experiencing the same behavior.
The file was being written, just not in the location I expected.

I had to use the network file path or Universal Naming Convention (UNC)
of the destination folder so the remote SQL Server could write to a location
on my local client machine.

Example Network File Paths (UNC Paths)

\\127.0.0.1\C$\Temp\Test.txt
\\localhost\C$\Temp\Test.txt
\\Full-Device.Name\C$\Temp\Test.txt

Note that C$ points to C:\

This SQL creates and writes 'Hello World' to C:\temp\Test.txt

-- Enabling OLE Automation (If it's already enabled, you don't need to run)
EXECUTE SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
 
EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE WITH OVERRIDE
GO

DECLARE @Auto    INT
DECLARE @FileID  INT

/*  ************************************************************************************************

    Use the network address of the file when you want a remote
    SQL server to save a file on a client. To obtain the network
    address/path of a file use the client IPV4 address or Full Device Name

    Examples:
      IP Address (nnn.nnn.nnn.nnn) - Find this by running 'ipconfig' on a command line of the destination machine and use IPV4 address
      \\IP Address\C$\...\Test.txt
      DECLARE @Path varchar(2000) = '\\127.0.0.1\C$\temp\Test.txt' -- This is C:\temp\Test.txt

      Full Device Name (FULL-NAME.Of.Machine) - Right click 'This PC' in file explorer on the destination machine and click properties
      \\Full Device Name\C$\...\Test.txt
      DECLARE @Path varchar(2000) = '\\localhost\C$\temp\Test.txt' -- This is C:\temp\Test.txt

Change 127.0.0.1 to the IPV4 address or 'Full device name' of the client (destination) machine

****************************************************************************************************/
DECLARE @Path varchar(2000) = '\\127.0.0.1\C$\temp\Test.txt' -- This is C:\temp\Test.txt
DECLARE @Text varchar(2000) = 'Hello World'

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_OAMethod @FileID, 'Close', Null, Null
EXECUTE sp_OADestroy @FileID
EXECUTE sp_OADestroy @Auto

-- Disabling OLE Automation (Run only if it wasn't enabled before)
EXEC sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE WITH OVERRIDE
GO
 
EXECUTE SP_CONFIGURE 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

Her is a diagram of running a query with local parameters
on a remote SQL server

╔════════════════════════════════════════════════════════╗         ╔════════════════════════════════════════════════════════╗
║       Remote SQL Server Machine                        ║         ║  Client                                                ║
╠════════════════════════════════════════════════════════╣         ╠════════════════════════════════════════════════════════╣
║                                                        ║         ║                                                        ║
║    ┌──────────────────────────────────────────────┐    ║         ║    ┌──────────────────────────────────────────────┐    ║
║    │  SQL Server                                  |    ║         ║    │  SQL Server Management Studio (SSMS)         |    ║
║    ├──────────────────────────────────────────────┤    ║         ║    ├──────────────────────────────────────────────┤    ║
║    | SQL ...                                      |    ║         ║    | SQL ...                                      |    ║
║    | EXECUTE @FileID 'WriteLine', Null, @Text ◄───┼────╫─────────╫────┼ All this is happening on the SQL Server      |    ║
║    | SQL ...            │                     ▲   |    ║         ║    | SQL ...                                      |    ║
║    └────────────────────┼─────────────────────┼───┘    ║         ║    └──────────────────────────────────────────────┘    ║
║                         |                     |        ║         ║                                                        ║
║                         |                     |        ║         ║     Nothing is written to the client file system       ║
║                         |                     |        ║         ║                                                        ║
║    ┌────────────────────┼─────┐   ┌───────────┼───┐    ║         ║    ┌──────────────────────────┐                        ║
║    │  File System       |     │   │  SSMS     |   │    ║         ║    │  Client File System      │                        ║
║    ├────────────────────┼─────┤   ├───────────┼───┤    ║         ║    ├──────────────────────────┤                        ║
║    | C                  │     |   |           │   |    ║         ║    | C                        |                        ║
║    | └─ Temp            │     |   |  SQL ... ─┘   |    ║         ║    | └─ Temp                  |                        ║
║    |    └─ Test.txt  ◄──┘     |   |               |    ║         ║    |    └─ Test.txt           |                        ║
║    └──────────────────────────┘   └───────────────┘    ║         ║    └──────────────────────────┘                        ║
╚════════════════════════════════════════════════════════╝         ╚════════════════════════════════════════════════════════╝

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

2 Answers2