| Author |
Message |
m.vanderwilt
Joined: 21 Nov 2012
Posts: 4
|
 Posted: Wed Nov 21, 2012 9:27 am Post subject: Error in creating upgrade script |
 |
|
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 |
|
 |
Brian Donahue
Joined: 23 Aug 2004
Posts: 6344
Location: Red Gate Software
|
| Posted: Fri Nov 23, 2012 3:41 pm Post subject: |
|
|
Are you using schema/owner mapping? Try using the default mapping and it may go away.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
m.vanderwilt
Joined: 21 Nov 2012
Posts: 4
|
| Posted: Wed Nov 28, 2012 10:44 am Post subject: |
|
|
| How can i do this with the commandline edition of SQLCompare? |
|
| Back to top |
|
|
m.vanderwilt
Joined: 21 Nov 2012
Posts: 4
|
| Posted: Wed Nov 28, 2012 11:43 am Post subject: |
|
|
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 |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6344
Location: Red Gate Software
|
| Posted: Mon Dec 03, 2012 10:47 am Post subject: |
|
|
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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
m.vanderwilt
Joined: 21 Nov 2012
Posts: 4
|
| Posted: Mon Dec 03, 2012 9:05 pm Post subject: |
|
|
Bummer. When will this be fixed?  |
|
| Back to top |
|
|
|
