Polling a table on the SQL Server

Results 1 to 2 of 2

Thread: Polling a table on the SQL Server

  1. #1
    Join Date
    Dec 1969

    Default Polling a table on the SQL Server

    Right now I have a client with an integration (that I wrote) that involves two SQL Servers. Server1 is linked to Server2 (no, they aren&#039;t REALLY named server1 and server2, hehe). A user performs an action on an app running from server1 and it in turn passes some parameters to a stored procedure on Server2.<BR><BR>Simple so far...<BR><BR>I&#039;m responsible for the code on Server2. Server2 has been under a load as of late and response times haven&#039;t been great. Every now and then, Server2 locks up... which in turn locks up Server1.<BR><BR>It&#039;s not the code in the integration... it&#039;s been tested and re-tested. It&#039;s likely due to some record locking that occurs with another application that runs on Server2.<BR><BR>What I would *like* to do is basically decouple Server1 and Server2. I&#039;d like for Server1 to populate a table with the parameter data, and for Server2 to poll this table regularly and basically run the integration procedures whenever a new record hits this table.<BR><BR>I suppose I could put a trigger on the queuing table to execute the sproc whenever a new record is inserted. Would this be the best way to go about this? When the parameters are passed from Server1, the process is part of a transaction... so I&#039;m afraid that by just putting a trigger on the table, I&#039;d still have problems with the SQL Servers locking each other up.<BR><BR>Any suggestions? Can I poll this Queuing table ever second or two and execute my procedures that way? What is the best way to do this?<BR><BR>Thanks!

  2. #2
    Join Date
    Dec 1969

    Default RE: Polling a table on the SQL Server

    In the Enterprise Manager it&#039;s possible to schedule an SQL job. <BR><BR>You could write some statements that check the table on server1 to see if there are new records and if so execute some stored procedures, and schedule that to run evey hour (or whatever) on server2.<BR><BR>Now server1 will no longer be dependent on server2, but server2 will be dependent on server1 though. But I think that you can specify a timeout on how long the scheduled job may run every time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts