Post reply

Cannot resolve the collation conflict

  • April 6, 2009 at 11:53 pm

    #384305

    Having a collation problem with an sql query in my database when running the following query. I'm pulling data from two databases

    /*The code below is a component of a much larger query that is used to pull performance data from the l;ive scom data base */

    use CMPDB_TST;

    select top 100 percent

    [PerformanceCounterId],

    [ObjectName],

    [CounterName],

    [ScaleFactor],

    [ScaleLegend]

    from [OperationsManager].dbo.PerformanceCounter (NOLOCK)

    where [ObjectName] + ' ' + [CounterName] in (Select distinct [ObjectName] + ' ' + [CounterName] from dbo.[DATA_FEED\SCOM\Tables\required counters])

    the error message that I get is

    Msg 468, Level 16, State 9, Line 2

    Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

    the collation property on the CMPDB_TST database has been set to Latin1_General_CI_AS but the collation property of the OperationsManager database is SQL_Latin1_General_CP1_CI_AS I have tried to fix the problem after much research on the net by trying to use the collate property in the order by clause of the select statement as shown below but still get the same error message short of changing the collation property of the captell database I am not sure as how to resolve the problem

    as from what I can understand it is possible to change the collation in the query somehow

    With required as

    (

    Select top 100 percent [ObjectName] + ' ' + [CounterName] as [requiredCounter]

    from dbo.[DATA_FEED\SCOM\Tables\required counters]

    )

    select top 100 percent

    [PerformanceCounterId],

    [ObjectName],

    [CounterName],

    [ScaleFactor],

    [ScaleLegend]

    from [OperationsManager].dbo.PerformanceCounter (NOLOCK)

    where [ObjectName] + ' ' + [CounterName] in (Select top 100 percent [requiredCounter] from required order by [requiredCounter] collate SQL_Latin1_General_CP1_CI_AS

    :crying:

  • April 7, 2009 at 12:08 am

    #973115

    ...

    from [OperationsManager].dbo.PerformanceCounter C

    where EXISTS (

    select 1 from from dbo.[DATA_FEED\SCOM\Tables\required counters] R

    where C.[ObjectName] = R.[ObjectName] COLLATE SQL_Latin1_General_CP1_CI_AS

    and C.[CounterName] = R.[CounterName] COLLATE SQL_Latin1_General_CP1_CI_AS

    )

    _____________
    Code for TallyGenerator

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply