what "dialect" do I use with sqlServer 2005?

yogiberr · **Posted:** Thu Jun 16, 2005 12:28 pm

hiya,

I'm using sqlServer2005.
I tried the following dialect in the appConfig:
"Nhibernate.Dialect.MsSql2005Dialect"

<error>
could not instantiate dialect class sql2005
<\error>

Can anyone post the correct appConfig dialect for sql2005?

many thanks,
yogi :wink:

martijnb · **Joined:** Sat May 14, 2005 8:40 am **Posts:** 130

I think the MsSql200Dialect will work fine in most (if not any) cases.

But there is one large reason for a MsSql2005 dialect: SQL Server 2005 has a rownumber function that works exactly the same as in DB2. This solves a current issue with the MsSql2000Dialect where the database always returns all results even if you set MaxResults and FirstResult.

baggins · **Joined:** Mon May 16, 2005 1:35 am **Posts:** 67

I agree. I think this should certainly be made a priority for the next release.

Guest · **Posted:** Fri Jun 17, 2005 5:18 am

cheers,
So, can I safely assume that as yet there is no sql2005 dialect?

Also, If i have 2 dbServers on a single machine that differ only in that they are differnt VERSIONS of sqlServer, eg:

1) SqlServer2000
2) SqlServer2005

Is the server name the ONLY thing that I need to change in the nHibernate section of the appConfig, so that the connection sucessfully points to the correct dbServer?

Sorry to ask.Can I clarify anything?

cheers,
yogi

martijnb · **Joined:** Sat May 14, 2005 8:40 am **Posts:** 130

Anonymous wrote:

cheers,
So, can I safely assume that as yet there is no sql2005 dialect?

Yes, no yet.

Quote:

Is the server name the ONLY thing that I need to change in the nHibernate section of the appConfig, so that the connection sucessfully points to the correct dbServer?

Yes

jemiller · **Posted:** Wed Apr 19, 2006 12:58 pm

Another reason for a MsSql2005Dialect would be to make it use nvarchar(max) and varbinary(max) data types instead of the now obsolete ntext and image data types.

The reason I need it is that I need to do a "distinct" on a query that contains an ntext and it won't work (ADO.NET throws an exception). However, it will work if I change the data type for the field to nvarchar(max) instead.

Does anyone know if there is a way to specify this data type in the mapping files? So far as I can tell, it isn't possible. I thought maybe I could use the "type" attribute of "property", but, I think it wants the .NET data type not the SQL data type here.

jemiller · **Posted:** Wed Apr 19, 2006 5:02 pm

I was able to create a patch for this (slight change to MsSql2000Dialect). I submitted it to JIRA, so, hopefully it will get in there by the next release...

Balagan · **Posted:** Thu Apr 20, 2006 10:08 am

The Jira NH-449 http://jira.nhibernate.org/browse/NH-449 has a couple of comments that point to implementations of SQL2005 dialect.

The one pointing to Ayende's blog has a working row_number implementation:
http://www.ayende.com/Blog/2006/03/08/NHibernateAndSQLServer2005.aspx.