coboljobs2003 (10/20/2009)
I am new to SQL server 2005 and I am trying to work on a project to invoke an existing webservice from SQL server 2005.(This webservice which is a .WSDL file is already being used by another Java application and this webservice connects to mainframe and brings data back from Mainframe).
Can anybody guide me (preferably step by step) how I would go about doing this.
Note: We do not have .net environment.
The absolute best method is to use a CLR function or procedure - see http://www.databasejournal.com/features/mssql/article.php/3821271/Calling-a-Web-Service-from-within-SQL-Server.htm for a good walkthrough.
If you insist on not using .NET (why?!) ...you'd end up doing it the SQL 2000 way: using sp_OACreate and the MSXML2.XMLHTTP library. There are examples all over Google, but here's one: http://blog.davebouwman.com/index.php/2008/07/call-geonames-web-services-from-a-sql-stored-procedure/
Though not relevant to your needs, I'm also going to link to Adam Machanic's interesting blog entry on validating URLs: http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/validate-a-url-from-sql-server.aspx
But please, especially as someone new to SQL Server, please, please, please don't go down this track (sp_OA methods). The way to do it is from a CLR component. If your boss won't let you use the correct tool, tell him it's impossible - you will save yourself considerable frustration and pain that way. Using sp_OA stuff to do this would be retarded masochistic in the extreme.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi