none
Can't able to create SQL Schema from SP in sql server 2005 from BizTalk server 2010

    Question

  • hi,

    I got BizTalk server 2010 in win7 system with sql server 2008, i am trying to add a sql schema connecting to sql server 2005 from external server (not from my local machine) to my project. But iam constantly getting error

    "failed to execute SQL Statement. Please ensure that the supplied syntax is correct. New Transaction cannot enlist in the specified transaction coordinator."

    i have configured my DTC setting as well to allow the inbound and outbound message.

    Please help to solve this issue. 

    [note: I can create sql schema easily when my stored procedure is in my local system itself in sql server 2008]

    Thanks in advance..

    Friday, March 30, 2012 7:59 AM

Answers

  • Refer to the Troubleshooting Problems with MSDTC for help. You also need to configure the DTC on the SQL server machine too in addition to BizTalk Server Machine. Have you done that.


    Please mark the post answered your question as answer, and mark other helpful posts as helpful, it'll help other users who are visiting your thread for the similar problem, Regards -Rohit Sharma (http://rohitt-sharma.blogspot.com/)

    Friday, March 30, 2012 8:06 AM
    Moderator

All replies

  • Hi Bala

    Can you please check whether the DTC setting is also correct on the SQL side? If yes, then you can use the DTCPing to check the DTC communication between the SQL and BizTalk Server.

    Thanks

    Deepak Sanghi


    Deepak Sanghi Happy Biztalking.........

    Friday, March 30, 2012 8:04 AM
  • Refer to the Troubleshooting Problems with MSDTC for help. You also need to configure the DTC on the SQL server machine too in addition to BizTalk Server Machine. Have you done that.


    Please mark the post answered your question as answer, and mark other helpful posts as helpful, it'll help other users who are visiting your thread for the similar problem, Regards -Rohit Sharma (http://rohitt-sharma.blogspot.com/)

    Friday, March 30, 2012 8:06 AM
    Moderator
  • Hi Bala,

    Are you using any of the following data types in SQL table or procedure,

    varbinary(MAX) data type, varchar(MAX) data type, nvarchar(MAX) data type, xml data type

    if Yes i would suggest you to look at the link.

    This reports specific issue with sql server data types in sql 2005.

     http://www.biztalkgurus.com/biztalk_server/biztalk_2006/f/10/p/6035/16417.aspx

    Do not use the SQL adapter in BizTalk Server to insert data into the database table in above case. Additionally, do not use the SQL adapter in BizTalk Server to retrieve data from the database table for such case. Refer to MSDN link for confirmation


    Thanks With Regards,
    Shailesh Kawade
    MCTS BizTalk Server
    Please Mark This As Answer If This Helps You.
    http://shaileshbiztalk.blogspot.com/




    Friday, March 30, 2012 8:10 AM
  • As Rohit and Deepak suggested , Check your MSDTC settings on SQL Server.

    Make sure your BizTalk Admin has rights on SQL server. And MSDTC has "no authentication" checked in the security settings

    There is a technet wiki here, it will help you while resolving msdtc issues


    HTH,
    Naushad Alam

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer
    alamnaushad.wordpress.com
    My new TechNet Wiki "BizTalk Server: Performance Tuning & Optimization"

    Friday, March 30, 2012 8:31 AM
    Moderator
  • hi Deepak,

    I did configured the DTC in SQL side as well. But DTCping is returning following message

    "Invoking RPC method on ps01db02

    Problem:fail to invoke remote RPC method
    Error(0x6BA) at dtcping.cpp @303
    -->RPC pinging exception
    -->1722(The RPC server is unavailable.)
    RPC test failed11"

    But with the same configuration i was able to create sql schema in BizTalk server 2009 from my Windows XP box in the past as well as now.

    Friday, March 30, 2012 8:50 AM
  • hi shailesh,

    NO, i don't have any of the datatype which you specified above. I had this kind of problem in past, but this time around, iam very sure that the datatype is not a trouble.

    Friday, March 30, 2012 8:53 AM
  • Please refer Troubleshooting "The RPC server is unavailable" for further troubleshooting options.

    You should resolve thsi error first before proceddign to create schema.


    HTH,
    Naushad Alam

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer
    alamnaushad.wordpress.com
    My new TechNet Wiki "BizTalk Server: Performance Tuning & Optimization"

    Friday, March 30, 2012 8:56 AM
    Moderator
  • hi all,

    Thanks for your guidance.. you are absolutely correct about the DTC setting. I configured the DTC setting in SQL as well as the BizTalk End, but it didnt work for me..

    I just tried to configure my DTC setting in my DNS server as well, and it did worked for me.

    Thanks once again..

    Friday, March 30, 2012 9:21 AM
  • Check your MS DTC settings match the following on both servers.

    If Windows Firewall is switched on switch it off on both servers and try DTCPing again.


    Colin Meade (MCTS BizTalk Server)


    • Edited by Colin Meade Friday, March 30, 2012 10:23 AM typo
    Friday, March 30, 2012 10:23 AM