December 8, 2016 at 2:29 pm
I need help with a script. I'm trying to log start and end times from a procedure executions.
Here is what I have so far. Struggling with it.
CREATE TABLE [dbo].[ExecutionHistory](
[ExecutionHistoryId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[DatabaseName] VARCHAR(100) ,
[ProcedureName] [varchar](100) NOT NULL,
[Start] [datetime] NOT NULL,
[Complete] [datetime] NULL,
[DurationMinutes] AS DATEDIFF(MINUTE,[Start],[Complete]) PERSISTED,
[EffectedRowCount] INT
)
ALTER PROCEDURE GetExecutionHistoryStart @Session VARCHAR(20)
AS
DECLARE @logid INT
-- Declaration statements
DECLARE @DatabaseName varchar(100) = DB_NAME ()
DECLARE @ProcedureName VARCHAR (100) = Object_Name(@@ProcID)
DECLARE @Start DATETIME = GETDATE()
DECLARE @Complete DATETIME = GETDATE()
DECLARE @EffectedRowCount int = @@ROWCOUNT
IF @Session ='Start'
-- Insert into the dbo.ErrorHandling table
INSERT INTO [PortalConfiguration].dbo.ExecutionHistory (
[DatabaseName]
,[ProcedureName]
,[Start]
,[EffectedRowCount]
)
SELECT
@DatabaseName
,@ProcedureName
,@Start
,@EffectedRowCount
SELECT @logid = SCOPE_IDENTITY()
UPDATE [PortalConfiguration].dbo.ExecutionHistory
SET Complete = GETDATE() WHERE ExecutionHistoryId = @logid
SELECT @logid
---Test the procedure below
CREATE PROCEDURE TestLogging
AS
EXEC GetExecutionHistoryStart 'Start'
WAITFOR DELAY '00:00:10'
EXEC GetExecutionHistoryStart 'End'
SELECT * FROM [PortalConfiguration].dbo.ExecutionHistory
***SQL born on date Spring 2013:-)
December 8, 2016 at 2:59 pm
thomashohner (12/8/2016)
I need help with a script. I'm trying to log start and end times from a procedure executions.Here is what I have so far. Struggling with it.
CREATE TABLE [dbo].[ExecutionHistory]([ExecutionHistoryId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[DatabaseName] VARCHAR(100) ,
[ProcedureName] [varchar](100) NOT NULL,
[Start] [datetime] NOT NULL,
[Complete] [datetime] NULL,
[DurationMinutes] AS DATEDIFF(MINUTE,[Start],[Complete]) PERSISTED,
[EffectedRowCount] INT
)
ALTER PROCEDURE GetExecutionHistoryStart @Session VARCHAR(20)
AS
DECLARE @logid INT
-- Declaration statements
DECLARE @DatabaseName varchar(100) = DB_NAME ()
DECLARE @ProcedureName VARCHAR (100) = Object_Name(@@ProcID)
DECLARE @Start DATETIME = GETDATE()
DECLARE @Complete DATETIME = GETDATE()
DECLARE @EffectedRowCount int = @@ROWCOUNT
IF @Session ='Start'
-- Insert into the dbo.ErrorHandling table
INSERT INTO [PortalConfiguration].dbo.ExecutionHistory (
[DatabaseName]
,[ProcedureName]
,[Start]
,[EffectedRowCount]
)
SELECT
@DatabaseName
,@ProcedureName
,@Start
,@EffectedRowCount
SELECT @logid = SCOPE_IDENTITY()
UPDATE [PortalConfiguration].dbo.ExecutionHistory
SET Complete = GETDATE() WHERE ExecutionHistoryId = @logid
SELECT @logid
---Test the procedure below
CREATE PROCEDURE TestLogging
AS
EXEC GetExecutionHistoryStart 'Start'
WAITFOR DELAY '00:00:10'
EXEC GetExecutionHistoryStart 'End'
SELECT * FROM [PortalConfiguration].dbo.ExecutionHistory
I'm wondering if you know exactly what is happening as a result of your IF statement? To help avoid confusion for others reading my code, I've found it far better to ALWAYS use a BEGIN END block to delineate exactly what code is to be executed when the condition in your IF evaluates to true. That way, there's no question as to what code will get executed. Assuming that everyone knows how SQL operates without that is an unwarranted assumption. What it does is execute just the next statement, and then moves on to the next statement after that regardless of whether or not the statement immediately following the IF was executed. Does that help?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 8, 2016 at 4:30 pm
Yeah, My bad on BEGIN/END its been a super long day. This did work but i'm sure someone has a better method. Also how do I grab the calling procs name. Its giving me my history procs name.
--CREATE TABLE [dbo].[ExecutionHistory](
--[ExecutionHistoryId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
--[DatabaseName] VARCHAR(100) ,
--[ProcedureName] [varchar](100) NOT NULL,
--[Start] [datetime] NOT NULL,
--[Complete] [datetime] NULL,
--[DurationMinutes] AS DATEDIFF(MINUTE,[Start],[Complete]) PERSISTED,
--[EffectedRowCount] INT
-- )
ALTER PROCEDURE GetExecutionHistoryStart @Session VARCHAR(20),@logid INT OUTPUT
AS
-- Declaration statements
DECLARE @DatabaseName varchar(100) = DB_NAME ()
DECLARE @ProcedureName VARCHAR (100) = Object_Name(@@ProcID)
DECLARE @Start DATETIME = GETDATE()
DECLARE @EffectedRowCount int = @@ROWCOUNT
IF @Session ='Start'
BEGIN
-- Insert into the dbo.ErrorHandling table
INSERT INTO [PortalConfiguration].dbo.ExecutionHistory (
[DatabaseName]
,[ProcedureName]
,[Start]
,[EffectedRowCount]
)
SELECT
@DatabaseName
,@ProcedureName
,@Start
,@EffectedRowCount
SELECT @logid = SCOPE_IDENTITY()
RETURN
END
ELSE
UPDATE [PortalConfiguration].dbo.ExecutionHistory
SET Complete = GETDATE() WHERE ExecutionHistoryId = @logid
/*********************************************
ALTER PROCEDURE Test
AS
DECLARE @MainID INT
EXEC GetExecutionHistoryStart'Start', @MainID OUTPUT
WAITFOR DELAY '00:01:10'
EXEC GetExecutionHistoryStart 'End', @MainID
*************************************************/
SELECT * FROM [PortalConfiguration].dbo.ExecutionHistory
***SQL born on date Spring 2013:-)
December 8, 2016 at 5:54 pm
Also how do I grab the calling procs name. Its giving me my history procs name.
You are setting @ProcedureName to Object_Name(@@ProcID) when you are in the ExecutionHistory procedure...so it's giving you that procedure name.
Pretty sure you need another input parameter added to GetExecutionHistoryStart and then pass in the Object_Name(@@ProcID)
So in your test procedure:
ALTER PROCEDURE Test
AS
DECLARE @MainID INT
DECLARE @ProcName nvarchar(128)
SET @ProcName = Object_Name(@@ProcID)
EXEC GetExecutionHistoryStart'Start', @ProcName, @MainID OUTPUT
WAITFOR DELAY '00:01:10'
EXEC GetExecutionHistoryStart 'End', @ProcName, @MainID
Sue
December 8, 2016 at 6:08 pm
Thanks Sue,
I had a feeling I would have to do that. Was just hoping there was some ultra clever way to call it the executing Proc from inside the GetHistory one. Looks like thats the way i'll have to go.
Thanks 🙂
***SQL born on date Spring 2013:-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply