none
Change destination column type from TEXT to XML? RRS feed

  • Question

  • Hi. A SQL Server 2000 system is being upgraded to SQL 2012. The current BizTalk integration inserts an XML document into a TEXT column in an "INQUEUE" table. The xml is then processed to load data into a database - I'm currently replacing this logic.  I want to replace the TEXT type with either a VARCHAR(max) or XML type.   I'm not a BizTalk developer.  Will the current BizTalk "adapter" - or whatever it is - fail if the type is changed to XML?  If I can use the XML type, I would not need to convert it myself.  Also, it might be nice if BizTalk gets the insert failure if the document is not valid XML.  (In one case, a special character was included in an element of the xml document sent from BizTalk.  SQL Server fails to convert it to xml.  I think it's better not to get a bad document and let the error be raised at the source; otherwise, we need a separate process to forward the error.) 

    Thanks


    Randy in Marin

    Thursday, March 19, 2015 10:08 PM

Answers

  • Since 2005?  Then it must be BizTalk Server 2004 which means the legacy SQL Adapter.

    That is significant leap in the database. You will have to test extensively.

    It may work ok of you don't change anything on either end, BizTalk or the database.

    The Adapter supports VARCHAR but the Schema in the BizTalk app would have to change, and that means recompile and redeploy.

    This is where I point out that BizTalk Server 2004 is long out of support and you should upgrade to BizTalk Server 2013 R2.

    Friday, March 20, 2015 1:25 AM
    Moderator

All replies

  • Without specifically testing this, I would vote yes, it will fail if you change just the data type on the column. The Schema is type aware and in practice, they have to match.

    You did not mention what version of BizTalk Server and which SQL Adapter you are using, legacy or WCF.

    Thursday, March 19, 2015 10:47 PM
    Moderator
  • Thanks.  I was afraid of that.  I will inquire re version and adapter.  I would not be surprised if it was legacy since it's been around since 2005.  Will the adapter have to be updated for a TEXT to VARCHAR(max) change as well?

    Randy in Marin

    Friday, March 20, 2015 12:35 AM
  • Since 2005?  Then it must be BizTalk Server 2004 which means the legacy SQL Adapter.

    That is significant leap in the database. You will have to test extensively.

    It may work ok of you don't change anything on either end, BizTalk or the database.

    The Adapter supports VARCHAR but the Schema in the BizTalk app would have to change, and that means recompile and redeploy.

    This is where I point out that BizTalk Server 2004 is long out of support and you should upgrade to BizTalk Server 2013 R2.

    Friday, March 20, 2015 1:25 AM
    Moderator
  • It actually depends on whether SQL will be able to typecast Text to Varchar(Max) or not.

    Because BizTalk will be agnostic of the sql side, it will simply send a XML file, later it all depends on SQL whether it will be able to accept the message or not.

    Also, BizTalk will definitely throw an error if it is not able to insert data in SQL due to any error.

    As John suggested, you will have to perform rigorous testing because jumping so many versions is really a big change :) 

     

    Thanks,
    Prashant
    ----------------------------------------
    Please mark this post accordingly if it answers your query or is helpful.

    Friday, March 20, 2015 3:43 PM
  • It was BizTalk 2004 at the start, but it was upgraded to BizTalk 2010.  It does use the legacy adapter now, but will be using WCF with the new table.  Will it make any difference (e.g., work and time) to the BizTalk team if we use varchar(max) vs xml types?  

    Randy in Marin

    Friday, March 20, 2015 4:20 PM
  • One of the BizTalk developers contacted me and gave me a link.  Because they are replacing the legacy adapter, they can change the type as well.  We will attempt to use the xml type for both inbound and outbound messages. 

    https://seroter.wordpress.com/2009/07/02/publishing-xml-content-from-sql-server-2008-to-biztalk-server-2009/

    Thanks.


    Randy in Marin

    Friday, March 20, 2015 5:45 PM
  • If they change the Adapter, they have to change the Schema.

    Since they're changing the Schema anyway, you can change the table in any way you want.

    If the content is Xml, they really should consider using the Xml data type.

    Friday, March 20, 2015 6:29 PM
    Moderator