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

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: 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.

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.

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:

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

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: