| Author |
Topic |
|
aohx075
Starting Member
4 Posts |
 Posted - 2010-02-02 : 18:03:32
|
| Hi guys,I'm looking for a technology to notify a C++ application when a change to a SQL Server table is made. Our middle-tier is C++ and we're not looking to move onto .NET infrastructure which means we can't use SQLDependency, or SQL Notification Servers. We need to support SQL Server 2005 which also means that we cannot use SQL Service Broker External Activation since it is introduced in SQL 2008.To give a broader understanding of what we're trying to achieve: our database is being updated with new information. Whenever the database is updated, we'd like to push this or atleast alert the C++ application so that its dashboard reflects up-to-date data for the user.We know we can do this by having the C++ application polling the database but I see this as inefficient architecture and would like to have SQL push the information or a notification to C++.I'm also very surprised at the lack of implementation and information in this area. I would have thought something like this would be quite common in the commercial environment. |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-02-02 : 20:05:48
|
| Have you looked at using an extended stored procedure? The logic inside it could notify your middleware service (?) that an update had taken place. The middleware could then make the appropriate calls to get the new information.Another technique would be to use the sp_OAxxx calls to launch an OLE object which would have the same basic logic.HTH=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
 |
|
|
aohx075
Starting Member
4 Posts |
Posted - 2010-02-02 : 22:31:44
|
| Thanks Bustaz Kool. I've had a brief look into it and my question is:Is it possible to have the Extended Stored Procedure (and the .dll) make a call to my main C++ application? My guess is that it's possible but I just want to make sure.Thanks! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-02 : 22:41:50
|
| Service Broker is the technology you want to look into...think of it as MSMQ for SQL Server.http://msdn.microsoft.com/en-us/library/ms345108(SQL.90).aspxrb[edit] by the way, no lack of implementation or documentation. i'm sure you'll find this suitable for your needs. |
|
|
|
aohx075
Starting Member
4 Posts |
Posted - 2010-02-02 : 22:54:05
|
| Russell, Could you explain in more detail how I can use Service Broker for what I'm after? We're already using Service Broker but using it internally to process updates from one SQL Server to another SQL Server. How can SB be used to send notifications/messages to a C++ application?A similar suggestion was made in another forum where I could use Query Notifications but that technology seemed to require the application to be built on .NET and the SQLDependency class.Cheers |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-02-03 : 07:58:52
|
| I would like to add xp_cmdshell to the suggestion pool. It's basically a cmd-prompt running from the sql server so anything you can do from a cmd-window you can also do from a query on the sql server. Do you know when the change to the table happens though? Do you have a procedure or something that inserts data in a batch to this table and you would like to get notified when it competes? If so, add something likeDECLARE @cmd varchar(200)DECLARE @dir varchar(200)SET @dir = 'c:\temp\'SET @cmd = 'dir ' + @dir EXEC xp_cmdshell @cmdat the end of your import.- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-02-03 : 08:01:34
|
And HTH...I get the giggles every time I read your username man...reminds me of the 90's  - LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-03 : 08:20:03
|
| xp_cmdshell is another good idea.service broker allows asynchronous messaging.how u implement it all depends on your application. will it be an executable that is called on demand, or will it be a service which is waiting for messages or listening on a port? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-02-03 : 08:24:34
|
| If you don't know when the updates to the table happens you could add an AFTER INSERT trigger to it and run the xp_cmdshell from there but I'd seriously consider other ideas before doing this. At least test it properly first...- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
aohx075
Starting Member
4 Posts |
Posted - 2010-02-03 : 17:45:36
|
| xp_cmdshell has been thought of but we've ruled that out for several reasons - performance/security; but I appreciate the suggestion!Russell - the service broker would need to send off a notification to a service that would be waiting for messages. What technology would need to be used for Service Broker to send off a message to an external application? Are you thinking Service Broker External Activation? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-03 : 18:34:35
|
| I'd have to know more about you're C++ app, but it could have a message queue listening and you could send the message anyway you want -- I'd probably http it an XML fragment, or SOAP message. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-02-03 : 19:35:17
|
quote:
Originally posted by Lumbago
And HTH...I get the giggles every time I read your username man...reminds me of the 90's - LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein
I have others, you know. My daughter and I used to sit around trying to come up with them. A brief selection...Tone DefChill E Dog DogTupac Sade (sah-DAY)Poppa KappLL Bean DipPuffy PantsWatts 4 Sup RMC Escher (in (and out) da house)Kickin' it home skool...=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
|
|
|
|
Notifying C++ app when a table is updated