How to output stored procedure query results to a text file

user_3692082 0
Viewed 2088

I have a simple query that pulls the IDs of all records with a certain name and status from a specific date. As far as output goes all I need is the IDs, preferably as a single line of text comma delimited. Currently I'm only able to get each record on a new line in a .txt document but that's also fine.

Here's my query that pulls the data I need (working with SAP B1 data in SSMS):

SELECT T0.[DocNum] 
FROM OINV T0 
WHERE T0.[DocDate] = CAST(CURRENT_TIMESTAMP AS DATE) 
  AND T0.CardName LIKE '%Name%' 
ORDER BY T0.CardName

I have this created as a stored procedure that I want to run on a schedule. My problem is that I can't figure out how to get this stored procedure to output the results to a .txt file when the query results contain more than one record.

After researching a bit I figured out a way to have the query output to a .txt file as I wanted by using SQLCMD and putting :OUT C:\file.txt before the query, but this method won't work as a stored procedure.

After researching a bit more I figured out a way to call xp_cmdshell from the stored procedure itself to write the output to a file, but this is where it errors out if there is more than one result.

Here's that stored procedure:

SET NOCOUNT ON;

DECLARE @timeStamp varchar(200) =  convert(varchar,getDate(), 112 )+'_'+ Replace(convert(varchar,getDate(), 114 ),':','') 
DECLARE @var NVARCHAR(MAX) = ''
DECLARE @fn varchar(500) = 'C:\file.txt';
DECLARE @cmd varchar(8000)

SET @var = (SELECT T0.[DocNum] FROM OINV T0 WHERE T0.[DocDate] = CAST(CURRENT_TIMESTAMP AS DATE) AND T0.CardName like '%Name%' ORDER BY T0.CardName)
SET @cmd = concat('echo ', @var, ' > "', @fn, '"');
    
EXEC xp_cmdshell @cmd;

Here's the error I get when that query returns more than one record:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I also read about using bcp, but I can't seem to get it to work with how I'm currently going about this (via stored procedure). I won't post the code for those attempts here because I'm sure it's way off base.

Some answers I'm seeing tell me to use "TOP 1" or using "WHERE" to ensure only one record is returned but I need to store all the results of this query, so those aren't valid workarounds for me.

Can anyone please tell me what the easiest way is to output stored procedure query results (multiple) to a text file?

Edit -

Thank you all for all of the advice and suggestions everyone has provided so far. I'm going to list all of the other solutions I've tried and where I've gotten stuck with them-

  • Using BCP

I tried using BCP via the following implementations, but both fail with the error

Procedure expects parameter 'command_string' of type 'varchar'

Implementation 1-

DECLARE @strbcpcmd NVARCHAR(max)
SET @strbcpcmd = 'bcp  "SELECT T0.[DocNum] FROM OINV T0 WHERE T0.[DocDate] = CAST(CURRENT_TIMESTAMP AS DATE) AND T0.CardName like ''%Name%''" queryout "C:\test.txt" -w -C OEM -t"$" -T -S'+@@servername    
EXEC master..xp_cmdshell @strbcpcmd

Implementation 2-

DECLARE @bcp nvarchar(max)
DECLARE @timeStamp varchar(200) =  convert(varchar,getDate(), 112 )+'_'+ Replace(convert(varchar,getDate(), 114 ),':','') 

SET @bcp='bcp "SELECT T0.[DocNum] FROM OINV T0 WHERE T0.[DocDate] = CAST(CURRENT_TIMESTAMP AS DATE) AND T0.CardName like ''%Name%''" queryout "C:\Test.txt" -c -t, -ServerName01 -T'

EXEC master.dbo.xp_cmdshell @bcp, no_output /*optional, remove no_output for debugging */

I want to note here that it would be preferable for the filename to be dynamic depending on the runtime, which is why I want to keep CURRENT_TIMESTAMP AS DATE in there if I can.

Edit X2 - I was able to get bcp "working" by changing the variable type like so DECLARE @bcp nvarchar(1000) but it looks like the query itself is failing now (although it definitely works outside of this)-

Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid object name 'OINV'.

&

Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to resolve column level collations

  • Using the SQL Job Agent

This is a bit confusing to me because I'm not sure where I separate the query from the output directive. The way I've tried it is creating the stored procedure that contains my query and then creating a job based off of that stored procedure. I added in an additional step of type "Operating system (CmdExec)" with :OUT C:\SQLOut\Test.txt as the command.

I also tried adding the full query along with the :OUT C:\SQLOut\Test.txt command in a single step. That results in the following error -

Executed as user: NT Service\SQLSERVERAGENT. The process could not be created for step 1 of job 0x2A9232A6AF3E4F4B8C53CCF50419245D (reason: The system cannot find the file specified). The step failed.

I'm assuming that this error is due to the fact that the file doesn't already exist. I'd like the files to be created by this process with the timestamp in the file name itself, and I'm not sure if that's possible with this method.

  • Using SSIS

I don't have any experience with this but I'd be willing to learn. Unfortunately though it doesn't look like we have BIDS installed anywhere so I'd have to set all of that up in our environment for me to continue down this path. I'm guessing that's going to be a lot more than what I want to get involved with for this issue alone, though.

  • Using STRING_AGG

This would be perfect, but we're running SQL Server 2016, so just a single version shy of having this functionality.

  • Using PowerShell

I think I could probably get this working, although my main hesitation has to do with the scheduling piece. I suppose I could use Task Scheduler, but this approach seems sloppy.

Final Edit -

Was able to get this working using bcp. The bcp method was definitely the way to go as it only required minimal modification to my original code to get working. My main issues were with the syntax.

This is my final (working) code implementation -

DECLARE @bcp nvarchar(1000)
DECLARE @timeStamp varchar(200) =  convert(varchar,getDate(), 112 )+'_'+ Replace(convert(varchar,getDate(), 114 ),':','') 

SET @bcp='bcp "SELECT T0.[DocNum] FROM MyDB.DBO.OINV T0 WHERE T0.[DocDate] = CAST(CURRENT_TIMESTAMP AS DATE) AND T0.CardName like ''%Name%''" queryout "C:\SQLOUT\Name' + @timeStamp + '.txt" -c -t, -Sservername -T'

EXEC master.dbo.xp_cmdshell @bcp--, no_output /*optional, remove no_output for debugging */
ssms sql-server sql
3 Answers

First, let me say something about the error you're getting.

@var is a single variable with a single type of nvarchar(max). Your select statement returns a result set... rows and columns. Each element, each intersection of some row and some column in the result set, is a single value with its own datatype. Your query returns one column, but multiple rows. So you have multiple values. You can't assign multiple values to one variable, much like a single cell in excel can't also be a whole column in excel.

As far as getting data out of sql to a text file, pure TSQL-based solutions aren't really a good fit for this. You want some kind of "client application" to run the SQL and then interact with the world outside the database.

Since you said you wanted to run this on a schedule, you might want to use SQL Agent as your "client application", since it's also a scheduler.

SQL Agent has a job step type called "Operating system (CmdExec)". You can use this job step type to run sqlcmd (have a read through this) and have sqlcmd output your query results to a text file in just the way you mentioned in your question.

You could also use a SQL Server integration services package. That's a particularly good idea if you already have an integration services catalog somewhere, or if you expect to be doing more exports. Otherwise, the CmdExec solution is fine.

If you choose to use SQL Agent with a CmdExec step, you might want to set up a proxy account with limited permissions to execute the job. This should get you started.

Don't use xp_cmdshell. In fact, the usual advice is to keep it disabled entirely.

user_71652790

Using BCP is quite straight forward, there's no need to try and store the result of a query.

A basic BCP statement you might use within your stored procedure would look like:

declare @bcp nvarchar(max)

set @bcp='bcp "SELECT T0.[DocNum] FROM DBNAME.DBO.OINV T0 WHERE T0.[DocDate] = CAST(CURRENT_TIMESTAMP AS DATE) AND T0.CardName like ''%Name%'' ORDER BY T0.CardName" queryout "c:\file.txt" -c -t, -Sservername -T'

exec master.dbo.xp_cmdshell @bcp, no_output /*optional, remove no_output for debugging */

In the BCP syntax above, replace servername with the DNS name or IP of theSQL Server; -T assumes trusted connection, you can also use -U and -P for specifying username & password.

You can use this directly in a procedure and run it using xp_cmdshell, or run it directly from an Agent Job using CmdExec option.

For debugging you can run it directly in an SSMS query window.

user_153326500

In regards to the error you are getting...@var is just expecting a single value, but you are trying to fill it with a query that is returning multiple rows.

Depending on what version of SQL you are running, you could use STRING_AGG (I believe introduced in SQL Server 2017) to stuff the results to a single value, with a defined separator (Pipe in this case, but you can change it to whatever you want).

SET @var = (SELECT STRING_AGG(T0.[DocNum], '|') as DocNum FROM OINV T0 WHERE T0.[DocDate] = CAST(CURRENT_TIMESTAMP AS DATE) AND T0.CardName like '%Name%' ORDER BY T0.CardName)

If you are on a version that doesn't support STRING_AGG, you can try to STUFF it:

DECLARE @var NVARCHAR(MAX) = ''
SET @var = 
STUFF(
    (
    SELECT DISTINCT  ', ' + T0.[DocNum]
    FROM OINV T0 
    WHERE T0.[DocDate] = CAST(CURRENT_TIMESTAMP AS DATE) 
    AND T0.CardName like '%Name%' 
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'),1,1,''
)

Sorry I am not much help with exporting it to a text file. If it were me, I would use integration services (SSIS) to do that.

user_30846990
Related