Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2007
    Posts
    9

    Unanswered: How to get last processed date of stored procedure

    Hello everybody

    Help me to get the last processed date of a stored procedure.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The last time it was invoked, or the last time the code was altered?

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ... and to short cut a bit.

    Invocation cannot be established after the event - you would need to log the execution somewhere or be running a trace.
    Alteration only shows up in SS 2005 - SS 2000 only has a creation date so if you altered rather than drop\ created you are out of luck.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jul 2007
    Posts
    9
    Quote Originally Posted by MCrowley
    The last time it was invoked, or the last time the code was altered?

    i just want the date on which the stored procedure was executed and where the lastexcuted date and other details are stored?

  5. #5
    Join Date
    Jul 2007
    Posts
    9
    Quote Originally Posted by sukumaster
    i just want the date on which the stored procedure was executed and where the lastexcuted date and other details are stored?
    one more doubt plz

    1) the date on which the stored procedure was excuted last time
    2)i want to know whether the stored procedure was excuted successfully or end with failure

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    As Pootle Flump mentioned, you will have to add that audit trail detail to the code of your stored procedure. SQL Server (nor any other RDBMS that I know of) will keep this sort of information.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I create my own logging for all stroed procedures...comes in very handy

    Just call this...get the datetime on the way in, and the last datetime on the way out

    Code:
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_LogProcCalls]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[usp_LogProcCalls]
    GO
    
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    
    
    
    CREATE PROCEDURE [dbo].[usp_LogProcCalls] (
    	  @SprocName 	sysname
    	, @TranStart 	datetime
    	, @TranEnd 	datetime
    	, @APPUSER	char(30)
    	, @Rows 	int
    	, @Err 		int
    	, @Paramters	varchar(255)
    	, @rc 		int OUTPUT)
    AS
    SET NOCOUNT ON
    --
    --                 	Enterprise Solutions
    --
    --           File:      \\paerscbvd0014\Succession\Procedures
    --           Date:      01/04/2006
    --         Author:      Brett Kaiser
    --         Server:      paerscbvd0014
    --       Database:      Succession
    --          Login:      sa
    --    Description:      This Procedure will log all procedures executed in a database
    --
    --
    --                      The stream will do the following:
    --
    --    '1. Function...
    
    --
    --    Tables Used: 	Sproc_Log
    --
    -- Tables Created:      None
    --
    --
    --  Row Estimates:
    -- name              rows        reserved           data               index_size             unused
    -- --------------------------------------------------------------------------------------------------
    -- Sproc_Log            0            0 KB           0 KB                     0 KB               0 KB
    --
    -- sp_spaceused Sproc_Log
    
    --Change Log
    --
    -- UserId    	Date         	Description
    -- -----------  --------------  ------------------------------------------------------------------------------------------
    -- x002548   	01/01/2006 	1. Initial release
    --
    --
    --
    
    Declare @error int, @RowCount int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int
    
    BEGIN TRAN
    	DECLARE @LogStart datetime
    	SELECT @rc = 0, @LogStart = GetDate()
    	 IF (SELECT @@TRANCOUNT) <> 1
    	  BEGIN 
    	   SELECT @Error_Loc = 1
    	        , @Error_Message =  'The logging procedure must be executed outside of any transaction.  @@TRANSCOUNT='
    			+ CONVERT(varchar(5),@@TRANCOUNT)
    	        , @Error_Type = 50002, @rc = -6661
    	   GOTO usp_LogProcCalls_Error
    	  END
    
    	INSERT INTO Sproc_Log (
    	  [SprocName]
    	, [TranStart]
    	, [TranEnd]
    	, [APP_USER]
    	, [LogStart]
    	, [LogEnd]
    	, [Rows]
    	, [Err]
    	, [Paramters])
    	SELECT 
    	  @SprocName
    	, @TranStart
    	, @TranEnd
    	, @APPUSER
    	, @LogStart
    	, GetDate()
    	, @Rows
    	, @Err
    	, @Paramters
    
    	 Select @RowCount = @@ROWCOUNT, @error = @@error
    	
    	 IF @error <> 0
    	  BEGIN
    	   SELECT @Error_Loc = 2, @Error_Type = 50001, @rc = -6662
    	   GOTO usp_LogProcCalls_Error
    	  END
    	
    	 IF @RowCount <> 1
    	  BEGIN 
    	   SELECT @Error_Loc = 3
    	        , @Error_Message =  'Expected 1 row to be inserted in to the sproc log.  Actual Number inserted = '
    			+ CONVERT(varchar(5),@RowCount)
    	        , @Error_Type = 50002, @rc = -6663
    	   GOTO usp_LogProcCalls_Error
    	  END
    
    COMMIT TRAN
    
    usp_LogProcCalls_Exit:
    
    -- Place any house keeping procedures here like...
    
    --Set ansi_warnings ON
    SET NOCOUNT OFF
    
    RETURN 
    
    usp_LogProcCalls_Error:
    
    Rollback TRAN
    
    If @Error_Type = 50001
     BEGIN
    
      Select @error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc))  
              + ',"' + '  @@ERROR: ' + ',"' + RTrim(Convert(char(6),error))
              + ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
    --        + ',"' + '  Message: ' + ',"' + RTrim(description)
              From master..sysmessages
            Where error = @error)
     END
    
    If @Error_Type = 50002
    
     BEGIN
      Select @Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc)) 
                       + ',"' + ' Severity:  UserLevel ' 
                  + ',"' + ' Message: ' + ',"' + RTrim(@Error_Message)
     END
    
    RAISERROR @Error_Type @Error_Message
    
    GOTO usp_LogProcCalls_Exit
    
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    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.

Posting Permissions

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