If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Cannot resolve the collation conflict??

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2008
Posts: 2
Cannot resolve the collation conflict??

Hi,

Firstly, please excuse my ignorance in MSSQL.
I have created a new club website using VWD2008, and a template found on the MS website.

When the site was created in VWD, it automatically created a DB (presumably in SQLSERVER Express) for the Club, and when I started using the Membership/Roles features of ASP.NET, it created a separate DB for the security tables.

When I had finished creating the site, I transfered it to my host (EUKHOST), and scripted both of my DBs, and exceuted the scripts using Enterprise Mngr to the same DB.

Everything has been working fine. I was able to create users etc, and upload photos blah blah.... everything was working fine.

Then one day, I used the ASP.Configuartion Tool to authorise some users into the relevant roles, only to find that it wouldn't work anymore.

The reason for this was due to two SPROCS no longer being in the DB.

The two missing Sprocs are:
aspnet_UsersInRoles_RemoveUsersFromRoles
aspnet_UsersInRoles_AddUsersToRoles

I haven't a clue why they are missing, but they are. As a result of these missing, I can no longer add or remove users into roles.

I tried to execute the original scripts again to try to replace the missing SPROCS, but I get this:

Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 50
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 58
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 84
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 92
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 45
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 52
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 76
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 83
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 90
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'aspnet_UsersInRoles_AddUsersToRoles', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'aspnet_UsersInRoles_RemoveUsersFromRoles', because it does not exist or you do not have permission.


I am not experienced enough to know what to do next. I have looked at plenty of messageboard explainations, but none seem to be relevant, and to be honest, I haven't a clue about 'collation'.

Any help to resolve this would be appreciated. Please remember that I am fairly capable, but not literate in MSSQL, so any explainations you could give would be great.
Reply With Quote
  #2 (permalink)  
Old
Wage drone 24601
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,439
Collation is a fancy way of saying Sort Order. Not all languages sort letters in the same way. Have a look through a Norwegian phonebook to see all the variations of the letter A that can exist ;-).

The problem you are facing is that the script generator "helpfully" includes the collation with every column definition in your table scripts. If you remove these, then the tables will be created with the database default collation, and these errors should go away.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Oct 2008
Posts: 2
Cannot resolve the collation conflict??

Thanks so much for the clear explaination. I have looked through the generated script for any references to the collation, but I can't find any. Am I therefore correct in saying that I need to regenerate the scripts of my two missing sprocs (from a good DB) and apply some 'setting' within the script generator, that essentially 'turns off' this collation?
Thanks again.
Reply With Quote
  #4 (permalink)  
Old
Wage drone 24601
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,439
You need to regenerate scripts for the underlying tables.

In the scripting wizard in SQL Management Studio, there is a "Script Collation" option in a list of a dozen or so others. This must be set to false.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On