JBoss + MS Sql server 2005: 'nvl' is not a recognized built-

[illi]

Hi All,

We have been trying to configure JBoss to use MS Sql server 2005 from our application. Technology we used in our application is hibernate. We are now able to access our application, but while loading some objects in one of screens, it is showing a error "Caused by: java.sql.SQLException: 'nvl' is not a recognized built-in function name." what more need to be configured to make it use such functions.

--> following is the ds config we used.
<local-tx-datasource>
<jndi-name>applDS</jndi-name>
<connection-url>jdbc:jtds:sqlserver://common:3403;instanceName=RESEARCH;databaseName=prdName</connection-url>
<driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
<use-java-context>false</use-java-context>
<user-name>myName</user-name>
myPwd

<type-mapping>MS SQLSERVER2005</type-mapping>

</local-tx-datasource>
--------------------------------------------------------------------------------------

[illi]

Adding full log info:

2008-08-04 12:05:30,613 INFO [STDOUT] Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.loader.Loader.doList(Loader.java:1565)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.loader.Loader.list(Loader.java:1545)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.impl.SessionImpl.list(SessionImpl.java:840)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
2008-08-04 12:05:30,613 INFO [STDOUT] at com.dcs.applications.service.LogicModuleServiceBean.getDisplayValueForValueObjectWithLocaleData(LogicModuleServiceBean.java:2228)
2008-08-04 12:05:30,613 INFO [STDOUT] at com.dcs.applications.service.LogicModuleServiceBean.getDisplayValueForValueObject(LogicModuleServiceBean.java:2157)
2008-08-04 12:05:30,613 INFO [STDOUT] at sun.reflect.GeneratedMethodAccessor298.invoke(Unknown Source)
2008-08-04 12:05:30,613 INFO [STDOUT] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
2008-08-04 12:05:30,613 INFO [STDOUT] at java.lang.reflect.Method.invoke(Method.java:585)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.invocation.Invocation.performCall(Invocation.java:345)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:214)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:185)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:130)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.webservice.server.ServiceEndpointInterceptor.invoke(ServiceEndpointInterceptor.java:51)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:48)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:105)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:335)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:166)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:139)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:192)
2008-08-04 12:05:30,613 INFO [STDOUT] ... 59 more
2008-08-04 12:05:30,613 INFO [STDOUT] Caused by: java.sql.SQLException: 'nvl' is not a recognized built-in function name.
2008-08-04 12:05:30,613 INFO [STDOUT] at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
2008-08-04 12:05:30,613 INFO [STDOUT] at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
2008-08-04 12:05:30,613 INFO [STDOUT] at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
2008-08-04 12:05:30,613 INFO [STDOUT] at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
2008-08-04 12:05:30,613 INFO [STDOUT] at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:418)
2008-08-04 12:05:30,613 INFO [STDOUT] at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:693)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:296)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:118)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.loader.Loader.getResultSet(Loader.java:1239)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.loader.Loader.doQuery(Loader.java:374)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:210)
2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.loader.Loader.doList(Loader.java:1562)

[jaikiran]

Can you post the query that is being fired, which runs into this error? You might have to enable show_sql on hibernate to see those queries in the logs.

[peterj]

Also, in your hibernate configuration file, what dialect are you using?