none
Stored Procedure - Programmatically Save Messages to Table

    Question

  • Hello:

    I have a stored procedure that returns messages regarding how many rows were affected by the stored procedure.  For example:

    (3300974 row(s) affected)
    Last Month's:
    (45426 row(s) affected)
    (222593 row(s) affected)
    (15616 row(s) affected)
    Current Month's:
    (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!

    Tuesday, February 26, 2013 1:25 AM

All replies

  • What are you using to run the stored procedure?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, February 26, 2013 1:46 AM
    Moderator
  • 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



    Regards, Dineshkumar
    Please "Mark as Answer" if my post answers your question or "Vote as Helpful" if it helps you

    Tuesday, February 26, 2013 1:48 AM
  • 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:

    EXEC stpNameBlahBlah

    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
    Tuesday, February 26, 2013 2:30 AM
  • I believe you should be able to get correct row count if you run it right after the stored procedure. However, you just need to try to confirm or deny.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, February 26, 2013 1:42 PM
    Moderator
  • 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
    Tuesday, February 26, 2013 6:25 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


    My blog

    Tuesday, February 26, 2013 6:39 PM
    Moderator
  • 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: http://msdn.microsoft.com/en-us/library/ms162773.aspx

    SQLCMD usage example:

    http://www.sqlusa.com/bestpractices2005/restoredbwithsqlcmd/


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012



    Thursday, March 07, 2013 12:40 PM
    Moderator