Red Gate forums :: View topic - sql compare script statements not properly ordered
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

sql compare script statements not properly ordered

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
wojtekk



Joined: 20 Jan 2012
Posts: 1

PostPosted: Fri Jan 20, 2012 9:52 am    Post subject: sql compare script statements not properly ordered Reply with quote

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'.
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 940
Location: Red Gate Software

PostPosted: Mon Jan 23, 2012 12:21 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group