Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

The SQL Transport Schema Generation Wizard closes unexpectedly in BizTalk Server 2006 or in BizTalk Server 2004


View products that this article applies to.

Symptoms

In Microsoft BizTalk Server 2006 or in Microsoft BizTalk Server 2004, the SQL Transport Schema Generation Wizard closes unexpectedly when you try to generate a schema for a SQL adapter send port. This problem occurs if you try to generate a schema based off a database table in Microsoft SQL Server 2005 or in Microsoft SQL Server 2000 when one of the following conditions is true:
  • The logged on user who runs the wizard is not the database owner.
  • One of the following updategrams to update a database table is being used:
    • An Insert updategram
    • An Update updategram
    • A Delete updategram
  • The SQL Adapter COM+ application is missing or is unregistered.
Additionally, you may receive an error message that resembles the following:
Failed to execute SQL Statement. Please ensure that the supplied syntax is correct. New transaction cannot enlist in the specified transaction coordinator.
You may receive this error message when you use the SQL Transport Schema Generation Wizard to query a database table in SQL Server 2005 or in SQL Server 2000.

↑ Back to the top


Cause

This problem occurs because one of the following conditions is true:
  • The SQL Server naming convention for database objects is as follows:
    [ServerName].[DatabaseName].[OwnerName].[ObjectName]
    By default, the value of ServerName is the current server, and the value of DatabaseName is the current database. By default, the value of OwnerName is the current user.
  • The database table contains a column that has one of the following data types:
    • The varbinary(MAX) data type
    • The varchar(MAX) data type
    • The nvarchar(MAX) data type
    • The xml data type
    These data types are not supported by the SQL adapter in BizTalk Server.
  • The Microsoft.BizTalk.Adapter.SQL COM+ application in Component Services becomes unregistered or is missing in Component Services.

↑ Back to the top


Resolution

Resolution 1

Make sure that the logged on user who runs the wizard has DBO access to the database.

Resolution 2

The data types that are listed in the "Cause" section are new data types in SQL Server 2005. When the database table contains a column that has one of these data types, do not use the SQL adapter in BizTalk Server to insert data into the database table. Additionally, do not use the SQL adapter in BizTalk Server to retrieve data from the database table.

Resolution 3

Reregister the Microsoft.BizTalk.Adapter.SQL COM+ application by using the Regsvcs.exe utility. On a computer that has Microsoft Visual Studio installed, follow these steps:
  1. Open the Visual Studio command window, and then locate the BizTalk installation folder.

    Note By default, BizTalk Server 2006 is installed in the C:\Program Files\Microsoft BizTalk Server 2006 folder. BizTalk Server 2004 is installed in the C:\Program Files\Microsoft BizTalk Server 2004 folder.
  2. Register the following DLLs. To do this, follow these steps:
    1. Type regsvcs Microsoft.BizTalk.Adapter.SQL.dll , and then press ENTER.
    2. Type regsvcs Microsoft.BizTalk.Adapter.SQLAdmin.dll, and then press ENTER.
    3. Type regasm /codebase Microsoft.BizTalk.Adapter.SQLAdmin.dll , and then press ENTER.
The Regsvcs.exe utility is located in the \WINNT\Microsoft.NET\Framework\Framework Version folder. For more information about Regsvcs.exe, refer to the Microsoft .NET Framework Software Development Kit (SDK) documentation or visit the following Microsoft Developer Network (MSDN) Web site:

↑ Back to the top


Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

↑ Back to the top


More information

For more information about data types in SQL Server 2005, visit the following Microsoft Developer Network (MSDN) Web site: For more information about support for BizTalk Server 2004 databases in SQL Server 2005, click the following article number to view the article in the Microsoft Knowledge Base:
915919� Microsoft SQL Server 2005 is not currently supported for Microsoft BizTalk Server 2004 databases

↑ Back to the top


Keywords: kbbtsadapters, kbbts, kbtshoot, kbprb, KB917847

↑ Back to the top

Article Info
Article ID : 917847
Revision : 4
Created on : 4/28/2008
Published on : 4/28/2008
Exists online : False
Views : 476