Generated script...#tmpErrors create table duplicate

Trikeb

The following script code is being generated from a compare between a script folder and a database. As you can see the default generated script code includes twice the "CREATE TABLE #tmpErrors (Error int)". The second one is causing the error.

What is the cause of this problem? Any workaround?

Thanks in advance,

Jonathan

/*
Script created by SQL Compare version 6.0.0 from Red Gate Software Ltd at 8/30/2007 10:58:01 AM
Run this script on [DATABASE NAME] to make it the same as [SCRIPT FOLDER]
Please back up your database before running this script
*/
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].[#tmpErrors]'
GO
CREATE TABLE [dbo].[#tmpErrors]
(
[Error] [int] NULL
)
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

Michelle T

The cause of the problem is that the script folder contains a CREATE statement for the table #tmpErrors.

This means that SQL Compare picks up that statement, assumes it's a necessary table for the database described by the script, and attempts to create it. But because the transaction glue specifies a table called #tmpErrors as well, it runs into problems.

Workarounds:

1) You can use the option 'Do not use transactions in SQL script' to disable the transaction glue, which will mean SQL Compare doesn't try to create a second #tmpErrors table (but will also mean that SQL Compare doesn't use transactions while synchronizing, and hence won't be able to roll back changes if there's a problem halfway through).

If you have #tmpErrors in the script folder because you used a SQL Compare synch script as the script database, use that option when creating the script for the script database to avoid getting #tmpErrors in there in the first place.

2) Find the CREATE TABLE #tmpErrors statement in your script folder and delete it. This will fix the problem.