September 15, 2008 at 4:17 pm
It appears that your hosts SQL Server DB and your home SQL DB do not have the same collations.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 15, 2008 at 4:26 pm
You can check a databases collation with this command:
select DATABASEPROPERTYEX('MyDB', 'Collation')
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 16, 2008 at 12:16 am
Ok thanks my hosted DB seems to have : SQL_Latin1_General_CP1_CI_AS
and my home DB seems to have: Latin1_General_CI_AS
How can I fix this?
September 16, 2008 at 2:51 am
After some more research it seems I have to alter every field of my db that has the wrong collation of embed the collation in the create table script.
Another way I found is reinstalling SQL but that is no option for me now.
Any tips ofcourse more than welcome
September 16, 2008 at 6:39 am
Well you can change the database's collation like this:
Alter Database itzakBG COLLATE SQL_Latin1_General_CP1_CI_AS
However it will probably fail because you already have objects bound to the other collation.
I think that you will have to drop your DotNetNuke db, then recreate the DB with the new collation and then reinstall DNN to that DB (I can't remember if DNN let's you do that). If not, then try changing the [model] database's collation and reinstall DNN (actually you may want to do that anyway).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 16, 2008 at 6:42 am
If you don't have anything else in your DB's it might be easier to just reinstall SQL Server. If you have other DB's that you need to retain, detach them first and copy their files (mdf's, ldf's) to a safe place. Then after the reinstall, copy them back and reattach them. Note that these retained DB's will also retain the old collation.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 16, 2008 at 7:16 am
Allright thanks alot. One question though before I start.
At home I am just using SQL Server Express 2005,
At work and on my shared hosting I have SQL Server 2005.
Can I choose somewhere in the install wizard of SQL Server Express 2005 to use the SQL_Latin1_General_CP1_CI_AS collation cuz I don't remember seeing that?
I guess it will be wise to always use collation in my stored procedures create table scripts so If I need other collation that I can search and replace the collation explicitly.
Found this great article btw explaining what collation actually is, what it does and how it works. Might be interesting for anyone else running into this problem with no knowlegde about collation:
http://www.elijournals.com/premier/showArticle.asp?aid=23701
September 16, 2008 at 8:16 am
Afraid I don't know much about Express Edition. Maybe someone else here can answer that question.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 17, 2008 at 1:18 am
Allright. I'll be testing this next week on a virtual machine.
For now I'm doing a reinstall of the dnn app and I'll edit my scripts so that they don't take over the collation of my development sql server.
Thanks alot for the help rbarryyoung. Much appreciated
Viewing 9 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply