Solved

Daily Exception Report from SQL Server

Posted on 2012-03-20
5
297 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CONVERT date time to a different time zone. 2 78
Help with simplifying SQL 6 54
kill process lock Sql server 9 79
Format Date fields 11 62
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 SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

737 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