java 如何解决 com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'dbo.Table2'
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31561870/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to solve com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'dbo.Table2'
提问by Arindam Das
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.jarin my Project.
The code for insertion is as following -
我通过从第一个数据库[WBSEDCL].[dbo].[Table1]读取数据，然后将其插入到第二个数据库[OrganizationMaster].[dbo].[Table2] 中，在 sql server 2008 DB 中插入来自 Java 的数据。
我在我的项目中使用sqljdbc41.jar。
插入代码如下 -
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 :
每当我运行代码时，我都会遇到 SQLException 的问题。堆栈跟踪如下：
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 ?
我完全被这个错误弄糊涂了，无法通过网络找到任何相关的问题解决方案！
任何帮助将不胜感激。谢谢。
编辑：在做了一些研究之后，我将 Table2 移到了第一个数据库，对连接字符串进行了必要的更改并执行了程序。执行的代码没有任何错误。所以问题仍然存在 -
1.为什么当 Table2 在第二个数据库下时没有更早地找到模式？
2.数据库中是否需要任何配置才能让 java 连接到正确的模式并访问表，如果是，那么什么？
回答by Elliott Frisch
Assuming your INSERTis for Table2then it should change from
假设你INSERT是为Table2那么它应该从
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 "
+ "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
回答by Sumit Bansal
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.
检查表DetailsList 是否存在并且用户对此表有权限，您可以检查我从该用户登录SQL server management studio 并尝试访问该表。
