Solved

Daily Exception Report from SQL Server

Posted on 2012-03-20
5
263 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 61
Updating statistics with error notification email in SQL server 4 94
Sql query 107 59
why sql server only update some statistics in the database ? 3 19
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

808 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