Bug or oversight? - or am I missing something

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

Bug or oversight? - or am I missing something

Postby BTP » Thu Feb 02, 2012 4:51 pm

Hi

I have at last managed to get a full license for the products and am now using them.

I have come across a bit of a problem today when deploying some changes to our live environment.

I added a new column to a table and inserted before the last 3 columns ie.


CREATE TABLE [dbo].[JobsArchive]
(
[Id] [int] NOT NULL,
[Description] [nvarchar] (max) COLLATE Latin1_General_CI_AS NULL,
[JobAssetSetId] [int] NOT NULL,
[Category] [nvarchar] (15) COLLATE Latin1_General_CI_AS NOT NULL,
[AccountCodeId] [int] NOT NULL,
[CostCentreId] [int] NULL,
[HealthandSafety] [bit] NOT NULL,
[ContractorId] [int] NULL,
[SpecId] [int] NULL,
[RaisedOn] [datetime] NOT NULL,
[Priority] [int] NOT NULL,
[TargetDate] [datetime] NULL,
[DefectId] [int] NULL,
[SafetyRequired] [bit] NOT NULL CONSTRAINT [DF_JobsArchive_SafetyRequired] DEFAULT ((0)),
[JobSafetySetId] [int] NULL,
[JobCPBSSafetyId] [int] NULL,
[IssuedDate] [datetime] NULL,
[ContractorComplete] [bit] NULL,
[ContractorCompDate] [datetime] NULL,
[FinanceComplete] [bit] NULL,
[FinanceCompDate] [datetime] NULL,
[FinanceComments] [nvarchar] (max) COLLATE Latin1_General_CI_AS NULL,
[InvoiceNo] [varchar] (20) COLLATE Latin1_General_CI_AS NULL,
[Active] [bit] NOT NULL CONSTRAINT [DF_JobsArchive_Active] DEFAULT ((1)),
[DateChanged] [datetime] NOT NULL,
[ChangedBy] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
)

Notice the InvoiceNo field

Compare shows no differences , yet if I look at the matching tables etc i see the target as

CREATE TABLE [dbo].[JobsArchive]
(
[Id] [int] NOT NULL,
[Description] [nvarchar] (max) COLLATE Latin1_General_CI_AS NULL,
[JobAssetSetId] [int] NOT NULL,
[Category] [nvarchar] (15) COLLATE Latin1_General_CI_AS NOT NULL,
[AccountCodeId] [int] NOT NULL,
[CostCentreId] [int] NULL,
[HealthandSafety] [bit] NOT NULL,
[ContractorId] [int] NULL,
[SpecId] [int] NULL,
[RaisedOn] [datetime] NOT NULL,
[Priority] [int] NOT NULL,
[TargetDate] [datetime] NULL,
[DefectId] [int] NULL,
[SafetyRequired] [bit] NOT NULL CONSTRAINT [DF_JobsArchive_SafetyRequired] DEFAULT ((0)),
[JobSafetySetId] [int] NULL,
[JobCPBSSafetyId] [int] NULL,
[IssuedDate] [datetime] NULL,
[ContractorComplete] [bit] NULL,
[ContractorCompDate] [datetime] NULL,
[FinanceComplete] [bit] NULL,
[FinanceCompDate] [datetime] NULL,
[FinanceComments] [nvarchar] (max) COLLATE Latin1_General_CI_AS NULL,
[Active] [bit] NOT NULL CONSTRAINT [DF_JobsArchive_Active] DEFAULT ((1)),
[DateChanged] [datetime] NOT NULL,
[ChangedBy] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL,
[InvoiceNo] [varchar] (20) COLLATE Latin1_General_CI_AS NULL
)
GO

You will notive InvoiceNo is placed at the end!! , it has to be before Active because there is a sproc that copies the Jobs table rows into the Archive using

INSERT dbo.JobsArchive
SELECT * ,
GETDATE() ,
@ChangedBy
FROM dbo.Jobs
WHERE Id = @Id

which now fails until i move the InvoiceNo field to the correct position.

Is this a bug or am i missing something. Why does SQL Compare not show it as a difference ?

P.S.

BTW Your forum is frustratingly slow!!!!!!![/img]
BTP
 
Posts: 2
Joined: Thu Feb 02, 2012 4:33 pm

Postby David Atkinson » Thu Feb 02, 2012 7:16 pm

Have you tried the "Force column order" option in Edit Project/Options/Behavior?

Let us know if this works for you.

Kind regards,

David Atkinson
Red Gate

PS Yes, the forum responsiveness can be quite temperamental! Sorry.
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby BTP » Fri Feb 03, 2012 9:58 am

Ah, found it...

Might i suggest that this should actually be a default.

I personally dont understand why you wouldnt want to preserve the order!

Forum got faster shortly after I posted :-)
Darren Lawrence
Developer
BTP
 
Posts: 2
Joined: Thu Feb 02, 2012 4:33 pm


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 1 guest