Output from OSQL

[SQLBill]

I am finally working with using OSQL and came across a problem I can't solve. The query runs fine and returns what I expect it to do. However, I am having it output the results to a .txt file and that's where the problem is. I'm having problems with formatting the output.

My results look like this (the data is different, but the format is the same):

Date&Time
LastName
FirstName
-------------
--------------
--------------
2006-03-31 04:00
Smith
Tom

2006-03-31 04:31
Doe,
Jane

What I want is:

Date&Time LastName FirstName
--------- -------- ---------
2006-03-31 04:00 Smith Tom
2006-03-31 04:31 Doe Jane

I've tried using the -w switch (-w 30, -w 40, -w 80) but none of those changed anything.

How do I get the results in the format I need?

-SQLBill

Posting advice: FAQ481-4875

 

[vongrunt]

How that prompt command looks like? (with masked sensitive information of course).

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLBill]

I'm running a stored procedure, so that will hide the information. The real query looks like:
[code}
osql -E -S SQLdb -Q "EXEC dbname..usp_testquery '2006-03-31 04:00', '2006-03-31 05:00', '1.2.3.4'"
[/code]
I have also tried:
[code}
osql -E -S SQLdb -w 30 -Q "EXEC dbname..usp_testquery '2006-03-31 04:00', '2006-03-31 05:00', '1.2.3.4'"
[/code]
The script gets anything from a log of events that occurs between those times (>= and <=) and with that IP address (of course, that's not a real IP address). In the second query, I have changed the -w value from 30 to 40 to 60 to 80 and the output remains the same.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

BTW- if it matters, my query returns six (6) columns.

-SQLBill

Posting advice: FAQ481-4875

 

[philhege]

Bill, how do you get this command to produce a .txt file? I made a test version and all it does is output to the command window. Are you directing output to a port?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.

 

[SQLBill]

Use the -o switch. Which (oops) I left off my code posting.

osql -E -S SQLdb -Q "EXEC dbname..usp_testquery '2006-03-31 04:00', '2006-03-31 05:00', '1.2.3.4'" -o testresult.txt

I have also tried:

osql -E -S SQLdb -w 30 -Q "EXEC dbname..usp_testquery '2006-03-31 04:00', '2006-03-31 05:00', '1.2.3.4'" -o testresult.txt

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Solved the issue. To everyone who was testing this to try and solve it - thank you.

The issue. The vendor supplied function that I use to convert certain values (IP addresses) apparently uses the default length of 50 characters. Returning three IP addresses is a length of 152 characters (one space in between each IP address). Then with the rest of the data returned, the actual length of the row is over 265 characters. The data is only 195 characters. So instead of getting one or two lines per row, I was getting a separate line for each value.

Using SUBSTRING to get just the length that I want for each column appears to be working. I'm going to try it with RTRIM for the values that are strings and see if that works better than SUBSTRING.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

RTRIM didn't work. So I have to stay with SUBSTRING. For some reason the vendor felt that IP addresses should be 60 characters long.

I also had to change the -w to a larger value.

-SQLBill

Posting advice: FAQ481-4875