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 |
|
rikleo2001
Posting Yak Master
185 Posts |
 Posted - 2006-03-21 : 21:45:22
|
| Hello Experts.I have a dataabse with Quite a few Stored procedures and complex queries which involes Temp tables (Creation and Utilizing) as well.It was working fine till last week, I re-built the server , Installed Sql Server with default settings with Serive Pack 3a.Now I am getting the error messages in some of my Queries (Mentioned in subject)Why is that? I checked the Collation (Tempdb and Master db has differant collation then in past, but they were still working) but now They stoped working now with that error.Any Ideas? without Installtion of the SQL Server 2000 again?Thanks is AdvanceSKR |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-22 : 01:56:50
|
| If your database and TEMPDB have different collation you will need to include a COLLATE statement (i.e. for the collation of your database) on any Varchar/Char/Text columns in all your CREATE TABLE statements [#Temp, @TableVar and permanent tables], and you will need to avoid usingSELECT * INTO #TempTable FROM MyTable(i.e. explicitly use CREATE TABLE first)This should mean that your database will work on any server with a different collation.The other reason this occurs is if you compare two columns from different databases which have different collations (or two columns that explicitly have different collations). You can get around this by forcing the CAST of the collation:WHERE MyColumn1 COLLATE SQL_Latin1_General_CP1_CI_AS = MyOtherColumn(Note that the COLLATE is only needed on one "side")Alternatively reinstall SQL Server and choose the collation that matches your databaseKristen |
 |
|
|
SqueakyPete
Starting Member
2 Posts |
Posted - 2006-03-22 : 07:32:44
|
| I've had the same problem in the past and would seriously recommend reinstalling before you get to the point where you can't go back. Save youself alot of hassle. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-22 : 07:43:05
|
| Second that.However, if there is any chance that your database/application might be hosted on someone else's server (either the client's or Shared Hosting etc.) in the future I would also recommend "best practice" of putting COLLATE statements in all your CREATE TABLE statements (and not using SELECT * INTO #TempTable FROM MyTable) to insulate you from the "maintenance-pain" in the future)Kristen |
|
|
|
rikleo2001
Posting Yak Master
185 Posts |
Posted - 2006-03-22 : 15:24:21
|
| Thank Guys..I also found out that the permanently fix is to Re-install SQL server with Custom installation, if you have more than 100 places to fix with Collate option.From now on I started using collate at the time of Temp tables creations.Thanks GuysSKR |
|
|
|
|
|
|
Cannot resolve collation conflict for equal to...