Error in creating upgrade script

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

Error in creating upgrade script

Postby m.vanderwilt » Wed Nov 21, 2012 9:27 am

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

Original table code in the database:

Code: Select all
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

Code: Select all
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?
m.vanderwilt
 
Posts: 4
Joined: Wed Nov 21, 2012 9:22 am

Postby Brian Donahue » Fri Nov 23, 2012 3:41 pm

Are you using schema/owner mapping? Try using the default mapping and it may go away.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby m.vanderwilt » Wed Nov 28, 2012 10:44 am

How can i do this with the commandline edition of SQLCompare?
m.vanderwilt
 
Posts: 4
Joined: Wed Nov 21, 2012 9:22 am

Postby m.vanderwilt » Wed Nov 28, 2012 11:43 am

Commandline Argument:

sqlcompare /argFile:\"TestArgsFile.xml\"


TestArgsFile:
Code: Select all
<?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>
m.vanderwilt
 
Posts: 4
Joined: Wed Nov 21, 2012 9:22 am

Postby Brian Donahue » Mon Dec 03, 2012 10:47 am

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.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby m.vanderwilt » Mon Dec 03, 2012 9:05 pm

Bummer. When will this be fixed? :-)
m.vanderwilt
 
Posts: 4
Joined: Wed Nov 21, 2012 9:22 am


Return to SQL Compare 10

Who is online

Users browsing this forum: SteveGTR and 0 guests