An nHibernate Head Scratcher

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by David Poole » An nHibernate Head Scratcher

22 posts, Page 2 of 3

««1 2 3 »»

An nHibernate Head Scratcher

Rate Topic

Display Mode

Topic Options

Author

Message

Steve Jones - SSC Editor

Posted Monday, April 11, 2011 9:55 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406, Visits: 13,722

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

Post #1091554

magarity kerns

Posted Monday, April 11, 2011 10:34 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 10:22 PM
Points: 358, Visits: 393

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.

Post #1091576

WayneS

Posted Monday, April 11, 2011 10:42 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 6,367, Visits: 8,225

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

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

Post #1091582

corey lawson

Posted Monday, April 11, 2011 12:44 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:28 PM
Points: 135, Visits: 199

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.

Post #1091668

John Lingerfelt

Posted Monday, April 11, 2011 3:04 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 10, 2013 12:30 PM
Points: 4, Visits: 57

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.

Post #1091776

David.Poole

Posted Monday, April 11, 2011 3:19 PM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:16 PM
Points: 2,749, Visits: 1,405

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

Post #1091785

beargle

Posted Monday, April 11, 2011 3:50 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 31, 2013 6:27 AM
Points: 195, Visits: 456

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.

Post #1091801

Thomas Keller

Posted Monday, April 11, 2011 6:54 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, January 14, 2013 2:54 AM
Points: 23, Visits: 97

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?

Post #1091835

jpenniman

Posted Tuesday, April 12, 2011 5:57 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 07, 2012 12:02 PM
Points: 7, 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.

Post #1091994

Richard Torrone

Posted Tuesday, April 12, 2011 11:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 2:31 PM
Points: 2, Visits: 77

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!

Post #1092278

« Prev Topic | Next Topic »

22 posts, Page 2 of 3

««1 2 3 »»

Permissions