December 26, 2018 at 3:31 am
Hi All,
I am currently implementing error handling in all the procedures in my project .Whenever an error occurs i have to make an entry in the error table with the following details.
1) Job_name
2) Stored_procedure name
3) error number
4) error message
...etc
I am currency facing an issue in getting the Job name dynamically. I have a stored procedure xyz which is being called in many jobs So i cannot hardcode the JobName in the catch block.
Note the Stored procedure xyz is not directly called in the Job step . The Stored procedure Abc is called in the Job step which in-turn call Stored procedure XYZ.
Can you guys help me in getting the Job name dynamically.
December 26, 2018 at 8:31 am
Since it's being called from multiple places, the only way to get it is to pass it in as a parameter
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 26, 2018 at 8:45 am
there are a number of built in functions you can use to get error and other information.
parent procedure (the name of the procedure calling a procedure, for example) or job name that is calling a procedure or process is not accessible; if it was an SSIS job, you might see some information int he host name or application name information.
here's a pair of auditing snippets to get you started:
a try catch to capture the errors: BEGIN TRY
BEGIN TRANSACTION;
--Do Work that we need to try
SELECT 1/0
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
DECLARE @ProcedureName VARCHAR(128),
@ErrorSeverity INT,
@ErrorNumber INT,
@ErrorMessage NVARCHAR(4000),
@ErrorState INT,
@ErrorLine INT;
SELECT @ProcedureName = ISNULL(OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID) ,'not a procedure'),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE()
PRINT 'Msg ' + CONVERT(VARCHAR, @ErrorNumber) + ', Level ' + CONVERT(VARCHAR, @ErrorSeverity) + ', State '+ CONVERT(VARCHAR, @ErrorState) + ',Line ' + CONVERT(VARCHAR, @ErrorLine) + CHAR(13) + CHAR(10) + @ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
PRINT @ProcedureName
PRINT 'Msg ' + CONVERT(VARCHAR, @ErrorNumber) + ', Level ' + CONVERT(VARCHAR, @ErrorSeverity) + ', State '+ CONVERT(VARCHAR, @ErrorState) + ',Line ' + CONVERT(VARCHAR, @ErrorLine) + CHAR(13) + CHAR(10) + @ErrorMessage;
RAISERROR('ErrorNumber %d, Level %d, State %d, Line %d Err: %s',@ErrorSeverity,@ErrorState,@ErrorNumber,@ErrorSeverity,@ErrorState,@ErrorLine,@ErrorMessage);
END CATCH;
a set of queries that are nice for grabbing whodunnit information: SELECT
getdate() AS EventDate,
DB_NAME() AS DBName,
CURRENT_USER AS CurrentUser,
HOST_NAME() AS HostName,
APP_NAME() AS ApplicationName,
OBJECT_NAME(@@PROCID) AS ProcedureName,
USER_ID() AS Userid,
USER_NAME() AS UserName,
SUSER_ID() AS sUserid,
SUSER_SNAME() AS sUserName,
IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],
IS_MEMBER('db_owner') AS [Is_DB_owner],
IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],
IS_MEMBER('db_datareader') AS [Is_DB_Datareader],
ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
ConnectionProperty('net_transport') AS 'net_transport',
ConnectionProperty('protocol_type') AS 'protocol_type',
ConnectionProperty('auth_scheme') AS 'auth_scheme',
ConnectionProperty('local_net_address') AS 'local_net_address',
ConnectionProperty('local_tcp_port') AS 'local_tcp_port',
ConnectionProperty('client_net_address') AS 'client_net_address',
ConnectionProperty('physical_net_transport') AS 'physical_net_transport'
Lowell
December 26, 2018 at 4:30 pm
The sys.dm_exec_sessions view will show jobs running TSQL job steps with a program name like "SQLAgent - TSQL JobStep (Job 0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF : Step 1)". You can parse out the job and step values and try to match them with the system job tables, and if they return something that matches the current session @@SPID then you've got what you need.
SELECT e.session_id, j.name, e.step_id, s.step_name
FROM (
SELECT session_id,
job_id = CONVERT(BINARY(16), SUBSTRING(program_name, 30, 34),1),
step_id = REPLACE(STUFF(program_name, 1, 71, ''), ')', '')
FROM sys.dm_exec_sessions
WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job % : Step %)'
) e
LEFT JOIN msdb.dbo.sysjobs j ON j.job_id = e.job_id
LEFT JOIN msdb.dbo.sysjobsteps s ON s.job_id = e.job_id AND s.step_id = e.step_id
WHERE e.session_id = @@SPID
If your jobs include a lot of SSIS packages you're out of luck, unless you can get all SSIS package authors to meticulously set something meaningful for the ApplicationName property on all SQL connections. Even then you'd have to figure out some way of deducing the package involved from the PROGRAM_NAME() value, and then searching SSIS job steps in sysjobsteps to find it. If you were determined, you could configure each SSIS job step to set a package variable to the job name, make sure this variable is implemented in all packages, and configure all SQL connection managers with the package variable as an expression for the ApplicationName property.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply