Click here to Skip to main content
15,566,994 members
Please Sign up or sign in to vote.
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
Updated 1-Dec-20 20:49pm

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
 
Share this answer
 
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?
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
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900