sql compare script statements not properly ordered

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

sql compare script statements not properly ordered

Postby wojtekk » Fri Jan 20, 2012 9:52 am

I've changed table structure adding some fields and added some stored procedures using those fields. SQL compare genereted script includes statements for new stored procedures first and next for tables what ends with errors during execute. Is that behavior by design?

Here is example:

/*
Run this script on:

(local).srgmanagement - This database will be modified

to synchronize it with:

bfgsis.srgmanagement

You are recommended to back up your database before running this script

Script created by SQL Compare version 9.0.0 from Red Gate Software Ltd at 2012-01-20 08:28:50

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[insertExpectedBank]'
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[insertExpectedBank]
-- Add the parameters for the stored procedure here
@ident varchar(4),
@expectedDate DATETIME,
@dataDate DATETIME
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
declare @exec_rights varchar(10)
set @exec_rights=(select prawa from SRGmanagement..users where dbo.match(system_user,'\\w+\\\\')+[login]=system_user)
if not (@exec_rights='admin' or @exec_rights='edycja')
raiserror('Brak uprawnień!',11,1)

insert into SRGmanagement..expectedbank (ident,expecteddate,DataDate) values (@ident, @expectedDate, @dataDate)
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[removeExpectedBank]'
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[removeExpectedBank]
-- Add the parameters for the stored procedure here
@ident varchar(4)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
declare @exec_rights varchar(10)
set @exec_rights=(select prawa from SRGmanagement..users where dbo.match(system_user,'\\w+\\\\')+[login]=system_user)
if not (@exec_rights='admin' or @exec_rights='edycja')
raiserror('Brak uprawnień!',11,1)

-- usuwa bank z listy jesli nie ma dla niego pakietu
DELETE from SRGmanagement..expectedbank where ident LIKE @ident AND pakietid IS NULL
-- ukrywa bank jesli jest dla niego pakiet
UPDATE SRGmanagement..ExpectedBank SET hidden = 1 WHERE ident LIKE @ident AND pakietid IS NOT NULL
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[updateExpectedBankPakietId]'
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[updateExpectedBankPakietId]
-- Add the parameters for the stored procedure here
@packetId INT,
@ident varchar(4)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
declare @exec_rights varchar(10)
set @exec_rights=(select prawa from SRGmanagement..users where dbo.match(system_user,'\\w+\\\\')+[login]=system_user)
if not (@exec_rights='admin' or @exec_rights='edycja')
raiserror('Brak uprawnień!',11,1)


UPDATE SRGmanagement..ExpectedBank SET pakietId = @packetId WHERE Ident = @ident AND hidden = 0
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[ExpectedBank]'
GO
ALTER TABLE [dbo].[ExpectedBank] ADD
[DataDate] [datetime] NULL,
[pakietId] [int] NULL,
[hidden] [bit] NOT NULL CONSTRAINT [DF_ExpectedBank_hidden] DEFAULT ((0))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[dbInsertPacketQueue]'
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[dbInsertPacketQueue]
-- Add the parameters for the stored procedure here
@packetId int,
@path varchar(50),
@ident VARCHAR(50)
AS
BEGIN
declare @id int
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
declare @exec_rights varchar(10)
set @exec_rights=(select prawa from SRGmanagement..users where dbo.match(system_user,'\\w+\\\\')+[login]=system_user)
if not (@exec_rights='admin' or @exec_rights='edycja')
raiserror('Brak uprawnień!',11,1)

INSERT into PacketQueue (packetId,[path],ident,imported,packetStatusId)
values (@packetId,@path,@ident,0,6)
set @id=IDENT_CURRENT('PacketQueue')
return @id
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[errorNumbers]'
GO
ALTER TABLE [dbo].[errorNumbers] ADD
[tName] [varchar] (50) COLLATE Polish_CI_AS NULL,
[colName] [varchar] (50) COLLATE Polish_CI_AS NULL,
[col1Title] [varchar] (50) COLLATE Polish_CI_AS NULL,
[col2Title] [varchar] (50) COLLATE Polish_CI_AS NULL,
[spName] [varchar] (50) COLLATE Polish_CI_AS NULL,
[reportName] [varchar] (50) COLLATE Polish_CI_AS NULL,
[insErrorsSubName] [varchar] (50) COLLATE Polish_CI_AS NULL,
[dictName] [varchar] (50) COLLATE Polish_CI_AS NULL,
[aktywny] [bit] NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[SuspendedPacket]'
GO
CREATE TABLE [dbo].[SuspendedPacket]
(
[packetId] [int] NOT NULL,
[path] [varchar] (250) COLLATE Polish_CI_AS NOT NULL,
[ident] [varchar] (50) COLLATE Polish_CI_AS NOT NULL,
[imported] [bit] NOT NULL,
[packetStatusId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[PacketHistory]'
GO
ALTER TABLE [dbo].[PacketHistory] ADD
CONSTRAINT [FK_PacketHistory_PacketQueue] FOREIGN KEY ([packetId]) REFERENCES [dbo].[PacketQueue] ([packetId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering permissions on [dbo].[sp_log]'
GO
GRANT EXECUTE ON [dbo].[sp_log] TO [bfglocal\\srg]
GRANT EXECUTE ON [dbo].[sp_log] TO [srg]
GO
PRINT N'Altering permissions on [dbo].[dbInsertLog]'
GO
GRANT EXECUTE ON [dbo].[dbInsertLog] TO [bfglocal\\srg]
GRANT EXECUTE ON [dbo].[dbInsertLog] TO [srg]
GO
PRINT N'Altering permissions on [dbo].[insertBank]'
GO
GRANT EXECUTE ON [dbo].[insertBank] TO [srg]
GO
PRINT N'Altering permissions on [dbo].[dbInsertUser]'
GO
GRANT EXECUTE ON [dbo].[dbInsertUser] TO [srg]
GO
PRINT N'Altering permissions on [dbo].[dbUpdateUser]'
GO
GRANT EXECUTE ON [dbo].[dbUpdateUser] TO [srg]
GO
PRINT N'Altering permissions on [dbo].[dbUpdatePacketQueue]'
GO
GRANT EXECUTE ON [dbo].[dbUpdatePacketQueue] TO [bfglocal\\srg]
GRANT EXECUTE ON [dbo].[dbUpdatePacketQueue] TO [srg]
GO
PRINT N'Altering permissions on [dbo].[dbInsertPacketQueue]'
GO
GRANT EXECUTE ON [dbo].[dbInsertPacketQueue] TO [bfglocal\\srg]
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO


and execute result:

Creating [dbo].[insertExpectedBank]
Msg 207, Level 16, State 1, Procedure insertExpectedBank, Line 23
Invalid column name 'DataDate'.

(1 row(s) affected)
Creating [dbo].[removeExpectedBank]
Msg 207, Level 16, State 1, Procedure removeExpectedBank, Line 22
Invalid column name 'pakietid'.
Msg 207, Level 16, State 1, Procedure removeExpectedBank, Line 24
Invalid column name 'pakietid'.

(1 row(s) affected)
Creating [dbo].[updateExpectedBankPakietId]
Msg 207, Level 16, State 1, Procedure updateExpectedBankPakietId, Line 23
Invalid column name 'hidden'.
wojtekk
 
Posts: 1
Joined: Fri Jan 20, 2012 9:18 am

Postby eddie davis » Mon Jan 23, 2012 12:21 pm

Thank you for your post into the forum and sorry that you have encountered a problem.

SQL Compare normally should create the migration or synchronization script in dependency order. In this case it appears to not have done so.

A support call has been created for you, the call reference number is F0056529.

Can you please send an e-mail to support@red-gate.com, include the call reference number in the subject field of the e-mail and attach a SQL Compare snapshot of both the source and target data sources.

Using the snapshots hopefully we will be able to replicate your problem and offer a solution to it.

Many Thanks
Eddie
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 943
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests