Collate Problem - Urgent
-
Greetings all.
I have a problem with collation.
The proc in question has been working fine for ages.
Suddenly, on a new server it throws an error. Cannot convert varchar bla bla bla.
The error happens when creating a temp table in temp db.
The collation on the main DB and Temp are the same.
I have commented out the part where it sets the db default on the temp table.
Have not yet heard if this works. Just hoping that someone has another solution I could use.
Ideas?
CREATE TABLE #Answers(
QID Int,
AID Varchar(50), -- COLLATE database_default,
ANS Varchar(1000), -- COLLATE database_default,
SortOrder Int Default(-1))
Cheers,
Crispin
Why don't you try practicing random acts of intelligence and senseless acts of self-control?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
You say "new server".....did you restore backups from the old server to the new one in your migration?
Are you sure the collation on the new server is the same as the collation on the old server?
-
no, they created the DB, tables and procs using a setup script which used to be used on the old server.
This script has also been used in the past without problems.
Waiting for reply on collation on new server. takes sbout an hour to get answers from them
.Will reply when they answer.
Why don't you try practicing random acts of intelligence and senseless acts of self-control?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
The script generated from the old server could contain the collation information (SQL 2K puts it in by default when scripting).
From EM, you can see the collation settings for the server by selecting the Server Properties. (Just in case you didnt know).
-
Not the problem. The script used was created by me a while ago. The same script has been used for a while now. No collation info in the script. It would use the DB default(?)
Why don't you try practicing random acts of intelligence and senseless acts of self-control?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Can you post a bit more info like the actual error message,code and the statement executing! Assume both(old,new) SQL 2000?
-
You might consider comparing the COLLATION_NAME on the MAIN DB with the COLLATION_NAME on the TEMP DB table for the column that is having the conversion problem. This way you can make sure both have the same collation settings.
Some thing like this:
select COLLATION_NAME
from main_db.information_schema.columns where
TABLE_NAME = 'problem_table'
and COLUMN_NAME = 'Problem_column'
and
select COLLATION_NAME
from tempdb.information_schema.columns where
TABLE_NAME like '#Answers%'
and (COLUMN_NAME = 'AID' or COLUMN_NAME =
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Is it possible that the model database has a different collation to that of the server instance. If it is then the tempdb collation will be rebuilt with the collation used by model whenever SQL Server is restarted. It is my experience that any newly created databases, by default, inherit the collation of the server instance NOT the collation used by model as stated in books online. This can cause collation conflicts in temp objects.
-
Could it be a conversion problem into DateTime? That you are converting VARCHAR data that contains a 'date' (from your Regional settings point of view) which cannot any longer be considered a date?
For example how should: 12/03/03 be interpreted? The third of december 2003? Or 12 of March 2003? If your regional settings has inserted the VARCHAR value of '12/03/03' and means 12 of March, what happens when SQL thinks it is 3 of December? What happens the 13th?
My server responds:
quote:
Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
But I do think I remember that MS SQL 7.0 gave me a much more obfuscated conversion error.
I know that this is a all to common problem to do but maybe that is not the issue here. Just a suggestion to look into.
Regards, H.Lindgren
-
Thanks all for the tips.
I finally got the collation from the client.
My DB has "SQL_Latin1_General_CP1_CI_AS"
Server (TempDB) has "Latin1_General_CI_AS"
Seems they are not compatible. Ideas why? Anyone got a chart of compatible collations?
Crispin
Why don't you try practicing random acts of intelligence and senseless acts of self-control?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
The collations you have on your client are the default installed in SQL Server setup. The problem you are experiencing (as you probably know) is that the server is configured differently.
If the server is SQL Server 2000 then you will need to add the collation information to the create statments for either the object or the database. If you are able to create the database that way then all subsequent objects will be created with that collation.
If the server is SQL Server 7.0 then I don't think you have any remedy as the default server collation is forced on all objects / databases (I think).
One thing to consider is that even if the server is SQL 2K you still may give your application fits if you change the collation on this object / database as they might have made some programming tweaks to rely on things such as case-sensitivity, etc.
I lived this nightmare for a while so I know your pain (inherited servers with odd collations). Hope this information helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
quote:
I lived this nightmare for a while so I know your pain (inherited servers with odd collations).It's the seconds time it's bitten me.
A way to get around it is when scripting the tables, set SQL 7.0 only.
This ignores the collation. When running the script, SQL uses the server/DB collation.
This does not have any affects on my app - yet...
Thanks for the info.
Crispin
Why don't you try practicing random acts of intelligence and senseless acts of self-control?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
this error occurs in my system when I compared between chars columns.
to solve the problem I converted this columns to binary...
Moshe.
-
I'm sure this is a common and easy problem to run into. In my experience it wouldn't be as much of a problem if it wasn't for the fact that SQL_Latin1_General_CP1_CI_AS is the default collation for a SQL 7 installation but the default collation for SQL 2K is Latin1_General_CI_AS. So if, for instance, you backup a database on SQL 7 and then restore on SQL 2K with these default collations on the respective installations you will have problems with anything that uses tempdb. This has certainly caused me a headache or 2 in the past! Why was the default collation changed from SQL 7 to 2000? Does anyone here know?
-
Moshe:
I don't think you solved it, you just avoided the error, how is the data now actually stored? How do you translate it back? Is the data even stored in Unicode? Otherwise; which code page has been used?
Happy bug searching! H.Lindgren
.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply