October 8, 2011 at 10:48 am
HI,
The architecture in our project is a bit complicated and it first creates dynamic queries (for different sources) and stores it in Queries table using a proc [DPPEngine] and then performs ETL task.
Recently, I found a easier way to execute the queries generated while maintaining the error logs.
I hope you would appreciate the same. My question here is "How do we incorporate a Trigger within the code to recieve an Email for a new entry in Error Table?"
Thanks in Advance.
(
@SOURCEID INT
)
AS
BEGIN
BEGIN TRY
TRUNCATE TABLE MIGRATION_DB.DBO.QUERIES
EXEC [DPPEngine] 'migration_db1','Filteration-1,Mapping,Mapping-dvt,Mapping-Update1,Filteration-2,Pre-Cflag,CFlag,InSourceMerging,CS-1','v4.0',@SOURCEID
END TRY
BEGIN CATCH
EXEC [pERROR_LOG_PROC]
END CATCH
BEGIN TRY
DECLARE @QUERY NVARCHAR(MAX)
DECLARE @QUERYID INT
DECLARE SUPERB CURSOR FOR
SELECT QUERY FROM MIGRATION_DB..QUERIES
WHERE QUERY NOT LIKE 'GO'
ORDER BY ID
OPEN SUPERB
FETCH NEXT FROM SUPERB INTO @QUERY
WHILE(@@FETCH_STATUS = 0)
BEGIN
PRINT @QUERY
EXECUTE SP_EXECUTESQL @QUERY
FETCH NEXT FROM SUPERB INTO @QUERY
END
END TRY
BEGIN CATCH
EXEC [pERROR_LOG_PROC]
END CATCH
DEALLOCATE SUPERB
CLOSE SUPERB
END"]
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 8, 2011 at 3:08 pm
Don't include such a thing in a trigger... if the email system goes down, it'll take that part of SQL Server with it.
Instead, setup a job to interogate your logs on a regular basis... say, once a minute. If it finds something new, have it send the email.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 9, 2011 at 12:34 am
Thanks Jeff 🙂
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 9, 2011 at 10:54 am
An alternative for such a scenario would be to use ServiceBroker. It's designed for exactly such a scenario (among others).
There are two disadvantages of the frequent job concept: The first one: it'll query the related table every minute regardless if there are any new entries or not. The second one: you'll get the notification up to a minute late.
The advantage: it's a known and common technology.
Using ServiceBroker will allow to get immediate notification only when there are new rows. But it'll take a moment to get used to the concept.
October 9, 2011 at 12:13 pm
Hmmm... Learning Service Broker would mean that I'd have to learn another SQL Server "4 letter word" 😛 but it does sound interesting. What I'd be most interested in is what it uses to instantly monitor such tables and what the cost of such monitoring actually is.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 9, 2011 at 1:17 pm
No doubt SSB would be the better scenario for this request(because is can carry a payload so it would only query a single row at best (in stead of a tb scan) ).
On the other hand, in stead of just having the job poll every x time, have the trigger raise an informative error that will launch the meant sqlagent job.
e.g. help to tighten use of cmdshell or sp_start_job[/url]
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 9, 2011 at 2:06 pm
ALZDBA (10/9/2011)
No doubt SSB would be the better scenario for this request(because is can carry a payload so it would only query a single row at best (in stead of a tb scan) ).On the other hand, in stead of just having the job poll every x time, have the trigger raise an informative error that will launch the meant sqlagent job.
Heh... I'll say "It Depends"... the word "better" is so very subjective especially when it comes to SQL Server 4 letter words such as SSAS, SSRS, SSIS, SSSB, etc. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 9, 2011 at 2:20 pm
Hi Sir,
Can you clarify a bit about SSB ? Would it ensure an Email to my mail box for each entry in ErrorLog table ?
_______________________________________________________________________________________________
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 9, 2011 at 2:44 pm
Jeff Moden (10/9/2011)
Hmmm... Learning Service Broker would mean that I'd have to learn another SQL Server "4 letter word" 😛 but it does sound interesting. What I'd be most interested in is what it uses to instantly monitor such tables and what the cost of such monitoring actually is.
The basic concept:
A trigger on the table to be monitored would send a ServiceBroker message. This message will be stored in a related message queue. The queue will "automagically" be processed until empty. This is independent of the source table so there is no delay even if there's any issue within the ServiceBroker. It can either process one message at a time or dealing with blocks as well.
Two sources to better describe the message processing concept than I can do:
http://rusanu.com/2008/08/03/understanding-queue-monitors/ (online)
ProSQL Server 2005(8) Assemblies by Klaus Aschenbrenner (book)
For the concept required recommended in the given scenario I would use a script based on Rusanus example[/url].
We're using SSB as a replacement for the "frequent scheduled job" concept since almost two years and never had any issues (after we configured it properly ...)
October 9, 2011 at 3:37 pm
The only thing you really need to take care of when using SSB is to build a good SSB monitoring system.
If SSB fails for some reason, it's better to get notified ASAP to prevent having to cope with a number of days worth of messages.
Just to get people started I experimented a bit a while ago. Adventures With Service Broker[/url]
Helper scripts: http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31867/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply