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
National Database & Registration Authority