CIPortAuthority
asked on
Daily Exception Report from SQL Server
We have a table in a database on SQL Server 2005. This table continuously has records added to it and an SQL Agent job processes it every few minutes and processes and then empties the collected records. Rarely, there are errors in some of the records that prevents them being processed properly. When this happens, manual intervention is required to see what is wrong and force processing of the records. But, I need to know that there is a problem with some records before I can investigate.
So, what I am looking for is a way to run a report on this table and email it to myself IF there are any records stuck in there. I am hoping that there is a simple way to do this without resorting to programming some application. We have integration server and reporting server installed on the same server as the database engine but I have no idea how to use either.
So, what I am looking for is a way to run a report on this table and email it to myself IF there are any records stuck in there. I am hoping that there is a simple way to do this without resorting to programming some application. We have integration server and reporting server installed on the same server as the database engine but I have no idea how to use either.
ASKER
I am still playing around with your suggestion to see if I can make it work. But I was hoping to be able to run a report and have it emailed to me with a little more detail about what's outstanding instead of just "an error happened" type message.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
While powershell could be used, it's much more complicated than another solution I found. Using the database mail service, you can email the results of a query to any email address you like. I created the following simple stored procedure which will get executed daily by an SQL Agent job:
DECLARE @C INT;
SELECT @C=COUNT(*) FROM PM.DBO.GL_TRAVERSE;
IF @C>0 BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name='Default',
@recipients= 'address@email.com',
@subject='Error!',
@query='select * from db.dbo.table order by key;'
END;
Your method is indeed easier in this instance. I was not aware of this option and appreciate learning of it from you!
To set up the test, sue the sqlsmd utility described in http://msdn.microsoft.com/en-us/library/ms190264.aspx
A sample command used in a job step is:
"c:\Program Files\myappdirectory\Tools
This one was basically set up just to test to see if a remote server was currently accessible, but could be reworked to check for rows in a table and raise an error if found. On the notifications tab, you can specify an e-mail to be sent. You will, of course, have to enable database e-mail and may have already done so...