How to resolve SQLServerException: Invalid object name?

Question

I am creating a spring boot application using MS SQL server. I have a requirement where I need to initialize USERS table in user_database database using data.sql file placed in /src/main/resources/ folder and rest of the tables should be created automatically in springboot_db database with the help of @Table annotation. Below is the code snippet.

applicaiton.properties file

spring.datasource.platform=mssql
spring.datasource.url=jdbc:sqlserver://localhost;databaseName=springboot_db
spring.datasource.username=sa
spring.datasource.password=einfochips@123
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create-drop
spring.datasource.initialize=true

data.sql

USE master
IF EXISTS(select name from sys.databases where name='user_database')
DROP DATABASE user_database;
CREATE DATABASE user_database;
USE user_database;
CREATE TABLE tbl_users (
  id INTEGER NOT NULL IDENTITY(1,1),
  name VARCHAR(25),
  email  VARCHAR(50),
  username VARCHAR(25),
  password VARCHAR(225),
  gender VARCHAR(1),
  contact_number VARCHAR(12),
  address VARCHAR(150),
  country VARCHAR(20),
  newsletter BIT,
  framework VARCHAR(500),
  skill VARCHAR(500),
  role_id INTEGER,
  PRIMARY KEY (id)
);
INSERT INTO tbl_users 
(name, email, username, password, gender, contact_number, address, country, newsletter, framework, skill, role_id) 
VALUES 
('Admin User1', '[email protected]', 'admin', '$2a$10$WOf9uuaNfUgqpfXrfK1QiO.scUjxJMA.wENEu4c8GJMbPhFwbxMwu', 'f', 919979294062, 'Ahmedabad', 'India', 0, 'Spring MVC', 'Spring', 1);

AppConfiguration.java (One of the entity file)

@Entity
@Table(name = "tbl_configuration_details")
public class AppConfiguration implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = 6657691404940352980L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "config_id")
    private Integer id;

    @Column(name = "schedule_time")
    private Integer scheduleTimePeriod;
// other variables and respective getter setters
}

If I am running my application keeping both the approaches separately than its working fine. When I merge both into a single application, it is showing following error.

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'tbl_configuration_details'.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:232) ~[mssql-jdbc-6.1.0.jre7.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1672) ~[mssql-jdbc-6.1.0.jre7.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:460) ~[mssql-jdbc-6.1.0.jre7.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:405) ~[mssql-jdbc-6.1.0.jre7.jar:na]
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7535) ~[mssql-jdbc-6.1.0.jre7.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2438) ~[mssql-jdbc-6.1.0.jre7.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:208) ~[mssql-jdbc-6.1.0.jre7.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:183) ~[mssql-jdbc-6.1.0.jre7.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:317) ~[mssql-jdbc-6.1.0.jre7.jar:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_144]
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_144]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_144]
    at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.8.0_144]
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) ~[tomcat-jdbc-8.5.23.jar:na]
    at com.sun.proxy.$Proxy92.executeQuery(Unknown Source) ~[na:na]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    ... 94 common frames omitted

Macadamia

Check your database connection string data source = springboot_db in property file and user_database in your SQL file. – Octane

Yes, it is supposed to be like that only because I need to maintain two different databases for two different modules. – Macadamia

Doesn't you need to add hibernate.dialect ex: spring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect And as per my understanding, you need to create the tbl_configuration_details in springboot_db. – Octane

Answer 1

Using the same database for both the tables resolves my issue. However, I am still confused as to why I am not allowed to use two different databases?

Macadamia

Answer 2

com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'tbl_configuration_details'.

You have defined table name as

CREATE TABLE tbl_users

But, in your code

@Table(name = "tbl_configuration_details")

Since, there isn't any object exists with the same name, you will get Invalid object name exception.

Jamie

Both are different tables. tbl_users is supposed to be initialized from sql file and tbl_configuration_details from annotation – Macadamia

@DharitaChokshi But that should be created in your table, if not, then Invalid object name is obvious – Jamie

I am not able to get you. Can you please elaborate? – Macadamia

@DharitaChokshi ultimately, SQL is going to be executed at database layer. If you haven't created that table in database. Then, from db layer you will get this exception – Jamie

@DharitaChokshi make sure you have access to that table. and using same database as I can notice you defined databaseName=springboot_db but in sql you are using USE user_database; Both are different database – Jamie

Yes, it is supposed to be like that only because I need to maintain two different databases for two different modules. – Macadamia

@DharitaChokshi it is upto your requirement, but they should be accessible – Jamie

Answer 3

I had same problem and find out that needed put in properties the database name.

Example for Spring Boot:

spring:
  datasource:
    url: jdbc:sqlserver://localhost:1234;databaseName=DATABASENAMEHERE 
    username: USER
    password: ******
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver

Obduce

Answer 4

I understood the error.

The nature of SQL Server is that it is multi-database.

So we have a DataServer that contains Databases that contains Schemas that contains Tables.

So the JDBC driver expects something as user_database.dbo.tbl_users in your case instead of tbl_users.

The Java generated by the annotation has to generate the absolute name "user_database.dbo.tbl_users" instead of the relative name "tbl_users".

Hope that helps.

UPDATE: if your table is in dbo you may not have a problem.

Linzy

Answer 5

I had the same error. I've found that hibernate makes table name and table columns to lowercase automatically. It's like: table Material with column fullName will be: material and column - full_name.

I added property:

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

in file

src/main/resources/application.properties 

and it solved my problem.

Garnishee

Answer 6

for me, i had to set schema in the table annotation

like this,

@Table(name="table_name", schema = "schema_name").

putting schema.table under name didn't work.

Landowner