How to suppress hyphens in SQLCMD

How can I suppress hyphens (------------) from the results set of this sqlcmd command:

C:\temp>sqlcmd -d AdventureWorks -s ";" 
 -Q "SET NOCOUNT ON SELECT top 5 FirstName, LastName FROM Person.Contact;"
FirstName                                         ;LastName
--------------------------------------------------;----------------------------
Gustavo                                           ;Achong
Catherine                                         ;Abel
Kim                      开发者_运维知识库                         ;Abercrombie
Humberto                                          ;Acevedo
Pilar                                             ;Ackerman

C:\temp>

---

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.

---

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

---

Using only sqlcmd and the Windows command line, with no stored procedures:

REM Get the column headers ("set nocount on" is necessary to suppress the rows affected message)
sqlcmd -S MyServer -Q "set nocount on;select top 0 * from MyDatabase.MySchema.MyTable" -o "MyTableColumns.csv" -s "," -W

REM Remove hyphen line
findstr /R /C:"^[^-]*$" MyTableColumns.csv > MyTableHeader.csv

REM Get data without headers
sqlcmd -S MyServer -Q "set nocount on;select * from MyDatabase.MySchema.MyTable" -o "MyTableData.csv" -h -1 -s "," -W
REM You can also use bcp for this step
REM bcp "select * from MyDatabase.MySchema.MyTable" queryout  "MyTableData.csv"  -c -t"," -r"\n" -S MyServer -T

REM Append header and data
type MyTableHeader.csv MyTableData.csv > MyTableDataWithHeader.csv

To handle data with delimiters inside (for example "Atlanta, GA") you'll need to specify the fields separately (rather than use "select *") and use the QUOTENAME function in SQL Server.

---

Or maybe post-process the output through sed as:

sqlcmd ... | sed -e '2d'

to delete the second line?

You can get a Win32 sed from http://gnuwin32.sourceforge.net/packages/sed.htm

---

To get rid of the hyphens, I add some code to my stored procedures that will output the column header only. Extracting data using SQLCMD is done in 2 parts.

First I call my stored procedure with a special set of parameters. In my case, when I call the SP with all NULLs, it mean I wish to have the column header.

Then I call SQLCMD a second time and append the output to the file that I just created before and voila!

Create a blank CSV file to hold the column header

sqlcmd -S server -d database -U username -P password -o "somefile.csv" -h-1 
  -Q "exec myStoredProcedure NULL, NULL" -W -w 2000 -s"," > sometextfile.csv

Now append the output result from the stored procedure

sqlcmd -S server -d database -U username -P password -o "somefile.csv" -h-1 
  -Q "exec myStoredProcedure 2011, 10" -W -w 2000 -s"," >> sometextfile.csv

Notice the first command uses > and the second one >>. When using one -> "Create or overwrite" and two -> "Append or create".

• -h-1 removes the header generated by SQLCMD
• -W removes the trailing spaces
• -w set the max row width
• -s defines columns separator

---

You can do the following:

exec 'sqlcmd -S YourServer -E -Q "set nocount on; select * from YourTable" -s "," -W | findstr /R /C:"^[^-]*$" > yourfile.csv'

This will create a csv file with the headers, without the dashes, and trimmed white space, in one fell swoop. Based on MichaelM's answer above.

---

I had to do a ton of research because none of the answers here exactly fit what I was looking for. So I am going to sum in hopes that it will help others.

For me, the easiest way was to use sed. While this was already suggested here, there was incorrect syntax to run sqlcmd and sed concurrently. If you're using Windows, as was the case for me, head to http://gnuwin32.sourceforge.net/packages/sed.htm to download sed

My final command looked something like:

SQLCMD -m 1 -S "userpc\SQLEXPRESS" -E -i "C:\someDirectory\sqlfile.sql" -W -s "," -o "C:\someDirectory\output.csv" && sed -i 2d "C:\someDirectory\output.csv"

Where

--m 1 eliminates the "Changed database context to..." line
-&& runs the second command if the first command runs successfully
-sed -i 2d deletes the second line containing the hyphens and overwrites the output of sed to the original output

Be aware that the sed output file name and the output from sqlcmd must match otherwise the output from sqlcmd will not be correctly overwritten. There should not be quotes around 2d in the sed statement as answered above and is present in their documentation or you will get an error.

Hope this helps!

---

if you can use PowerShell instead

• Invoke-DbaQuery from https://github.com/sqlcollaborative/dbatools
• Invoke-SqlCmd -Query from official MSSQL PowerShell module

working example:

Invoke-DbaQuery "SELECT * FROM [MyApp].[dbo].[ClientConfig] WHERE ClientID='Default'" -SqlInstance mysql.mydomain.com -ReadOnly `
  | Export-Csv -Path clientconfig-default.csv -Encoding utf8 -UseQuotes AsNeeded

Note -ReadOnly is only helpful on clusters, and probably noise (early optimization). But I'm a developer querying/diffing production databases, so overly cautious so minimize load which might be noticed by DBAs.

I sometimes use https://docs.dbatools.io/#Copy-DbaDbTableData to copy whole tables mssql-to-mssql. It is fast, I think uses BCP.EXE or BulkInsert.

---

If you could output to a file first (using the -o option), another post-process option using powershell would work by reading the file's contents--while skipping the second line--and writing it back to itself.

(Get-Content "file.txt" | Where {$_.ReadCount -ne 2}) | Set-Content "file.txt"

---

MichaelM has a decent solution, but as slingshot pointed out ... findstr could be over aggressive and remove data lines that contain hyphens. Another approach would be calling sqlcommand to get the data set and then using set /p to grab the first row from the output file and assign it to a variable. Then delete the orignal output file. Next, echo out the headers to a new file. Lastly, pipe another headerless sqlcmd to that file.

sqlcmd -S server -d database -E -Q "exec myStoredProcedure" -W -o my_headers.csv -m-1
set /p headers=< my_headers.csv
del my_headers.csv
echo %headers% > my_data.csv
sqlcmd -S server -d database -E -Q "exec myStoredProcedure" -W -m-1 -h-1 >> my_data.csv

Also Top 0 is nice choice to create a "headers only file" but only if you are using a select statement. If you're calling a stored procedure, look into using FMTONLY ON to grab the headers only from the SP.

sqlcmd -S server -d database -E -Q "Set NOCOUNT ON; Set FMTONLY ON; exec myStoredProcedure" -W -o my_headers.csv -m-1

A caveat being that the FMTONLY ON can't be used against SP's using #temp tables. This is because FMTONLY ON doesn't execute the SP. It only grabs metadata. But if the column names are coming from tables that don't exist pre-execution then you can't get those column names.

• Troy

---

Public Sub HyphenDelete(strFilename1 As String, Hyphens As Integer)


Const FOR_READING = 1
Const FOR_WRITING = 2

strFileName = strFilename1

strCheckForString = Left("-------", Hyphens)
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objTS = objFS.OpenTextFile(strFileName, FOR_READING)
strContents = objTS.ReadAll
objTS.Close

arrLines = Split(strContents, vbNewLine)
Set objTS = objFS.OpenTextFile(strFileName, FOR_WRITING)

For Each strLine In arrLines
   If Not (Left(UCase(LTrim(strLine)), Len(strCheckForString)) = strCheckForString) Then
      objTS.WriteLine strLine
   End If
Next

End Sub

---

If outputting to a file try the following upon successful execution:

    findstr /v /c:"---" sqloutput.dat > finaloutput.dat

I use "---" as all my columns are over 3 characters and I never have that string in my data but you could also use "-;-" to reduce the risk further or any delimiter based on your data in place of the ";".

---

remove dashes from results:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'testMail',
@recipients = 'krishan@gmail.com',
@query = '  
SET NOCOUNT ON
SELECT W.* FROM (
(SELECT CAST("id" AS VARCHAR(5) ) as id , CAST("name" AS VARCHAR(50) )as name ) 
UNION ALL 

SELECT CAST( id AS VARCHAR(5) ) AS id, CAST (name AS VARCHAR(50) ) AS name from mydb.dbo.bank
 )W
' ,
@subject = 'Test email',
@attach_query_result_as_file = 1,
@query_attachment_filename='filename.xls',
@query_result_separator='   ',   
@query_result_header = 0

    -----remember @query..separator should be--Tab space----

---

I typically process the resulting .csv file as follows:

sed -i '2d' "$file"  # remove 2nd line of dashes
sed -i '$d' "$file"  # remove last line (row count string)
sed -i '$d' "$file"  # remove one more empty line at the end of the file

Where:

• -i edits the file in-place...
• 2d deletes line 2, in this case, the lines of dashes...
• $d deletes single line at the end of the file...
• "$file" is the bash variable holding the path string to the .csv file.

---

This is what I ended up with and works well for me:

sqlcmd -S 12.34.56.789 -i "C:\Qry.sql" -s"," -W -w1000 | findstr /V /R /C:"^[-,]*$" > Reprt.csv

I believe at this point, the findstr & regex are all that needs to be explained:

findstr /V /R /C:"^[-,]*$"

The regex states "At the beginning ^, has either - or , [-,] any number of times *, until you reach the end of the line $.

/V means, show me anything that doesn't match.

The > following it redirects the display to a file.

I did also add this to my SQL file:

SET NOCOUNT ON;

---

The following line:

findstr /R /C:"^[^-]*$" MyTableColumns.csv > MyTableHeader.csv

is very dangerous as it removes all lines containing a "-". You better have a look at findstr /? and use something like:

findstr /B /V /C:"-----" MyTableColumns.csv > MyTableHeader.csv

---

if you want to spool the data out then you can set the following switch:

SET UNDERLINE OFF;

---

I don't think there's any option available to achieve this - you'll have to live with those headers, I'm afraid.