Calling Web Service From Stored Procedure or Function

Question

Calling Web Service From Stored Procedure or Function

Ken Parchinski

SSChasing Mays

Points: 632
More actions
October 17, 2007 at 9:41 am

#375913

Is it possible to call a web service from a stored procedure? We are looking
to access google maps' web service to obtain latitude and longitude values
for addresses to calculate more accurate distance calculations. Do I have to use the CLR Integration and a C# assembly, for example, or can I do it strictly in T-SQL? Thanks in
advance.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Source-NH SSCarpal Tunnel Points: 4165 More actions · Answer 1

Ken,

Your best bet would be to write a CLR based function to return the Lat / Long based on the address passed to it. This way it will be reusable code for all of your stored procedures and functions.

HTH

Ken Parchinski SSChasing Mays Points: 632 More actions · Answer 2

Thanks Rich. That is exactly what all of my research has returned.

noeld SSC Guru Points: 96590 More actions · Answer 3

To be honest I would not do this on the DB Server. I'd rather have a client App do the WebService search ... but then again is your call, not mine 😀

* Noel

Ken Parchinski SSChasing Mays Points: 632 More actions · Answer 4

What are the pros and cons of implementing from the DB server as opposed to a client application? Security? Performance? Operability? Thanks in advance.

Source-NH SSCarpal Tunnel Points: 4165 More actions · Answer 5

Off the top of my head, there are a couple of pretty significant cons.

1- You will be allowing SQL to go outside of your firewall, which is never a good thing.

2- You will be creating a synchronous process inside your database that will be dependant upon a web based process. This could potentially create locking problems if transactions start to take a very long time.

I'm sure there are more, but these are 2 that I can see right off the top of my head.

HTH :hehe:

Ken Parchinski SSChasing Mays Points: 632 More actions · Answer 6

yes, it helps. thank you. i will also investigate other gotchas.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 7

You have to be careful, but this can be implemented pretty safely. Using the CLR to call a web service works fine.

The most common things to work out are always in the handling of unusual situations - what happens if the web service is unavailable or your network connection is down?, how long do you wait for a timeout?, etc.

Ken Parchinski SSChasing Mays Points: 632 More actions · Answer 8

Good points. I assume these concerns would normally be handled in the web service (assuming SQL Server was not involved). If SQL Server was utililezed to implement, how would these conditions be best handled?

noeld SSC Guru Points: 96590 More actions · Answer 9

The implementation of this at the DB Level implies also are :

1. "CLR" memory must be consumed. In other words less Memory / CPU for what SQL Does best.

2. You must track resource usage /leakage on the server. If something goes wrong a Server reboot is not a pretty picture on the client it would be no biggie

3. Hopefully you don't call this proc in a middle of a transaction but you are opening those doors 😉

And many more. Don't get me wrong you can use CLR stored procedures but so far I have found that external access are alot better handled by the clients in the long run.

Then again I am just one case maybe others have had better luck :hehe:

Cheers,

* Noel

Ken Parchinski SSChasing Mays Points: 632 More actions · Answer 10

Thank you. The overwhelming trend seems to be to do this at the client level.