Incorrect syntax near ')'

kevinw

Hi,

I'm using SQL Compare 10.3.8.406. I'm comparing a TFS source controlled script folder to a database.

From the command line or via the UI it appears that SQL compare is not reading the content of a script file correctly resulting in a badly formed migration script.

The script file contains this :

CREATE TABLE [dbo].[CategorySales]
(
[CategorySalesId] [int] NOT NULL IDENTITY(1, 1),
[TransactionDate] [datetime] NOT NULL,
[LocationId] [int] NOT NULL,
[CategoryId] [int] NOT NULL,
[TransactionYear] [int] NOT NULL,
[TransactionMonth] [int] NOT NULL,
[TotalValue] [money] NOT NULL,
[TotalCost] [money] NOT NULL CONSTRAINT [DF_CategorySales_TotalCost] DEFAULT ((0)),
[TotalMargin] [money] NOT NULL CONSTRAINT [DF_CategorySales_TotalMargin] DEFAULT ((0)),
[SalesTax] [money] NOT NULL CONSTRAINT [DF_CategorySales_SalesTax] DEFAULT ((0))
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [CU_CategorySales_TransDateLocationIdCategoryId] ON [dbo].[CategorySales] ([TransactionDate], [LocationId], [CategoryId]) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_CategorySales_TransactionMonth] ON [dbo].[CategorySales] ([TransactionMonth]) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_CategorySales_TransactionWeek] ON [dbo].[CategorySales] ([TransactionWeek]) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_CategorySales_TransactionYear] ON [dbo].[CategorySales] ([TransactionYear]) ON [PRIMARY]

ALTER TABLE [dbo].[CategorySales] ADD CONSTRAINT [PK_CategorySales] PRIMARY KEY NONCLUSTERED ([CategorySalesId]) ON [PRIMARY]

GO

But when I point SQL compare to the script folder as the source and view the differences it appears like this :

-- Columns

CREATE TABLE [dbo].[CategorySales]
(
[CategorySalesId] [int] NOT NULL IDENTITY(1, 1),
[TransactionDate] [datetime] NOT NULL,
[LocationId] [int] NOT NULL,
[CategoryId] [int] NOT NULL,
[TransactionYear] [int] NOT NULL,
[TransactionMonth] [int] NOT NULL,
[TotalValue] [money] NOT NULL,
[TotalCost] [money] NOT NULL CONSTRAINT [DF_CategorySales_TotalCost] DEFAULT ((0)),
[TotalMargin] [money] NOT NULL CONSTRAINT [DF_CategorySales_TotalMargin] DEFAULT ((0)),
[SalesTax] [money] NOT NULL CONSTRAINT [DF_CategorySales_SalesTax] DEFAULT ((0))
)
GO
-- Constraints and Indexes

ALTER TABLE [dbo].[CategorySales] ADD CONSTRAINT [PK_CategorySales] PRIMARY KEY NONCLUSTERED ([CategorySalesId])
GO
CREATE NONCLUSTERED INDEX [IX_CategorySales_TransactionWeek] ON [dbo].[CategorySales])
GO
CREATE UNIQUE CLUSTERED INDEX [CU_CategorySales_TransDateLocationIdCategoryId] ON [dbo].[CategorySales] ([TransactionDate], [LocationId], [CategoryId])
GO
CREATE NONCLUSTERED INDEX [IX_CategorySales_TransactionMonth] ON [dbo].[CategorySales] ([TransactionMonth])
GO
CREATE NONCLUSTERED INDEX [IX_CategorySales_TransactionYear] ON [dbo].[CategorySales] ([TransactionYear])
GO

It's missed off some of the sql where it's creating the indexes. This results in the migration script having incorrect syntax.

It looks like a bug in SQL compare to me. How can I resolve this?

Thanks
Kevin

kevinw

Sorry. Ignore me. It was caused by a bad merge that removed the TransactionWeek column.

Anu D

We are glad that it is working now!