CODEWITHSUNDEEP
sqlsql-servercsvexport-to-csv
Silviu
Silviu

Reputation: 123

Suppressing query output information from the export file

Trying to export a SQL Server database query results to a .csv file I have similarly output situation according to what it's presented here but having a different approach I open a new thread.

I have a complex query that has joins on few tables using some filters via some temporary tables. I am executing the stored procedure:

EXEC xp_cmdshell 'SQLCMD -S . -d myDATABASE -Q "EXECUTE myStoreProcedure;" -s " " -x -o "d:\result.csv"';

I get all the data into result.csv but the beginning of the file contains some

(10 rows affected)
(4 rows affected)
(23 rows affected)
(5 rows affected)
(8 rows affected)
(2 rows affected)
//followed by the header columns - as expected
----------------------------------------------------------------------
//and effective results - as expected

I would prefer not having output and dashed rows. I'm open to any suggestions / workarounds.

Upvotes: 0

Views: 1171

Answers (1)

Robert Sheahan
Robert Sheahan

Reputation: 2100

I think you want to add 

SET NOCOUNT ON;

as the first line in your stored procedure

and add "-h -1" to your SQLCMD call

EDIT: @siyual beat me on the nocount part. I'll leave it in but he got it first :-)

EDIT 2: OK, I coded a short example showing what I mean and the output it produces

--EXEC xp_cmdshell 'SQLCMD -h -1 -S myserver -d Scratchpad_A -Q "EXECUTE spDummy;" -s " " -x -o "C:\TEMP result.csv"';
CREATE procedure spDummy as 
SET NOCOUNT ON;
DECLARE @T TABLE( Person varchar(7), FavCol varchar(7));
INSERT INTO @T(Person, FavCol) VALUES ('Alice','Red'), ('Bob','Yellow'), ('Cindy','Green'), ('Dan','Blue');
SELECT 'Person' as H1, 'FavCol' as H2;
SELECT * FROM @T

and it gives me the output

Person FavCol
Alice   Red    
Bob     Yellow 
Cindy   Green  
Dan     Blue

How does this compare to what you need? If that's not what you're looking for, maybe you could try a different way of explaining it?

Upvotes: 2

Related Questions