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.
| Author |
Topic |
|
Tigger
Yak Posting Veteran
85 Posts |
 Posted - 2003-04-03 : 19:53:22
|
| Following on from my post "Cannot resolve collation conflict", does anyone have any ideas whyCREATE TABLE Foo1(nameCI varchar(30) COLLATE Latin1_General_CI_AS,nameCS varchar(30) COLLATE Latin1_General_CI_AS)works on DatabaseA but not on DatabaseB where I get Server: Msg 170, Level 15, State 1, Line 2Line 2: Incorrect syntax near 'COLLATE'.Both databases are on the same server, however DatabaseA has collation Latin1_General_CI_AS and DatabaseB has collation SQL_Latin1_General_CP1_CI_AS.(Changing the COLLATE statement to COLLATE SQL_Latin1_General_CP1_CI_ASgives the same result). |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-04-03 : 20:26:23
|
Check the compatibility level for the DatabaseBie:sp_dbcmptlevel 'DatabaseB' It will definately be set at 70 or less.To change the database to support COLLATE.sp_dbcmptlevel 'DatabaseB', 80 HTHDavidM"SQL-3 is an abomination.." |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-03 : 20:38:05
|
| Yes compatibility level! Why didn't I think of that? I only remembered to ask Tigger in the other post about what version SQL Server is but not what compatibility level the databases are set at.Tara |
|
|
|
Tigger
Yak Posting Veteran
85 Posts |
Posted - 2003-04-07 : 00:13:21
|
Problem solved - in a round about way.byrmol you were right about the compatability levels. Unfortunately DatabaseB is provided by an external company and so we can't/daren't fiddle with it.Thought about recreating DatabaseA with the same collation but then figured "Hey, if they've changed the collation for DatabaseB once - they'll do it again !!"Ended up changing the one table in DatabaseA to the same collation as DatabaseB and then using the COLLATE statement in DatabaseA to force load the data. All seems to be working happily now - until the collation changes again. But hey, next time I will know how to fix it.Thanks for your help guys !! You saved me from having to find out if Tigger's go grey  |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-04-07 : 07:00:12
|
| "Unfortunately DatabaseB is provided by an external company and so we can't/daren't fiddle with it."But I presume you informed your company accountant to discount their next invoice by an amount equal to the cost of the time you invested in resolving the problem caused by an unnotified/unanticipated change!!!! |
|
|
|
|
|
|
COLLATE