Solved

Daily Exception Report from SQL Server

Posted on 2012-03-20
5
238 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:CIPortAuthority
  • 3
  • 2
5 Comments
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37744425
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...
0
 

Author Comment

by:CIPortAuthority
ID: 37765989
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.
0
 
LVL 6

Accepted Solution

by:
SJCFL-Admin earned 500 total points
ID: 37766122
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.
0
 

Author Closing Comment

by:CIPortAuthority
ID: 37781993
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

0
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37782345
Your method is indeed easier in this instance.  I was not aware of this option and appreciate learning of it from you!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now