Avatar of edrstone
edrstone
 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
DatabasesMicrosoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
Pavel Celba

8/22/2022 - Mon
myrotarycar

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
Pavel Celba

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
edrstone

ASKER
Pcelba,

Genius!! That works beautifully!! Thank you!!
Pavel Celba

You are welcome!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
edrstone

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
Pavel Celba

sqlcmd is better but it would be too easy to use it from the beginning :-)