July 16, 2008 at 1:16 am
Hi!
I want to call a web service from T-SQL (sql2005). Is that any way to do this? I see the endpoints, but I don't understand, how can I call a web service, and what I need to this.
Can someone post me a sample script?
Thanx:
Peter
July 16, 2008 at 2:29 am
Hi, am not sure, but have read somewhere that if you are use sql server 2000 then the MSXML2.XMLHTTP object & sp_OACreate sp is one of the solution for this requirement.
🙂
July 16, 2008 at 3:01 am
Hi Peter ,
you can get help from this link:
http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx
And as per Endpoint Concept in SQL Server 2005, You can call a Stored Procedure in side the Webservice. not Webservice inside the Stored Procedure.
IMP: You create an HTTP Endpoint and expose stored procedures and functions through the endpoint. Application that can send and receive SOAP messages (like Visual Studio) can connect to the endpoint and request the data. The various objects that you expose through the endpoint are called services and since they are exposed over HTTP, you get the familiar term web services. All of these services are described using the standard WSDL (web services description language) format.
Follow the link for more inputs..
http://www32.brinkster.com/srisamp/sqlArticles/article_45.htm
Cheers!
Sandy.
--
July 16, 2008 at 8:45 am
Straight from T-SQL, you can't. This is one of the valid reasons to use CLR procs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2008 at 12:02 pm
But I would go even further. When you find yourself in this situation you should try to call the webservice from the "CLIENT" not on the server.
There are many reasons for this!
* Noel
July 16, 2008 at 12:25 pm
noeld (7/16/2008)
But I would go even further. When you find yourself in this situation you should try to call the webservice from the "CLIENT" not on the server.There are many reasons for this!
That's quite true, there are issues to watch out for when implementing web services from SQL.
However, it is often a requirement to run a web service as a scheduled job (ie. from within SQL Agent), while at the same time interacting with data from a database. This type of scenario makes using the SQLCLR for webservices an attractive option.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 16, 2008 at 1:47 pm
Thank you guys. I think, I will try to call from client. I develop in Delphi, but I've an application, what cannot communicate in Windows Server 2003 via THTTPRIO, and I can't find any solution.
Peter
July 17, 2008 at 12:10 am
noeld (7/16/2008)
But I would go even further. When you find yourself in this situation you should try to call the webservice from the "CLIENT" not on the server.
Fully agreed. If there's a choice in the matter, call the web services from somewhere other than the DB.
Sometimes you don't have a choice though. I have a situation at the moment where, due to insane security rules, the clients can't see the server with the web service, but the SQL Server can. Hence, using web service call in CLR.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2008 at 7:14 am
GilaMonster (7/17/2008)
...due to insane security rules...
Gail -- If it's any consolation, I am delighted to see I am not the only one. Think it's called "security theater"!
July 18, 2008 at 11:35 am
GilaMonster (7/17/2008)
noeld (7/16/2008)
But I would go even further. When you find yourself in this situation you should try to call the webservice from the "CLIENT" not on the server.Fully agreed. If there's a choice in the matter, call the web services from somewhere other than the DB.
Sometimes you don't have a choice though. I have a situation at the moment where, due to insane security rules, the clients can't see the server with the web service, but the SQL Server can. Hence, using web service call in CLR.
I feel your pain! I have been there too. I have to give the "right" choice first then you do whatever you "CAN" do. The world is not perfect. 🙂
* Noel
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply