asked on
OSQL output file append has a blank line
I am using OSQL to create an output file from a SQL Statement. I am using the syntax that appends to an existing file but everytime it appends, there is a blank line between the previouse output file. Is their anyway to supress the blank line? Here is my code:
osql -S localhost -E -d master -w700 -Q "set nocount on; select '\"' + CAST(name AS Varchar(15)) + '\"', '\"' + CAST(type_desc AS varchar(10)) + '\"' from sys.tables" -h-1 -s "," >> edtest.txt
osql -S localhost -E -d master -w700 -Q "set nocount on; select '\"' + CAST(name AS Varchar(15)) + '\"', '\"' + CAST(type_desc AS varchar(10)) + '\"' from sys.tables" -h-1 -s "," >> edtest.txt
DatabasesMicrosoft SQL Server 2005Microsoft SQL Server
Last Comment
Pavel Celba
I wonder if the header (-h) argument is causing the added line between appends; have you tried supressing it for testing purposes?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Pcelba,
Genius!! That works beautifully!! Thank you!!
Genius!! That works beautifully!! Thank you!!
You are welcome!
ASKER
Follow up post. If you are not locked in to using the OSQL command (deprecated), SQLCMD Utility does not write the blank line at the end of the output file. Also, SQLCMD Utility provides the -W switch which can be used to eliminate the whitespace padding that OSQL adds for column width to each column.
One more thing to note is that in OSQL \" is used to include quotes within your SQL Query. Those need to be changed to "" in SQLCMD.
If you are using OSQL and have to for some reason, then pcelba's brilliant solution above works like magic. I was not locked into using OSQL so I am going with SQLCMD version.
This code is what I used:
sqlcmd -S localhost -E -d master -w700 -Q "set nocount on; select '""' + CAST(name AS Varchar(15)) + '""', '""' + CAST(type_desc AS varchar(10)) + '""' from sys.tables" -h-1 -s "," -W >> edtest.txt
One more thing to note is that in OSQL \" is used to include quotes within your SQL Query. Those need to be changed to "" in SQLCMD.
If you are using OSQL and have to for some reason, then pcelba's brilliant solution above works like magic. I was not locked into using OSQL so I am going with SQLCMD version.
This code is what I used:
sqlcmd -S localhost -E -d master -w700 -Q "set nocount on; select '""' + CAST(name AS Varchar(15)) + '""', '""' + CAST(type_desc AS varchar(10)) + '""' from sys.tables" -h-1 -s "," -W >> edtest.txt
sqlcmd is better but it would be too easy to use it from the beginning :-)