question

sridhar rajendran avatar image
sridhar rajendran asked

we are calling webservice from db and we need wait untill get reply from web service. how to achieve this is sql server?

Dear all, we are calling webservice from db and we need wait untill get reply from web service. how to achieve this is sql server?
sql-server-2008web-service
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What programming language?
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
A couple of possible techniques come to mind: The first possibility is to use the CLR - check for a starting point. Alternatively, you may be able to use the OLE Automation functionality - check for your starting point.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Thomas, i had create CLR procedure successfully but its not wait till reply came from webservice. kindly give any idea ..
0 Likes 0 ·
@sridhar - I'm not the best person to ask. The last time I did any serious programming, Web Services hadn't been invented!
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
What the web service is doing? Returning data? Maybe you can try SSIS and the Web Service Task. But it really depends on what you want to achieve.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
My recommendation is to avoid that kind Of functionality inside the database. Move the call to a business layer or use service broker or biztalk. Keep it simple. What are you going to to if you never get a response? While you wait fir the response you are locking up resources
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Absolutely, I agree with Pavel/Håkan you must resist the temptation and keep web service calls out of CLR, they starve SQL worker threads waiting for a response. Left unchecked you will get to the point where you have to restart the server.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.