How to suppress hyphens in SQLCMD
The only thing I can think of is removing the header using the -h -1
switch and adding the column names as the first row to the SQL Query:
SELECT 'FirstName' as FirstName, 'LastName' as LastName
UNION
SELECT top 5 FirstName, LastName FROM Person.Contact
Note that if there are other data types then (var)char
, you need to convert the field using : CAST(MyColumnName AS varchar(32)) as MyColumnName
That's it!
How can I supress hyphens (------------) from the results set of this sqlcmd command:
You can do it all in a simple way in 1 command, without any script or file manipulation!!
sqlcmd -d AdventureWorks -s ";" -Q "SET NOCOUNT ON; SELECT top 5 FirstName, LastName FROM Person.Contact" |findstr /v /c:"---"
Add set nocount on;
to remove the (X rows affected)
line.
Add |findstr /v /c:"---"
to remove the underlines.
This way you get a clean answer, with only:
FirstName ;LastName
Gustavo ;Achong
Catherine ;Abel
Kim ;Abercrombie
Humberto ;Acevedo
Pilar ;Ackerman
I didn't see all this info in once place and thought the next person looking for it might appreciate it...
use the -h -1
option to remove the dashes (--------) from the output and SET NOCOUNT ON
to remove the "rows affected". This is great if you're creating a report or CSV file for another system to process.
Example:
SQLCMD -S 127.0.0.1\SQL_SERVER_Instance -d db_name -U db_login -P password -i your_script.sql -o your_output.csv -h -1
In your SQL script:
SET NOCOUNT ON -- removes (rows affected from the output)
select 'your_column_1, your_column_2'
select * from your_table
You don't need to use a union between your select statements for this.