April 22, 2009 at 9:31 am
I have a count query (I think I learned it here) that is very fast. This is an example:
DBCC UPDATEUSAGE ('BMC')
SELECT o.[name], i.rowcnt
FROM sysobjects o
LEFT OUTER JOIN sysindexes i
ON o.[id] = i.[id]
WHERE o.xtype = 'U' AND i.indid < 2 and o.[name] = 'claimfile''
I would like to email the results of the count....
My query for emailing results looks like this:
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'user@domain.com',@body='Here is the count of the records',
@subject ='RPH File',@profile_name ='DBMailProfile',
@body_format = 'html',
@execute_query_database ='pssjobs',@query ='my count query',
@attach_query_result_as_file = 1,@query_attachment_filename ='count.txt'
I tried to copy and paste the count query into the @query parameter and had real trouble with the single quotes and the double quotes.
Two questions: is this the best way to go about sending a count via email and can someone help with putting this count query into the email syntax?
Thank You
April 22, 2009 at 10:20 am
Maybe
Schedule it as a SQL Agent job, and email the "operator" the job step details
Don't you just replace ' with '' for your sendmail query parameter?
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'user@domain.com',@body='Here is the count of the records',
@subject ='RPH File',@profile_name ='DBMailProfile',
@body_format = 'html',
@execute_query_database ='pssjobs',@query ='
DBCC UPDATEUSAGE (''BMC'')
SELECT o.[name]
,i.rowcnt
FROM sysobjects o
LEFT OUTER JOIN sysindexes i ON o.[id] = i.[id]
WHERE o.xtype = ''U''
AND i.indid < 2
AND o.[name] = ''claimfile''
',
@attach_query_result_as_file = 1,@query_attachment_filename ='count.txt'
April 22, 2009 at 11:43 am
Thank you Jerry, that worked perfectly
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply