Comments
6 comments
-
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 -
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 -
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 -
So according to you, #tmpErrors is the only real temporary table that is created during script-generation?!
/Anders -
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 -
Great, thanks!
/Anders
Add comment
Please sign in to leave a comment.
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?