June 27, 2011 at 1:36 pm
I wanna Send an XML (body of a xml or a select * from table FOR XML AUTO) from SQL to a specific port. I tried service broker but it use 2 instances of MSSQL, I wanna Send an XML from SQL to a windows service running on a specific port.
June 27, 2011 at 1:48 pm
You could look at using SQLCLR to call out to the service, but that would open a whole can of worms regarding security.
Why can't the service retrieve the XML from SQL Server?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 27, 2011 at 2:06 pm
I have a trigger on a table on insert that i wanna send some data via xml to this service on a specific port.
June 27, 2011 at 2:09 pm
Boooo 🙂 Poor design. Please save yourself the trouble and do not do this work in a trigger. Save your "work to do" request to a table and have another sweeper process come by and process the requests in the "work to do" table.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 27, 2011 at 2:26 pm
Ya , i know but its not my design , i have a application that works with sql (stored procedures,tables) and i only have access to sql , so i need to send a xml data to this application via SQL :angry:.
June 27, 2011 at 2:30 pm
I don't know of antyhing in native T-SQL that can do what you're asking. SQLCLR could provide the illusion of native T-SQL, or you could shell out to a cmd prompt and do something from there using xp_CmdShell but that comes with even more security baggage than SQLCLR.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 28, 2011 at 6:19 am
Security is not an issue , i am on a intranet , with no internet connections.
June 28, 2011 at 6:35 am
opc.three is right; I've done something similar with console apps and xp_cmdshell, as well as CLR.
no matter how you do it, it's outside of SQL...so it doesnt belong inside a trigger.
one of the first rules of triggers is that if something needs to be done outside of the database in question, it doesn't belong in a trigger.ANY error, whether network or anything, rolls back the transaction and results in lost data.
i really recommend opc.three 's suggestion of a sweeper job called once every x minutes, looking at a table that has the data to be processed is the right way, and the sweeper process marks the data it touches as processed in some way...a flag, deleting the data from the work table, something.
Lowell
June 28, 2011 at 1:36 pm
Another option would be to fire a ServiceBroker message using the trigger.
This will separate the two processes ad still provide (almost) real time processing.
We've had the once-per-minute-job approach installed for several years but since the table in question received inserts at random intervals (from sub-second to a few hours) that needed to be processed asap, we've changed it to the ServiceBroker approach and got rid of a job firing 80% of the time for no reason. Works without any issues.
June 28, 2011 at 2:42 pm
I tried service broker but it works only between 2 instances of sql(if i am right), i have the same problem as u i need to process data instantly i just need to send a xml via trigger from sql to a local process on the same server that listen to port xxxx. If its work with service broker give me an example plz(how to configure endpoint, route, service) .
June 28, 2011 at 3:04 pm
I can't really give you an example but you might find a solution on the web when searching for "SQL Server 2005 External activation".
I'm not sure if the SQL 2008 Feature Pack will work against SS2K5 as well, but IIRC, this has been mentioned a while ago.
The concept of using external activation with SS2K5 also is explained in Klaus Aschenbrenners book "SQL Server 2005 Service Broker" so I assume it works 😉
June 28, 2011 at 3:11 pm
I don't think you'll be able to go directly from Service Broker to the Windows Service but please let us know if you find a way to do that because that would be a very interesting thing to know about for future solutions.
At the very least I could see you going from Service Broker to an executable (using External Activation as mentioned) and that executable could go to the Windows Service. It's a different approach than the approach I outlined initially. I could summarize it as push versus pull:
1) Push: Service Broker would push information in the queue to an external executable and that executable would talk to the Win Service as required
2) Pull: An executable would check the "work to do" table on a regular basis, pull any unprocessed records and talk to the Win Service as required
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 28, 2011 at 3:27 pm
Ok, this is the link where it's been mentioned that the app available on the web has been incorporated into SS2K8:
http://www.sqlskills.com/blogs/bobb/post/A-supported-Service-Broker-external-activator.aspx
The link also include a link to the original 2005 application download. (the link originally posted in Klaus' book and many other resources doesn't work anymore....)
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply