Java/MSSQL: java.sql.SQLException Invalid object name 'TableName'.

views:

1287

answers:

+1 Q:

Java/MSSQL: java.sql.SQLException Invalid object name 'TableName'.

I'm trying to move a java application from an old server to a new server. The application runs on Tomcat, uses Microsoft SQL Server as the backend DB, and used a system DSN defined in Data Sources (ODBC) to decide where to connect to. The old server used Windows 2000/SQL server 2000, the new server uses Windows 2003/SQL Server 2005.

The ODBC definition is identical between servers, and defines the DB to use.

On the new server when a user tries to login the following appears in the stdout.log:

user is being checked Error: java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'UserTable'.

A table called UserTable does exist in the DB, but shows as "dbo.USerTable" through the management interface.

Is there a setting somewhere in MSSQL I should be adjusting to make it happy with the "dbo." prefix being missing, or is there something else I've missed?

[edit] Windows authentication is used, and the Public and Guest roles have been given full rights to the DB as a troubleshooting step.

+1 A:

Do you have to use ODBC in the first place? In my experience the "native" (type 4) SQL Server drivers are better. There's a Microsoft driver and jTDS. I don't have much experience with the MS driver, but the jTDS one is good.

Of course, if you're forced to use ODBC by other factors, this won't be any use to you - but if you have the option, it would be worth a try. It would be one fewer thing to configure on the Windows box.

Jon Skeet 2009-05-26 08:54:20

I'd prefer to not have to do any recoding of the app - it's a old legacy tool, and works fine using ODBC on the current server so it's hard to justify time redoing the DB code, especially since everyone involved with its creation is long gone so we'd need to spend a lot of time figuing out what they did.Redoing the entire thing from scratch would be a nicer option, but it doesn't look like that will happen. :-(

DrStalker 2009-05-26 09:02:06

+1 A:

Are the DB logins different? the dbo "prefix" is just the schema that the object is defined under. So if your ODBC connector is using the dbo credentials to login that object should be in it's default schema.

Gandalf 2009-05-26 20:05:53

Setting the defaut DB for the sa account fixed this, even though the sa account wasn't being used to log in (and the application had no way of knowing the sa credentials)Strange, but an acceptable workaround given that this is a stand-alone system and this app is all the DB will host.

DrStalker 2009-05-26 23:58:34

ansaurus

tags:

views:

answers:

Java/MSSQL: java.sql.SQLException Invalid object name 'TableName'.

related questions