With SQL Server 2005, can a C# stored procedure execute a SOAP call?

We have two remote data sources that we will access via a SOAP call.





But then we need to operate on the two sets (remove dups, etc.) and, ideally, return a SQL result set to the client app.





So questions:


* Can we make a SOAP call using C# from within SQL Server?


* Can SQL Server operate on the resulting data (an XML record)?


* Can SQL Server return the combined results as a standard results set?





Thank you

With SQL Server 2005, can a C# stored procedure execute a SOAP call?
Hi Scott, you may be trying to ask a question that is way above your head, and my head too, as I also have a similar question open that no one has dared answer yet for several days.





Since there is no good answer here either, after 2 days let me just "attempt" for an answer:





SOAP is used to communicate between the "webservice" (an application running at your remote site where your 2 sqlserver databases are) and "webservice client" (an application at your site). You don't care what language the remote "webservice" is written in. The "webservice client" at your site is perhaps written in C#. Whoever is administering the remote "webservice" will have to





i) add a new function to the application according to your specification to query the 2 sqlserver data bases (using SQL commands or running prewritten stored procedures) to cleanup duplicates;





ii) provide you with the new "function name" - aka "method" in oop jargon, and parameters it needs from you to call the function. He will typicaly provide this to you as a WSDL file. WSDL = Web Service Definition Language.





Since you are responsible for administering your site, you will need to write a "webservice client" or modify an existing one (written in C# ?) to call the function provided to you by the remote administrator. To make this call you will need to construct a SOAP message from your C# program - as I mentioned earlier SOAP is the standard for communication between "webservice" and "webservice clients". BTW, if you are writing a new client from scratch you may write it using any language you are comfortable with, as long as the language supports sockets, and you have a SOAP toolkit for that language. SOAP toolkit is a set of prewritten functions and APIs for your language, that makes it easy to construct SOAP messages; you do not want to waste time or introduce added complexities by attempting to construct SOAP messages from scratch. Sending a SOAP message is only half the story, your client application will also have to parse a SOAP message coming back from the remote "webservice"; again a SOAP toolkit comes in handy for doing this. Your remote "webservice" will return the SQL resultset enveloped in SOAP.





Now what? It depends on what you are trying to do. Do you have a third sqlserver database at you local site where you are trying to store clean data (no dups)? If so, your client application may write out a XML file with the clean recordsets, and a separate step from sqlserver will import records from the recordset. A better approach may be for the client application to directly invoke SQL commands to write the resultset to your sqlserver database.





Next question is, how will your client application be invoked to initiate the whole process? You decide. You could invoke it from command line, a GUI interface, or maybe even from your local sqlserver - I think it can invoke any program that can be invoked from a command line, you will need to check this.





Now here is the ultimate question you should ask - why are you using a "webservice" to access the remote databases? You could do a RPC to remote server, execute SQL to query the 2 databases and get the resulting recordset back in your local machine just like that. The only reason I can guess is that the remote server is behind a "firewall", and most administrators will feel oozy opening up their RPC port. Webservices are great, because they communicate over HTTP with SOAP.
Reply:hey bro i'm also new to sql srv 2005.......... i really donno about ur question........ but i have sent that to my pal whos knows it.......... i'll gt bk 2 u soon





siri_sangabodhi@yahoo.com