Capture Stored Proc output to file

[Tim]

I am running a stored proc that does many updates. I want to capture the results of the stored proc in a text file. Results returned such as, 18 rows affected (the stuff that shows in results pane of query analyzer). Do I have to run the stored proc in batch mode to do this and capture via the output file in OSQL? I also want to capture error messages in case the stored proc aborts in the middle.

I am running SQL Server 7.0 SP2 on Win2K. I am running the stored proc in a DTS package.

Thanks.

Tim

[PTH]

Hi Tim,
If you want to capture results of the stored proc in a text file, you can try following statement in your SP:
declare @filename varchar(255)
declare @string varchar(255)
select @string = 'value you want to capture'
select @filename = 'c: emp est.txt'
select @str = 'echo ' + @string + '>>' + @filename
exec master..sp_cmdshell @str
Note with option:
'>': add each value in a file
'>>': add all values in the same file
PTH

------------
Tim at 1/8/01 5:37:39 PM

I am running a stored proc that does many updates. I want to capture the results of the stored proc in a text file. Results returned such as, 18 rows affected (the stuff that shows in results pane of query analyzer). Do I have to run the stored proc in batch mode to do this and capture via the output file in OSQL? I also want to capture error messages in case the stored proc aborts in the middle.

I am running SQL Server 7.0 SP2 on Win2K. I am running the stored proc in a DTS package.

Thanks.

Tim

[Tim]

I want to capture the output of the stored proc. If I have a stored proc called usptest and I run it and it writes the output to the results pane, how do I capture the results pane in your response below 'value you want to capture'?

Thanks.


------------
PTH at 1/8/01 7:14:45 PM

Hi Tim,
If you want to capture results of the stored proc in a text file, you can try following statement in your SP:
declare @filename varchar(255)
declare @string varchar(255)
select @string = 'value you want to capture'
select @filename = 'c: emp est.txt'
select @str = 'echo ' + @string + '>>' + @filename
exec master..sp_cmdshell @str
Note with option:
'>': add each value in a file
'>>': add all values in the same file
PTH

------------
Tim at 1/8/01 5:37:39 PM

I am running a stored proc that does many updates. I want to capture the results of the stored proc in a text file. Results returned such as, 18 rows affected (the stuff that shows in results pane of query analyzer). Do I have to run the stored proc in batch mode to do this and capture via the output file in OSQL? I also want to capture error messages in case the stored proc aborts in the middle.

I am running SQL Server 7.0 SP2 on Win2K. I am running the stored proc in a DTS package.

Thanks.

Tim

[Gurinder Singh Kohli]

Use @@ROWCOUNT TO CAPTURE the # of rows affected and @@ERROR to
capture the Error #, it is not straightforward though.

Other way

Write a Stored Procedure, use osql to run it, write output to a file.

Hope this helps.

Thaxns.

Gurinder Singh Kohli

------------
Tim at 1/8/01 7:20:46 PM

I want to capture the output of the stored proc. If I have a stored proc called usptest and I run it and it writes the output to the results pane, how do I capture the results pane in your response below 'value you want to capture'?

Thanks.


------------
PTH at 1/8/01 7:14:45 PM

Hi Tim,
If you want to capture results of the stored proc in a text file, you can try following statement in your SP:
declare @filename varchar(255)
declare @string varchar(255)
select @string = 'value you want to capture'
select @filename = 'c: emp est.txt'
select @str = 'echo ' + @string + '>>' + @filename
exec master..sp_cmdshell @str
Note with option:
'>': add each value in a file
'>>': add all values in the same file
PTH

------------
Tim at 1/8/01 5:37:39 PM

I am running a stored proc that does many updates. I want to capture the results of the stored proc in a text file. Results returned such as, 18 rows affected (the stuff that shows in results pane of query analyzer). Do I have to run the stored proc in batch mode to do this and capture via the output file in OSQL? I also want to capture error messages in case the stored proc aborts in the middle.

I am running SQL Server 7.0 SP2 on Win2K. I am running the stored proc in a DTS package.

Thanks.

Tim