1

I want to execute the OpenRowSet command to pull the contents of a .txt file into 1 column. In my script, I need to loop through a collection of existing records and build a dynamic file path for each .txt file I want to import into the database, and therefore, I am using the sp_executesql procedure to execute the OpenRowSet command and output the contents of the .txt file to an output parameter.

I have tested OpenRowSet with a hard-coded file path and without passing it to the sp_executesql procedure, and I am able to retrieve the .txt file contents and insert it into my desired table in SQL. All of that is working. The issue I run into with the sp_executesql procedure is the output parameter is coming back empty. Here is a snip of the code I am running in MSSQL SMS version 15.0.18206.0 on Windows Server 2019.

DECLARE     @rootDirectory          VARCHAR(100)
DECLARE     @filePathWithName       VARCHAR(255)
DECLARE     @txtFileContents        VARCHAR(MAX)
DECLARE     @commandText            NVARCHAR(MAX)

Set @commandText = N'(Select BulkColumn FROM OPENROWSET (BULK '''+ @rootDirectory + @filePathWithName + ''', SINGLE_CLOB) Myfile)'

-- Print the command text, should contain full text file path.
print 'Command Text: ' + @commandText

-- Execute command and output text file contents.
EXEC sp_executesql @commandText, 
                   N'@fileContentsOut VARCHAR(MAX) OUTPUT', 
                   @fileContentsOut = @txtFileContents OUTPUT;

-- Select the file contents output.
SELECT @txtFileContents;  -- <-- comes back empty???

When I execute the command above, I get a query result window that shows the contents of the .txt file in a column called "BulkColumn", but @txtFileContents parameter is empty.

Here is what the @commandText looks like before executed:

Command Text: (Select BulkColumn FROM OPENROWSET (BULK 'F:\Assets\mypath\myfile12345.TXT', SINGLE_CLOB) Myfile)

I don't understand why @txtfileContents comes back empty when I select it.

GJGerson
  • 211
  • 1
  • 9
  • 20

2 Answers2

0

Try this instead:

DECLARE @txtFileContents VARCHAR(MAX);

SELECT @txtFileContents = BulkColumn
FROM OPENROWSET ( BULK 'F:\Assets\mypath\myfile12345.TXT', SINGLE_CLOB ) AS x;

SELECT @txtFileContents AS TxtFileContents;

UPDATE:

I am iterating through a list of records and building a dynamic file path each time through...

This works for me:

DECLARE 
    @cmd nvarchar(1000),
    @file nvarchar(255) = 'F:\Assets\mypath\myfile12345.TXT',
    @txtFileContents varchar(MAX);

SET @cmd = FORMATMESSAGE ( 'SELECT @txtFileContents = BulkColumn FROM OPENROWSET ( BULK ''%s'', SINGLE_CLOB ) AS x;', @file );
EXEC sp_executesql @cmd, N'@txtFileContents varchar(MAX) OUT', @txtFileContents = @txtFileContents OUT;

SELECT @txtFileContents AS TxtFileContents;

It turns out you're missing SELECT @fileContentsOut = BulkColumn in your @commandText.

This:

SET @commandText = N'SELECT BulkColumn FROM OPENROWSET (BULK '''+ @rootDirectory + @filePathWithName + ''', SINGLE_CLOB) Myfile;'

Needs to be:

SET @commandText = N'SELECT @fileContentsOut = BulkColumn FROM OPENROWSET (BULK '''+ @rootDirectory + @filePathWithName + ''', SINGLE_CLOB) Myfile;'
critical_error
  • 6,306
  • 3
  • 14
  • 16
  • Critical error, that is what I was doing to successfully with the hard coded file path, but as I mentioned, I am iterating through a list of records and building a dynamic file path each time through, and this method will not accept a parameter for the file path. That’s why I’m using the sp_executesql procedure. – GJGerson Oct 27 '20 at 02:23
  • Well, that’s a bummer. I’ll dig further. – critical_error Oct 27 '20 at 02:25
  • Yeah, it’s gotta be something simple I’m doing wrong. Just can’t figure out why the output parameter is coming back empty. – GJGerson Oct 27 '20 at 02:53
  • @GJGerson check out the update to my answer. This works for me. – critical_error Oct 27 '20 at 16:05
  • @GJGerson For the record, you were right. It was a simple issue, lol. – critical_error Oct 27 '20 at 18:53
0

Could use SSIS with a for each loop container.

https://www.red-gate.com/simple-talk/sql/ssis/ssis-basics-introducing-the-foreach-loop-container/

Brandon Frenchak
  • 486
  • 4
  • 12