Red Gate forums :: View topic - Error in creating upgrade script
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

Error in creating upgrade script

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
m.vanderwilt



Joined: 21 Nov 2012
Posts: 4

PostPosted: Wed Nov 21, 2012 9:27 am    Post subject: Error in creating upgrade script Reply with quote

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

Original table code in the database:

Code:
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:
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?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6670

PostPosted: Fri Nov 23, 2012 3:41 pm    Post subject: Reply with quote

Are you using schema/owner mapping? Try using the default mapping and it may go away.
Back to top
View user's profile Send private message
m.vanderwilt



Joined: 21 Nov 2012
Posts: 4

PostPosted: Wed Nov 28, 2012 10:44 am    Post subject: Reply with quote

How can i do this with the commandline edition of SQLCompare?
Back to top
View user's profile Send private message
m.vanderwilt



Joined: 21 Nov 2012
Posts: 4

PostPosted: Wed Nov 28, 2012 11:43 am    Post subject: Reply with quote

Commandline Argument:

sqlcompare /argFile:"TestArgsFile.xml"


TestArgsFile:
Code:
<?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>
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6670

PostPosted: Mon Dec 03, 2012 10:47 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
m.vanderwilt



Joined: 21 Nov 2012
Posts: 4

PostPosted: Mon Dec 03, 2012 9:05 pm    Post subject: Reply with quote

Bummer. When will this be fixed? Smile
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group