+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Problem with Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict

  1. Problem with Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict

    Hi,
    I'm unable to modify my view due to an error:

    Implicit conversion of varchar value to varchar cannot be performed because the collation of the
    value is unresolved due to a collation conflict

    Database colation is set to SQL_Latin1_General_CP1_CI_AS

    Here is my ddl:
    CREATE TABLE [dbo].[TB_INDUSTRIEKALENDER](

    [KALENDERDATUM] [datetime] NOT NULL,

    [KALENDERWOCHENTAG] [tinyint] NOT NULL,

    [KALENDERWOCHE] [tinyint] NOT NULL,

    [KALENDERJAHR] [int] NOT NULL,

    [KALENDERMONAT] [tinyint] NULL,

    [KALENDERTAG] [tinyint] NULL,

    [QUARTAL] [tinyint] NULL,

    [KALENDERWOCHENTAG_TEXT] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [KALENDERMONAT_TEXT] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ERSTELLTDATUMZEIT] [dbo].[T_SERVERDEFAULTDATUMZEIT] NULL CONSTRAINT
    [DF__TB_INDUST__ERSTE__38996AB5] DEFAULT (getdate()),

    [ERSTELLTBENUTZER] [dbo].[T_NAME] NULL CONSTRAINT [DF__TB_INDUST__ERSTE__092A4EB5] DEFAULT
    (suser_sname()),

    [GEAENDERTDATUMZEIT] [datetime] NULL,

    [GEAENDERTBENUTZER] [dbo].[T_NAME] NULL,

    [TIMESTAMP] [timestamp] NULL,

    CONSTRAINT [PK_TB_INDUSTRIEKALENDER] PRIMARY KEY CLUSTERED

    (

    [KALENDERDATUM] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

    CONSTRAINT [IX_TB_INDUSTRIEKALENDER] UNIQUE NONCLUSTERED

    (

    [KALENDERJAHR] ASC,

    [KALENDERWOCHE] ASC,

    [KALENDERWOCHENTAG] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]


    --
    The error occurs when I attempt to create this view:
    create View [dbo].[vw_Report]

    as

    SELECT ,[JAHRMONAT]

    ,[KALENDERJAHR]

    ,[KALENDERMONAT]

    ,[KALENDERMONAT_TEXT]

    FROM [dbo].[vw_AdressReport1]

    UNION ALL

    SELECT ,JAHRMONAT

    ,[KALENDERJAHR]

    ,[KALENDERMONAT]

    ,[KALENDERMONAT_TEXT]

    FROM [dbo].[vw_AdressReport2]

    The error refers to column [KALENDERMONAT_TEXT] in the view above.

    ----------------------------------------------------------

    DDL of [dbo].[vw_AdressReport1]

    ---------------------------------------------------------

    SELECT col1,

    KALENDERJAHR

    ,KALENDERMONAT

    ,KALENDERMONAT_TEXT

    ,MONATJAHR_TEXT

    FROM [dbo].[TB_BETRIEB_FACTS] BF

    Inner Join dbo.vw_KalenderJahrMonat KAL On

    BF.[JAHRMONAT] = KAL.JAHRMONAT

    ----------------------------------------

    DDL of vw_KalenderJahrMonat

    ---------------------------------------

    Create View [dbo].[vw_KalenderJahrMonat]

    as

    SELECT [KALENDERJAHR]

    ,[KALENDERMONAT]

    ,[KALENDERMONAT_TEXT]

    ,([KALENDERJAHR]*100) + [KALENDERMONAT] as JAHRMONAT

    ,Left(KALENDERMONAT_TEXT,3) + ' ' + convert(varchar,KALENDERJAHR) as MONATJAHR_TEXT

    ,Convert(datetime, convert(varchar,[KALENDERJAHR]) + Right('00'+convert(varchar,[KALENDERMONAT]),2)
    + '01') as MONATSERSTER

    FROM [dbo].[TB_INDUSTRIEKALENDER]

    Group by [KALENDERJAHR]

    ,[KALENDERMONAT]

    ,[KALENDERMONAT_TEXT]

    --------------------------------------------------

    DDL of [dbo].[vw_AdressReport2]

    --------------------------------------------------

    SELECT EREIGNISDATUM,

    KALENDERJAHR

    ,KALENDERMONAT

    ,KALENDERMONAT_TEXT

    ,MONATJAHR_TEXT

    FROM dbo.TB_FELDDATEN F

    Inner Join dbo.TB_FAHRZEUGMARKE M On

    Inner Join dbo.TB_INDUSTRIEKALENDER KAL On

    KALENDERDATUM = EREIGNISDATUM





    Anyone has any ideas on how to troubleshoot?



    Thanks in advance

    Bodo



  2. Re: Problem with Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict

    Bodo (1) writes:
    > I'm unable to modify my view due to an error:
    >
    > Implicit conversion of varchar value to varchar cannot be performed
    > because the collation of the value is unresolved due to a collation
    > conflict
    >
    > Database colation is set to SQL_Latin1_General_CP1_CI_AS
    >...
    > The error refers to column [KALENDERMONAT_TEXT] in the view above.


    In TB_INDUSTRIEKALENDER, the column with this name has the
    collation SQL_Latin1_General_CP1_CI_AS. But in the view:


    > DDL of [dbo].[vw_AdressReport1]
    > --------------------------------------------------
    >
    > SELECT col1,
    > KALENDERJAHR
    > ,KALENDERMONAT
    > ,KALENDERMONAT_TEXT
    > ,MONATJAHR_TEXT
    > FROM [dbo].[TB_BETRIEB_FACTS] BF
    > Inner Join dbo.vw_KalenderJahrMonat KAL On
    > BF.[JAHRMONAT] = KAL.JAHRMONAT


    KALENDERMONAT_TEXT comes from another table, where the collation
    apparently is a different one.

    You can find all columns that deviates from the database collation
    with this query:

    select o.name, c.name, c.collation_name
    from sys.columns c
    join sys.objects o on c.object_id = o.object_id
    where c.collation_name <>
    convert(nvarchar(100), databasepropertyex(db_name(), 'Collation'))
    and o.schema_id <> 4
    order by o.name, c.name



    --
    Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx


  3. Re: Problem with Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict

    Hi Erland,
    thanks for responding.

    I run your query posted and as a result column KALENDERMONAT_TEXT collation
    is Latin1_General_CI_AS whereas database and server collation is set to SQL_Latin1_General_CP1_CI_AS
    I checked view definition that has no explicit collation option assigned and in the table columns
    collation definition
    is set to database default.

    Appreciate any thoughts on how to troubleshoot.
    Thanks
    Bodo


    "Erland Sommarskog" schrieb im Newsbeitrag
    news:Xns9D79B9C0FE2E3Yazormanat127dot0.0.1...
    > Bodo (1) writes:
    >> I'm unable to modify my view due to an error:
    >>
    >> Implicit conversion of varchar value to varchar cannot be performed
    >> because the collation of the value is unresolved due to a collation
    >> conflict
    >>
    >> Database colation is set to SQL_Latin1_General_CP1_CI_AS
    >>...
    >> The error refers to column [KALENDERMONAT_TEXT] in the view above.

    >
    > In TB_INDUSTRIEKALENDER, the column with this name has the
    > collation SQL_Latin1_General_CP1_CI_AS. But in the view:
    >
    >
    >> DDL of [dbo].[vw_AdressReport1]
    >> --------------------------------------------------
    >>
    >> SELECT col1,
    >> KALENDERJAHR
    >> ,KALENDERMONAT
    >> ,KALENDERMONAT_TEXT
    >> ,MONATJAHR_TEXT
    >> FROM [dbo].[TB_BETRIEB_FACTS] BF
    >> Inner Join dbo.vw_KalenderJahrMonat KAL On
    >> BF.[JAHRMONAT] = KAL.JAHRMONAT

    >
    > KALENDERMONAT_TEXT comes from another table, where the collation
    > apparently is a different one.
    >
    > You can find all columns that deviates from the database collation
    > with this query:
    >
    > select o.name, c.name, c.collation_name
    > from sys.columns c
    > join sys.objects o on c.object_id = o.object_id
    > where c.collation_name <>
    > convert(nvarchar(100), databasepropertyex(db_name(), 'Collation'))
    > and o.schema_id <> 4
    > order by o.name, c.name
    >
    >
    >
    > --
    > Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse
    >
    > Links for SQL Server Books Online:
    > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    > SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx
    >




  4. Re: Problem with Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict

    Thanks John,
    yes that works very well.

    However in the production database there are more tables
    with inconsistent collation order.
    So I have to modify these columns first and then rebuild
    all depending views.

    Again many thanks for jumping in.

    Bodo

    "John Bell" schrieb im Newsbeitrag
    news:sju1v5tcckvcn4vu980j6mkpi0q6h0eokfat4axdotcom...
    > Hi Bodo
    >
    > Have you tried dropping and re-creating the view?
    >
    > John
    >
    > On Mon, 17 May 2010 09:28:11 +0200, "Bodo" <1> wrote:
    >
    >>Hi Erland,
    >>thanks for responding.
    >>
    >>I run your query posted and as a result column KALENDERMONAT_TEXT collation
    >>is Latin1_General_CI_AS whereas database and server collation is set to
    >>SQL_Latin1_General_CP1_CI_AS
    >>I checked view definition that has no explicit collation option assigned and in the table columns
    >>collation definition
    >>is set to database default.
    >>
    >>Appreciate any thoughts on how to troubleshoot.
    >>Thanks
    >>Bodo
    >>
    >>
    >>"Erland Sommarskog" schrieb im Newsbeitrag
    >>news:Xns9D79B9C0FE2E3Yazormanat127dot0.0.1...
    >>> Bodo (1) writes:
    >>>> I'm unable to modify my view due to an error:
    >>>>
    >>>> Implicit conversion of varchar value to varchar cannot be performed
    >>>> because the collation of the value is unresolved due to a collation
    >>>> conflict
    >>>>
    >>>> Database colation is set to SQL_Latin1_General_CP1_CI_AS
    >>>>...
    >>>> The error refers to column [KALENDERMONAT_TEXT] in the view above.
    >>>
    >>> In TB_INDUSTRIEKALENDER, the column with this name has the
    >>> collation SQL_Latin1_General_CP1_CI_AS. But in the view:
    >>>
    >>>
    >>>> DDL of [dbo].[vw_AdressReport1]
    >>>> --------------------------------------------------
    >>>>
    >>>> SELECT col1,
    >>>> KALENDERJAHR
    >>>> ,KALENDERMONAT
    >>>> ,KALENDERMONAT_TEXT
    >>>> ,MONATJAHR_TEXT
    >>>> FROM [dbo].[TB_BETRIEB_FACTS] BF
    >>>> Inner Join dbo.vw_KalenderJahrMonat KAL On
    >>>> BF.[JAHRMONAT] = KAL.JAHRMONAT
    >>>
    >>> KALENDERMONAT_TEXT comes from another table, where the collation
    >>> apparently is a different one.
    >>>
    >>> You can find all columns that deviates from the database collation
    >>> with this query:
    >>>
    >>> select o.name, c.name, c.collation_name
    >>> from sys.columns c
    >>> join sys.objects o on c.object_id = o.object_id
    >>> where c.collation_name <>
    >>> convert(nvarchar(100), databasepropertyex(db_name(), 'Collation'))
    >>> and o.schema_id <> 4
    >>> order by o.name, c.name
    >>>
    >>>
    >>>
    >>> --
    >>> Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse
    >>>
    >>> Links for SQL Server Books Online:
    >>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    >>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    >>> SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx
    >>>

    >>




  5. Re: Problem with Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict

    Bodo (1) writes:
    > Thanks John,
    > yes that works very well.
    >
    > However in the production database there are more tables
    > with inconsistent collation order.
    > So I have to modify these columns first and then rebuild
    > all depending views.
    >
    > Again many thanks for jumping in.


    Looks like you have a mess to sort out due to sloppy routines when
    installing SQL Server and creating databases. You should make sure that
    you and your organisation has a well-defined routine when installing
    SQL Server and which collation you should use. Else, you will have this
    mess again and again.

    The default collation when you install SQL Server is determined from your
    system locale. You get if SQL_Latin1_General_CP1_CI_AS if your system
    local is English (United States). If your system locale is German, you
    will get Latin1_General_CI_AS.

    I would also recommend that you use this latter collation, unless there is
    a conscious decision to use SQL collations.

    --
    Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

    Books Online for SQL Server 2005 at
    http://www.microsoft.com/technet/pro...ads/books.mspx
    Books Online for SQL Server 2000 at
    http://www.microsoft.com/sql/prodinf...ons/books.mspx

  6. Re: Problem with Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict


    "Erland Sommarskog" schrieb im Newsbeitrag
    news:Xns9D7B943EE4D81Yazormanat127dot0.0.1...
    > Bodo (1) writes:


    > Looks like you have a mess to sort out due to sloppy routines when
    > installing SQL Server and creating databases. You should make sure that
    > you and your organisation has a well-defined routine when installing
    > SQL Server and which collation you should use. Else, you will have this
    > mess again and again.
    >
    > The default collation when you install SQL Server is determined from your
    > system locale. You get if SQL_Latin1_General_CP1_CI_AS if your system
    > local is English (United States). If your system locale is German, you
    > will get Latin1_General_CI_AS.
    >
    > I would also recommend that you use this latter collation, unless there is
    > a conscious decision to use SQL collations.
    >
    > --
    > Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse


    Thanks Erland for your post and sample query on how to determine inconsistency
    in collation order.
    This was very valueable to find out the table columns affected.

    Unfortunately SQL Server Management Studio "supports" you with collation by
    adding the db default collation with each Create table statement that you can produce
    by right klick on a table ... script table to...
    e.g:
    CREATE TABLE [dbo].[TB_ADRESSBERICHT_BETRIEB](

    [SATZART] [varchar](1) COLLATE Latin1_General_CI_AS NOT NULL,

    [KALENDERJAHR] [int] NOT NULL,

    [KALENDERMONAT] [tinyint] NOT NULL,

    ....

    So there has been a few scripts in the past with explicit Collate specification that I give to my
    client.
    Later on I always removed that clause from each create statement.
    Now the production database defintion is consistent to my development system.

    Again many thanks to both of you!

    Best regards
    Bodo



  7. Re: Problem with Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict


    > Unfortunately SQL Server Management Studio "supports" you with collation
    > by
    > adding the db default collation with each Create table statement that you
    > can produce
    > by right klick on a table ... script table to...
    > e.g:
    > CREATE TABLE [dbo].[TB_ADRESSBERICHT_BETRIEB](
    >
    > [SATZART] [varchar](1) COLLATE Latin1_General_CI_AS NOT NULL,
    >
    > [KALENDERJAHR] [int] NOT NULL,
    >
    > [KALENDERMONAT] [tinyint] NOT NULL,
    >
    > ...
    >
    > So there has been a few scripts in the past with explicit Collate
    > specification that I give to my client.
    > Later on I always removed that clause from each create statement.
    > Now the production database defintion is consistent to my development
    > system.
    >
    > Again many thanks to both of you!
    >
    > Best regards
    > Bodo


    You can deactivate the option of scripting the collations: Tools | Options |
    SQL Server Object Explorer | Scripting | Include collation -> set to False.

    --
    Sylvain Lafontaine, ing.
    MVP - Windows Live Platform
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server
    (French)




  8. Re: Problem with Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict


    "Sylvain Lafontaine" schrieb im Newsbeitrag
    news:eFrcWte9KHA.1872atTK2MSFTNGP02dotphx.gbl...
    >
    >> Unfortunately SQL Server Management Studio "supports" you with collation by
    >> adding the db default collation with each Create table statement that you can produce
    >> by right klick on a table ... script table to...
    >> e.g:
    >> CREATE TABLE [dbo].[TB_ADRESSBERICHT_BETRIEB](
    >>
    >> [SATZART] [varchar](1) COLLATE Latin1_General_CI_AS NOT NULL,
    >>
    >> [KALENDERJAHR] [int] NOT NULL,
    >>
    >> [KALENDERMONAT] [tinyint] NOT NULL,
    >>
    >> ...
    >>
    >> So there has been a few scripts in the past with explicit Collate specification that I give to my
    >> client.
    >> Later on I always removed that clause from each create statement.
    >> Now the production database defintion is consistent to my development system.
    >>
    >> Again many thanks to both of you!
    >>
    >> Best regards
    >> Bodo

    >
    > You can deactivate the option of scripting the collations: Tools | Options | SQL Server Object
    > Explorer | Scripting | Include collation -> set to False.
    >
    > --
    > Sylvain Lafontaine, ing.
    > MVP - Windows Live Platform
    > Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    > Independent consultant and remote programming for Access and SQL-Server (French)
    >
    >
    >

    Thanks Sylvain,
    I thought there must be such option in SSMS, however I can't find this option in Tools-Options-SQL
    Server Object Explorer.
    I work with MS SSMS 9.0 (2005).

    Thanks
    Bodo



  9. Re: Problem with Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict

    I don't know for sure for SSMS 2005 but for SSMS 2008, make sure that you
    are looking under the Scripting node instead of the Commands node when
    expanding the SQL Server Object Explorer properties.

    It's the third option under the sub-node "Table and view options" (still for
    SSMS 2008).

    Also, you can install and use SSMS 2008 for working against SQL-Server 2005
    databases.

    --
    Sylvain Lafontaine, ing.
    MVP - Windows Live Platform
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server
    (French)


    "Bodo" <1> wrote in message news:Ohb9i2e9KHA.3880atTK2MSFTNGP04dotphx.gbl...
    >
    > "Sylvain Lafontaine" schrieb im
    > Newsbeitrag news:eFrcWte9KHA.1872atTK2MSFTNGP02dotphx.gbl...
    >>
    >>> Unfortunately SQL Server Management Studio "supports" you with collation
    >>> by
    >>> adding the db default collation with each Create table statement that
    >>> you can produce
    >>> by right klick on a table ... script table to...
    >>> e.g:
    >>> CREATE TABLE [dbo].[TB_ADRESSBERICHT_BETRIEB](
    >>>
    >>> [SATZART] [varchar](1) COLLATE Latin1_General_CI_AS NOT NULL,
    >>>
    >>> [KALENDERJAHR] [int] NOT NULL,
    >>>
    >>> [KALENDERMONAT] [tinyint] NOT NULL,
    >>>
    >>> ...
    >>>
    >>> So there has been a few scripts in the past with explicit Collate
    >>> specification that I give to my client.
    >>> Later on I always removed that clause from each create statement.
    >>> Now the production database defintion is consistent to my development
    >>> system.
    >>>
    >>> Again many thanks to both of you!
    >>>
    >>> Best regards
    >>> Bodo

    >>
    >> You can deactivate the option of scripting the collations: Tools |
    >> Options | SQL Server Object Explorer | Scripting | Include collation ->
    >> set to False.
    >>
    >> --
    >> Sylvain Lafontaine, ing.
    >> MVP - Windows Live Platform
    >> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    >> Independent consultant and remote programming for Access and SQL-Server
    >> (French)
    >>
    >>
    >>

    > Thanks Sylvain,
    > I thought there must be such option in SSMS, however I can't find this
    > option in Tools-Options-SQL Server Object Explorer.
    > I work with MS SSMS 9.0 (2005).
    >
    > Thanks
    > Bodo
    >




  10. Re: Problem with Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict

    Bodo (1) writes:
    > I thought there must be such option in SSMS, however I can't find this
    > option in Tools-Options-SQL Server Object Explorer.


    They are in the place that Sylvain described. However, the general
    Scripting options were added in Service Pack 2 of SQL 2005, so if you
    have failed to install any service pack, you don't have this option.

    I strongly recommend that you download and install the latest Service
    Pack, which is SP3 for SQL 2005.

    --
    Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx


+ Reply to Thread
Page 1 of 2 1 2 LastLast