September 11, 2008 at 10:22 am
Hi,
I am using an OSQL like this:
OSQL -E -d databasename -w340 -i quart_02.sql -n -h-1 > q2.txt
The output from the sql file should be exact 340 character long. But OSQL throws extra lines after every line. I can only get rid of it if I increase the width to 5000 or more. But I want the line width to be exact 340 because the data is exact 340 character in width.
Any ideas how can I restrict the width to 340 and avoid all the extra empty lines/spaces ?
Thanks for the help.
Sumit
September 11, 2008 at 11:27 am
from bol:
-w column_width
Allows the user to set the screen width for output. The default is 80 characters. When an output line has reached its maximum screen width, it is broken into multiple lines.
osql /? will give you more info (and BOL)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 11, 2008 at 11:34 am
In my case it is not breaking the data in multiple line. It does shows the 340 characters correctly and in one single line. But on top of that it would append about 10 blank lines after every data line.
Hope this makes my post clearer.
Thanks.
ALZDBA (9/11/2008)
from bol:-w column_width
Allows the user to set the screen width for output. The default is 80 characters. When an output line has reached its maximum screen width, it is broken into multiple lines.
osql /? will give you more info (and BOL)
September 11, 2008 at 11:55 am
can you post the query you're trying to execute (including the table ddl) ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 11, 2008 at 12:05 pm
Unfortunately, I can't post the query since it is a tax related query with certain rules/logics involved.
The query is concatenation of 45 fields whose total width when selected as single column is 340 characters.
Something like:
select (COMPNY+SM+LNAME+FNAME+MNAME+UIWAGES+WK+EXCESSE+TAXABLE+WS+TCDE+ADDRESS+CITY+ST+ZIP+OCC_TITLE+CI+SI+FIL4+AC+HRS+NEWEMPHD+V+ANNUAL_TAX+EETAXWH+OFFWAGES+ANNUAL_WGS+CAMESIT+NYOTHER+CAPITWAGES+COMPRT+CT+FLR4+CG+SUBJWAGES+YTDGROSSWG+CC+F+COMMCODE+WORK_UNIT_NUM+EMP_MONTH1+EMP_MONTH2+EMP_MONTH3+DT_FIRST_EMPL)
from (
1st query
UNION ALL
2nd query
UNION ALL
3rd query
)x
September 12, 2008 at 12:05 am
I have the impressions it comes to your output capturing way.
Use this:
SQLCMD -E -S .\SQL2000PE -d master -i ".\Osq_Test.sql" -o ".\Osq_Test.txt" -w340 -n -h-1
my Osql_test.sql contains:
/* just produce an output of 340 chars */
declare @t table (c340 varchar(340) null)
set nocount on
insert into @t
select replicate('1',340)
Select C340 from @t
-- Select datalength(c340) from @t
It is producing the correct output length in a single line.
Also keep in mind when inspection using wordpad, to shut off word wrap.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 12, 2008 at 9:06 am
Ok I replicated exactly what you did but instead of SQLCMD I used OSQL. The result:
1st line :"Single space1111111...till 340th character" --- 339 1s in this line
2nd line :"Single Tab1"
And if I use my query I get 25 empty lines with a single tab
1st line :"Single spaceMYDATA...till 340th character"
2nd line :"single Tab"
3rd line :"single Tab"
.
.
.
26th line :"single Tab"
27th line :"Single spaceMYDATA...till 340th character"
I checked in my data or the last character and it is not being followed by any tabs but this is happening when i get the output via OSQL.
I am going to try out for a while else write a vb script to remove tabs and empty lines afterwards but thats not an efficient way.
Anyways Thanks ALZDBA for all the help !!
Sumit
September 12, 2008 at 10:32 am
I'm sorry, I've overlooked the fact I was using sqlcmd. :blush:
Copy / paste .. you know :ermm:
did you try using the column separator parameter ?
from bol:
http://msdn.microsoft.com/en-us/library/aa214012(SQL.80).aspx
-s col_separator
Specifies the column-separator character, which is a blank space by default.
To use characters that have special meaning to the operating system
(for example, | ; & ), enclose the character in double quotation marks (").
[/quote$
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 12, 2008 at 12:27 pm
Alrighttttttttt heres how I got this to work....
In my original query I did a cast(.. as VARCHAR(339). For some reason it might have been throwing some trailing spaces. Casting into VARCHAR will remove any trailing spaces which we can't remove via OSQL in 2000.
OSQL -E -S SERVERNAME -d DBNAME -i ".\quart_02.sql" -o ".\output.txt" -s "," -w340 -n -h-1
Also, I tried this on 2005 as well but using SQLCMD with the -W
SQLCMD -E -S SERVERNAME -d DBNAME -i ".\quart_02.sql" -o ".\output.txt" -w340 -h-1 -W
Both of them work.
Thanks Again,
Sumit 🙂
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply