I have a stored procedure that returns messages regarding how many rows were affected by the stored procedure. For example:
(3300974 row(s) affected)
(45426 row(s) affected)
(222593 row(s) affected)
(15616 row(s) affected)
(22682 row(s) affected)
(132794 row(s) affected)
(6720 row(s) affected)
For auditing purposes, I always compare the numbers from these 7 rows to the messages I've received from the last time I ran the data. This way, I know that if I usually expect ~3,300,974 rows affected but only end up with ~100,000 rows affected on the first line, something went wrong.
How can I programmatically save these messages to a table whenever the stored procedure is run?
(Note that I want to save the messages since the stored procedure is not returning any results set.) I am using SQL Server 2008 R2.
Thanks in advance!
Try using @@Rowcount, like below,
declare @table table (Data varchar(5)) declare @audit table(AuditDate datetime,RowcountNum int,RowcountMessage varchar(max)) --insert one insert into @table select 'data1' union select 'data2' union select 'data3' union select 'data4' union select 'data5' --first audit insert into @audit select getdate(),@@ROWCOUNT,'( '+CAST(@@rowcount as varchar(max))+' row(s) affected )' --insert two insert into @table select 'data6' union select 'data7' union select 'data8' --second audit insert into @audit select getdate(),@@ROWCOUNT,'( '+CAST(@@rowcount as varchar(max))+' row(s) affected )' select * from @audit
Please "Mark as Answer" if my post answers your question or "Vote as Helpful" if it helps you
For now, I am having other people run the stored procedure through SQL Server Management Studio as if they are running a regular query. In the future, I will set up a job for it.
I'm not sure if @@RowCount is going to work in this situation because my stored procedure actually executes 3 other stored procedures. Those other stored procedure may actually be calling another stored procedure.
In other words, my stored procedure (let's call it stpMain) is a bit more complicated such that the definition might look something like this:
PRINT 'Last Month''s Data:'
EXEC stpName @BegOfLastMonth,@BegOfCurrMonth
PRINT 'Current Month''s Data:'
EXEC stpName @BegOfCurrMonth, @CurrDate
..such that stpName may actually be calling a few more stored procedures. However, rdineshkumar may have brought a good insight. If I can get access to the underlying stored procedures, I may have to modify it through there instead. Is there any other way? Correct me if I'm wrong, but f I use @@RowCount after Exec stpMain, I don't think I will be able to get all the rows affected from the embedded stored procedures.
Thanks in advance!
- Edited by yosedesh Tuesday, February 26, 2013 2:31 AM
It seems that I will have to use @@RowCount if I can get access to the underlying stp's (which may not always be the case).
If I have something as follow:
CREATE PROCEDURE [stpLoggingMultipleSTPTest] AS BEGIN EXEC [stpLoggingTest] 'BLAH', '2013-02-26 10:03:40.840', 111 EXEC [stpLoggingTest] 'BLAH', '2013-02-26 10:03:41.840', 222 END GO
If I only have permission to execute, but not modify [stpLoggingMultipleSTPTest], when I run EXEC stpLoggingMultipleSTPTest, I will get the folloing Messages:
(125 row(s) affected)
(100 row(s) affected)
I need to log both of those.
If I run the following:
EXEC [stpLoggingMultipleSTPTest] SELECT @@ROWCOUNT
I will only get 100 (not both 125 and 100).
Is there a way to get all of the Messages and save it to a table?
- Edited by yosedesh Tuesday, February 26, 2013 6:29 PM
From SSMS I don't think there is a way. In .NET there is a special class allowing to save these messages. I forgot at the moment what it is, will do research later if you need it.
For every expert, there is an equal and opposite expert. - Becker's Law
There is only one way to do it automatically:
1. Create a .sql file executing the stored procedure(s)
2. Run it with SQLCMD -i (input.sql) -o (output.txt)
3. You can set it up as a SQL Server Agent job
The desired row count messages will be in output.txt.
SQLCMD usage example:
Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012
- Edited by Kalman TothModerator Thursday, March 07, 2013 12:42 PM