none
SQL Adapter support for new types in SQL Server 2005 RRS feed

  • Question

  • Is the SQL Adapter in R2 enhanced/optimized for SQL Server 2005? Specifically, is the adapter metadata generation wizard able to work directly with CLR Stored Procs as well as receive the new XML datatypes into BizTalk R2?

     

    Thx,

    Coz

     

     

    Saturday, May 5, 2007 9:05 PM

Answers

  • No - the SQL Adapter is not different in R2 - it supports both SQL 2000 and 2005.  It doesn't support the SQL Server XML datatype.
    Saturday, May 26, 2007 2:45 PM
  • Hi,

     

    I've found the main limitation of the SQL adapter to be the schema generation wizard. With SQL 2005 it's possible to get more complex hirarchical XML back from SQL Server, but the SQL adapter wizard will not generate the correct schema for it. It is, however, possible to create the schema by hand:

     

    1 Run the required stored proc, and save the XML as a file (no need to use the xmldata option).

    2 Edit the XML to include a root node and target namespace

    3 Use the Generate Schema from Well Formed XML to create a schema from the file

    4 Use this schema for the return message from the SQL adapter

     

    When you configure the adapter, you specify the root node and target namespace, the adapter will insert the XML stream from the database in a root node with the specified name and namespace. As long as you have a schema matching it, you can get the message in BizTalk.

     

    Regards,

     

    Alan

     

    Monday, May 28, 2007 8:55 AM

All replies

  • No - the SQL Adapter is not different in R2 - it supports both SQL 2000 and 2005.  It doesn't support the SQL Server XML datatype.
    Saturday, May 26, 2007 2:45 PM
  • Hi,

     

    I've found the main limitation of the SQL adapter to be the schema generation wizard. With SQL 2005 it's possible to get more complex hirarchical XML back from SQL Server, but the SQL adapter wizard will not generate the correct schema for it. It is, however, possible to create the schema by hand:

     

    1 Run the required stored proc, and save the XML as a file (no need to use the xmldata option).

    2 Edit the XML to include a root node and target namespace

    3 Use the Generate Schema from Well Formed XML to create a schema from the file

    4 Use this schema for the return message from the SQL adapter

     

    When you configure the adapter, you specify the root node and target namespace, the adapter will insert the XML stream from the database in a root node with the specified name and namespace. As long as you have a schema matching it, you can get the message in BizTalk.

     

    Regards,

     

    Alan

     

    Monday, May 28, 2007 8:55 AM
  • Yes - that's a very useful  technique Alan - but have you made it work with the SQL 2005 XML data type?
    Monday, May 28, 2007 2:09 PM
  • Hi,

     

    Yes, it seems to work fine when receiving the XML data type.

     

    You need to specify a root node and target namespece in the adapter configuration (for receive, and solicit response), and the adapter will enclose the XML from the database in that tag. It's probably possible to use an envelope schema for this if you want to get just the XML document from the database, but I've not tried it.

     

    Regards,

     

    Alan

     

     

     

    Tuesday, May 29, 2007 7:37 AM
  • I think we could separate two things:
    1) what the SQL adapter does and
    2) what the SQLXML parser can do.
     
    I mean
    1) SQL adapter envelopes/deenvelopes the message to/from SQL
    2) using string to pass the data to/from the stored procedure (as we usualy do)
     
    "EXEC sp_xml_preparedocument @i OUTPUT, @Xml_Text, '<ns0:A6_DB xmlns:ns0=...
    with
    SELECT ...
    ...OPENXML(@i, '/ns0:...
     - to
    or
    SELECT ...
    ...for XML...
     - from
     
     There is nothing new about 1) with SQL 2005 and something new with 2)
     
    As we get the Xml inside stored procedure we can use all new stuff.
    Wednesday, May 30, 2007 4:09 PM
  • Hi Alan, Everyone

     

    This reply is rather late, but many Thanks for your help looking into this issue... I had resorted to HTTP EndPoints as a quick workaround and failed to continue checking this thread for answers. I thought I was to be notified when replies were posted, but then again...

     

    Thanks for the workaround Alan, I'll be putting this into use and testing very quickly. Funny thing though, even with using the new HTTP EndPoints within BizTalk, I had to wrap the received XML inside a root node and target namespace since the XML arrives as a collection of nodes.

     

    The BizTalk SQL Adapter obviously needs an upgrade to meet the evolution of the SQL Server. Perhaps we could undertake this as a side project...

     

    Thx,

     

    Coz   

    Wednesday, July 25, 2007 4:58 AM