Error 446 - help!
-
Hi!
Whenever I want to add a new user to a database (any database) in SQL Server 2000, I get the error message: Error 446: Cannot resolve collation conflict for equal to operation. What does collation have to do with adding new users to a database? Especially since SQL Server has been "conducting" itself quite normally, and then all of a sudden - the error message. What is happening?
Thanks!
-
My first thought is that you have different collation of master and model/tempdb databases.
Can you run these 3 commands
select databasepropertyex('master','collation')
select databasepropertyex('model','collation')
select databasepropertyex('tempdb','collation')
and post results?
-
You are right, here are the results:
select databasepropertyex('master','collation')
- SQL_Latin1_General_CP1251_CS_AS
select databasepropertyex('model','collation')
- SQL_Latin1_General_CP1_CI_AS
select databasepropertyex('tempdb','collation')
-SQL_Latin1_General_CP1_CI_AS
But what does that mean? What exactly is collation? And what am I supposed to do?
Thanx
-
Basically collation is a set of information how character data is stored and treated in db. Most notably code page, sort order and case-sensitivity. You can find a lot of info on collation in SQL Server documentation.
As to your problem - your system databases use different code page (1251x1252) and case-sensitivity (CSxCI) - that's the reason of the error you receive. First of all you need to find out how this layout came into existence and whether it has a purpose or not. My bet is that this is not by design but by some mistake. From my previous experience, this situation can be caused by using model db with different collation from master db's collation. Tempdb is created on every startup from model db settings, so you end up with collation resolution errors. Another way might be to explicitely issue ALTER DATABASE command to change model collation (but not 100% sure about it) with the same result.
If I were you, I would check collation of all user dbs present on the server and if it's the same as the master db, it's highly probable that there's a mistake involved.
To resolve the problem I would install sql server on a test machine (the same edition, the same service pack level, the same hot fixes) with the same collation as your master db has. Then I would replace model datafiles on the affected server by files from the test server (you need to stop both sql servers to do that and if you have some custom setting in model it'll be lost) and after startup master and tempdb should be on the same collation and your problem should go away.
I performed these steps some time ago on test machine and it worked ,but of course, consider everything very carefully before you start doing anything. Mainly bacause tempdb specific settings could be very important for your applications to run smoothly.
By the way how long have you been experiencing this behavior? Because with you current layout it could never work.
-
Well, there you have it - user dbs have the same collation as master. I would also say that there's some kind of mistake involved, because no one has issued an ALTER DATABASE command - that I know.
As for the period of time how long this problem exists - I have no idea. It used to work just fine. All I know is that all of a sudden I can't add a user. You wouldn't happen to know how could come to this problem in the first place? I mean, are there any known situations? (apart from issuing an ALTER DATABASE command)
What troubles me the most is the fact that when the server was installed, it was installed with default settings - and that includes collation. And by default settings, code page was not 1251 (cyrilic).
Thanks again, Martin.
-
I was wrong, Martin.
User dbs don't have the same collation as master db. master db's collation is SQL_Latin1_General_CP1251_CS_AS, and user dbs are CI.
I am totaly confused ...
-
I don't know any other causes of the problem but someone else here might do. On install, all system dbs have the same collation that you specify in setup, personally I would be very reluctant to get system dbs collation out of synch unless it was required by software vendor etc. If you perform periodic server settings documentation you might look into it and see if there's a change of system db settings.
You can still add users through Query Analyzer and stored procedures but I think that this problem needs to be tackled anyway.
-
Well, Martin, thank you VEEERYYY much! Your information has been most helpful. I'll look into the matter.
Thanks again!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply