Incorrect syntax near ')'

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

Incorrect syntax near ')'

Postby kevinw » Fri May 17, 2013 1:18 pm

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
 
Posts: 3
Joined: Fri May 17, 2013 12:41 pm

Postby kevinw » Fri May 17, 2013 2:00 pm

Sorry. Ignore me. It was caused by a bad merge that removed the TransactionWeek column.
kevinw
 
Posts: 3
Joined: Fri May 17, 2013 12:41 pm

Postby Anu Deshpande » Mon May 20, 2013 9:51 am

We are glad that it is working now!
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests