Different collation on user db and SQL Server instance.

anders_rehnstrom

My user data base and SQL Server instance has different collation. Having different collation might cause problem when "combining" temporary tables (same collation as SQL Server instance) and user db tables (same collation as user db).
My question is: does the Redgate SQL Compare software (version 7) use temporary tables, which potentially could lead to problem in my case with different collation on user db. Or put another way: can I choose collation on my user db independently of the collation of the SQL Server instance?

peter.peart

Hi Anders,

Thanks for your post. SQL Compare will make use of temporary tables to hold data in whilst the tool performs schema changes.

You can try using the "ignore collation" option, however depending on which collations you are working with you may get conflicts if they are radically different.

There is however no way to choose the collation independently from the collation set wihtin SQL Server itself.

HTH!

Pete

anders_rehnstrom

We have noticed you (as SQL Server Management Studio) using TMP_XXX as temporary tables (that is ordinary tables within the database) and these are of no problem since they obey the collation of the current db.

In which cases do you create real temp tables reciding within tempdb using # or ## before the table name? (These are the objects of concern)

/Anders

peter.peart

Hi Anders,

Thanks for your reply. Based on the code SQL Compare is generating, I would say we are using a single hash:

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

HTH!

Pete

anders_rehnstrom

So according to you, #tmpErrors is the only real temporary table that is created during script-generation?!

/Anders

peter.peart

Hi Anders,

That's correct. When the tool creates temp tables to preserve data etc., these aren't actual proper "temp" tables. They are user tables with a naming convention based on the original table name, i.e. [dbo].[tmp_rg_xx_WidgetReferences].

The only use of a proper temp table is in for the rollback of errors, and that's preceded by a single hash.

If in doubt though, you can always check the script SQL Compare is generating prior to actually performing the sync.

Pete

anders_rehnstrom

Great, thanks!

/Anders