Hi friends,

We have a scheduled job set to run every 4 seconds from the time the job is enabled. The below sql in the job runs and appends the results every 4 seconds in the output file. Once the program ends we disable the job.

Would like to know if this can be done in a stored procedure so that it can be called from the program itself and it can be easier to track down when the error occurs.. Please give me your suggestions

SELECT

spid = Lock.request_session_id,

HostName = Session.host_name,

ProgramName = Session.program_name,

Cmd = Request.Command,

LoginName = Session.login_name,

dbid = Lock.resource_database_id,

DatabaseName = DB_NAME(Lock.resource_database_id),

Type = Lock.resource_type,

LockType = Lock.request_type,

Mode = Lock.request_mode,

Status = request_status,

UserTable.Name

FROM

sys.dm_tran_locks AS Lock

LEFT JOIN

sys.dm_exec_sessions AS Session ON

Session.session_id = Lock.request_session_id

LEFT JOIN

sys.dm_exec_requests AS Request ON

Request.session_id = Session.session_id

LEFT JOIN

sys.tables AS UserTable ON

UserTable.object_id = Lock.resource_associated_entity_id

AND

type = 'U'

WHERE

Lock.resource_type = 'OBJECT'

go

Thanks a lot