Error in creating upgrade script

m.vanderwilt

I'm getting an invalid upgrade script when letting SQLCompare generate this table:

Original table code in the database:

CREATE TABLE [Dts].[ProcessInterference](
	[ProcessCode] [nvarchar](50) NOT NULL,
	[ProcessCode2] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](100) NOT NULL,
	[AltKey]  AS (case when [ProcessCode]<[ProcessCode2] then concat([ProcessCode],';',[ProcessCode2]) else concat([ProcessCode2],';',[ProcessCode]) end),
 CONSTRAINT [pk_ProcessInterference] PRIMARY KEY CLUSTERED 
(
	[ProcessCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [ProcessInterference_AltKey] UNIQUE NONCLUSTERED 
(
	[AltKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Code resulting in an error in the upgrade file generated by SQLCompare

CREATE TABLE [Dts].[ProcessInterference]
(
[ProcessCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ProcessCode2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AltKey] AS (case  when [ProcessCode]<[ProcessCode2] then [concat]([ProcessCode],';',[ProcessCode2]) else [concat]([ProcessCode2],';',[ProcessCode]) end)
)

The error occures in the [AltKey] computed column.

Running the create statement created by SQLCompare results in this error:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '('.

Both the source as target database are Microsoft SQL Server 2012.
We're using SQL Compare 10.2.3.1

How can i resolve this?

Brian Donahue

Are you using schema/owner mapping? Try using the default mapping and it may go away.

m.vanderwilt

How can i do this with the commandline edition of SQLCompare?

m.vanderwilt

Commandline Argument:

sqlcompare /argFile:"TestArgsFile.xml"


TestArgsFile:

<?xml version="1.0"?>
<commandline>
	<sourcecontrol1></sourcecontrol1>
	<revision1>HEAD</revision1>
	<server2>s2L</server2>		
	<database2>d2</database2> 	
	<username2>u2</username2>
	<password2> p2 </password2>
	
	<scriptsfolderxml>SourceScriptsArgs.sfx</scriptsfolderxml>
	<mfx>SourceMigrationScriptsArgs.mfx</mfx>
	<scriptFile>c:\temp\TestBuild\build.sql</scriptFile>
	
	<options>none</options>
	
	<include>Assembly</include>
	
	<include>AsymmetricKey</include>
	<include>Certificate</include>
	<include>Contract</include>
	<include>DdlTrigger</include>
	<include>EventNotification</include>
	<include>FullTextCatalog</include>
	<include>FullTextStoplist</include>
	
	<include>Function</include>
	
	<include>MessageType</include>
	<include>PartitionFunction</include>
	<include>Queue</include>
	<include>Role</include>
	<include>Route</include>
	<include>Rule</include>
	
	<include>Schema</include>
	
	<include>Service</include>
	<include>ServiceBinding</include>
	<include>StoredProcedure</include>
	<include>SymmetricKey</include>
	<include>Synonym</include>
	
	<include>Table</include>
	
	<include>User</include>
	<include>UserDefinedType</include>

	<include>View</include>	
	
	<include>XmlSchemaCollection</include>

    
	
	
	
	<!-- Exclude because of SQL Compare + SQL 2012 function bug -->
	<!--
	<exclude>View:\[Dts\]\.\[vw_Block_Job_Process\]</exclude>
	<exclude>View:\[Dts\]\.\[vw_Job_Failure_Info\]</exclude>
	<exclude>View:\[Dts\]\.\[vw_Job_Status\]</exclude>
	<exclude>View:\[Dts\]\.\[vw_Process_SwitchTable_Info\]</exclude>
	
	<exclude>StoredProcedure:\[System\]\.\[usp_SetSwitchTrigger\]</exclude>
	<exclude>StoredProcedure:\[Dts\]\.\[usp_GetJobStep\]</exclude>
	<exclude>StoredProcedure:\[Dts\]\.\[usp_SetJobStepStart\]</exclude>
	<exclude>StoredProcedure:.*\]\.\[usp_GetJobInfo.*</exclude>
	<exclude>StoredProcedure:\[Dts\]\.\[usp_GetPackage]</exclude>
	<exclude>StoredProcedure:\[Dts\]\.\[usp_StartFirstAvailableJobStep]</exclude>
	<exclude>StoredProcedure:\[System\]\.\[usp_GetDataChangeDate]</exclude>
	<exclude>StoredProcedure:\[System\]\.\[usp_CheckRelation]</exclude>
	<exclude>StoredProcedure:\[System\]\.\[usp_FireSwitchTriggers]</exclude>
	<exclude>StoredProcedure:\[System\]\.\[usp_SwitchOutPartition]</exclude>
	<exclude>StoredProcedure:\[System\]\.\[usp_SwitchInPartition]</exclude>
	
	<exclude>StoredProcedure:\[System\]\.\[usp_CheckConsistencyOfSwitchTables]</exclude>
	


	
	<exclude>Table:\[System\]\.\[SwitchTrigger\]</exclude>
	<exclude>Table:\[System\]\.\[SwitchTriggerStatus\]</exclude>
	<exclude>Table:\[System\]\.\[TableUsage\]</exclude>
	-->
	
	<!--
	<exclude>Table:\[Dts\]\.\[ProcessInterference\]</exclude> -->	<!-- Regex to exclude [Dts].[ProcessInterference] table -->
	
	
	<exclude>Table:.*\].\[_.*</exclude> 						<!-- Regex to exclude temp table starting with _ -->
	
	<ignoreparsererrors />
	
	<!--
	
	<include>FullTextCatalog</include>
	
	<include>Schema</include>
	<include>StoredProcedure</include>
	
	<include>View</include>
	<include>identical</include>
	
	<exclude>StoredProcedure</exclude>
	<exclude>Assembly</exclude>
	-->
</commandline>

Brian Donahue

The problem is, SQL Compare is treating function names like object names, surrounding them by SQL Identifiers, ie [concat]. This is definitely a bug in SQL Compare, I was just trying to identify whether it is a new or existing bug. There is an existing bug where this happens only when you attempt to map schemas. So this looks like an entirely new bug.

m.vanderwilt

Bummer. When will this be fixed? :-)