Save results to txt file without any other formatting in sqlcmd

Question

When i run this command:
sqlcmd -S server -U sa -P xxxxxxx -i d:clients.sql -o D:\clients.txt
but in file i have x rows affected, "1> 2> 3> 4> 5> 6> 7> 8>" and many other spaces
I wan't only result without any formatting.
In sql file i have some selects.

Thank in advance.

Answer 1

You can add SET NOCOUNT ON to your script to remove the rows affected message and add -h-1 to the command line to remove the column names and the lines underneath them. This works for me and returns row data only:

sqlcmd -E -i query.sql -o tmp.txt -h-1

Where query.sql looks like this:

set nocount on;
select name from sys.objects;

But I don't know where the 1> 2> 3> 4> 5> 6> 7> 8> characters are coming from. You may want to post a minimal example of a SQL script that gives that output.

And as a general comment, I personally don't like to use sqlcmd.exe for running queries because it's difficult to control the output. Some other things to consider are:

• Use bcp.exe if you want a delimited file
• Use a script in PowerShell, Perl or whatever language you prefer
• Use an SSIS package
• Put your script contents into a stored procedure and execute it instead of using an input file