Incorrect Synatax Error

Brian Donahue

Hi Grant,

SQL Data Compare isn't supposed to allow you to compare any tables which
don't have an identical primary key. In this case, though, it seems to have
made an exception. Since the primary key column on one side had a datatype
of [int] and [smallint] on the other side, Data Compare lets the table pass
when it shouldn't.

If you 'normalize' the tables so that the datatype of the primary key
column matches, then you can compare the data.

Regards,

Brian Donahue
Red Gate Technical Support



"Grant Fritchey" <grant.fritchey@fmglobal.com> wrote in message
news:SKu0mIjIEHA.1848@server53...
> While trying to run a compare using version 3.3.5.237, I get an error that
> states: Line 1: Incorrect syntax near 'B'.
>
> I narrowed it down to a particular table causing the error. I've included
> the script to create the table from the source and the target. Apart from
> the INT/SMALLINT and a missing FK constraint, I can't see why this error
> would occur.
>
> Any help?
>
> Grant
>
> SOURCE:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[StandardInsuranceFormUsage]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [StandardInsuranceFormUsage]
> GO
>
> CREATE TABLE [StandardInsuranceFormUsage] (
> [StandardInsuranceFormUsageId] [smallint] IDENTITY (1, 1) NOT NULL ,
> [PolicyInsurerTypeId] [smallint] NOT NULL ,
> [BeginDt] [smalldatetime] NOT NULL ,
> [UpdateDt] [datetime] NOT NULL ,
> [Row_Version] [timestamp] NOT NULL ,
> [StandardInsuranceFormTypeId] [smallint] NOT NULL ,
> [EndDt] [smalldatetime] NULL ,
> [SortSeq] [tinyint] NULL ,
> CONSTRAINT [PK_StandardInsuranceFormUsage] PRIMARY KEY NONCLUSTERED
> (
> [StandardInsuranceFormUsageId]
> ) ON [PRIMARY] ,
> CONSTRAINT [C_PolicyInsurerType_FK_StandardInsuranceFormUsage] FOREIGN
KEY
> (
> [PolicyInsurerTypeId]
> ) REFERENCES [C_PolicyInsurerType] (
> [PolicyInsurerTypeId]
> ),
> CONSTRAINT [C_StandardInsuranceFormType_FK_StandardInsuranceFormUsage]
> FOREIGN KEY
> (
> [StandardInsuranceFormTypeId]
> ) REFERENCES [C_StandardInsuranceFormType] (
> [StandardInsuranceFormTypeId]
> )
> ) ON [PRIMARY]
> GO
>
>
>
>
>
> TARGET:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[StandardInsuranceFormUsage]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [StandardInsuranceFormUsage]
> GO
>
> CREATE TABLE [StandardInsuranceFormUsage] (
> [StandardInsuranceFormUsageId] [int] IDENTITY (1, 1) NOT NULL ,
> [PolicyInsurerTypeId] [smallint] NOT NULL ,
> [BeginDt] [smalldatetime] NOT NULL ,
> [UpdateDt] [datetime] NOT NULL ,
> [Row_Version] [timestamp] NOT NULL ,
> [StandardInsuranceFormTypeId] [smallint] NOT NULL ,
> [EndDt] [smalldatetime] NULL ,
> [SortSeq] [tinyint] NULL ,
> CONSTRAINT [PK_StandardInsuranceFormUsage] PRIMARY KEY NONCLUSTERED
> (
> [StandardInsuranceFormUsageId]
> ) ON [PRIMARY] ,
> CONSTRAINT [C_PolicyInsurerType_FK_StandardInsuranceFormUsage] FOREIGN
KEY
> (
> [PolicyInsurerTypeId]
> ) REFERENCES [C_PolicyInsurerType] (
> [PolicyInsurerTypeId]
> )
> ) ON [PRIMARY]
> GO
>
>
>
>