udb.logInsert

This procedure provides a standardized mechanism to log information data to a standard log table.  The procedure provides the ability to be resilient in the face of errors not passing those errors up the stack if so desired.  

Syntax

udb.logInsert [@set_id=] uniqueIdentifier,

[@calling_sql_name]=varchar(1000),

[@step]=smallint,

[@total_steps]=smallint,

[@message]=varchar(1000),

[@user_name]=nvarchar(128),

[@user_type]=tinyint,

[@raise_error]=bit

Arguments

[@set_id=] uniqueIdentifier

A unique value to identifier a calling processes, procedure, group of procedures.  Use the same value across multiple procedure calls if you want to tie the process together in the log table. This could give the view of procedures call one after the other or nested inside each other.

[@calling_sql_name]=varchar(1000)

The name of the process or procedure call.  If it is a database object the standard @calling_sql_name would be the SchemaName.ObjectName of the object.

[@step]=smallint

The current step that is being logged. Default value of 1

[@total_steps]=smallint

The total number of steps in the @calling_sql_name process or procedure that is being logged.  Default value of 1

[@message]=varchar(1000)

The informational text that is to be written to the log table.

[@user_name]=nvarchar(128)

The name of the user that is logging the message.  Default value of NULL which result in the @user_name being SUSER_NAME(), USER_NAME(), or ORIGINAL_LOGIN() based  on the value of the @user_type parameter.

[@user_type]=tinyint

Determines the which system function should be used if the @user_name value is left NULL.  1=SUSER_NAME(), 2=USER_NAME(), 3=ORIGINAL_LOGIN().  Default value of 1

[@raise_error]=bit

The bit is used to determine if a error in this procedure should be raised or silently caught. 0=Internal error messages will be captured by a try catch block and not raised. 1=Error messages will be raised.  Default value of 1.

Return Codes

0 (success) or 1 (failure)

Result Sets

N/A

Remarks

In general the performance of this procedure is good.  It works well for logging messages during a procedure or complex TSQL script execution.  However if you need to log a large number of messages, a large number being more messages than rows of data manipulated by the rest of the process than the high performance/transaction independent procedure udb.logInsertTableVariable maybe a better option. 

Examples

Example 1

The following example shows the minimal approach to calling the udb.logInsert procedure to capture some basic information.

execute udb.logInsert
@calling_sql_name='Quick_Log_Example'
,@message='This is an example of how quickly a message can be logged with this procedure';

execute udb.logInsert
@calling_sql_name='Quick_Log_Example'
,@message='This is an example of how quickly a message can be logged with this procedure';

Example 2

The following example  shows a way to tie two separate log messages together using the same @set_id and @Calling_SQL_Name values.

declare @set_id as uniqueidentifier
set @set_id=newID();

execute udb.logInsert
@set_id=@set_id
,@calling_sql_name='Complex SQL Task'
,@step=2
,@total_steps=2
,@message='The Before Complex SQL Task Message';

/*Complex SQL Task HERE*/
print 'Complex SQL Task!';

execute udb.logInsert
@set_id=@set_id
,@calling_sql_name='Complex SQL Task'
,@step=2
,@total_steps=2
,@message='The After Complex SQL Task Message';

Output: (select * from udb.logging;)

logging_id set_id date_logged calling_sql_name user_name step total_steps message
2 C72F6361-ABA6-4014-B18F-C09ABB266E7B 2014-01-10 20:35:18.7782103 Complex SQL Task test\testUser 1 2 The Before Complex SQL Task Message
3 C72F6361-ABA6-4014-B18F-C09ABB266E7B 2014-01-10 20:35:18.7782103 Complex SQL Task test\testUser 2 2 The After Complex SQL Task Message


 

See Also

udb.logInsertTableVariable

udb.logging

Last edited Jan 11, 2014 at 12:49 AM by sfibich, version 8

Comments

No comments yet.