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.
CIPortAuthorityAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SJCFL-AdminCommented:
My recommendation would be to utilize the built in Job Notifications capability by adding a step to your current job to test teh status of the table after the process finishes.  if there are still entires, have it e-mail 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\sqlcmd.exe"   -S "tcp:mysqlserver\mysqlinstance" -d master -Q "SELECT @@version ; " -e -I -t 3 -b -p -X

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...
CIPortAuthorityAuthor Commented:
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.
SJCFL-AdminCommented:
For those types of reports, I switched to using powershell.  I find powershell to be much more flexible and responsive to the types of changes that I decide to incorporate along the way.  (Usually from reporting on something to taking action on it to repait the problem...  If you ahve not already dealved into this scripting language at all, I'd highly recommend going to the scripting guys site and taking a look at it.

You absically pick the language you are comfortable with and powershell wraps the command in its scripting language.  Here is a good sample.

http://blogs.technet.com/b/heyscriptingguy/archive/2011/05/31/use-powershell-to-get-the-sql-server-error-log.aspx

I am pretty much a database kind of gal, so I shy away from .net unless my programmers run into problems.  then I wrap snippets of their code into a powershell script to help debug the problem.  I've found it especially helpfull for all kinds of tasks from error reporting, defragmenting disks, checking deployments, etc.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CIPortAuthorityAuthor Commented:
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;

Open in new window

SJCFL-AdminCommented:
Your method is indeed easier in this instance.  I was not aware of this option and appreciate learning of it from you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.