SQL Compare - Script fails

DaleDeWitt

I have a table that I am dropping columns from, then creating a view over that table with a name of a column that was dropped. SQL Compare chokes on it, saying the object already exists... Do I need to break up the scripts somehow ?? If so, I have many dependencies.... Please advise....

The code below.... There is a column dropped named VendorNonStockProgram, the code fails on the create of the new view named VendorNonStockProgram....


ALTER TABLE [dbo].[ItemMaster] ADD
[ItemMasterID] [int] NOT NULL IDENTITY(1, 1),
[PartTypeID] [tinyint] NOT NULL CONSTRAINT [DF_ItemMaster_PartTypeID] DEFAULT ((1)),
[PartDescription] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PartWeight] [decimal] (19, 5) NULL,
[MarketingDescription] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ItemMasterNote] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ChangeNote] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ItemMaster] DROP
COLUMN [EffectiveDate],
COLUMN [NetPrice],
COLUMN [ListPrice],
COLUMN [CoreCost],
COLUMN [PartDescriptionID],
COLUMN [VendorNonStockProgram]
GO
@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_rename N'[dbo].[ItemMaster].[PartWeightID]', N'SuperseddedByItemMasterID', N'COLUMN'
GO
@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ItemMaster] ALTER COLUMN [DataSupplierID] [smallint] NULL
ALTER TABLE [dbo].[ItemMaster] ALTER COLUMN [PicCodeSubID] [smallint] NULL
GO
@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_ItemMaster_1] on [dbo].[ItemMaster]'
GO
ALTER TABLE [dbo].[ItemMaster] ADD CONSTRAINT [PK_ItemMaster_1] PRIMARY KEY CLUSTERED ([ItemMasterID])
GO
@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_ItemMaster] on [dbo].[ItemMaster]'
GO
CREATE NONCLUSTERED INDEX [IX_ItemMaster] ON [dbo].[ItemMaster] ([VendorID]) INCLUDE ([CaseQuantity], [ChangeNote], [DataSupplierID], [IsStockedItem], [ItemMasterID], [ItemMasterNote], [MarketingDescription], [MCFACompanyCodeID], [MCFAServiceIndicatorID], [MinimumOrderQuantity], [ModifiedBy], [ModifiedDate], [PartDescription], [PartID], [PartTypeID], [PartWeight], [PicCodeSubID], [SuperseddedByItemMasterID])
GO
@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_ItemMaster_A4] on [dbo].[ItemMaster]'
GO
CREATE NONCLUSTERED INDEX [IX_ItemMaster_A4] ON [dbo].[ItemMaster] ([PartTypeID], [VendorID]) INCLUDE ([MCFACompanyCodeID], [PartID], [PicCodeSubID])
GO
@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_ItemMaster_A6] on [dbo].[ItemMaster]'
GO
CREATE NONCLUSTERED INDEX [IX_ItemMaster_A6] ON [dbo].[ItemMaster] ([PicCodeSubID]) INCLUDE ([ItemMasterID], [PartID], [VendorID])
GO
@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_ItemMaster_A3] on [dbo].[ItemMaster]'
GO
CREATE NONCLUSTERED INDEX [IX_ItemMaster_A3] ON [dbo].[ItemMaster] ([PartTypeID]) INCLUDE ([ItemMasterID])
GO
@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_ItemMaster_A5] on [dbo].[ItemMaster]'
GO
CREATE NONCLUSTERED INDEX [IX_ItemMaster_A5] ON [dbo].[ItemMaster] ([VendorID]) INCLUDE ([ItemMasterID], [ModifiedDate], [PartID])
GO
@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_ItemMaster_2] on [dbo].[ItemMaster]'
GO
CREATE NONCLUSTERED INDEX [IX_ItemMaster_2] ON [dbo].[ItemMaster] ([PartDescription])
GO
@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_ItemMaster_A1] on [dbo].[ItemMaster]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_ItemMaster_A1] ON [dbo].[ItemMaster] ([PartID], [VendorID])
GO
@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[VendorNonStockProgram]'
GO

CREATE VIEW [dbo].[VendorNonStockProgram]
AS
SELECT i.PartID
,i.VendorID
,i.PartWeight
,i.PartDescription
,i.MinimumOrderQuantity
,i.CaseQuantity
,i.IsStockedItem
,i.DataSupplierID
,i.MCFACompanyCodeID
,i.PicCodeSubID
,i.MCFAServiceIndicatorID
,i.ModifiedBy
,i.ModifiedDate
,i.ItemMasterID
FROM dbo.ItemMaster i WITH ( NOLOCK )
WHERE i.PartTypeID = 4
AND i.VendorID = 284
GO
@ERROR <> 0 SET NOEXEC ON
GO

Andrew P

Hello Dale,

Thanks for posting your question.

We will follow up with you via email to resolve this.

Warmly,
Andrew Pierce
Product Support Engineer
Redgate Software