How to solve com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'dbo.Table2'

Question

I am inserting data from Java in sql server 2008 DB by reading the data from 1st Database [WBSEDCL].[dbo].[Table1] and then inserting it into second Database [OrganizationMaster].[dbo].[Table2] .
I am using sqljdbc41.jar in my Project.
The code for insertion is as following -

private static Connection getNewDBConnection() {
        System.out.println("************************Inside Get DB Connection**************************");
        Properties props = new Properties();
        if (connection != null)
            try {
                if (!connection.isClosed())
                    return connection;
            } catch (SQLException e) {
                e.printStackTrace();
            }

        try {
            String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
            String url = "jdbc:sqlserver://142.168.0.112:1733;DatabaseName=OrganizationMasterDB";
            String user = "sa";
            String password = "Dsdf@123";
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user, password);
            if (!connection.isClosed())
                System.out.println("---------------------DB Connection is Established----------------------");
        } catch (ClassNotFoundException e) {
            System.out.println("Class Not Found Exception Thrown ");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("SQL Exception Thrown");
            e.printStackTrace();
        }

        return connection;
    }


     public static void insertDetailsList(PersonalLedger pl) {
        Connection conn = getNewDBConnection();
        PreparedStatement statement = null;
        DetailsList dl = null;
        int temp=0,shareAmount=0,shareBalance=0,theiftFundAmount=0,theiftFundInterest=0,GAmtDepo=0,GInterest=0,ShareWithdrawn=0;
        String EmNo=null,MemberNo=null, fundString = "Share",status="Unknown",remarks="OtherAmount for Share is The Withdrawn Share Amount",sql=null;
        boolean flag= false;
        Date sdate = pl.SDate,gdate=pl.Gdate,tdate=pl.TDate;
         EmNo = pl.EmNo;
         MemberNo = pl.MemberNo;
         shareAmount = pl.SAmtDepo;  
         shareBalance = pl.balance;
         ShareWithdrawn = pl.ShareWithdrawn;
         theiftFundAmount = pl.TAmtDepo;
         theiftFundInterest = pl.TInterest;
         GAmtDepo = pl.GAmtDepo;
         GInterest = pl.GInterest;
        DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        Calendar cal = Calendar.getInstance();
        try{
            System.out.println("*****************INSERTING SHARE FUND DETAILS******************");
            sql = "INSERT INTO [dbo].[DetailsList] VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
            statement = conn.prepareStatement(sql);
            statement.setString(1, pl.EmNo);
            statement.setString(2, pl.MemberNo);
            statement.setString(3,"Share");
            statement.setLong(4, shareAmount);
            /*Date share_date = (Date) formatter.parse(new Date());*/
            statement.setLong(5,0);
            statement.setLong(6,pl.ShareWithdrawn);
            statement.setString(7,"Unknown");
            statement.setString(8,"OtherAmount for Share is The Withdrawn Share Amount");
            statement.setDate(9, pl.SDate);
            statement.setDate(10,null);
            statement.setLong(11, shareBalance);
            temp = statement.executeUpdate();
            if (temp != 0) {
                flag = true;
                System.out.println("ROW INSERTED SUCCESSFULLY");
            }
        } catch (Exception e) {
            System.out.println("Exception in Insert Details List Items");
            e.printStackTrace();
        }
        }

The problem whenever i run the code i get SQLException. The stackTrace is as below :

Exception in Insert Details List Items
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'dbo.DetailsList'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:215)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1635)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:426)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:372)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5846)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1719)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:184)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:159)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:315)
at Test.insertDetailsList(Test.java:203)
at Test.main(Test.java:290)

I am completely dumbstruck by this error and could not find any relevant solution for the problem over the net !
Any help will be appreciated. Thanks.

EDIT: After doing some research I moved Table2 to the 1st Database, made necessary changes in connection string and executed the program. The code executed without any Error. So the question remains -
1.why the schema not found earlier when Table2 was under 2nd Database?
2.Is there any configuration required in the Database in order for java to connect with the correct schema and access the table, if so then What ?

Solution 1

Use the [dbname].[dbo].[tblname] query, e.g.select * from [dbname].[dbo].[tblname]

Solution 2

Assuming your INSERT is for Table2 then it should change from

sql = "INSERT INTO [dbo].[DetailsList] VALUES "
        + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

to include the target database name. With something like (and you don't need the semi-colon in the query)

sql = "INSERT INTO [OrganizationMaster].[dbo].[DetailsList] VALUES "
        + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

Solution 3

Check table DetailsList is exist and user has permission on this table, You can check this my logging into SQL server management studio from that user and try to access the table.