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

Question

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

NineIron

SSChampion

Points: 12537
More actions
July 18, 2016 at 11:01 am

#309212

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<>''

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

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

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

terry999 SSCertifiable Points: 5036 More actions · Answer 2

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

NineIron SSChampion Points: 12537 More actions · Answer 3

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

NineIron SSChampion Points: 12537 More actions · Answer 4

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

select *

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

NineIron SSChampion Points: 12537 More actions · Answer 5

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.

Sergiy SSC Guru Points: 110208 More actions · Answer 6

You must use

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

_____________
Code for TallyGenerator