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 ««123»»

An nHibernate Head Scratcher Expand / Collapse
Author
Message
Posted Monday, April 11, 2011 9:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:56 PM
Points: 33,202, Visits: 15,348
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
Posted Monday, April 11, 2011 10:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 8, 2014 10:08 PM
Points: 358, 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.
Post #1091576
Posted Monday, April 11, 2011 10:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:50 AM
Points: 6,594, Visits: 8,879
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
Posted Monday, April 11, 2011 12:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:22 PM
Points: 155, Visits: 350
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
Posted Monday, April 11, 2011 3:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:31 AM
Points: 4, Visits: 71
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
Posted Monday, April 11, 2011 3:19 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:07 AM
Points: 2,901, Visits: 1,805
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
Newbie on www.simple-talk.com
Post #1091785
Posted Monday, April 11, 2011 3:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:51 PM
Points: 198, Visits: 499
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
Posted Monday, April 11, 2011 6:54 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:42 AM
Points: 32, Visits: 121
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
Posted Tuesday, April 12, 2011 5:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 7, 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
Posted Tuesday, April 12, 2011 11:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:26 PM
Points: 3, Visits: 109
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 »

Add to briefcase ««123»»

Permissions Expand / Collapse