SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


An nHibernate Head Scratcher


An nHibernate Head Scratcher

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)

Group: Administrators
Points: 147696 Visits: 19440
nvarchar has a higher precedence, so the varchar -> nvarchar works fine, as noted by Mr. Poole.

http://msdn.microsoft.com/en-us/library/ms190309.aspx

Converting down causes the index issues. Lots of ORMs and some frameworks will submit N'' values and if you have varchar fields and indexes, they don't get used.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
magarity kerns
magarity kerns
SSC Eights!
SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)

Group: General Forum Members
Points: 910 Visits: 397
dg81328 (4/11/2011)
Not a bug in SQL Server because you can choose a different collation when you are setting up SQL Server. It is more of a human bug for not checking how to do a proper setup.

You should read the bit where the author said the installer ignored his selected collation before you say snooty things like this.
WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21812 Visits: 10652
FYI, a short time ago I started a forum post asking for discussion on the usage of ORM tools:
http://www.sqlservercentral.com/Forums/Topic1087686-391-1.aspx

If you have experience (good or bad) in working with ORM tools with SQL, please post your experience and thoughts there.

Thanks!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

corey lawson
corey lawson
SSC Eights!
SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)

Group: General Forum Members
Points: 939 Visits: 576
Except, in the analysis, it isn't really a problem related to or caused by the ORM itself or the SQL it produces, it's a subtle issue invoked by implicit casting of types and collations, that could sneak in unobtrusively and unintentionally from anywhere.

And, throw in a subtle bug for a non-US English installs...

I think I've dealt with a similar issue with just straight up data loaded from different data sources...

VERY subtle bug.
John Lingerfelt
John Lingerfelt
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 86
We discovered this same problem with nHibernate about 6 months ago. It was running index scans instead of seeks. The datbase columns were defined ad VARCHR but Hibernate was passing NVARCHAR. After we brought it to the attention of the people who developed the offending application they were able to make some modifications to the configuration parameters for nHibernate and corrected the problem. I am not familier enough with nHibernate to know exactly what they did.
Dave Poole
Dave Poole
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16888 Visits: 3403
There are a couple of ways of fixing it. The one that springs to mind is as mentioned in Todd's blog. Change the mapping file in nHibernate from using String to using AnsiString.

LinkedIn Profile
www.simple-talk.com
beargle
beargle
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 700
David, this was an interesting read. Thanks for your post. We have used the script here:

http://blogs.msdn.com/b/ikovalenko/archive/2006/12/03/alter-database-alter-collation-forced.aspx

when changing database collations. Hopefully you can also put it to good use when the time comes.
Thomas Keller
Thomas Keller
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 158
Rather than changing the server default collation just to avoid the issue on the next database creation, how about changing the collation in the model database?
jpenniman
jpenniman
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 31
We had a similar problem in one of our apps. The solution for us was to use the correct nhibernate types in the mapping.

Like you, our tables have Varchar. We noticed that hibernate was generating nvarchar for the data types.

Per the hibernate documentation, string will generate nvarchar in sql. The use of AnsiString will generate varchar parameter types and Sql Server is happy.

I'm on my smartphone at the moment, so I can't provide specifics. When I'm at a pc, I'll update with specific docs and code samples.
Richard Torrone
Richard Torrone
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 135
I know you aren't happy about using the NVarchar datatype on the table columns but still I have this question. If you converted the varchar in the table to be nvarchar to match the parameter coming in from the Unicode ORM framework would it have caused a seek, solving the problem, and negating the need to change coallation settings on the DB/Server.

It's so much fun to watch someone else assume the issue to be the lowest level, index/data corruption type issue, and finally work their way back up to 30,000 feet and find the issue right outside the window. I wonder if the Guru DBAs would have started with coallation? Gosh, I hope not.

Great Article!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search