What are the challenges you face when working across database platforms? Take the survey
Options

SQL Compare - Script fails

DaleDeWittDaleDeWitt Posts: 6
edited February 12, 2015 3:56PM in SQL Compare 11
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

Comments

  • Options
    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
    Andrew Pierce
    Technical Sales Engineer
    Redgate Software
Sign In or Register to comment.