Options

Error in creating upgrade script

m.vanderwiltm.vanderwilt Posts: 5
edited December 3, 2012 3:05PM in SQL Compare Previous Versions
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?
· Share on Twitter

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Are you using schema/owner mapping? Try using the default mapping and it may go away.
    · Share on Twitter
  • Options
    m.vanderwiltm.vanderwilt Posts: 5
    How can i do this with the commandline edition of SQLCompare?
    · Share on Twitter
  • Options
    m.vanderwiltm.vanderwilt Posts: 5
    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>
    · Share on Twitter
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
    · Share on Twitter
  • Options
    m.vanderwiltm.vanderwilt Posts: 5
    Bummer. When will this be fixed? :-)
    · Share on Twitter
Sign In or Register to comment.