ISQL and OSQL Output Lines Wrapped Around at 256 Characters?

[Jay Chan]

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

[Simon Hayes]

ja******@hotmai l.com (Jay Chan) wrote in message news:<c7******* *************** ***@posting.goo gle.com>...

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

[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

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

[John Bell]

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******@hotma il.com> wrote in message
news:c7******** *************** ***@posting.goo gle.com...

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

[Erland Sommarskog]

Jay Chan (ja******@hotma il.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

[Jay Chan]

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