none
SQL Data Adapter with Insert stored proc errors RRS feed

  • Question

  • My organization is just implementing Biztalk Server R2 Enterprise on Windows Server 2003 and we are having some issues.  We are trying to use the SQL Transport Schema Generation Wizard and am specifying that we use a stored proc.  The generate script button  generates the following 
    exec [CreateCriticalChange] @BranchId=" ", @CreatedBy=" ", @CriticalChangeCategory=" ", @CriticalChangeType=" ", @ExpirationDate=" ", @ExternalId=" ", @Notes=" ", @SourceSystem=" "

    however when selecting next I receive the following message "failed to execute sql statement, please ensure that the supplied syntax is correct. New transaction cannont enlist in the specified transaction coordinator".  I have tried the solution listed at http://support.microsoft.com/default.aspx/kb/917847 with no luck.  Any help is greatly appreciated!
    Adam
    Thursday, January 22, 2009 5:19 PM

All replies

  • You can try using DTCPing (http://www.microsoft.com/downloads/details.aspx?FamilyID=5e325025-4dcd-4658-a549-1d549ac17644&DisplayLang=en) to test connectivity to the DTC coordinator.
    What are the security settings for DTC? If you set it to "No Authentication", and "Allow Inbound" + "Allow Outbound", does that help?
    Thursday, January 22, 2009 6:18 PM
  • I have the security settings you recommended but still have no luck.  DTCPing stated the RPC test is successful.
    Adam
    Thursday, January 22, 2009 6:36 PM
  • If you are running a SQL select statement at the end of your stored proc, I would create an adapter schema through the wizard with just this select query first so you can get through the wizard initially. If not, create a dummy query with the same data types as what should be returned from the stored proc and use this. Then after you generate the schema you can modify it to instead call your stored proc. The SQL adapter wizard is not that user-friendly.

    Another option would be to install the latest Adapter Pack because it includes the WCF-SQL adapter which provides a much better user interface for calling SQL stored procedures and other database objects. I wrote a short blog post on the improvements with the WCF-SQL adapter here: http://msinnovations.spaces.live.com/blog/cns!62E68922E47BC425!533.entry.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    • Proposed as answer by edhickey Friday, January 30, 2009 12:11 AM
    Monday, January 26, 2009 3:39 PM
    Moderator