Microsoft SQL Server forum - iSQL - Blank line at the end of the iSql output file

Author

iSQL - Blank line at the end of the iSql output file

yanakal@gmail.com

2005-09-23, 8:23 pm

Hi,
I'm using isql to query data and output the same to a flat file.
The isql has the following command options ' -h-1 -w500 -n -b -s"" '.
In the SQL_CODE, the first two lines before the select statement are
use dbname
set nocount on
go
When I run this, an additional blank line is put into the output file.
Actually, there are two lines after the last result set in the output
file. This file is being fed into another system and the blank line is
causing validation issues.
How can I supress this blank line?

This script is run from windows and the isql is called from a bat
script.
Batch script ...
====================
====================
==================
.....
isql -Uuserid -Ppassword -Sserver -i"%SQL_CODE%" -h-1 -w500 -n -b -s""[color=darkred]
IF ERRORLEVEL 0 SET RC=0
IF ERRORLEVEL 1 exit 4
====================
====================
==================
SQL code ...
====================
====================
==================
use punclaim
set NOCOUNT ON
GO
select * from XYZ;
GO
====================
====================
==================

Your help is greatly appreciated.
Yash

Erland Sommarskog

2005-09-23, 8:23 pm

(yanakal@gmail.com) writes:
> I'm using isql to query data and output the same to a flat file.
> The isql has the following command options ' -h-1 -w500 -n -b -s"" '.
> In the SQL_CODE, the first two lines before the select statement are
> use dbname
> set nocount on
> go
> When I run this, an additional blank line is put into the output file.
> Actually, there are two lines after the last result set in the output
> file. This file is being fed into another system and the blank line is
> causing validation issues.
> How can I supress this blank line?

You would have to pipe the output to something that strips the last line.

But is there any special reason you use ISQL and not BCP?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

John Bell

2005-09-24, 7:23 am

Hi

You may want to try passing this through findstr e.g.

osql -E -d punclaim -ifred.sql -h-1 -w500 -n -b | findstr /V /r /c:"^$" >
fred.txt

Specifying the -d parameter to osql/isql will remove the need for the USE
statement in your SQL script, alternatively the SQL statements:

SET NOCOUNT ON
select * from punclaim..XYZ

There will still be a final carrage return, but not the blank line.

John

<yanakal@gmail.com> wrote in message
news:1127507732.803909.296490@z14g2000cwz.googlegroups.com...
> Hi,
> I'm using isql to query data and output the same to a flat file.
> The isql has the following command options ' -h-1 -w500 -n -b -s"" '.
> In the SQL_CODE, the first two lines before the select statement are
> use dbname
> set nocount on
> go
> When I run this, an additional blank line is put into the output file.
> Actually, there are two lines after the last result set in the output
> file. This file is being fed into another system and the blank line is
> causing validation issues.
> How can I supress this blank line?
>
> This script is run from windows and the isql is called from a bat
> script.
> Batch script ...
> ====================
====================
==================
> ....
> isql -Uuserid -Ppassword -Sserver -i"%SQL_CODE%" -h-1 -w500 -n -b -s""
> IF ERRORLEVEL 0 SET RC=0
> IF ERRORLEVEL 1 exit 4
> ====================
====================
==================
> SQL code ...
> ====================
====================
==================
> use punclaim
> set NOCOUNT ON
> GO
> select * from XYZ;
> GO
> ====================
====================
==================
>
> Your help is greatly appreciated.
> Yash
>

yanakal@gmail.com

2005-09-26, 8:24 pm

John, Thank you very much. The solution you suggested worked.

Yash

Sponsored Links