error accessing member list page

Post here for help with installing or upgrading mojoPortal pre-compiled release packages. When posting in this forum, please provide all relevant details. You may also want to review the installation or upgrading documentation.

If you have questions about using the source code or working with mojoPortal in Visual Studio, please post in the Developer forum.

Post here for help with installation of mojoPortal pre-compiled release packages

When posting in this forum, please try to provide as many relevant details as possible. Particularly the following:

  • What operating system were you running when the bug appeared?
  • What database platform is your site using?
  • What version of mojoPortal are you running?
  • What version of .NET do you use?
  • What steps are necessary to reproduce the issue? Compare expected results vs actual results.

You may also want to review the installation or upgrading documentation.

If you have questions about using the source code or working with mojoPortal in Visual Studio, please post in the Developer forum.

This thread is closed to new posts. You must sign in to post in the forums.
6/22/2011 5:56:38 PM
SpikeMelbost
Gravatar
Total Posts 55
View Posts
mojoPortal Community Expert
Arvixe Web Hosting / mojoPortal Community Liaison Looking for quality mojoPortal hosting? Try Arvixe Check out the MojoPortal Blogs at Arvixe

error accessing member list page

If I try to access the member list page, I get an error thrown up. Its a collation conflict (see below).

When I installed, I had different collations on my local & remote database. Everything seems to work except this one page. I've also tried it with a default installation of MojoPortal and get same error.

I'd assumed it was because my local and remote databases had differing collations, and would go away if I changed them.

After a lot of head-scratching, I've changed the collation of my local database to match the remote one ( SQL_Latin1_General_CP1_CI_AS ) BUT I still get the collation conflict.

Can you help?

some more detail:

Initially I changed just the collation of the local database. That didn't work & I read about tempdb. So I used rebuilddatabase to change the collation of the whole thing, including system databases.

I'm using SQL Server 2008 r2 express.

server info:

mojoPortal Version 2.3.6.6 MSSQL
Operating System Microsoft Windows NT 6.0.6002 Service Pack 2
ASP.NET Info v4.0.30319 Running in Full Trust
Server Time Zone Pacific Daylight Time
Server Local Time (GMT -7) 22/06/2011 15:53:53
Greenwich Mean Time (GMT/UTC) 22/06/2011 22:53:53

The error I see is:

Server Error in '/' Application.
--------------------------------------------------------------------------------

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
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 the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

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 the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2030802
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5009584
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
   System.Data.SqlClient.SqlDataReader.get_MetaData() +86
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +311
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +70
   mojoPortal.Data.SqlHelper.ExecuteReader(String connectionString, CommandType commandType, String commandText, Int32 commandTimeout, SqlParameter[] commandParameters) +235
   mojoPortal.Data.SqlParameterHelper.ExecuteReader() +73
   mojoPortal.Data.DBSiteUser.GetUserListPage(Int32 siteId, Int32 pageNumber, Int32 pageSize, String userNameBeginsWith) +257
   mojoPortal.Business.SiteUser.GetPage(Int32 siteId, Int32 pageNumber, Int32 pageSize, String userNameBeginsWith, Int32& totalPages) +106
   mojoPortal.Web.UI.Pages.MemberList.BindAlphaList() +53
   mojoPortal.Web.UI.Pages.MemberList.PopulateControls() +302
   mojoPortal.Web.UI.Pages.MemberList.Page_Load(Object sender, EventArgs e) +141
   System.Web.UI.Control.OnLoad(EventArgs e) +91
   mojoPortal.Web.mojoBasePage.OnLoad(EventArgs e) +13
   System.Web.UI.Control.LoadRecursive() +74
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207

6/23/2011 3:21:51 PM
SpikeMelbost
Gravatar
Total Posts 55
View Posts
mojoPortal Community Expert
Arvixe Web Hosting / mojoPortal Community Liaison Looking for quality mojoPortal hosting? Try Arvixe Check out the MojoPortal Blogs at Arvixe

Re: error accessing member list page

I'm maybe getting somewhere with this, but would still welcome any help / insight / suggestions.

It seems that collation is set at column level. I don't know if this is normal or if its particular to MojoPortal or maybe due to WebMatrix. I know that collation can be set at column level, but I had assumed that generally speaking it would use the database setting.

So, I can now see the problem collation type when I look at the properties of individual columns in individual tables in the database. An example is the name column in the user table.

I have also noticed that I now see the error on my local website. This is expected because I similarly have the new collation set at database level (including system databases) but the old one at column level.

Further backing this up, I created another new MojoPortal installation. This time (because I have corrected the database collation) I see the correct collation at column level.

So now I need to change the collation at column level for my existing database, and hope thats the end of it.

Changing the collation for one column looks like it should be quite easy. But there are lots of tables each with lots of columns. It'll take forever to do it manually. Anyone have a handy tool for this?

Also I read:

You cannot change the collation of a column that is currently referenced by any one of the following:

  • A computed column
  • An index
  • Distribution statistics, either generated automatically or by the CREATE STATISTICS statement
  • A CHECK constraint
  • A FOREIGN KEY constraint

That makes it sound like I'm stuffed.

 

6/24/2011 1:03:52 PM
Joe Audette
Gravatar
Total Posts 18439
View Posts

Re: error accessing member list page

Sorry but I have no easy advice to solve that problem, there may be tools out there to help but I'm not aware of them myself, the tedious column by column approach may be best..

The mojoPortal sql scripts are neutral and don't specify a collation so when the database is first populated with tables and procs and such, it would use whatever collation is already specific on your db. When the db is created it would be created with the defaults from the server default charset and collation settings.

I know some years ago I had a similar collation issue with the MySql database used on this site and I had to solve it by going through the table columns one by one to correct the collation. In my case somehow when I first created the MySql db it was in swedish charset/collation which I guess was the default on the host I was using at the time.

Maybe someone else will post if they know of a tool to help quickly change the collation on all columns.

Best,

Joe

 

You must sign in to post in the forums. This thread is closed to new posts.