September 15, 2008 at 2:46 pm
Hi,
I'm having a real strange error all of a sudden.
I have a dotnetnuke application, I installed it on shared hosting (using an SQL 2005 DB)
I have a testsite at home using SQL Server Express 2005
I used SQL Examiner to synchronize the databases. I only synchronized my own custom tables and my own custom stored procedures to the "live" SQL 2005 Server.
After this when I browse to my site I get the following error. The error is about a view I didn't even touch or create, it is a view created by dotnetnuke itself and worked perfectly before.
I have no clue about collation and what went wrong here, but more important how to fix it and make sure it doesn't happen again.
Really need some help on this. Thanks in advance!
Error:
Server Error in '/' Application.
Cannot resolve collation conflict for column 3 in SELECT statement.
Cannot resolve collation conflict for column 18 in SELECT statement.
Could not use view or function 'dbo.vw_Portals' because of binding errors.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot resolve collation conflict for column 3 in SELECT statement.
Cannot resolve collation conflict for column 18 in SELECT statement.
Could not use view or function 'dbo.vw_Portals' because of binding errors.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Cannot resolve collation conflict for column 3 in SELECT statement.
Cannot resolve collation conflict for column 18 in SELECT statement.
Could not use view or function 'dbo.vw_Portals' because of binding errors.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
System.Data.SqlClient.SqlDataReader.get_MetaData() +62
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1005
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +62
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, String commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership) +499
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) +201
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, CommandType commandType, String commandText) +40
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, String spName, Object[] parameterValues) +262
DotNetNuke.Data.SqlDataProvider.GetPortals() +47
DotNetNuke.Common.Initialize.CacheMappedDirectory() +40
DotNetNuke.Common.Initialize.InitializeApp(HttpApplication app) +446
DotNetNuke.Common.Initialize.Init(HttpApplication app) +125
DotNetNuke.HttpModules.RequestFilter.RequestFilterModule.FilterRequest(Object sender, EventArgs e) +177
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
September 15, 2008 at 4:17 pm
It appears that your hosts SQL Server DB and your home SQL DB do not have the same collations.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 15, 2008 at 4:26 pm
You can check a databases collation with this command:
select DATABASEPROPERTYEX('MyDB', 'Collation')
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 16, 2008 at 12:16 am
Ok thanks my hosted DB seems to have : SQL_Latin1_General_CP1_CI_AS
and my home DB seems to have: Latin1_General_CI_AS
How can I fix this?
September 16, 2008 at 2:51 am
After some more research it seems I have to alter every field of my db that has the wrong collation of embed the collation in the create table script.
Another way I found is reinstalling SQL but that is no option for me now.
Any tips ofcourse more than welcome
September 16, 2008 at 6:39 am
Well you can change the database's collation like this:
Alter Database itzakBG COLLATE SQL_Latin1_General_CP1_CI_AS
However it will probably fail because you already have objects bound to the other collation.
I think that you will have to drop your DotNetNuke db, then recreate the DB with the new collation and then reinstall DNN to that DB (I can't remember if DNN let's you do that). If not, then try changing the [model] database's collation and reinstall DNN (actually you may want to do that anyway).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 16, 2008 at 6:42 am
If you don't have anything else in your DB's it might be easier to just reinstall SQL Server. If you have other DB's that you need to retain, detach them first and copy their files (mdf's, ldf's) to a safe place. Then after the reinstall, copy them back and reattach them. Note that these retained DB's will also retain the old collation.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 16, 2008 at 7:16 am
Allright thanks alot. One question though before I start.
At home I am just using SQL Server Express 2005,
At work and on my shared hosting I have SQL Server 2005.
Can I choose somewhere in the install wizard of SQL Server Express 2005 to use the SQL_Latin1_General_CP1_CI_AS collation cuz I don't remember seeing that?
I guess it will be wise to always use collation in my stored procedures create table scripts so If I need other collation that I can search and replace the collation explicitly.
Found this great article btw explaining what collation actually is, what it does and how it works. Might be interesting for anyone else running into this problem with no knowlegde about collation:
http://www.elijournals.com/premier/showArticle.asp?aid=23701
September 16, 2008 at 8:16 am
Afraid I don't know much about Express Edition. Maybe someone else here can answer that question.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 17, 2008 at 1:18 am
Allright. I'll be testing this next week on a virtual machine.
For now I'm doing a reinstall of the dnn app and I'll edit my scripts so that they don't take over the collation of my development sql server.
Thanks alot for the help rbarryyoung. Much appreciated
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply