Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Daily Exception Report from SQL Server

Posted on 2012-03-20
5
Medium Priority
?
346 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 1000 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

705 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