Hi,

My application needs to fire a web service and pass a record to it as soon as one particular field is updated on the databse.
I serched on net and came to know that this is very much posible in SQL 2005 but my production server has SQL 2003.

Is firing a web servie from trigger possible in SQL 2003???
I need to cll this web service real time when one field is updated. Is there any othee way??

Thanks for going through this any help is very much appreciated.

Thanks,
Nilay

Problem with triggers is that they are synchronous - everything else has to wait until the trigger has finished. So you really should avoid contacting the outside world from within a trigger.

And no, there is no bypass if you let the trigger insert data into another table, which has its own trigger that fires the web service - the first trigger will wait for the second one to finish.

Not sure there is a SQL 2003 - you are probably running SQL 2000 on Windows 2003.

The common way in SQL 2000 is to have a log table where your trigger dumps the data that needs further processing. You then have a job that executes at a set time interval, and does the processing - in this case, somehow mixed up with a webservice.

Thanks for the reply, and you are right its SQL 2000, sorryy for that
but actually my Trigger is not inserting data into any other table, There is only one trigger involved and one web service.

I know this situation is quite wierd but this is how the setup is currently where i'v joined in. There is one application which inserts records into the database and at that time the status field for that record is set to "default". Afterwards when record is finally udated the status field changes to "Completed" and at that time i have to fire this web serivce which does some final validations. So at this point i have to call the web service real time and pass the record to it.

This is the problem that I cannot have a batch running in night and pick up all the records and then call web service, but i have to call it then and there real time...this is the main problem.

I know i can do it on SQL 2005 but how to accomplih this goal on SQL 2000??

Adriaan,

Thanks for reply..

Sorry I misunderstood earllier.. Yes it is Sql Server 2000 on Windows 2003. They are planning to upgrade to 2005 but dont know when.. and I heard in 2005 it is possible to call web service.. But even in that case since it would still be synchronous then it wouldnt be good option to call directly from trigger.. right..

I think I will have to go with the way u suggested no matter what..

Nilay.

Yes you can call the webservices with an URL, but not advisable to use a trigger in this case as explained by Adrian above, because when there is a change in webservice then the trigger will have troubles. I think it is a better option to write a C# class that calls the web service and expose it as a COM component. You could then call the COM component from the SQL trigger.

---

Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

The simple answer is "Never Do That"

Roji. P. Thomas
http://toponewithties.blogspot.com

This is my guess....
Don't you think you can write a procedure for the update and return the OUTPUT to the application when the column/field updated then you call the webservice within your application without impacting the database.


Mohammed U.

this is sooooo wrong. triggers need to be fast. web services can be slow for any number of reasons, for example, the web server that hosts it is down.

do you really want your db to lock up for 30 sec on each insert while your trigger waits for some web server to timeout? []

the only thing i can think of that might be worse would be popping a messagebox from a trigger. or perhaps calling Environment.Exit() []

---

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
www.elsasoft.org

I believe you haven't read out the thread completely, it is not advisable in this case.
(I have seen your email too)
Explain your requirement so that the resolution can be achievable in a better way here.

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Even better to start a new thread than hijacking someone else's one. []

--
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de