February 12, 2015 at 8:30 am
Yeah, this whole scenario seems weird to me.
You can't suggest a consultant or admit you aren't good at optimizing or you'll be fired?
That very much makes it sound like you told them you WERE good at doing this, and accepted a role where there was an expectation that you would be doing it. Is that accurate?
If so, then there is very little anyone can do to help, and your focus should be on how to GET good at doing this as quickly as possible. Or get out of this job and into one where the expectations match your abilities. Even if we somehow rewrote this giant procedure for you without knowing any of the underlying structure, data, or requirements, there would always be the next one. Posting these as urgent internet questions whenever they come up to make it look like you can do it is simply NOT a viable strategy.
If your role does not encompass creating and rewriting these procedures, but simply administration, then the extent of your involvement should be, as Chris says, saying that the developers need to rewrite this in set based fashion. And any suggestion of a consultant should be reflecting poorly on them, not you. More importantly, it would also help teach WHOEVER is responsible how to do it better.
February 12, 2015 at 11:54 am
Please review the following stored procedure CreateInvoice to determine what we can do for logging an exception that occurs within it ??
I like to keep a log table available that I can insert records like "starting at..." plus date and time and "finished at..." plus date and time. Make sure these records are inserted OUTSIDE of a transaction that encompasses the rest of the work otherwise the records will disappear as a result of an implicit rollback when the timeout occurrs.
I ran into this exact scenario, 30 second timeout and all 🙂
February 16, 2015 at 7:33 am
[/quote]
I like to keep a log table available that I can insert records like "starting at..." plus date and time and "finished at..." plus date and time. Make sure these records are inserted OUTSIDE of a transaction that encompasses the rest of the work otherwise the records will disappear as a result of an implicit rollback when the timeout occurrs.
I ran into this exact scenario, 30 second timeout and all 🙂
[/quote]
Thanks for your help
To log exceptions in table I have created following Table and stored procedure
Please let me know Does that work?
/****** Object: Table [dbo].[ProcedureLog] Script Date: 02/16/2015 19:29:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProcedureLog](
[LogDate] [datetime] NOT NULL,
[DatabaseID] [int] NULL,
[ObjectID] [int] NULL,
[ProcedureName] [nvarchar](400) NULL,
[ErrorLine] [int] NULL,
[ErrorMessage] [nvarchar](max) NULL,
[ErrorState] [int] NULL,
[ErrorSeverity] [int] NULL,
[AdditionalInfo] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[CallProcedureLog] Script Date: 02/16/2015 18:59:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CallProcedureLog]
@ObjectID INT,
@DatabaseID INT = NULL,
@AdditionalInfo NVARCHAR(MAX) = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@ProcedureName NVARCHAR(400);
SELECT
@DatabaseID = COALESCE(@DatabaseID, DB_ID()),
@ProcedureName = COALESCE ( QUOTENAME(DB_NAME(@DatabaseID)) + '.' + QUOTENAME(OBJECT_SCHEMA_NAME(@ObjectID, @DatabaseID)) + '.' + QUOTENAME(OBJECT_NAME(@ObjectID, @DatabaseID)), ERROR_PROCEDURE() );
INSERT ProcedureLog
(
DatabaseID,
ObjectID,
ProcedureName,
ErrorLine,
ErrorMessage,
ErrorState,
ErrorSeverity,
AdditionalInfo
)
SELECT
@DatabaseID,
@ObjectID,
@ProcedureName,
ERROR_LINE(),
ERROR_MESSAGE(),
ERROR_STATE(),
ERROR_SEVERITY(),
@AdditionalInfo;
END
ALTER TABLE [dbo].[ProcedureLog] ADD CONSTRAINT [DF__Procedure__LogDa__1DF06171] DEFAULT (getdate()) FOR [LogDate]
GO
Here is the place where I have implemented my call to CallProcedureLog in my original SP.
BEGIN CATCH
IF (XACT_STATE() <> 0)
ROLLBACK TRANSACTION
DECLARE @msg NVARCHAR(MAX);
SET @msg = 'Error occour in CreateInvoice SP. Error number = ' + ERROR_NUMBER();
EXEC CallProcedureLog
@ObjectID = @@PROCID,
@AdditionalInfo = @msg;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
END
February 16, 2015 at 8:47 am
Seems like it would work. I would however also insert a record in your log when the procedure starts, and when it successfully finishes. The absense of a successful finish could imply a dropped connection due to a timeout.
Its worth noting that when I played with timeouts and had an insert into a log table in the CATCH block, after the rollback, I DID NOT get a record inserted. This means that a client timeout did not result in code in the CATCH block getting executed, and that might mean your logging in the catch block will not catch an instance of a client timeout. You might want to take that into consideration. Try a test procedure and force a timeout in your client, I used "sqlcmd" with the -t <timeoutvalue> parameter.
In my case, a client timeout was the only remaining explanation, but I did not get a log record written to support this. You could consider a trace, but about all I saw in the default trace selection is an "SQL:BatchCompleted" and an starttime / endtime duration pretty much describing the timeout plus some change.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply