May 14, 2010 at 10:01 am
Hello,
I have for awhile been adding code to my sp's that will log each step when the Log bit is set as an input parameter. I can then go to a table and step through each line and see what is going on. However as time goes on and new capabilities / features are released I like to review my "old" methods and bounce them off others to see what new ideas might be out there. Here is a sample snippet from a long sp and I'm looking for others to chime in with their thoughts.
TIA
JB
--Now we calculate the poverty line by adding the quantity family members to the base line
SET @FamSuplmnt = @FamSuplmnt * @BorrFamCount --Number of "CLAIMED" dependants in the house
SET @BorPovLine = @YrAmt + @FamSuplmnt --This is the poverty line
IF @ErrorLogFlg = 1
BEGIN
INSERT INTO Sp_LoggingTable(SpExecName, SpExecStamp, SpExecStep, SpExecStepCmd, SpExecStepCmdRslt)
VALUES (@SPName, @SpExecTime, 'Calc Claimed Dependants', 'SET ' + @FamSuplmnt + ' = ' + @FamSuplmnt + ' * ' + @BorrFamCount, '@FamSuplmnt=' + @FamSuplmnt)
INSERT INTO Sp_LoggingTable(SpExecName, SpExecStamp, SpExecStep, SpExecStepCmd, SpExecStepCmdRslt)
VALUES (@SPName, @SpExecTime, 'Calc Poverty Line With Family Members', 'SET ' + @BorPovLine + ' = ' + @YrAmt + ' + ' + @FamSuplmnt, '@BorPovLine=' + @BorPovLine)
END
May 14, 2010 at 10:16 am
i personally never liked going to a table to look at SP logs for SQL jobs. i like to look at it in sql agent. there was an article here a few weeks ago about how to import the data as well
May 14, 2010 at 1:02 pm
OK so this might be something new to me. To clarify I am writing Sp's that are accessed from a business object in between dbase and web front end. I know how to look at SQL Agent job log but does it also log SP execution? In other words these sp's will not be called by the SQL Agent but by ADO business object??
May 14, 2010 at 4:59 pm
I am wondering what your log sp logs when the main sp does something within transaction and transaction is rolled-back. Unlike Oracle there is no autonomous transaction concept in SQL Server as such (you can, kind of, simulate it using table variables or in SQL2008 using loopback linked server).
I have always used RAISERROR with low severity and NOWAIT option to get the log message back. You can code your business object can catch these messages and log them appropriately.
Example:
RAISERROR (N'My Log Message', 10,1) WITH NOWAIT
The above works like PRINT statement, except the fact that the message in returned instantly!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply