May 20, 2008 at 2:07 pm
Is there a way to create a stored procedure to count the number of locks and send me an email with the information?
I have this query which works perfecttly
SELECT COUNT(*) AS 'NumberofLockRecords', DB_NAME(resource_database_id)
FROM master.sys.dm_tran_locks
GROUP BY resource_database_id;
GO
but i want to create a job with the count number.
May 21, 2008 at 10:37 am
Hi
Use this code below to mail you with HTML format to ur mail,
declare @body1 varchar(MAX)
set @body1=' '
set @body1=@body1+'
'
SELECT @body1=@body1 + ' '
+ ' '
FROM master.sys.dm_tran_locks
GROUP BY resource_database_id;
SELECT @body1=@body1 + ' '
Print @Body1
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = ,
@recipients = ,
@subject = ,
@body =@body1,
@body_format ='HTML'
hope this helps. Put this code in a job and then schedule it for ur convinent timing
May 21, 2008 at 10:39 am
yes, it does! thank you so much.
May 21, 2008 at 10:43 am
You can also use the @query parameter of sp_send_dbmail which sends the results of the query as the body of the message or as an attachment. This lists the syntax and parameters of sp_send_dbmail http://msdn.microsoft.com/en-us/library/ms190307.aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply