Save results to txt file without any other formatting in sqlcmd
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
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