Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Collation issues Expand / Collapse
Author
Message
Posted Monday, August 5, 2013 6:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 25, 2013 2:22 AM
Points: 12, Visits: 25
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
Any help appreciated.

Tx,
Martin
Post #1480888
Posted Monday, August 5, 2013 7:33 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 9:02 AM
Points: 702, Visits: 2,171
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




MCSA: SQL Server 2012
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1480910
Posted Monday, August 5, 2013 8:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 25, 2013 2:22 AM
Points: 12, Visits: 25
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
Post #1480937
Posted Monday, August 5, 2013 8:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1480943
Posted Monday, August 5, 2013 8:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 5,230, Visits: 9,455
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
Post #1480947
Posted Monday, August 5, 2013 10:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 25, 2013 2:22 AM
Points: 12, Visits: 25
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 .
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
Post #1481002
Posted Tuesday, August 6, 2013 1:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 5,230, Visits: 9,455
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
Post #1481173
Posted Wednesday, August 7, 2013 8:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 25, 2013 2:22 AM
Points: 12, Visits: 25
Ok, here it is. Thanks.

  Post Attachments 
cycanl-new-20130805.zip (2 views, 129.00 KB)
Post #1481880
Posted Wednesday, August 7, 2013 8:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 5,230, Visits: 9,455
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
Post #1481901
Posted Thursday, August 8, 2013 1:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 25, 2013 2:22 AM
Points: 12, Visits: 25
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
Post #1482163
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse