473,320 Members | 1,820 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

ISQL and OSQL Output Lines Wrapped Around at 256 Characters?

I am trying to use a command line program to run a stored procedure
that generates output in a comma-delimitted format. Somehow, ISQL or
OSQL always wrap the lines at 256 characters. I believe this has
something to do with the column width switch (-w). But enlarging the
column width to 800 characters max still doesn't help. The following
is a stored procedure that is essentially doing what my stored
procedure is doing:

create procedure MyTest as
set ansi_padding on
set nocount on

declare @sTest varchar(300)

-- Output three lines. Each line has 259 characters.

select @sTest = "1234 6789 ... 1234 6789"
print @sTest

select @sTest = "1 3 5 7 9 ... 1 3 5 7 9"
print @sTest

select @sTest = "1 3 5 7 9 ... 1 3 5 7 9"
print @sTest

set nocount off
return( 0 )

I invoke this stored procedure using this command:

isql -SMyDbSrv -E -dMyDb -w800 -x800 -h-1 -n -Q"exec MyTest"
-oMyTest.txt
-- or --
osql -SMyDbSrv -E -dMyDb -w800 -h-1 -n -Q"exec MyTest" -oMyTest.txt

But they have the same problem. The output lines all wrap around at
256 characters.

Strangely, if I store the result in a temporary table, and then use
SELECT to output the result from the temporary table, I will not have
that problem. Seem like the "-w" switch only works for output from
tables, but not for output coming from PRINT. Unfortunately, using
this approach has another set of problems (one blank space in front of
each line, "number-of-rows affected" shows up at the bottom).
Therefore, I would like to stick with using PRINT statements to output
the result.

Please suggest a way to fix this line-wrapping-around problem.

Thanks.

Jay Chan
Jul 20 '05 #1
5 23288
ja******@hotmail.com (Jay Chan) wrote in message news:<c7*************************@posting.google.c om>...
I am trying to use a command line program to run a stored procedure
that generates output in a comma-delimitted format. Somehow, ISQL or
OSQL always wrap the lines at 256 characters. I believe this has
something to do with the column width switch (-w). But enlarging the
column width to 800 characters max still doesn't help. The following
is a stored procedure that is essentially doing what my stored
procedure is doing:

create procedure MyTest as
set ansi_padding on
set nocount on

declare @sTest varchar(300)

-- Output three lines. Each line has 259 characters.

select @sTest = "1234 6789 ... 1234 6789"
print @sTest

select @sTest = "1 3 5 7 9 ... 1 3 5 7 9"
print @sTest

select @sTest = "1 3 5 7 9 ... 1 3 5 7 9"
print @sTest

set nocount off
return( 0 )

I invoke this stored procedure using this command:

isql -SMyDbSrv -E -dMyDb -w800 -x800 -h-1 -n -Q"exec MyTest"
-oMyTest.txt
-- or --
osql -SMyDbSrv -E -dMyDb -w800 -h-1 -n -Q"exec MyTest" -oMyTest.txt

But they have the same problem. The output lines all wrap around at
256 characters.

Strangely, if I store the result in a temporary table, and then use
SELECT to output the result from the temporary table, I will not have
that problem. Seem like the "-w" switch only works for output from
tables, but not for output coming from PRINT. Unfortunately, using
this approach has another set of problems (one blank space in front of
each line, "number-of-rows affected" shows up at the bottom).
Therefore, I would like to stick with using PRINT statements to output
the result.

Please suggest a way to fix this line-wrapping-around problem.

Thanks.

Jay Chan


Unfortunately PRINT always wraps at 256 characters, and SELECT always
has a blank line between results in the output (but you shouldn't get
number of rows, since you've used -n). Or at least that appears to be
the behaviour, based on some quick experimentation. However, you could
use SELECT and strip out the blank lines before the results are
written to the file - here is a rather kludgy possible solution:

del MyTest.txt && for /F "usebackq eol= tokens=*" %i in (`osql -dMyDb
-E -h-1 -n -Q"exec dbo.MyTest" -w800 -s""`) do @echo %i >> MyTest.txt

Simon
Jul 20 '05 #2
> Unfortunately PRINT always wraps at 256 characters, and SELECT always
has a blank line between results in the output (but you shouldn't get
number of rows, since you've used -n). Or at least that appears to be
the behaviour, based on some quick experimentation. However, you could
use SELECT and strip out the blank lines before the results are
written to the file - here is a rather kludgy possible solution:

del MyTest.txt && for /F "usebackq eol= tokens=*" %i in (`osql -dMyDb
-E -h-1 -n -Q"exec dbo.MyTest" -w800 -s""`) do @echo %i >> MyTest.txt


Thanks for the info about the fact that PRINT always wrap around at
256 characters.

Seem like I will have to use SELECT instead of PRINT. And then write
another program to remove the leading blank space and the "(# of rows
affected)" message from the output.

Somehow, '-n' doesn't work. It doesn't remove the "(# of rows
affected)" message from the end of the result. Actually, I don't
really know what '-n' is supposed to do. ISQL/OSQL output the same
thing regardless I use '-n' or not.

Oh well...

Jay Chan
Jul 20 '05 #3
Hi

You may be better using BCP (or DTS) to generate these files.

To remove the count use SET NOCOUNT ON in your script.

John

"Jay Chan" <ja******@hotmail.com> wrote in message
news:c7**************************@posting.google.c om...
Unfortunately PRINT always wraps at 256 characters, and SELECT always
has a blank line between results in the output (but you shouldn't get
number of rows, since you've used -n). Or at least that appears to be
the behaviour, based on some quick experimentation. However, you could
use SELECT and strip out the blank lines before the results are
written to the file - here is a rather kludgy possible solution:

del MyTest.txt && for /F "usebackq eol= tokens=*" %i in (`osql -dMyDb
-E -h-1 -n -Q"exec dbo.MyTest" -w800 -s""`) do @echo %i >> MyTest.txt


Thanks for the info about the fact that PRINT always wrap around at
256 characters.

Seem like I will have to use SELECT instead of PRINT. And then write
another program to remove the leading blank space and the "(# of rows
affected)" message from the output.

Somehow, '-n' doesn't work. It doesn't remove the "(# of rows
affected)" message from the end of the result. Actually, I don't
really know what '-n' is supposed to do. ISQL/OSQL output the same
thing regardless I use '-n' or not.

Oh well...

Jay Chan

Jul 20 '05 #4
Jay Chan (ja******@hotmail.com) writes:
Somehow, '-n' doesn't work. It doesn't remove the "(# of rows
affected)" message from the end of the result. Actually, I don't
really know what '-n' is supposed to do. ISQL/OSQL output the same
thing regardless I use '-n' or not.


-n removes 1> 2> etc from the output.

To get rid ot "rows affected", use SET NOUCOUNT ON.

John's suggestion to use BCP sounds like a good approach.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5
> -n removes 1> 2> etc from the output.

I believe this is what it is supposed to do. Strangely, that -n switch
doesn't do anything for me. The output always doesn't have 1> 2>...
regardless I use -n or not. Anyway, this is not where my problem is.
Therefore, I will not get too deep into this.
John's suggestion to use BCP sounds like a good approach.
All my other programs use ISQL. Therefore, I want to stick with the
"tried and sort-of-true". Thanks anyway.
To get rid ot "rows affected", use SET NOUCOUNT ON.


Good. This gets rid of the "rows affected" message. Turned out this
particular problem was caused by two factors:

1. Somewhere in my stored procedure, it restored SET NOCOUNT ON to
OFF. That was the reason why I could not remove that "rows affected"
message.

2. ISQL always outputs "rows affected" message regardless I use SET
NOCOUNT ON or not. OSQL doesn't have this problem. This means I should
use OSQL from now on.

With "rows affected" out of the way, the remaining problem is the
extra blank space in the beginning of each line in the output when I
replace PRINT statements with one SELECT statement.

I will contact the outside developer who is developing a program that
will import the output file from my stored procedure. If he is OK with
the extra blank space in front of each line in the output file, I will
not need to do anything other than changing the PRINT statements in my
stored procedure with one SELECT statements. And I will be done.

Thanks for the information.

Jay Chan
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Taki Jeden | last post by:
Hi I'm trying to figure out how to produce, in wxPython, a table/list/grid/whatever which would wrap long lines, and of course adjust row height. But I can't find any ready-made widget, nor any...
2
by: Bob | last post by:
Everybody, I've been doing a lot of on-line research and cannot find any reference to the exact problem I'm having. Let me preface this question with the fact that I'm coming from an Oracle...
1
by: Alan Zhong | last post by:
hi, i had been removed the row counts and the column spaces... but i failed to remove the extra tabs between rows of data return from the osql output. how to detect the row delimiter? i noticed...
1
by: Robizzle | last post by:
I'm writting a script that produces html source code from form input. My problem is that I want to format my html source with new lines and tabs. So, I have a string that holds all the contents...
1
by: Philip Bondi | last post by:
Hello to all SQL Server junkies who work with non-English characters: For people running scripts from the command line using ANSI files with special characters, it is very important to use isql...
9
by: julian_m | last post by:
given that php echo '<div>'; echo 'whatever'; echo '</div>'; echo '<div>'; echo 'whatever'; echo '</div>'; echo '<div>'; echo 'whatever'; echo '</div>';
1
by: vps | last post by:
Do I need to break a mail message into lines of 70 characters each? Apache 2.2 with PHP 5.2.5 sends messages with lines of any length at my localhost with all characters intact. Thank you very...
8
by: Jonathan Sachs | last post by:
I'm trying to compose a list of items, each of which consists of text that wraps around a picture at the left margin. The examples I have seen tell me to do it like this: <p><img src="xxxx.jpg"...
2
by: ncsthbell | last post by:
I am trying to construct a 'Copy' statement in Access 2000 vb that I will actually write out to create a .bat file. My problem is that I need to put quotes around the vcFileFrom and vcFileTo...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.