Problem using Hibernate and SQL Server 2008

Question

I'm having problems using Hibernate and SQL Server 2008. When I try to save an object to database Hibernate throws this:

could not retrieve snapshot: com.my.MyClass

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name `'emanagement.patient_visit'.`

The user has select, insert,update privileges in database. So I ruled that problem out.

This is the SQL generated:

select
        patientvis_.account_number,
        patientvis_.status as status1_,
        patientvis_.cpt_code as cpt3_1_,
        patientvis_.locked as locked1_,
        patientvis_.state as state1_,
        patientvis_.userid as userid1_ 
    from
        emanagement.patient_visit patientvis_ 
    where
        patientvis_.account_number=?

If I run the above SQL in SQL Server it says invalid object name emanagement.patient_visit, but if I manually add that "dbo" emanagement.dbo.patient_visit than it will get exsecuted.

So is there any other Hibernate configuration I need to make?

This is my Hibernate mapping. The below mapping works under MySQL. I can read and update patient_visit in database. But when switching to MS Server it fails. I have tried other hibernate mappings which work for both MySQL and MS Server and they both use the same declarations as below like table="db_table" schema="my_database". The only difference is that I created this new emanagement database under MS Server, so I'm thinking that I missed some specific database configuration on the MS Server management tool. The only way to prove this is for me to move the new tables from emanagement to an existing database and see if it works.

<class name="com.domain.patient.model.PatientVisit" table="patient_visit"    schema="emanagement">
        <id name="accountNumber" type="java.lang.Long">
            <column name="account_number" precision="22" scale="0" />
            <generator class="assigned"/>
        </id> 
        <property name="status" type="string">
            <column name="status"/>
        </property>
        <property name="cptCode" type="string">
            <column name="cpt_code"/>
        </property> 
        <property name="locked" type="boolean">
            <column name="locked" precision="1" scale="0"/>
        </property>  
        <property name="state" type="string">
            <column name="state"/>
        </property>  
        <property name="userId" type="string">
            <column name="userid"/>
        </property>  
               <set name="documents" lazy="false">
            <key column="account_number"/>
            <one-to-many class="com.domain.document.model.Document"/>
        </set>     
    </class>

Thanks in advance.

Answer 1

So is there any other Hibernate configuration I need to make?

With your current setup, I guess you'll have to specify the schema. For example, in the mapping:

<class name="Users" table="Users" schema="dbo" catalog="suiteaccess">

But you can also specify the default schema using the hibernate.default_schema property (see 3.4. Optional configuration properties).

Just in case, you can create your own schema.

Answer 2

Change the schema to dbo. In SQL Server, schema is a container in the database, not the database itself. This may be different in MySQL, but in this case, your schema should be dbo (based on your comment about it working if you add "dbo." to your resultant query.

Thanks, Eric