format output of osql 1

[JonnoGT]

Hi,
New to SQL so please excuse if this is a dumb question.

I am running this query:
osql /U user /P password /S server -d TNGDB -Q"select containerhost,dsm_obj_name from Agent where dsm_obj_name LIKE 'Log%'" -o c:\temp\query.out

The query.out file looks like this:
containerhost dsm_obj_name
SERVERNAME Log#20Agent#20V2

IE the containerhost column and the dsm_obj_name column appear on different lines.
How to I format the output so that it doesn't put different columns on different lines?

 

[JonnoGT]

Sorry, the post didn't come out right.
The output file looks like:
containerhost
dsm_obj_name

When I want it to look like:
containerhost dsm_obj_name

 

[mkrausnick]

It sounds like the fields are wrapping because the combined field length is wider than your page size. What are you using to view the output, or is the output being printed?



Mike Krausnick

 

[JonnoGT]

I'm using Notepad.
Is there something I can put in the select command to tell it to ignore or cut off any blank spaces?

 

[mkrausnick]

Hmmm. Notepad doesn't wrap by itself, so osql might be inserting CR/LFs to format the output for you (big help huh?) Try truncating the fields using LEFT():

osql /U user /P password /S server -d TNGDB -Q"select left(containerhost,20) as containerhost,left(dsm_obj_name,20) as dsm_obj_name from Agent where dsm_obj_name LIKE 'Log%'" -o c:\temp\query.out

If the first 20 characters of each field are not enough (i.e. some names are truncatred), increase the number until you get complete data values, but still on one line.

If LEFT() doesn't solve the problem then maybe the problem is something other that wrapping.

Hope this helps.


Mike Krausnick

 

[JonnoGT]

Jobs a goodun,
Thanks very much for your help.

 

[tlbroadbent]

You don't need to trim the output to fit on one line. The default line width is 80 for OSQL output. You can change the width with the -w parameter. -w 200 will allow up to 200 characters per line. -w 1000 will cause OSQL to output up to 1000 characters per line.

See SQL BOL for more info on OSQL command line arguments.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm