I see a lot of post on how to omit the column headers (the ----- under the headers too) and the last result of a query "111 Rows affected" statement. I have tried inserting the '-H-1' and 'SET NOCOUNT ON' statements to my bat file command but keep getting same results. I went into the sql query options and unchecked the setting for column headers in the output as well. Am I missing something simple?
SET NOCOUNT ON
sqlcmd -H-1 -S xx.x.xx.x -d MAS_ACT -E -Q " SELECT * from MAS_ACT.dbo.Test_SM_Header " -s "," -o "C:\reporting\SanMar\Header3.txt"
You need to put SET NOCOUNT ON inside the query statement:
sqlcmd -H-1 -S xx.x.xx.x -d MAS_ACT -E -Q "SET NOCOUNT ON; SELECT * from MAS_ACT.dbo.Test_SM_Header " -s "," -o "C:\reporting\SanMar\Header3.txt"
If you just need a CSV file output with no headers and no trailing output, the bcp utility will do:
bcp "SELECT * from MAS_ACT.dbo.Test_SM_Header " queryout "C:\reporting\SanMar\Header3.txt" -S xx.x.xx.x -T -t ","
The rows counted statement is omitted now, but my headers are still in the txt file output? I also tried rewriting the query outside the GUI to trim the fields without trailing spaces, since I am used of just using the "views" as my query I am referencing. The new query written is saved as a file, do you just reference the file location or could I just edit the above code to
SELECT * from MAS_ACT.dbo.SQLQuery4.sql
Maybe a lower h instead of capitol H will help you out.
sqlcmd utility - SQL Server | Microsoft Learn
-H workstation_name
-h rows_per_header