sql compare script statements not properly ordered

wojtekk

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
@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
@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
@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
@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
@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
@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
@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
@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
@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
@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
@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
@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
@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
@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
@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
@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
@TRANCOUNT&gt;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'.

Eddie D

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