August 5, 2013 at 6:24 am
Hello all,
My first post here.
I am a total newb and have been battling my way through setting up a slightly more robust membership system than is provided out-the-box using MS Visual Studio 2010.
I have reached the point where everything that I need from it has been achieved.
However, when I transfer the SQL Server Xpress DB from my local machine to a SQL Server 2008 DB at my web host (shared server) I receive collation errors.
The process I am following is:
1. The "Publish to Provider" option on the Xpress DB from within VS2010. An error free script is generated
2. Using SQL Management Studio I then execute this script to the SQL Server 2008 DB on my shared server.
The errors reported are:
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 53
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 87
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 48
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 79
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 93
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 have no idea where to start starting this little lot out :Wow:
Any help appreciated.
Tx,
Martin
August 5, 2013 at 7:33 am
Hi,
Essentially the collation of your server and your database are different. In our environment our ASPNETDB is set to SQL_Latin1_General_CP1_CI_AS so i therefore assume that your server is Latin1_General_CI_AS. The collation is basically the character set that your server uses.
The error you're getting is saying that it can't compare strings from one with the other. Therefore you need to either change the collation of your database (easy), or change the collation of the server (not so easy).
If you want to change the collation of the server you need to rebuild the master database with the collation you want, probably SQL_Latin1_General_CP1_CI_AS. If you just want to change the collation of the database, you can right-click the DB in SSMS and then go to the options page and select Latin1_General_CI_AS from the drop down list. However if the default collation of the ASPNETDB is SQL_Latin1_General_CP1_CI_AS then the more correct solution is to change the collaiton of the server.
Changing the collation of the server will affect any other databases that currently reside on it though so don't undertake this lightly.
Thanks,
Simon
August 5, 2013 at 8:20 am
Thanks very much Simon. I think I actually understand all of that. Useful!
I'm always happy to follow the easy route so I'll try to change the collation of the local DB. Before I can do that, another issue has arisen.
In SSMS, the local DB (ASPNETDB.MDF) is not listed amongst the local DB's. How come?
Greetings,
Martin
August 5, 2013 at 8:30 am
You can also put the COLLATE to the column names where you are getting collation error like this:
Column1 Collate collationname = Column2 Collate collationname
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 5, 2013 at 8:37 am
martin 7826 (8/5/2013)
Thanks very much Simon. I think I actually understand all of that. Useful!I'm always happy to follow the easy route so I'll try to change the collation of the local DB.
Martin
Changing the collation of the database doesn't change the collation of the existing columns in the database, so you may find that you still get the errors. I assume your code uses temp tables and you get the errors when you JOIN or UNION them with tables in the database? If that's the case, you may find it less painful to change the code so that all temp tables are created with columns that have the same collation as your database (the columns that have data type char, varchar, etc).
Before I can do that, another issue has arisen.
In SSMS, the local DB (ASPNETDB.MDF) is not listed amongst the local DB's. How come?
What does this return?
SELECT name FROM master.sys.databases
John
August 5, 2013 at 10:49 am
Thanks John.
I must admit to mostly not understanding your suggestions. So I'll start from the easy part... the bottom ...
Where would I enter that query. In SSMS somewhere?
Then... does my code use temp tables? My zero knowledge of anything SQL precludes me from being able to give any kind of answer :unsure:.
What I do know is that I added an extra table (with 8 fields) to the "out of the box" ASPNETDB.MDF to be able hold more user information than what "standard" ASP.NET Membership system offers. My layman experience tells me this table is causing the issues.
Greetings,
Martin
August 6, 2013 at 1:03 am
Martin
Yes, open SSMS, connect to your server, click on New Query, paste the query into the ensuing window and press F5.
I've just re-read your original post. Please will you share the script that caused the initial errors?
John
August 7, 2013 at 8:00 am
Ok, here it is. Thanks.
August 7, 2013 at 8:29 am
Martin
You're declaring table variables (@tbNames etc), which are materialised in tempdb, and comparing data in them with data in your database, which has a different collation from tempdb. Hence the conflict.
Not that it'll help you solve this problem directly, but why are you using sp_executesql to create your procedures? Why not just execute the CREATE PROCEDURE statement?
The way to solve your problem would be to add a COLLATE clause to the nvarchar columns in your table variable declarations so that they have the same collation as your database. (I've never used a COLLATE clause in a table variable before, so I'm not 100% sure it's allowed. If it's not, use a temp table instead.)
However, before you try that, you should consider whether you actually need temp variables at all. You should be able to do away with those WHILE loops and do the whole thing with one DELETE or INSERT statement. Performance will improve and you'll get into the good habit of writing set-based code.
John
August 8, 2013 at 1:15 am
John,
To be brutally honest, I've never done anything like this before. I hadn't expected to be going so far down the road with it, but that's usually the case when offering to do something for your local non-profit. Ouch! In a nutshell, I really have no idea how to answer any of your questions as it's a foreign language for me :-).
Being a newb, I simply used the "Publish to Provider" function on Visual Studio to create a script from my local DB and then used SSMS to run the script against the DB at my host. Probably quite naïve expecting that just to work, but that's about the sum total of my knowledge.
Thanks for your time!
Cheers,
Martin
August 8, 2013 at 2:09 am
Martin
Let me see if I can be of a little bit more help. I'm disappointed, but not altogether surprised, that the code you posted is generated by a Microsoft utility. Given that, there's little point in trying to refactor the code, since it will only come out exactly the same next time you press the button.
You should be able to get the code to work with just a couple of tweaks. If you study the error messages in your original post, you'll notice that the errors are occurring in just two stored procedures. You need to find the definitions for those stored procedures and tweak the table variable declarations thus:
DECLARE @tbNames table(Name nvarchar(256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL PRIMARY KEY)
Here I'm making the same assumption that Simon did, namely that SQL_Latin1_General_CP1_CI_AS is your database collation and Latin1_General_CI_AS is the server collation. Once you've changed that line for each of the two offending stored procedures in your script, you can just run it in the normal way.
One option I didn't suggest before is to build or comandeer a SQL Server instance where the server collation is the same as that of your database, and move everything over.
John
August 8, 2013 at 11:47 am
Hello John,
By hook or by crook I actually managed. Still not sure how... but all's well that ends well. It did unearth other self-inflicted issues too unfortunately.
Thanks to you and Simon. The learning curve is very steep. But's that's no bad thing.
Martin
August 9, 2013 at 2:08 am
Glad you got it sorted. At this rate of learning you'll be a fully fledged battle worn DBA by the middle of next week. 😉
August 9, 2013 at 7:02 am
Not sure about that 😀
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply