BCP command execute each row SQL command in table

Question

0.00/5 (No votes)

See more:

I had write a procedure that export each of the SQL Query data to the csv file with header. But I dont know the error shown as below.

Msg 512, Level 16, State 1, Line 26 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. Msg 512, Level 16, State 1, Line 27 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Here with my sample database
https://i.stack.imgur.com/kDjJE.png[^]

What I have tried:

DECLARE @SQLCmd varchar(max)
    DECLARE @FilePathCmd varchar(max)
    DECLARE @ExportCmd varchar(max)

    SET @SQLCmd=(SELECT CommandText from TestDB.dbo.CommandExportData)
    SET @FilePathCmd=(SELECT FilePath from TestDB.dbo.CommandExportData)

    

   SELECT @ExportCmd='bcp "'+@SQLCmd+'" queryout "'+@FilePathCmd+'" -c -t, -T -S' + @@servername
   print(@ExportCmd)
   exec master..xp_cmdshell @ExportCmd

Posted 1-Dec-20 3:39am

Eng Soon Cheah

Updated 1-Dec-20 19:49pm

Add a Solution

2 solutions

Solution 2

use TestDB

DECLARE @storedprocedure_name varchar(100)
DECLARE @cmd VARCHAR(4000)

DECLARE @FilePath varchar(100)
DECLARE @FileNames varchar(100)

DECLARE spCursor CURSOR FAST_FORWARD FOR
SELECT CommandText,FilePath,FileNames FROM dbo.MultipleQuery
OPEN spCursor
FETCH NEXT FROM spCursor INTO @storedprocedure_name ,@FilePath,@FileNames
WHILE @@fetch_status = 0
BEGIN

SET @cmd = 'bcp "' +
@storedprocedure_name + '" queryout "' + @FilePath +@FileNames +'" -c -UTF8 -T -S'+@@SERVERNAME
PRINT @cmd

EXEC master..xp_cmdshell @cmd

FETCH NEXT FROM spCursor INTO @storedprocedure_name,@FilePath,@FileNames
END
CLOSE spCursor
DEALLOCATE spCursor

Here with my solution for reading each row of data

Posted 1-Dec-20 19:49pm

Eng Soon Cheah

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

BabyYoda · Accepted Answer · 2020-12-01T05:13:00

Solution 1

These 2 lines are the problem,

SET @SQLCmd=(SELECT CommandText from TestDB.dbo.CommandExportData)
SET @FilePathCmd=(SELECT FilePath from TestDB.dbo.CommandExportData)

The error message is telling you that your subquery is returning more than one row. Since more than one row is returned you can't store them into a variable.

You could add TOP 1, for example, to your select statements.

Assuming there is only 1 row in CommandExportData, you could also do:

SQL

SELECT @SQLCmd = CommandText, @FilePathCmd = FilePath
FROM CommandExportData

Posted 1-Dec-20 5:13am

BabyYoda

Comments

Eng Soon Cheah 1-Dec-20 11:35am

If Multiple line, then I need to use Cursor?

CHill60 1-Dec-20 11:39am

No - you need to change the sub-query so that only 1 row is returned or redesign your query. There is very very rarely any need to use a cursor

BabyYoda 1-Dec-20 11:51am

No, you need to add a where clause to make sure you only get the one record you want. You are filling in a variable which means you only want one record. Or maybe you are trying to do something else?