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 |
|
malletts
Starting Member
16 Posts |
 Posted - 2003-12-16 : 09:45:09
|
| Hi,I'm getting a collation conflict in a stored procedure which uses creates a temporary table, inserts values into it and then does a join between the temporary table and a data table. The full error I am getting is:' Error setting subreport recordset. Cannot resolve collation conflict for = operation' -2147217900I've checked books online and know that in SQL Server 2000, it is possible to change the collation value at a database level, table level and server level (using serverproperty function)However in SQL Server 7.0, I get a syntax error when I try to put the (collate database_default) option into my create table script and also when I attempt to run the serverIs there no way to modify this without rebuilding the master database and how is it possible to find the current collation valueAny help gratefully appreciatedStephen |
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-12-16 : 10:43:39
|
Mallets,You ought to be aware that the your temporary tables are created in the tempdb database which has defaults based on the model database.Whatever database you're referring to when you mention the 'data table' the temp table is joining on, it has a different collation to the tempdb database.Select DatabasePropertyEX( 'tempdb' , 'Collation' ) will return the collation name of your tempdb database. Compare that to the collation order of your other database and you will find them to be different. You can change the collation of your database by reading up on the 'ALTER DATABASE' command which also allows you to modify the collation. But this will only affect tables created beyind this point. Any previous tables created with a different collation will retain the default collation of the database. If you are using SQL Server 2000, it's an easier task for you to ALTER individual columns to set the collation order but SQL Server 7 does not contain this feature. Your other option would have to involve generating the create statements for all your tables, dropping and recreating the database while ensuring that you use the correct collation!Good luck.________________Make love not war! |
 |
|
|
|
|
|
Collation error in SQL Server 7.0