Hyphen padding in t-sql query result

A newbie question here:

In T-SQL my query to text file results in the padded second line containing a set of hyphens. How do I turn that off so that just the first line contains the headers and the results immediately follow in line 2.

Thanks!

You need to describe your problem a bit. It would help to have relevant code, data definition, what the incorrect results look like and the correct results look like. It would also help to have the associated sample data.|||

Certainly.

I'm composing a job in sql server using sqlcmd. Here's my command-line

EXEC master..xp_cmdshell 'sqlcmd -X -S server -q "SET NOCOUNT ON SELECT * FROM pubs.dbo.test" -s , -w 255 -u -o "\\server\output.csv"'

Output is just fine with the exception of that pesky line 2 of padded hyphens. Appears like this:

department,FundCode,Month

-,--,--,
DM ,SAZXZ , 8

DM ,ESJXZ , 7
DM ,DGLXS , 9
GN ,DGLXZ , 8

|||

That 'pesky line 2' is part of the output 'header'.

You can turn the headers off (that will include the line 1 with the column names) by adding this switch to the SQLCmd command line:

-h-1

(No space between the h and the -1.)

You probably don't need line 1 or 'pesky line 2' in your application.

As far as I am aware, those are your choices...

|||

Plus, if you don't like their output, it is pretty easy to build a console app to get the results. I had to do it once to deal with Text values that were very large and I couldn't get sqlcmd to do.

In 2005, you might even be able to write a CLR stored procedure (albeit an unsafe one) that will do the work directly that you have complete control over.

|||I was aware of the -h switch but I'd prefer to keep the headers to be explicit. If sqlcmd doesn't support it then I'll have to write something up. Thanks for your input everyone!|||This is kind of a cheesy solution, but it works.

Use this command:

EXEC master..xp_cmdshell 'sqlcmd -X -S server -q "SET NOCOUNT ON Select 'department','FundCode','Month' SELECT * FROM pubs.dbo.test" -s , -w 255 -u -o "\\server\output.csv" -h-1'

Notice the header switch (-h) has a value of -1 which means that it is off. The first Select statement returns one more row of results which contains the "headers"

Your output should look like this:

department,FundCode,Month

DM ,SAZXZ , 8

DM ,ESJXZ , 7
DM ,DGLXS , 9
GN ,DGLXZ , 8

|||

Brilliant! I had to use double single-quotes (e.g., ''Department'') to get it to parse correctly but this is what I needed.

Not cheesy at all. I too hide my inelegance with cleverness.