Hi,

we came across this when switching from MySQL to MS SQL Server 2005. The generated SQL (see below) has the database name prefixed before the table name. While this certainly makes sense in a way and works fine with MySQL, it fails with the MS SQL database.

Hibernate version: 3.1.1

Full stack trace of any exception that occurs:
WARN JDBCExceptionReporter.logExceptions SQL Error: 208, SQLState: S0002
ERROR JDBCExceptionReporter.logExceptions Ungültiger Objektname 'elsbeth.ptl_mandant'.

Name and version of the database you are using: MS SQL Server 2005

The generated SQL:
select
this_.MDT_ID as MDT1_0_0_,
this_.MDT_CODE as MDT2_0_0_,
this_.MDT_KEY as MDT3_0_0_
from
elsbeth.ptl_mandant this_
where
this_.MDT_KEY=?

1. To work for MS SQL, either the database name has to be omit or the database owner name must be included:

select ... from ptl_mandant this_ ...
select ... from elsbeth.dbo.ptl_mandant this_ ...

(dbo is the usual name of the database's owner).

2. I've tried to find some similiar posts / problems with no success. However, anybody who posted his SQL did not have the database prefixed before the table name. So we might have a wrong setup, though it's quite standard:

Our mapping:
Hibernate references to a JNDI datasource, configured in the context.xml of our Tomcat

<hibernate-configuration>
<session-factory>
<property name="connection.datasource">java:comp/env/jdbc/elsbeth</property>
<property name="show_sql">true</property>
<property name="format_sql">true</property>
<property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>
<property name="current_session_context_class">thread</property>

<mapping resource="..." />
...
</session-factory>
</hibernate-configuration>

The context.xml:
<Context crossContext="true" debug="0"
docBase="elsbeth_portal" path="elsbeth_portal" reloadable="true">

<Resource auth="Container"
description="DB Connection for the Elsbeth portal"
driverClass="com.microsoft.sqlserver.jdbc.SQLServerDriver"
maxPoolSize="50"
minPoolSize="5"
acquireIncrement="1"
name="jdbc/elsbeth"
user="elsbeth"
password="******"
factory="org.apache.naming.factory.BeanFactory"
type="com.mchange.v2.c3p0.ComboPooledDataSource"
jdbcUrl="jdbc:sqlserver://192.168.100.10;databaseName=elsbeth"
automaticTestTable="connectionTest"
idleConnectionTestPeriod="20"
testConnectionOnCheckout="true"/>
</Context>

Do you have the [schema="xxxx"] attribute in your mapping file (.hbm.xml)?

Curtis ...

Hi,

the solution has been found, the following entry needs to be added to the hibernate.cfg.xml:

<property name="hibernate.default_schema">dbo</property>

Thanks to CWitherow for the basic idea.