August 25, 2005 at 7:14 am
Hello,
Recently, after moving our database from a previous web host down to our own machine I have found that the 'default' collation they used is different to our server. In order to resolve this I (which I feel bad for, though time was pressed) tried to get around this by setting the columns that really needed it to the collation as required. I am now (as I expected) finding that the collation issues of 'cannot compare one to the other' scenario is happening more.
Finally getting some time to look into this a little more, I decied to run a ALTER DATABASE [dbname] COLLATE CollationRequired on a test database to see, firstly if it would work correctly, secondly to see how long it would take... However, instead I received an error
Server: Msg 5075, Level 16, State 1, Line 1
The column 'Table1.ColumnX' is dependent on database collation.
This error seems to have very little help, however this is not the strangest thing. This column is actually a computed column from two money values. Without the ability to physically set the collation on this column it appears as though this ALTER statement would not work.
The problem is that I don't have the luxury of having some offline time in order to DTS the information into a new DB with the correct collation. With the DB being in excess of 150GB, I could only assume this would take quite some time and quite some disk space to complete.
I am hoping that someone may be able to render some assistance or perhaps point me in the direction of a solution to this issue aside from simply setting the correct collation on each column that is required..
Thank you in advance for any assistance offered.
Anubis.
August 25, 2005 at 8:51 am
If the new server only hosts your db, the simplest way is to reinstall SQL server and resttach ur db.
August 25, 2005 at 12:56 pm
The error is very clear because SQL Server 2000 have column level collation and table X collation is in confilict because it is different so ALTER the table to the database collation then run the ALTER Database with the desired collation. Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
August 25, 2005 at 6:36 pm
Thank you for your welcome comments and suggestions thus far.
The strange part about this error is the fact that it's happening on computed columns. Just to eliminate the chance anyway, I ran ALTER TABLE Table1 ALTER COLUMN Col1 COLLATE CollationName, however this unfortunately made no difference.
I have done a little more searching with regards to collation on computed columns but alas, I have come up blank. It appears as though I'm not going to get around this one easily.
Thank you very much for your assistance.
Anubis.
August 25, 2005 at 6:47 pm
Thanks for adding the computed column info, so try the link below for the not easy solution from Microsoft. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_819v.asp
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
August 25, 2005 at 7:45 pm
Again, thank you for your assistance.
I attempted their coding:
ALTER TABLE Table1 ALTER COLUMN Col1
smallmoney COLLATE SQL_Latin1_General_CP1_CI_AS
But received this error:
Server: Msg 447, Level 16, State 1, Line 1
Expression type smallmoney is invalid for COLLATE clause.
As the column is computed, smallmoney is the actual data type used following the computation. Unfortunately, I don't think this is going to be an easy change over... looks like I'm going to have to go through each table and update the collations on each field and "try" not to forget to set it on any future columns...
Thanks again.
Anubis.
August 25, 2005 at 8:00 pm
If you read the info in the link you will see the following
"You cannot alter the collation of a column that is currently referenced by:
so your only option is at the bottom of that page moving all your data and rebuilding the Master database. I think you should read all the info in that page because all you need to change the collation is there. Hope this help.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
August 26, 2005 at 10:05 am
Hi,
yours seems to be another case of Collation Hell.
Changing the collation of a database does not change existing columns, it changes only the system tables, and sets the default for _new_ columns.
There are scripts here to drop all constraints, foreign keys etc., change the collation of the existing tables and recreate the dependencies, but on a db of the size we are talking about you don't want to do that; it simply takes to long...
I see only one way to resolve your problems: completely script your db, edit the script and remove all collations, add the wanted collation to the db-creation-script, create the db and transfer the data...
regards karl
PS: rebuilding master with a new collation changes the _servers_ collation, not that of the user db's
Best regards
karl
August 26, 2005 at 12:15 pm
I did not tell the person to just rebuild the Master, I also said the person should move the data before rebuilding the Master. And at this moment I think the person should just move the table with the computed column to a new database, drop the existing one in the database and try to change the collation again. I think that may be one short cut to the situation.
Kind regards,
Gift Peddie
August 26, 2005 at 12:26 pm
A computed column doesn't really exist. Try this: DROP the computed column. Change the collation and then ALTER the table to ADD the computed column back in.
Before anyone 'jumps' on my comment about computed columns not really existing...here's the quote from the BOL.
A computed column is a virtual column not physically stored in the table.
-SQLBill
August 26, 2005 at 12:39 pm
To complete >> it can be physically stord on disk if you index the column. But for 99% of the calculated columns, they are calculated on the fly when you request them.
August 26, 2005 at 12:41 pm
Here is another item of interest from BOL:
collation_name
Specifies the collation for the column. Collation name can be either a Windows collation name or a SQL collation name. The collation_name is applicable only for columns of the char, varchar, text, nchar, nvarchar, and ntext data types.
Notice that last line.......you are using SMALLMONEY, which isn't listed. Therefore, what appears to be happening is you are trying to set a collation on a computed column that can not accept ANY collation.
I still think your best solution is to drop the computed column, change the database collation, then add the column back in. (Make sure you know what the computation is before you drop the column).
-SQLBill
August 26, 2005 at 6:15 pm
Hello All,
Thank you very much for your valued comments.
With the changes I wanted to perform, it was only to change the collation on the user db. Not the server as the server is already on the collation we desire. The database is approx 150gb in size with many, many tables with collatable columns. I knew that collation on a column that had its collation set and not to <database default> would not alter (which is what I wanted also).
I am aware that collation cannot be set to smallmoney columns, which further adds to the confusion of why (I believe all) of the computed columns were causing a problem when attempting to alter the collation as a whole on the database.
If someone has a quick script that can generate a resultset to see what columns are using what collation type, this would help. If not, I'll 'try' and write one. Then for now, manually update the collations on the fields that are set to database default.
Hopefully, if I smile, ask nicely and say pretty please with a cherry on top, then I might get enough offline time to re-create and dts... but that's really hoping....
Thanks again guys!
Anubis.
August 29, 2005 at 3:17 am
This might help:
select name, collation from syscolumns where id in (select id from sysobjects where name = '<yourtablename>')
Run the script on your database and change <yourtablename> to the name of the table you wish to query.
For the sake of it here a list of types that have a collation. I created a table with all the default settings for each type:
name collation
tBIGINT NULL
tBINARY NULL
tBIT NULL
tCHAR SQL_Latin1_General_CP1_CI_AS
tDATETIME NULL
tDECIMAL NULL
tFLOAT NULL
tIMAGE NULL
tINT NULL
tMONEY NULL
tNCHAR SQL_Latin1_General_CP1_CI_AS
tNTEXT SQL_Latin1_General_CP1_CI_AS
tNUMERIC NULL
tNVARCHAR SQL_Latin1_General_CP1_CI_AS
tREAL NULL
tSMALLDATETIME NULL
tSMALLINT NULL
tSMALLMONEY NULL
tSQL_VARIANT NULL
tTEXT SQL_Latin1_General_CP1_CI_AS
tTIMESTAMP NULL
tTINYINT NULL
tUNIQUEIDENTIFIER NULL
tVARBINARY NULL
tVARCHAR SQL_Latin1_General_CP1_CI_AS
Good hunting
hot2use
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
August 29, 2005 at 8:00 am
See this about changing collation.
http://support.microsoft.com/default.aspx?scid=kb;en-us;325335
I have a procedure as well (in word format) I wrote for changing the collation of an entire database (including computed columns...).
private me if you want it
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply