The element orders in schemas generated by WCF-SQL adapter are different for SQL 2012 and SQL 2014


Repro steps:

==================================

 

  1. Use the following sample SQL script to create sample tables on both SQL 2012 and SQL 2014.
  2. In VS, create a BizTalk project and use ‘Add Generated Items’ to generate schemas for Outbound operation->Tables->[dbo].[Employees]->Insert.

 

USE [BiztalkEducationDB]
GO

/****** Object:  Table [dbo].[Employees]    Script Date: 2015/2/25 上午 11:00:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Employees](
 [EmployeeID] [nvarchar](50) NOT NULL,
 [LastName] [nvarchar](20) NULL,
 [FirstName] [nvarchar](10) NULL,
 [Title] [nvarchar](30) NULL,
 [TitleOfCourtesy] [nvarchar](25) NULL,
 [BirthDate] [datetime] NULL,
 [HireDate] [datetime] NULL,
 [Address] [nvarchar](60) NULL,
 [City] [nvarchar](15) NULL,
 [Region] [nvarchar](15) NULL,
 [PostalCode] [nvarchar](10) NULL,
 [Country] [nvarchar](15) NULL,
 [HomePhone] [nvarchar](24) NULL,
 [Extension] [nvarchar](4) NULL,
 [ReportsTo] [nvarchar](50) NULL,
 [PhotoPath] [nvarchar](255) NULL,
 [CreateDate] [datetime] NULL CONSTRAINT [DF_Employees_CreateDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
 [EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

  

The schema element orders are different for the same table on difference SQL versions(2012 and 2014)

 

On SQL 2012, the order is the following which is the same as the column order in SQL management studio:

 

EmployeeID

LastName

FirstName

Title

TitleOfCourtesy

BirthDate

HireDate

Address

City

Region

PostalCode

Country

HomePhone

Extension

ReportsTo

PhotoPath

CreateDate

 

On SQL 2014,
the order is the following which seems to be disorganized:

 

CreateDate

Extension

BirthDate

FirstName

Country

EmployeeID

ReportsTo

City

PhotoPath

Region

HireDate

HomePhone

Title

Address

TitleOfCourtesy

PostalCode

LastName

 

Analysis:

==================================

  

The main concern is that the different schemas can cause incompatibility issues of BizTalk application when running against different SQL versions.

 

After research, we found the problem is caused by the following query executed by the WCF-SQL adapter code returns the column lists in different orders on SQL 2012 and 2014.
The result on SQL 2012 is expected. NOTE: The query doesn’t have an ‘Order By’.

 

SELECT clmns.name AS [Name], usrt.name AS [DataType],

SCHEMA_NAME(usrt.schema_id) AS DataTypeSchemaName,

usrt.is_assembly_type AS [IsAssemblyType],
clmns.is_identity AS
[IsIdentity],

 

ISNULL(baset.name, N'') AS [SystemType],

 

CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],

 

CAST(clmns.[precision] AS int) AS
[NumericPrecision], CAST(clmns.[scale] AS int) AS [NumericScale], clmns.is_nullable as [IsNullable],
clmns.is_computed as
[IsComputed], clmns.is_filestream
as [IsFileStream],

 

AT.assembly_qualified_name
AS AssemblyQualifiedName, defCst.definition AS
[DefaultValue]

 

FROM sys.columns as clmns LEFT OUTER JOIN sys.default_constraints defCst on
defCst.parent_object_id = clmns.object_id
and defCst.parent_column_id
= clmns.column_id
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id
= clmns.system_type_id
and baset.user_type_id
= baset.system_type_id
LEFT JOIN sys.assembly_types AT ON
AT.[name] =
usrt.name AND
AT.schema_id
= usrt.schema_id

 

WHERE clmns.object_id = (SELECT object_id FROM sys.objects o WHERE o.name=N'Employees' and SCHEMA_NAME(o.schema_id)=N'dbo')

 

 

Since the problem is caused by different behavior of SQL versions and BizTalk part code hasn't changed, we are still in the process of finding a appropriate solution on this issue.

 

Best regards,

WenJun Zhang


Comments (3)

  1. ErikEJ says:

    Correct solution is to never expect ordering, and add an ORDER BY clause

  2. Gigi Paul says:

    We are facing the same problem. The Schema is being generated with out the column order clause, which is causing a column sequence mismatch.

    Erik, this is generated by the biztalk adapter, hence there is no way to specify the order clause.

    Any  solutions by anybody?

    Thanks

  3. We now have a hotfix to correct the behavior. Please contact Microsoft Support to obtain the fix if you are facing the same issue.

Skip to main content