Linked Server - Collation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Linked Server - Collation

Author

Topic

dumbo
Starting Member

8 Posts

Posted - 2002-06-10 : 21:59:29

Hi orl

This works:
select FTH.*
FROm [COUNT].timectrl.dbo.FTHEADER FTH

but this doesn't
select FTH.*
FROm [COUNT].timectrl.dbo.FTHEADER FTH
where FTH.FTH_DESC like '%WIFA%'

Error returned is:
Server: Msg 8180, Level 16, State 1, Line 2
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 2
Line 1: Incorrect syntax near 'COLLATE'.

However, if the select is in a view, I can do a search using like - no problems.

big ears are better than none

JamesH
Posting Yak Master

149 Posts

Posted - 2002-06-10 : 22:32:16

Don't know your linked server setup but if you search on Collations.'in distributed queries' you might find your answer. It sounds to me, like you're querying a data source where SQL doesn't support or recognize the source's collation.

HTH,

JamesH.

dumbo
Starting Member

8 Posts

Posted - 2002-06-10 : 22:36:30

quote:

It sounds to me, like you're querying a data source where SQL doesn't support or recognize the source's collation.

Thanks James, but what does that mean? (PS the Linked Server is SQL 7 and "my" server is 2000)

big ears are better than none

Edited by - dumbo on 06/10/2002 22:41:50

JamesH
Posting Yak Master

149 Posts

Posted - 2002-06-11 : 17:37:41

I mean on One server you may have built it using the default collations and on the other, for example, you may have the server setup as Character set 850, binary sort order. In your linked server properties you should be able to specify the collation compatible but read up on it more first.

JamesH.

dumbo
Starting Member

8 Posts

Posted - 2002-06-11 : 23:46:42

Thanks James

both
select DATABASEPROPERTYEX( 'timectrl', 'Collation' )
and
select DATABASEPROPERTYEX( 'timectrl', 'SQLSortOrder')

return identical results on both servers.

Anyone else got any ideas?

big ears are better than none

JamesH
Posting Yak Master

149 Posts

Posted - 2002-06-12 : 10:09:03

I found a Q article that may(or may not) help you. I've tried to duplicate this but since I don't have your table script I'm really at a loss. Good Luck.

JamesH.

Q288411

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources