tags:

views:

73

answers:

3

Is it possible to make a call/notification to a C++ application from Microsoft SQL Server?

To give a broader understanding of what I'm trying to achieve: our database is being updated with new information; Whenever a new piece of information is received, we'd like to push this to 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++.

Any light shed on this area is greatly appreciated!

----- 28th Jan 3:40pm ----

OK After some reading around on Service Broker External Activation it seems like the right technology to use; however it seems to technology that's introduced in SQL Server 2008; and unfortunately we're using SQL Server 2005. Are there any other suggestive technologies or architectural designs we could use?

A: 

Try using xp_cmdshell. It's not something I'd do lightly, and I would definitely make sure you're NOT letting anything access the SQL box with sysadmin rights. Create a stored proc which can EXECUTE AS a sysadmin user, and run xp_cmdshell from there. You'll still need to enable it though...

Rob Farley
We know that we can use xp_cmdshell but my boss doesn't want to go that way for various reasons.
n00b
Fair enough. A CLR stored procedure could maybe do it too.
Rob Farley
+3  A: 

You might want to look in to using the Service Broker and handling the events it queues. Here's MSDN: http://msdn.microsoft.com/en-us/sqlserver/cc511479.aspx

fatcat1111
Very interesting~ I'll have a look into this!
n00b
Just updated the link to one that's more useful.
fatcat1111
Thanks Fatcat~ External Activation seems to be exclusive to SQL Server 2008 and by luck of the draw we are currently using 2005. Do you have any other suggestions?
n00b
Nothing clean comes to mind. It's been a while since I've used SQL Server 2005, but IIRC while it doesn't support external activation, it does support an external activator, and it's possible to write your own handler. Might be worth looking in to.
fatcat1111
+1  A: 

There is an alternative, you don't need the app to poll the database, you can create a trigger for the table which sends a message to your application whenever something changes. this would be the more straightforward (and safer) approach

Look at this

Anders K.
I'm assuming this message would be using service broker?Thanks for the lead! I'll look into it!
n00b