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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Karl Engvold (KarlEngvold_at_discussions.microsoft.com)
Date: 08/10/04

  • Next message: Papanii: "SQLXMLWeb service clients" 
    Date: Tue, 10 Aug 2004 12:27:04 -0700

    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

  • Next message: Papanii: "SQLXMLWeb service clients"

    Relevant Pages

    • Re: Annotated schema with views?
      ... Will SQL 2005 support SQLXML natively and will the generated queries use FOR ... XML PATH instead of the bulky FOR XML EXPLICIT. ... I'm running into limitations in terms of performace with my XSD Schema ... query elements in fragments and add children manually. ...
      (microsoft.public.sqlserver.xml)
    • Re: my first xml query
      ... I opened the sql pane and ran the xml.value query below and it worked. ... I ran the xml value query below and it worked. ... SELECT failed because the following SET options have incorrect ...
      (microsoft.public.sqlserver.xml)
    • Re: Is there a way to convert ms sql 2008 tables to XML files?
      ... After looking at the code I made it work, since I removed 2 nVARCHARcolumns from the SQL statement where it is used as an RTF. ... I have the 2 XML files the bad and the good which are below: ... Verdana;}{\f1\fnil\fcharset0 Microsoft Sans ... Here is the actual string copied from the ms sql 2008 which works fine; ...
      (microsoft.public.dotnet.framework.aspnet)
    • Re: Changing Query behavior based on local vs. remote context?
      ... Sounds like larger network packets are getting dropped. ... Senior SQL Infrastructure Consultant ... When I run queries against the installed instance I get different behavior depending on if the query is run from Management Studio locally or from SSMS running on a remote machine. ...
      (microsoft.public.sqlserver.security)
    • Re: how to know if query is processed in remote server
      ... One of the steps in execution plan shows "Remote Query". ... the data has been selected on remote server before it's pulled over to ... Links for SQL Server Books Online: ...
      (microsoft.public.sqlserver.programming)