Results 1 to 4 of 4
  1. #1
    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.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,539
    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.

  3. #3
    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.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,539
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •