Sleeping a custom trigger script

Message Share Topic Printable Version Google Delicious Digg StumbleUpon Windows Live Yahoo Bookmarks reddit Facebook MySpace Newsvine Furl Topic Search Topic Options Post Reply Create New Topic

   We have a Trigger that uses a custom script to call a stored 
procedure in our production SQL Server instance and conditionally 
forward the message.  There are times, however, where we will have 
downtime on our production database, our worry is that we will miss 
processing critical messages during these times.

We understand 
that there is no way to automatically reprocess the message after a 
period of time 
(http://www.parker-software.com/forum/forum_posts.asp?TID=7290&OB=ASC&PN=1&title=error-2147217887),
 but should we detect a downtime in the production server, we are 
considering calling Sleep within the script for several minutes before 
retrying the stored procedure.

Can you forsee any issue with this
 (especially concerning threading)?

Thanks in advance for any 
insight.

Author	Message Share Topic Printable Version Google Delicious Digg StumbleUpon Windows Live Yahoo Bookmarks reddit Facebook MySpace Newsvine Furl Topic Search Topic Options Post Reply Create New Topic
jimcasey Members Profile Send Private Message Find Members Posts Add to Buddy List New User Joined: 16 Mar 2010 Location: Denver, CO Posts: 1	Post Options Post Reply Quote jimcasey Report Post Quote Reply Topic: Sleeping a custom trigger script Posted: 17 Mar 2010 at 12:11am
	We have a Trigger that uses a custom script to call a stored procedure in our production SQL Server instance and conditionally forward the message. There are times, however, where we will have downtime on our production database, our worry is that we will miss processing critical messages during these times. We understand that there is no way to automatically reprocess the message after a period of time (http://www.parker-software.com/forum/forum_posts.asp?TID=7290&OB=ASC&PN=1&title=error-2147217887), but should we detect a downtime in the production server, we are considering calling Sleep within the script for several minutes before retrying the stored procedure. Can you forsee any issue with this (especially concerning threading)? Thanks in advance for any insight.