How can we help you today? How can we help you today?

Invalid script when using VARCHAR(MAX) different collation

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
Powlin
0

Comments

6 comments

Sort by
  • Chris Auckland
    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 Auckland
    0
  • Powlin
    Yes, but has it's a Comparaison behavior it doesn't change the script generated.
    Powlin
    0
  • Chris Auckland
    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 Auckland
    0
  • Powlin
    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
    Powlin
    0
  • Powlin
    -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
    Powlin
    0
  • Chris Auckland
    We now have a patch that fixes this:

    http://www.red-gate.com/messageboard/vi ... php?t=9880
    Chris Auckland
    0

Add comment

Please sign in to leave a comment.