0

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.

Silviu
  • 103
  • 1
  • 7

1 Answers1

2

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?

Robert Sheahan
  • 2,100
  • 1
  • 10
  • 12
  • First thanks! The "NONCOUNT" has the expected result but adding "-h -1" it removes the header, also witch it's worse. – Silviu Jul 15 '14 at 15:02
  • Same results when I tried an adaptation of http://stackoverflow.com/a/24329991/1154285 solution. – Silviu Jul 15 '14 at 15:17
  • OK, I guess I didn't understand, so you want the column names but not the dashed row under them? Could you have the stored procedure output the the column names as strings and then output the values? Also, is it possible to use SSIS which has very granular control over output format? – Robert Sheahan Jul 15 '14 at 17:43
  • Thank you for your efforts. It is not what I expected but the workaround it's accepted. As far as I see you're using the xp_cmdshell parameters but witch suppress the headers ("-h -1" - tried these in advanced, too) and in this way you're hiding the header, defining after that you're own header with the first select. – Silviu Jul 16 '14 at 13:35
  • You're welcome, I'm glad to give back to a community that's given me so much. Yes, hiding the headers and rebuilding my column names is exactly what I'm doing. It's clunky but it's going inside a stored procedure so at least you can control it. The output can get ugly if you use numeric types or if you have varchar strings with spaces, if you do consider converting your output to strings and manually embedding delimiters (i.e. tabs) between them. Again, clunky but inside a SP so you can control it. – Robert Sheahan Jul 16 '14 at 15:01