How to receive notifications from sql server

Question

How to receive notifications from sql server

shahzad_ssh

SSC Enthusiast

Points: 151
More actions
September 24, 2004 at 11:55 am

#102218

Hi all,
I have developed an Application in Visual Basic 6.0, runs on approx 100 machines (clients) may be more, these clients simultaneously and frequently request SQL SERVER to get a single record if available by calling a stored procedure, the problem I am facing is if no record is availabe then all clients are in the waiting state continuosly requesting for a record, in other words using lot of server resources, task manger shows the 100% CPU usage and the server becomes very slow.
I just want to know that is there any method to receive notification from SQL SERVER. Like I can write a trigger that can send a notification to a client, or how to send a notification to a client Application that will raise an event in client application that now data is available.
SQL SERVER NOTIFICATION services are helpfull? if they are how i implement it in VB 6.0.
Please help me in this regard. Thanks
Shahzad Hassan
Sr.Software Engineer.
National Database & Registration Authority
Pakistan.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Aaron Templeton SSCarpal Tunnel Points: 4655 More actions · Answer 1

This first one is, well, crude and rude but kind of elegant in a way. When the client wants the record have it make the request using a seperate connection from any other SQL it may execute in the mean time. Have the request SP write the connection ID to a table when no record is available. Have the client leave the connection open after the SP returns no record. Have the client check the status of the connection periodically (rather than requery). When the server finds that a record is now available, have it query the table for all the waiting connections and KILL them . The client will then detect that the connection was lost, reconnect, and re-request the record. If the connection was lost due to some network problem or an idle timeout or whatever then it will just go into a holding pattern again. I would capture the Login Time in addition to the process ID of the connection and prior to KILLing it verify that the current version of the process ID has the same Login Time as the one in the table just in case the connection was closed without cleaning up the table and then the process ID reused for a completely different connection. If your "check the status of the connection" timer is really short (less than 10 seconds or so) you should wait a random number of milliseconds (0.000 - 10.000 seconds perhaps) to prevent a ton of connection requests hitting the server all at once.

Another option would be to use sp_cmdShell to run something (anything really) from the OS. You will still have to have a table, perhaps to record the list of people to be notified and perhaps the machine name/port needed to contact them (depends on how you choose to contact them). Very flexible. One way or another it could definately work.

shahzad_ssh SSC Enthusiast Points: 151 More actions · Answer 2

Hi Aaron,

Thanks for the reply, the solution you have suggested seems convincing at first glance but the problem is that client will never know that connection is terminated by the SQL "KILL" command. Might be something worng at my end but i have tried it at differenet systems, client does not receive any notification that connection is terminated, so checking connection state is not helpful it returns always 1 until unless explicitly closed by the application using

cnn.close

I'll try xp_cmdshell but I think I have to go some other way arround or please suggest me some other.

Regards

Shahzad Hassan

Aaron Templeton SSCarpal Tunnel Points: 4655 More actions · Answer 3

That's unfortunate news. I was obviously hoping that there was some attempt to notify the client that the connection was terminated and I've obviously never had occasion to test this theory in practice. This forces you to reduce the polling interval should you take this route, but you can perform a minimal transaction to check the connection state periodically. For example, you could send a SQL statement only consisting of "set nocount on". It should prepare and run with virtually no drain on the server (the prepare should be cached and reused), but it does still invoke a small amount of network traffic and a small amount of work on the SQL Server that should be controlled as a 10, 30 or 60 second polling interval. Whatever you can afford. Good luck.