invalid column name

Discussion in 'Databases' started by Skrilla, Nov 7, 2010.

  1. Prolly this:

    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 53
    Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_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 "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 87
    Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_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 "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 48
    Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_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 "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 79
    Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_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 "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 93
    Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_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.

    im having same problems.
     
  2. rum

    rum Winhost Staff

    It looks like the collation of your development database is different from the collation of your production database on our server. Please make sure the collations of you databases are the same. Please see this KB article for the instructions on how to change the collation of a database.
     
    Last edited by a moderator: Oct 14, 2015
  3. im not sure what to put as the collation name

    how do I know which collation name to use?
     
  4. rum

    rum Winhost Staff

    You should put either Latin1_General_CI_AS or SQL_Latin1_General_CP1_CI_AS, depending on which collation you want to set for you database.
     
  5. I get an error using either one of those..
    It says invalid column name
    This is really driving me nuts.. noone seems to know what the problem is and I keep getting sent to the same articles which do not help me. my roles work! it is only the remove and add user to roles procedures that do not.. all the scripting is the same so why do the isuserinrole procedure work but not the removeuserfromrole? makes no sense to me
     
  6. Ray

    Ray

    First off how are you uploading your local database to our server? Did you already upload it?

    If you look at the error closely, it gives two clear error messages.

    1. collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS"

    This means that there is a collation conflict. The only resolution is to change the collation on your local database to match what is on our SQL 2008 server or change the collation on the SQL server to match that of your local database.

    2. Cannot find the object 'aspnet_UsersInRoles_AddUsersToRoles'

    This is a stored procedure (aspnet_UsersInRoles_AddUsersToRoles) that is made when you setup the membership/roles schema on the server. This either means it was not uploaded to our SQL server or it was deleted. I'm not sure exactly how your transfered your membership/roles schema to our sql server so its hard to comment on this, but the fact remains that its not on our server. Have you read this kb article and followed these instructions on how to setup ASP.Net membership/roles provider on our server?
    http://support.Winhost.com/KB/a619/how-to-configure-the-aspnet-membershiproles-provider.aspx
    Going through these steps typically works and ensure that all the objects and tables are created on our SQL server to get membership/roles started.
     
    Last edited by a moderator: Oct 14, 2015
  7. I uploaded my dbase through sqlserver 2008 I published my databse from within vs and then ran the script file in sqlserver.

    Why is it only affecting those two procedures? isuserinrole has been uploaded fine.

    I am going to try and drop the database and re-upload.. maybe that fixes it

    As far as changing the collation name.. I have tried as you see above.. but I get the error
    sry guys im still a noob at this..

    ---

    Well that did not work.. im lost I do not know what do do.. changing collation does not work. deleting and re-uploading doest not work. Should I delete all my roles and disable them.. then upload the database and once online then configure and enable roles?

    this is rediculous
     
  8. Ray

    Ray

    Try uploading your database to our SQL server but this time use this procedure.

    http://support.Winhost.com/KB/a771/how-do-i-generate-or-restore-a-backup-of-my-database.aspx

    Now, keep in mind that this will override anything this is already in our SQL server. So if you have any new data you may want to run the backup procedure first, so that you have a backup of your Winhost database.

    Send us a new copy of the error message when you finish running these steps.
     
    Last edited by a moderator: Oct 14, 2015
  9. the link you posted is to make a backup or restore a dbase.. not upload one.. am I missing something?

    I backed up the database and then restored it.. but I do not see how this solves anything.. the procedures were not there to begin with.

    I put a copy of my local dbase in the app_data folder if you care to take a look.. I am using vs 2008 and sqlserver 2008 I do not see any reason why there should be any conflicts.

    this is getting to be quite a pain in my *** :p

    I have tried everything.. deleted dbase re-upload etc etc. changinc ollation does not work.. are you telling me that everytime I create a dbase in VS I will have this problem? Please help i'm about to give up on windows :(


    btw here is the message I get:


    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 53
    Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_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 "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 87
    Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_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 "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 48
    Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_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 "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 79
    Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_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 "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 93
    Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_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.
     
  10. Ray

    Ray

    The link I gave you was to backup your local database, then upload it to the web server. Then go back into your Winhost control panel go to the Site manager go to the SQL 2008 Manager and use the Restore tool to restore the local database you backed up on your computer to our sql server. I suggested this because I want to make sure that everything is restore on our sql server. Base off your error, the error is saying that not everything is restored. Did you do that?
     
    Last edited by a moderator: Oct 14, 2015
  11. no I backed up the dbase on the server.. 1 sec ill do it

    im a bit confused.. it only allows me to backup the dbase on the server.. does not let me backup anything that is local. also when restoring it must be a .bak file and it must also be on the server.. :/
     
  12. Ray

    Ray

    What exactly are you doing that is making you get this error? Do you have instructions for us so we can replicate this error on our end? I'm think you are running some kind of script on your Visual Studio, and that is what I'm trying to get you away from for now.
    The kb article I gave you tells you how to backup your local database. The database on your computer. But you need to use SQL Server Management Studio. That is something you need to download off the Internet off on Microsofts web site and install it on your computer.
     
  13. I have followed the directions.. it says clearly to open my control panel and do it through there.. never mentioned sqlserver until I get to the point of deploying.. here is the sterps im taking:

    open vs right click my database and choose publish to provider. go through all the steps and save the file to my docs folder..
    I then open sqlserver manager and connect to the server database. I then right click my database and choose new query.. in there I take the script that was generated(the .mdf file) and copy paste it in the query window and execute.. this has worked fine for everything else.. idgi what am I doing wrong?
    I have no idea how to create a backup of my local dbase from within my Winhost control panel

    ---

    I guess you have given up on me lol I don't know, the article says nothing about how to back-up a local dbase. only how to back-up the server dbase

    I guess if I read it 10 more times it might magically tell me lol
     
    Last edited by a moderator: Oct 14, 2015
  14. Ray

    Ray

    I just replied to your email. I gave you some instructions on how to run the aspnet_regsql.exe. Try that as an alternative. If you do not want to go that route let me know and I'll lay out instructions for you on how to backup your local database so you can get that .bak file we are referring to.
     

Share This Page