"Cannot resolve the collation conflict..." issue in the where clause

  • Here's my query.........I have dealt successfully with collating issues before but, they have been in the joins between tables. I don't know where to place the "collate database_default" in this query. Any thoughts?

    selectProvNPI,

    ProvMnem,

    concat('TW',PROVID)

    from [SH-SQL01].DataWarehouse.dbo.formatIDX_Providers

    where charindex('(',ProvLastName)=0

    and ProvNPI<>''

  • NineIron (7/18/2016)


    Here's my query.........I have dealt successfully with collating issues before but, they have been in the joins between tables. I don't know where to place the "collate database_default" in this query. Any thoughts?

    selectProvNPI,

    ProvMnem,

    concat('TW',PROVID)

    from [SH-SQL01].DataWarehouse.dbo.formatIDX_Providers

    where charindex('(',ProvLastName)=0

    and ProvNPI<>''

    If you remove the WHERE clause:

    select top 10 ProvNPI,

    ProvMnem,

    concat('TW',PROVID)

    from [SH-SQL01].DataWarehouse.dbo.formatIDX_Providers

    Will it work?

    _____________
    Code for TallyGenerator

  • Is formatIDX_Providers a table, if it were a view you could look at it's defintion

  • formatIDX_Providers is a view. If I run the query without the where clause, I still get the error.

  • If I run the query as below, I don't get the error.

    select *

    from [SH-SQL01].DataWarehouse.dbo.formatIDX_Providers

  • Rather than using a linked server, I ran the query directly on "SH-SQL01". I got this error. This server must be an earlier version. I'll use TW + PROVID, instead. Thanx for your help with this.

    Msg 195, Level 15, State 10, Line 3

    'concat' is not a recognized built-in function name.

  • You must use

    'TW' COLLATE {same collation as defined for PROVID}

    _____________
    Code for TallyGenerator

Viewing 7 posts - 1 through 6 (of 6 total)

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