output of isql

[wimvanherp]

I want to output the result of a stored procedure to a text file but, only the results not the messages like :
(1 row affected)

if i use isql ....-o"c:\temp\result.txt" i also get this messages i do not want in my result file
is this possible ? Wim Vanherp
Wim.Vanherp@belgacom.net

 

[saikrish]

Hi Wim Vanherp,
I guess the isql command by itself does not provide options to supress the results of the command executed on the database like the '1 row affected' etc. that you are making a mention of. But you could explicitly direct only the output that you desire to a file by having a construct similar to the one given below.

isql -Uabc -Pxyz -Sdef -Dlkj <<EOF | egrep -v 'rows affected | return status' > &quot;c:\temp\result.txt&quot;
exec abcd_sp
go
EOF

In the above example 'abcd_sp' is your stored procedure. From the isql output all the rows excepting the ones having 'rows affected' and 'return status' are being directed to the required file. You could modify the egrep expression suitably to meet more stringent requirements.

All the best,
Vijay

 

[wimvanherp]

HI Vijay,

could you give me the syntax how to get it to work in a windows environment ? I always get the message : << not expected . I also tried with < but it does not help .
I suppose this is an example that works in Unix ?
Wim Vanherp
Wim.Vanherp@belgacom.net

 

[JeanNiBee]

Try this, it's a little more of a DB way of doing it an not so OS dependant.

Create your .sql file you want to execute. (Ex: abc.sql)

In this file you start your commands with
SET FLUSHMESSAGE OFF

Then run your .sql through isql. You shouldn't get the row count information anymore.

Hope this helps.

Don't forget to SET FLUSHMESSAGE ON when you are done just to be sure they will be displayed the next time you exec a script.

 

[wimvanherp]

Hi JeanIBee

unfortunately, the SET FLUSHMESSAGE OFF doesn't seem to have any effect on the output of my sql-script .
But thanks anyway for the help Wim Vanherp
Wim.Vanherp@belgacom.net

 

[JeanNiBee]

Can you show me your script?

I use it here (FLUSHMESSAGE OFF) and it supresses the &quot;1 row(s) affected&quot; message.

 

[markmar1]

I usually use the command:

set nocount on

It will remove the rows affected messages....

 

[JeanNiBee]

My mistake.

FLUSHMESSAGE is used to flush out the buffer. So you are right that wouldn't work.

It's SET NOCOUNT ON

Again, sorry about that.

 

[wimvanherp]

thanks a lot

that works fine Wim Vanherp
Wim.Vanherp@belgacom.net