Home » discuss » Public forums » iMIS Community

How to change database collation

Posted August 29th, 2012 by dcook
|

A new client recently upgraded from iMIS 10.6 to 15.2.5.  During the upgrade, I discovered that the database is case-sensitive.  Apparently this collation setting is creating problems in the new version of iMIS.  For instance, some functions that staff try to perform, such as billing dues, generate an error message stating that iMIS "cannot resolve the collation conflict".  I tried to run the ALTER DATABASE command for changing the database collation from SQL_Latin1_General_CP850_BIN (case-sensitive) to SQL_Latin1_General_CP1_CI_AS (case-insensitive).  However, SQL throws all kinds of errors saying that numerous objects and columns are "dependent on database collation."  Two of these obects were views, which I simply altered to get rid of the "WITH SCHEMABINDING" until the collation could be changed, then put them back.  Some of these objects were Functions that I deleted temporarily.  Then I ran into a coupe of tables whose columns were dependent of the db schema, after which I got stuck.

Rather than go through all of this hassle, maybe someone out there has a better way.  If anyone has a script or methodology for changing the db collation, would you be willing to share it?  If you don't know of a better way, do you know how to temporarily remove the schema depedency for columns in a table?  The columns are found in the GiftAidClaimDetail table.  One is called TaxReclaimAmount and the other is called LabelName.  Both of these columns are COMPUTED, which is probably where the schema dependency is coming in. 

Thanks for any help you can give.

-David

 

‹ New Schema Points to Lots of Re-work AheadPages stuck in publish-pending status ›
login or register to post comments | printer friendly version

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

The SQL Server Collation must match the iMIS DB Collation

On August 29th, 2012 Paul Bradshaw says:

If you put the database onto a server whose default install collation is identical to the iMIS Database's default collation, everything should work as-is.

The problems result when the collation for the master/tempdb is different from the iMIS Database.

There is no way to globally change a database's collation, at least not easily.

The fast/easy way is to install a new SQL Server instance with the same collation, and move the database to that new server instance.

However, since we do not support case-sensitive databases for iMIS Installs, there are no guarantees that there won't be other problems or bugs that crop up (for example, from SQL run by the system where the case of a column name is different than in the database... like "UserID" vs "Userid").

 

login or register to post comments

The other way I know of

On August 29th, 2012 Bruce Wilson says:

The other way I know of to resolve this:

1. Create a new database with the correct collation.
2. Generate a script to transfer the objects (SSMS can do it).
3. Review the script as needed to make sure it is not explicitly assigning the old collation. (May be able to turn this off in the transfer options.)
4. Run the script in the new database.

As a bonus, they get shiny new tables with contiguous space allocations.

--
Bruce Wilson
Director, Technology Solutions
McGladrey LLP

login or register to post comments

David Cook 501 CIO, LLC

On August 29th, 2012 dcook says:

David Cook 501 CIO, LLC 770-595-0452 dcook@501cio.com www.501cio.com

 

Why won't the

ALTER DATABASE COLLATE SQL_Latin1_General_CP1_CI_AS 

command work?  I did manage to finally run this script successfully in the dev db, and confirmed that the collation in the database properties reflected the change.  However, the collation errors continue to appear when trying to bill dues.

-David

login or register to post comments

That only changes DB Default Collation

On August 29th, 2012 Paul Bradshaw says:

That only changes the DB Default collation.  If you now create tables with varchar and nvarchar columns, without specifying a collation, they'll be in the new collation.  But the existing columns will retain their old collations.

I did find one site that claims you can change the collation without having to create a new DB and then copying all the old schema and data into the new DB (which is very tricky given dependencies)

I offer this with the "Use at your own risk" disclaimer: http://www.dbforums.com/microsoft-sql-server/998397-change-database-collation-quickly.html

 

 

login or register to post comments

You can try this tool:

On August 29th, 2012 lpapageorgiou says:

You can try this tool: http://www.codeproject.com/KB/database/ChangeCollation.aspx

I know a few consultants that used it, and it worked without any issues.

Regards,
Leo

login or register to post comments

David Cook 501 CIO, LLC

On August 29th, 2012 dcook says:

David Cook 501 CIO, LLC 770-595-0452 dcook@501cio.com www.501cio.com

 

Leo, this tool looks promising, but it's for SQL 2000.  The database is housed in SQL 2008 R2.  The upgrade occurred fairly recently from iMIS 10.6.  I suppose we could run this tool in that environment, which is SQL 2000.
  
-David

login or register to post comments

Hi David, It was originally

On August 29th, 2012 lpapageorgiou says:

Hi David,

It was originally written for SQL 2000, but if you view the change history, it was upgraded to work with SQL 2005. If you also view the comments made by people further down the page, you can see that people have successfully used it on SQL 2008 R2

Regards,
Leo

login or register to post comments

David Cook 501 CIO, LLC

On August 29th, 2012 dcook says:

David Cook 501 CIO, LLC 770-595-0452 dcook@501cio.com www.501cio.com

 

Thanks Leo.  I see it now.

login or register to post comments