Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2009
    Posts
    7

    Lightbulb Unanswered: How to insert code into every stored procedure

    Hi, I'm working with SQL Server 2008 and now I need to insert 3 lines code into the beginning of every stored procedure in database. (The purpose to do this is to track execution history) There are about 3000 stored procedures so definitely I dont wanna do it manually. Here is an example about how I wanna the sp looks like:

    ALTER PROCEDURE [dbo].[StoredProcudureSample] @variable int
    AS
    SET NOCOUNT ON

    DECLARE @ProcName VARCHAR(200)

    SET @ProcName = OBJECT_NAME(@@PROCID)

    EXEC [dbo].[Sproc_SaveStoredProcedureExecute] @StoredProcedureName = @ProcName


    --original stored procedure body
    ...

    Does anyone have good idea about this?

    TIA,

    Julie

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm playing the devil's advocate here, but have you considered using SQL Profiler to do this? No code modifications, no hotspots, insignificant resource utilization... A better solution all around in my opinion.

    If this is a "one time shot" where you want to run it for a day or two, just start the SQL Profiler running on either the server or any spare machine with enough disk to log what you need.

    If this is an ongoing requirement, build the trace and test it using the SQL Profiler GUI, then automate it as either a SQL Agent Job or a startup procedure.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    yeah what Pat said. Script a server side trace.

    or if you are hell bent on your solution use regex and CLR for the find and replace on your DEV server. use SSIS querying sys.sqlmodules to spit your files out of the database. check them into source control and use those files to deploy to Test. the boss likes this little trick.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Oct 2009
    Posts
    7
    Thanks for your reply. I know we can do it by SQL Profiler, but we dont wanna keep an agent job running all the time in our production server. To insert the codes into every sp, we can track every execution history anytime without worrying about sql agent job failed by some random reasons

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Rabbit, one method would be to script out your sprocs to one long file, and then do a search and replace on "SET NOCOUNT ON" to include your auditing call.

    For myself, I add this type of auditing to each of my procedures, though my method also records the parameters passed to the sproc and the logon that called it. It is extremely handy for debugging application issues, especially in development. I originally wrote it to catch an error the developers were reporting, where it turned out they were submitting zero-length strings when they thought they were submitting nulls. I've used it and refined it ever since then.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Oct 2009
    Posts
    7
    Thanks Blindman. Search and replace was the only method I can recalled before I asking this question. The problem is not all my stored procedures include SET NOCOUNT ON But I think I can figure that out how to do that later. Just wanna see whether there is any other better way to do it.

    Thanks~

  7. #7
    Join Date
    Oct 2009
    Posts
    7
    Thanks Thrasymachus too

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK...not for nothin

    BUT

    What happens if you rollback the TRAN?

    Then you won't log anything

    I do this as well...but I put the sprocLog execution in the exit: outside of any transactions

    BEGIN TRAN

    @s = GetDate()

    ..code

    SELECT @error = @@ERROR

    If @error <> 0
    goto error

    ...fall though

    COMMIT TRAN

    exit:

    @e = GetDate()

    exec SprocLog

    return

    error:

    ROLLBACK TRAN

    handles

    goto exit

    Want the sproc log sproc
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by blindman
    Rabbit, one method would be to script out your sprocs to one long file, and then do a search and replace on "SET NOCOUNT ON" to include your auditing call.

    For myself, I add this type of auditing to each of my procedures, though my method also records the parameters passed to the sproc and the logon that called it. It is extremely handy for debugging application issues, especially in development. I originally wrote it to catch an error the developers were reporting, where it turned out they were submitting zero-length strings when they thought they were submitting nulls. I've used it and refined it ever since then.
    You care to share it?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    @Brett: The OP said they are using 2008 so isn't TRY...CATCH a more appropraite construct?
    George
    Home | Blog

  11. #11
    Join Date
    Oct 2009
    Posts
    19
    Could you not just use the built in stats? and using the view sys.dm_exec_query_stats to get at the data
    Example

    Code:
            SELECT * FROM sys.dm_exec_query_stats
              qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle)
              st JOIN sys.dm_exec_cached_plans cp ON (qs.plan_handle = cp.plan_handle)
              WHERE DB_NAME(st.DBID) is not null and cp.objtype = 'proc'
    -----------------------------------
    Free SQL server monitoring for DBA's
    SQL DBA manager by BlueThames.com

  12. #12
    Join Date
    Oct 2009
    Posts
    7
    @OxfordSmarty: I think by using sys.dm_exec_query_stats you only can get the execution history since last database refresh... what I want is to store every execution history and build reports on it.

    Thanks

  13. #13
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    make sure you don't put your snippet in the body of this one:

    Sproc_SaveStoredProcedureExecute


  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Is there any benefit tor TRY/CATCH over our original???
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Oct 2009
    Posts
    7
    Thanks for the reminder, Jezemine

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •