Different results using command line tool

mhorn

Hi all,

comparing scripts vs scripts and scripts vs database using the command line tool ends up in different generated sync script files.
Actually the scripts vs scripts comparison result is erroneous: a primary key for a new created table is not included in to the sync script. References to the new created table fails.

Sample:
database version 1:

CREATE TABLE [dbo].[Detail]
(
[DetailID] [int] NOT NULL IDENTITY(1, 1),
[Column01] [varchar] (255) COLLATE Latin1_General_CI_AS NULL,
[Column02] [varchar] (128) COLLATE Latin1_General_CI_AS NULL
)
GO
ALTER TABLE [dbo].[Detail] ADD CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED  ([DetailID])
GO

database version 2:

CREATE TABLE [dbo].[Detail]
(
[DetailID] [int] NOT NULL IDENTITY(1, 1),
[MasterID] [int] NOT NULL,
[Column01] [varchar] (255) COLLATE Latin1_General_CI_AS NULL,
[Column02] [varchar] (128) COLLATE Latin1_General_CI_AS NULL
)
GO
ALTER TABLE [dbo].[Detail] ADD CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED  ([DetailID])
GO
ALTER TABLE [dbo].[Detail] ADD CONSTRAINT [FK_Detail_Master] FOREIGN KEY ([MasterID]) REFERENCES [dbo].[Master] ([MasterID])
GO

CREATE TABLE [dbo].[Master]
(
[MasterID] [int] NOT NULL IDENTITY(1, 1),
[Column01] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL
)
GO
ALTER TABLE [dbo].[Master] ADD CONSTRAINT [PK_Master] PRIMARY KEY CLUSTERED  ([MasterID])
GO

My command line calls:

"C:\Program Files\Red Gate\SQL Compare 8\SQLCompare.exe" /scripts1:C:\temp\sample_v2 /scripts2:C:\temp\sample_v1 /scriptfile:c:\temp\sample-script-to-script.sql /force /options:default
"C:\Program Files\Red Gate\SQL Compare 8\SQLCompare.exe" /scripts1:C:\temp\sample_v2 /server2:"127.0.0.1,1434" /database2:sample_v1 /scriptfile:c:\temp\sample-script-to-database.sql /force /options:default

I use the latest SQL Compare cumulative patch V8.1.2.54 with SQL Server 2005.

The resulting sync script files differ in size: 2KB vs 4KB.
Please contact me if i should send you those files.

Best Regards
Markus

Brian Donahue

Hi Markus,

Comparing using the example scripts that you have posted seems to work fine -- primary and foreign keys all get created. I don't know what the problem could be. If you load the scripts folders into the SQL Compare (UI) program and try to produce a synchronization script, do you get any warning messages?

mhorn

Hi Brian,

Using the UI I got two comprehensible warnings:

Severity: High
Object: Detail
Title: The column [MasterID] on table [dbo].[Detail] must be added but has no default and does not allow NULL values. The table must be rebuilt. If the table contains data then the migration script will not work. To avoid this, add a default to the column or mark it as allowing NULL values.
----------------------------------------------------
Severity: Medium
Object: Detail
Title: The column order in table [dbo].[Detail] has changed. The table must be rebuilt. The data in the table apart from dropped columns will be preserved.
----------------------------------------------------

The generated sync script by the UI comparing scripts folder vs scripts folder is the same as the generated sync script by the command line tool comparing scripts folder vs database.

We use the SQL Compare command line tool to generate delta scripts for our software product release in our release management system.
But sometimes the generated scripts are not usable as described in my first post.

Best Regards
Markus

Brian Donahue

Hi Markus,

Is the difference between the two migrations that one script is rebuilding the table and the other is not? You mentioned one script is 2KB and the other is 4KB.

mhorn

Hi Brian,

yes, rebuilding of tables (e.g. for applying new column positions) is missing.
But the main problem is the missing Primary Key for newly added tables.

Best Regards,
Markus

Brian Donahue

Hi Markus,

So far, this seems to be the problem:
SQL Compare needs to recreate the table when comparing a database to a script, but not when comparing a script to a script. This may be because you have partitioned data or some other thing that causes a rebuild only when you compare a live database. The reasons for table rebuilds are here: http://www.red-gate.com/supportcenter/C ... 000113.htm

When the rebuild is scripted, primary keys are not added back into the table.

Does that sound like an accurate description of the problem?

Brian Donahue

I think we're going to need the offending scripts. You can send them to support (at) red-gate.com and don't forget to quote reference number F0031661 (your call reference) in the subject line.

Brian Donahue

SQL Compare scripts a table rebuild when a new column is added to a table that does not allow nulls. This was not in the product documentation, so I have added it to the knowledge base.

That accounts for the explanation of why the table is being rebuilt, but unfortunately we still cannot reproduce the missing primary key issue, even using your scripts.