Possible? SQL Svr obtaining data via SOAP/XML from another SQL Sv

Hello All,
My challenge is to create a process that will bring together data from two
SQL servers (one local, one remote through the internet). The task is to
create a Stored Procedure that will perform a relational query based on data
from the two databases and return the resulting record set to a Crystal
Report. The remote database is part of our company's CRM HOSTED solution
which provides data accessible via a SOAP/XML interface.
I have implemented solutions in the past using a SOAP/XML data feed, whereby
I used VBScript, MSXML, MSSOAP to connect, query, parse and insert data into
an SQL database. For this implementation, however, I want to avoid this
method at all costs since there would be a tremendous overhead of converting,
maintaining and updating dozens of Crystal Reports to this new environment.
Instead, I would like to be able to make an SQL Stored Procedure invoke the
SOAP call and, in turn, load and process the resulting XML dataset as though
it were an SQL Record Set. I have found technical leads supporting the theory
that I might be able to achieve this goal. The following are technical
"bread-crumbs" I found or technical areas I have investigated thus far:
(1) SOAP/XML can be delivered from an MSSQL server in several ways via "AS
XML" functionality provided in SELECT verb. I am hypothesizing that the CRM
can deliver the XML data per a direct SQL query through this method. The nut
to crack here is that our XML data would be delivered back to the caller
"shrink-wrapped" in a SOAP Envelope and thus there would be the task of
unwrapping the XML dataset from the SOAP envelope upon the return of data to
the caller.
(2) MSSQL provides a method via "OPENXML" to open and parse an XML source
file and associate data returned with SELECT / Relational logic. CAVEAT: I
have only seen the OPENXML deal with XML data stored in FILE resources...not
an XML object such as that found implemented in the MSXML object classes
which I have used in VBSCRIPT.
I am trying to efficiently architect a solution without having to develop
too many components and excessive rework of prior development. My goal is to
perform this data query task ALL within the MSSQL environment. Any direction
or validation is greatly appreciated.
Sincerely,
Karl Engvold
Website: http://www.mcint.net
In SQLServer 2005 we have Native Web Services Support. This allows one to expose a stored procedure as a web service. The stored procedcure could be written in CLR or T-SQL. We also generate WSDL on demand. The SOAP responses contain "rowsets" to captu
re the output of select statements. These rowsets use the diffgram format on the wire and as such can be mapped to datasets on the client. Using a VS>net proxy generator to generate the client code does this automatically for you.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||as mentioned earlier this fucntionality is available in SQL2005 Beta bits. If you can get a hold of these bits and .Net framewwork Beta1 you can do what I suggested.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...