Jim Bonnie's Blog

Collation Settings causing issues with migrating dotnetnuke SQL Server Databases for DNN 4

Jul 1

Written by: jbonnie
Saturday, July 01, 2006 4:56:23 AM 

Guess what happened on the way back from the simple transfer procedure from my ISP to my local development box.
 
You guessed it, some new features added to DNN (or our modules) are causing some SQL Server Collation settings at the column level, which caused a conflict when moving the DB to another SQL host.

I got the following error when calling up the DNN Log viewer,
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.

I found these scripts that can help get things working again.
This first script will show you the Collation settings for your DB, Server, and TempDB.
Modify the DNNDB as appropiate.

SELECT

DATABASEPROPERTYEX( 'DNNDB' , 'Collation' ) AS DotNetNukeDB_Collation,

DATABASEPROPERTYEX( 'tempdb' , 'Collation' ) AS TempDB_Collation,

SERVERPROPERTY( 'Collation' ) AS Server_Default_Collation

If these are different then you can have a problem.
This problem manifested after porting the production system locally and trying to look at the log viewer. When a nasty error as follows was shown instead.

I ran the script above, and saw that the collations were different, so I changed the DNNDB collation with the following script:

ALTER

DATABASE DNNDB SET SINGLE_USER

alter

database DNNDB collate Latin1_General_CI_AS

ALTER

DATABASE DNNDB SET MULTI_USER

But, we were not out of the woods yet. The log viewer still did not work.
After a little poking around, I found that three tables had collation settings at the column level. I went in and manually changed all of the CHAR column to use the default collation and and all was well....until the next time to copy the production DB, locally.
The following DNN tables were affected: EventLog, EventLogTypes, and EventLogConfig

Looks like another script is in order!

Trackback Print
Location: Blogs Jim Bonnie's Blog
Tags:

Your name:
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Add Comment   Cancel 

Navigation

Register Login

About Me

Thanks for stopping by. My name is Jim Bonnie and I have been writing software for over 20 years. After building financial data systems for Reuters for nearly 18 years I have started doing contract work and am excited about technology and currently focus on Microsoft solutions. DotNetNuke has helped provide a good introduction to ASP.NET web application development, and I am now starting to branch out into other areas. Data Access techniques and SubSonic is something that I am looking into now. This has helped me get an invitation to speak at Las Vegas at DNN Open Force 07. it was a great time. And I am looking forward to OpenForce08, where I wil be presenting on TDD for DNN.

 

Reading the codebetter blogs and attending a nothing but .net training class with JP Boodhoo helped spark my interest in ALT.NET. I attended the first conference in Austin Texas, and it was amazing to see Scott Guthrie introduce the ASP.NET MVC framework.

Add to Technorati Favorites

View blog top tags

Links

Jim Bonnie's Blog

Jim Bonnie's Blog

Jim Bonnie's Blog