Options

Invalid script when using VARCHAR(MAX) different collation

PowlinPowlin Posts: 42 Bronze 3
edited November 12, 2009 5:44AM in SQL Data Compare Previous Versions
I try to migrate data from one database to another that is using 2 different collation. When the field is a varchar(MAX) the script is creating VARCHAR(-1) and fail to execute, I have to open the script and replace -1 by MAX for every field.

The problem seem to occur when the collation is different and the script need to cast it.

Here's is an example of what is produced:

DELETE FROM [dbo].[Profile] WHERE [NomUsager]=cast(N'poumar02' COLLATE French_CI_AS as varchar(20)) AND [LgnService]=cast(N',6549,7352,' COLLATE French_CI_AS as varchar(-1))

NomUsager : is a varchar(20)
LgnService : is a varchar(MAX)

I'm using SQL Data Compare 8.0.2.5
· Share on Twitter

Comments

  • Options
    Chris AucklandChris Auckland Posts: 769
    Thanks for your post.

    Can you try using the 'force binary collation' option, and see if that helps.

    If not, can you let me know which collations you have set for the source and target columns, and I'll try and recreate your issue.
    Chris
    · Share on Twitter
  • Options
    PowlinPowlin Posts: 42 Bronze 3
    Yes, but has it's a Comparaison behavior it doesn't change the script generated.
    · Share on Twitter
  • Options
    Chris AucklandChris Auckland Posts: 769
    Would you be able to give me the table structures for the source and target tables, and also let me know if you have used any WHERE clause in the comparison?

    Can you also let me know what you used for a comaprison key?
    Chris
    · Share on Twitter
  • Options
    PowlinPowlin Posts: 42 Bronze 3
    here's is the full detail from SQL Compare

    CREATE TABLE [dbo].[Profile]
    (
    [NomUsager] [varchar] (20) COLLATE French_CI_AS NOT NULL,
    [NomProfile] [varchar] (255) COLLATE French_CI_AS NOT NULL CONSTRAINT [DF__Profile__NomProf__4B7734FF] DEFAULT (''),
    [DateCreation] [datetime] NOT NULL CONSTRAINT [DF_Profile_DateCreation] DEFAULT (getdate()),
    [Periode] [int] NULL,
    [PeriodeDate] [datetime] NULL,
    [PeriodeDiff] [int] NULL CONSTRAINT [DF__Profile__Periode__3E1D39E1] DEFAULT ((0)),
    [PeriodeLength] [int] NULL CONSTRAINT [DF__Profile__Periode__47A6A41B] DEFAULT ((1)),
    [UseDateDebut] [bit] NULL CONSTRAINT [DF_Profile_UseDateDebut] DEFAULT ((1)),
    [UseDateFin] [bit] NULL CONSTRAINT [DF_Profile_UseDateFin] DEFAULT ((1)),
    [UseHeure] [bit] NULL CONSTRAINT [DF_Profile_UseHeure] DEFAULT ((0)),
    [HeureDebut] [datetime] NULL,
    [HeureFin] [datetime] NULL,
    [LgnService] [varchar] (max) COLLATE French_CI_AS NULL CONSTRAINT [DF__Profile__LgnServ__3C34F16F] DEFAULT (''),
    [NoLgnService] [bit] NULL CONSTRAINT [DF__Profile__NoLgnSe__3F115E1A] DEFAULT ((0)),
    [LgnRegion] [varchar] (max) COLLATE French_CI_AS NULL CONSTRAINT [DF__Profile__LgnRegi__3D2915A8] DEFAULT (''),
    [UseRegion] [bit] NULL CONSTRAINT [DF_Profile_UseRegion] DEFAULT ((0)),
    [TypeDemande] [varchar] (max) COLLATE French_CI_AS NULL CONSTRAINT [DF__Profile__TypeDem__43D61337] DEFAULT (''),
    [UseTypeDemande] [bit] NULL CONSTRAINT [DF_Profile_UseTypeDemande] DEFAULT ((0)),
    [Actif] [varchar] (max) COLLATE French_CI_AS NULL CONSTRAINT [DF__Profile__Actif__40F9A68C] DEFAULT (''),
    [UseActif] [bit] NULL CONSTRAINT [DF_Profile_UseActif] DEFAULT ((0)),
    [Priorite] [varchar] (max) COLLATE French_CI_AS NULL,
    [UsePriorite] [bit] NULL CONSTRAINT [DF_Profile_UsePriorite] DEFAULT ((0)),
    [LgnServiceEtat] [int] NULL,
    [IdTri] [varchar] (4) COLLATE French_CI_AS NULL,
    [NoClasse] [int] NULL,
    [UseNoClasse] [bit] NULL CONSTRAINT [DF_Profile_UseNoClasse] DEFAULT ((0)),
    [Palier] [varchar] (max) COLLATE French_CI_AS NULL CONSTRAINT [DF__Profile__Palier__40058253] DEFAULT (''),
    [UsePalier] [bit] NULL CONSTRAINT [DF_Profile_UsePalier] DEFAULT ((0)),
    [Descriptif] [varchar] (100) COLLATE French_CI_AS NULL CONSTRAINT [DF__Profile__Descrip__41EDCAC5] DEFAULT (''),
    [Top] [varchar] (20) COLLATE French_CI_AS NULL CONSTRAINT [DF__Profile__Top__42E1EEFE] DEFAULT (''),
    [Configuration] [int] NULL,
    [CleRapport] [varchar] (5) COLLATE French_CI_AS NULL,
    [TypeEtablissement] [varchar] (max) COLLATE French_CI_AS NULL CONSTRAINT [DF__Profile__TypeEta__44CA3770] DEFAULT (''),
    [NoEtablissement] [bit] NULL CONSTRAINT [DF__Profile__NoEtabl__46B27FE2] DEFAULT ((1)),
    [Ressource] [varchar] (max) COLLATE French_CI_AS NULL CONSTRAINT [DF__Profile__Ressour__45BE5BA9] DEFAULT (''),
    [NoRessource] [bit] NULL CONSTRAINT [DF__Profile__NoResso__498EEC8D] DEFAULT ((1)),
    [FiltreComparatif] [int] NULL,
    [UseFiltreComparatif] [bit] NULL CONSTRAINT [DF__Profile__UseFilt__4C6B5938] DEFAULT ((0)),
    [StatutDemande] [varchar] (1) COLLATE French_CI_AS NULL,
    [PeriodeGroup] [varchar] (1) COLLATE French_CI_AS NULL,
    [TriDecroissant] [bit] NULL CONSTRAINT [DF__Profile__TriDecr__4A8310C6] DEFAULT ((1)),
    [Publication] [varchar] (max) COLLATE French_CI_AS NULL,
    [Fournisseur] [varchar] (max) COLLATE French_CI_AS NULL,
    [UseFournisseur] [bit] NULL CONSTRAINT [DF_Profile_UseFiltreComparatif1] DEFAULT ((0)),
    [Etat] [varchar] (max) COLLATE French_CI_AS NULL,
    [EtatNotInclude] [bit] NULL CONSTRAINT [DF_Profile_EtatNotInclude] DEFAULT ((0)),
    [UseEtat] [bit] NULL CONSTRAINT [DF_Profile_UseEtat] DEFAULT ((0)),
    [RessourceCreator] [varchar] (max) COLLATE French_CI_AS NULL,
    [UseRessourceCreator] [bit] NULL CONSTRAINT [DF_Profile_UseFiltreComparatif1_1] DEFAULT ((0)),
    [NoParameter] [bit] NULL CONSTRAINT [DF_Profile_NoParameter] DEFAULT ((0)),
    [ValeurAxeGraphique] [varchar] (max) COLLATE French_CI_AS NULL,
    [Rapport] [varchar] (max) COLLATE French_CI_AS NULL,
    [UseRapport] [bit] NULL CONSTRAINT [DF_Profile_NoRapport] DEFAULT ((0)),
    [Utilisateur] [varchar] (max) COLLATE French_CI_AS NULL,
    [UseUtilisateur] [bit] NULL CONSTRAINT [DF_Profile_UseUtilisateur] DEFAULT ((0)),
    [TitreDynamique] [varchar] (max) COLLATE French_CI_AS NULL,
    [Organisme] [varchar] (max) COLLATE French_CI_AS NULL,
    [UseOrganisme] [bit] NULL CONSTRAINT [DF_Profile_UseOrganisme] DEFAULT ((0)),
    [Regroup] [varchar] (4) COLLATE French_CI_AS NULL CONSTRAINT [DF_Profile_Regroup] DEFAULT ('NONE'),
    [Call_IDList] [varchar] (max) COLLATE French_CI_AS NULL,
    [ClassifSpec] [varchar] (max) COLLATE French_CI_AS NULL,
    [UseClassifSpec] [bit] NULL CONSTRAINT [DF_Profile_UseOrganisme1] DEFAULT ((0)),
    [CodeFermeture] [varchar] (max) COLLATE French_CI_AS NULL,
    [UseCodeFermeture] [bit] NULL CONSTRAINT [DF_Profile_UseCodeFermeture] DEFAULT ((0)),
    [LigneServiceEscalade] [varchar] (max) COLLATE French_CI_AS NULL,
    [UseLigneServiceEscalade] [bit] NULL CONSTRAINT [DF_Profile_UseCodeFermeture1] DEFAULT ((0)),
    [FournisseurEscalade] [varchar] (max) COLLATE French_CI_AS NULL,
    [UseFournisseurEscalade] [bit] NULL CONSTRAINT [DF_Profile_UseLigneServiceEscalade1] DEFAULT ((0)),
    [Id_CritereDate] [char] (1) COLLATE French_CI_AS NULL,
    [Id_Etat] [char] (1) COLLATE French_CI_AS NULL,
    [Plage] [varchar] (max) COLLATE French_CI_AS NULL CONSTRAINT [DF_Profile_Palier1] DEFAULT (''),
    [UsePlage] [bit] NULL CONSTRAINT [DF_Profile_UsePalier1] DEFAULT ((0)),
    [Theme] [varchar] (5) COLLATE French_CI_AS NULL CONSTRAINT [DF_Profile_Theme] DEFAULT ('BLCL')
    )

    -- Columns

    CREATE TABLE [dbo].[Profile]
    (
    [NomUsager] [varchar] (20) COLLATE French_CI_AS NOT NULL,
    [NomProfile] [varchar] (255) COLLATE French_CI_AS NOT NULL CONSTRAINT [DF__Profile__NomProf__4B7734FF] DEFAULT (''),
    [DateCreation] [datetime] NOT NULL CONSTRAINT [DF_Profile_DateCreation] DEFAULT (getdate()),
    [Periode] [int] NULL,
    [PeriodeDate] [datetime] NULL,
    [PeriodeDiff] [int] NULL CONSTRAINT [DF__Profile__Periode__3E1D39E1] DEFAULT ((0)),
    [PeriodeLength] [int] NULL CONSTRAINT [DF__Profile__Periode__47A6A41B] DEFAULT ((1)),
    [UseDateDebut] [bit] NULL CONSTRAINT [DF_Profile_UseDateDebut] DEFAULT ((1)),
    [UseDateFin] [bit] NULL CONSTRAINT [DF_Profile_UseDateFin] DEFAULT ((1)),
    [UseHeure] [bit] NULL CONSTRAINT [DF_Profile_UseHeure] DEFAULT ((0)),
    [HeureDebut] [datetime] NULL,
    [HeureFin] [datetime] NULL,
    [LgnService] [varchar] (max) COLLATE French_CI_AS NULL CONSTRAINT [DF__Profile__LgnServ__3C34F16F] DEFAULT (''),
    [NoLgnService] [bit] NULL CONSTRAINT [DF__Profile__NoLgnSe__3F115E1A] DEFAULT ((0)),
    [LgnRegion] [varchar] (max) COLLATE French_CI_AS NULL CONSTRAINT [DF__Profile__LgnRegi__3D2915A8] DEFAULT (''),
    [UseRegion] [bit] NULL CONSTRAINT [DF_Profile_UseRegion] DEFAULT ((0)),
    [TypeDemande] [varchar] (max) COLLATE French_CI_AS NULL CONSTRAINT [DF__Profile__TypeDem__43D61337] DEFAULT (''),
    [UseTypeDemande] [bit] NULL CONSTRAINT [DF_Profile_UseTypeDemande] DEFAULT ((0)),
    [Actif] [varchar] (max) COLLATE French_CI_AS NULL CONSTRAINT [DF__Profile__Actif__40F9A68C] DEFAULT (''),
    [UseActif] [bit] NULL CONSTRAINT [DF_Profile_UseActif] DEFAULT ((0)),
    [Priorite] [varchar] (max) COLLATE French_CI_AS NULL,
    [UsePriorite] [bit] NULL CONSTRAINT [DF_Profile_UsePriorite] DEFAULT ((0)),
    [LgnServiceEtat] [int] NULL,
    [IdTri] [varchar] (4) COLLATE French_CI_AS NULL,
    [NoClasse] [int] NULL,
    [UseNoClasse] [bit] NULL CONSTRAINT [DF_Profile_UseNoClasse] DEFAULT ((0)),
    [Palier] [varchar] (max) COLLATE French_CI_AS NULL CONSTRAINT [DF__Profile__Palier__40058253] DEFAULT (''),
    [UsePalier] [bit] NULL CONSTRAINT [DF_Profile_UsePalier] DEFAULT ((0)),
    [Descriptif] [varchar] (100) COLLATE French_CI_AS NULL CONSTRAINT [DF__Profile__Descrip__41EDCAC5] DEFAULT (''),
    [Top] [varchar] (20) COLLATE French_CI_AS NULL CONSTRAINT [DF__Profile__Top__42E1EEFE] DEFAULT (''),
    [Configuration] [int] NULL,
    [CleRapport] [varchar] (5) COLLATE French_CI_AS NULL,
    [TypeEtablissement] [varchar] (max) COLLATE French_CI_AS NULL CONSTRAINT [DF__Profile__TypeEta__44CA3770] DEFAULT (''),
    [NoEtablissement] [bit] NULL CONSTRAINT [DF__Profile__NoEtabl__46B27FE2] DEFAULT ((1)),
    [Ressource] [varchar] (max) COLLATE French_CI_AS NULL CONSTRAINT [DF__Profile__Ressour__45BE5BA9] DEFAULT (''),
    [NoRessource] [bit] NULL CONSTRAINT [DF__Profile__NoResso__498EEC8D] DEFAULT ((1)),
    [FiltreComparatif] [int] NULL,
    [UseFiltreComparatif] [bit] NULL CONSTRAINT [DF__Profile__UseFilt__4C6B5938] DEFAULT ((0)),
    [StatutDemande] [varchar] (1) COLLATE French_CI_AS NULL,
    [PeriodeGroup] [varchar] (1) COLLATE French_CI_AS NULL,
    [TriDecroissant] [bit] NULL CONSTRAINT [DF__Profile__TriDecr__4A8310C6] DEFAULT ((1)),
    [Publication] [varchar] (max) COLLATE French_CI_AS NULL,
    [Fournisseur] [varchar] (max) COLLATE French_CI_AS NULL,
    [UseFournisseur] [bit] NULL CONSTRAINT [DF_Profile_UseFiltreComparatif1] DEFAULT ((0)),
    [Etat] [varchar] (max) COLLATE French_CI_AS NULL,
    [EtatNotInclude] [bit] NULL CONSTRAINT [DF_Profile_EtatNotInclude] DEFAULT ((0)),
    [UseEtat] [bit] NULL CONSTRAINT [DF_Profile_UseEtat] DEFAULT ((0)),
    [RessourceCreator] [varchar] (max) COLLATE French_CI_AS NULL,
    [UseRessourceCreator] [bit] NULL CONSTRAINT [DF_Profile_UseFiltreComparatif1_1] DEFAULT ((0)),
    [NoParameter] [bit] NULL CONSTRAINT [DF_Profile_NoParameter] DEFAULT ((0)),
    [ValeurAxeGraphique] [varchar] (max) COLLATE French_CI_AS NULL,
    [Rapport] [varchar] (max) COLLATE French_CI_AS NULL,
    [UseRapport] [bit] NULL CONSTRAINT [DF_Profile_NoRapport] DEFAULT ((0)),
    [Utilisateur] [varchar] (max) COLLATE French_CI_AS NULL,
    [UseUtilisateur] [bit] NULL CONSTRAINT [DF_Profile_UseUtilisateur] DEFAULT ((0)),
    [TitreDynamique] [varchar] (max) COLLATE French_CI_AS NULL,
    [Organisme] [varchar] (max) COLLATE French_CI_AS NULL,
    [UseOrganisme] [bit] NULL CONSTRAINT [DF_Profile_UseOrganisme] DEFAULT ((0)),
    [Regroup] [varchar] (4) COLLATE French_CI_AS NULL CONSTRAINT [DF_Profile_Regroup] DEFAULT ('NONE'),
    [Call_IDList] [varchar] (max) COLLATE French_CI_AS NULL,
    [ClassifSpec] [varchar] (max) COLLATE French_CI_AS NULL,
    [UseClassifSpec] [bit] NULL CONSTRAINT [DF_Profile_UseOrganisme1] DEFAULT ((0)),
    [CodeFermeture] [varchar] (max) COLLATE French_CI_AS NULL,
    [UseCodeFermeture] [bit] NULL CONSTRAINT [DF_Profile_UseCodeFermeture] DEFAULT ((0)),
    [LigneServiceEscalade] [varchar] (max) COLLATE French_CI_AS NULL,
    [UseLigneServiceEscalade] [bit] NULL CONSTRAINT [DF_Profile_UseCodeFermeture1] DEFAULT ((0)),
    [FournisseurEscalade] [varchar] (max) COLLATE French_CI_AS NULL,
    [UseFournisseurEscalade] [bit] NULL CONSTRAINT [DF_Profile_UseLigneServiceEscalade1] DEFAULT ((0)),
    [Id_CritereDate] [char] (1) COLLATE French_CI_AS NULL,
    [Id_Etat] [char] (1) COLLATE French_CI_AS NULL,
    [Plage] [varchar] (max) COLLATE French_CI_AS NULL CONSTRAINT [DF_Profile_Palier1] DEFAULT (''),
    [UsePlage] [bit] NULL CONSTRAINT [DF_Profile_UsePalier1] DEFAULT ((0)),
    [Theme] [varchar] (5) COLLATE French_CI_AS NULL CONSTRAINT [DF_Profile_Theme] DEFAULT ('BLCL')
    )

    Detail from SQL Data Compare

    Table & Views - Comparaison Key (Custom) : NomUsager, NomProfile
    · Share on Twitter
  • Options
    PowlinPowlin Posts: 42 Bronze 3
    -Here is a full row with all columns from the script

    DELETE FROM [dbo].[Profile] WHERE [NomUsager]=cast(N'pepjea01' COLLATE French_CI_AS as varchar(20)) AND [NomProfile]=cast(N'GI-008 - Moyenne du temps de traitement des requêtes (1.8.2)' COLLATE French_CI_AS as varchar(255)) AND [DateCreation]='2009-06-08 09:55:40.390' AND [Periode]=1 AND [PeriodeDate] IS NULL AND [PeriodeDiff]=1 AND [PeriodeLength]=1 AND [UseDateDebut]=1 AND [UseDateFin]=1 AND [UseHeure]=0 AND [HeureDebut] IS NULL AND [HeureFin] IS NULL AND [LgnService]=cast(N',5069,9664,5071,6579,6549,7352,5682,5106,6567,8246,7010,6235,5684,6234,6708,7638,5681,5766,5680,6581,5767,5073,8300,5070,6800,7322,6200,5778,9768,8200,5770,5769,5775,5776,6030,5777,5774,6106,5765,5779,9148,9002,5773,8090,6028,7743,6605,5768,5836,5771,9094,6294,6806,7857,9008,5780,7482,5772,8202,6508,5807,7087,5072,6852,6765,' COLLATE French_CI_AS as varchar(-1)) AND [NoLgnService]=0 AND [LgnRegion]=cast(N'' COLLATE French_CI_AS as varchar(-1)) AND [UseRegion]=0 AND [TypeDemande]=cast(N',5000,5103,5693,5694,' COLLATE French_CI_AS as varchar(-1)) AND [UseTypeDemande]=1 AND [Actif]=cast(N'' COLLATE French_CI_AS as varchar(-1)) AND [UseActif]=0 AND [Priorite]=cast(N'' COLLATE French_CI_AS as varchar(-1)) AND [UsePriorite]=0 AND [LgnServiceEtat]=0 AND [IdTri] IS NULL AND [NoClasse] IS NULL AND [UseNoClasse]=1 AND [Palier]=cast(N',1008,' COLLATE French_CI_AS as varchar(-1)) AND [UsePalier]=0 AND [Descriptif]=cast(N'' COLLATE French_CI_AS as varchar(100)) AND [Top]=cast(N'' COLLATE French_CI_AS as varchar(20)) AND [Configuration] IS NULL AND [CleRapport]=cast(N'182' COLLATE French_CI_AS as varchar(5)) AND [TypeEtablissement]=cast(N',1008,' COLLATE French_CI_AS as varchar(-1)) AND [NoEtablissement]=1 AND [Ressource]=cast(N'' COLLATE French_CI_AS as varchar(-1)) AND [NoRessource]=1 AND [FiltreComparatif] IS NULL AND [UseFiltreComparatif]=0 AND [StatutDemande]=cast(N'P' COLLATE French_CI_AS as varchar(1)) AND [PeriodeGroup] IS NULL AND [TriDecroissant]=0 AND [Publication]=cast(N',chkDate,chkLigneDeService,' COLLATE French_CI_AS as varchar(-1)) AND [Fournisseur]=cast(N',1008,' COLLATE French_CI_AS as varchar(-1)) AND [UseFournisseur]=0 AND [Etat]=cast(N'' COLLATE French_CI_AS as varchar(-1)) AND [EtatNotInclude]=0 AND [UseEtat]=0 AND [RessourceCreator]=cast(N'' COLLATE French_CI_AS as varchar(-1)) AND [UseRessourceCreator]=0 AND [NoParameter]=0 AND [ValeurAxeGraphique]=cast(N'' COLLATE French_CI_AS as varchar(-1)) AND [Rapport]=cast(N'' COLLATE French_CI_AS as varchar(-1)) AND [UseRapport]=0 AND [Utilisateur]=cast(N'' COLLATE French_CI_AS as varchar(-1)) AND [UseUtilisateur]=0 AND [TitreDynamique]=cast(N'' COLLATE French_CI_AS as varchar(-1)) AND [Organisme]=cast(N'' COLLATE French_CI_AS as varchar(-1)) AND [UseOrganisme]=0 AND [Regroup]=cast(N'NONE' COLLATE French_CI_AS as varchar(4)) AND [Call_IDList]=cast(N'' COLLATE French_CI_AS as varchar(-1)) AND [ClassifSpec]=cast(N'' COLLATE French_CI_AS as varchar(-1)) AND [UseClassifSpec]=0 AND [CodeFermeture]=cast(N'' COLLATE French_CI_AS as varchar(-1)) AND [UseCodeFermeture]=0 AND [LigneServiceEscalade]=cast(N'' COLLATE French_CI_AS as varchar(-1)) AND [UseLigneServiceEscalade]=0 AND [FournisseurEscalade]=cast(N'' COLLATE French_CI_AS as varchar(-1)) AND [UseFournisseurEscalade]=0 AND [Id_CritereDate] IS NULL AND [Id_Etat] IS NULL AND [Plage]=cast(N'' COLLATE French_CI_AS as varchar(-1)) AND [UsePlage]=0
    · Share on Twitter
  • Options
    Chris AucklandChris Auckland Posts: 769
    We now have a patch that fixes this:

    http://www.red-gate.com/messageboard/vi ... php?t=9880
    Chris
    · Share on Twitter
Sign In or Register to comment.