Different collation on user db and SQL Server instance.

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?
AR

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
    Peter Peart
    Red Gate Software Ltd
    +44 (0)870 160 0037 ext. 8569
    1 866 RED GATE ext. 8569
  • 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
    AR
  • 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
    Peter Peart
    Red Gate Software Ltd
    +44 (0)870 160 0037 ext. 8569
    1 866 RED GATE ext. 8569
  • So according to you, #tmpErrors is the only real temporary table that is created during script-generation?!

    /Anders
    AR
  • 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
    Peter Peart
    Red Gate Software Ltd
    +44 (0)870 160 0037 ext. 8569
    1 866 RED GATE ext. 8569
  • Great, thanks!

    /Anders
    AR
Sign In or Register to comment.