aspose file tools*
The moose likes JDBC and the fly likes Can't return data from MSSQL 2008 named instance Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Can Watch "Can New topic
Author

Can't return data from MSSQL 2008 named instance

Mbulelo Jadula
Greenhorn

Joined: Apr 23, 2010
Posts: 3
Hi All

I can't return data from my MSSQL 2008 database.Iget the following error:
"Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'Marketing.SiteVisitors'.
I know for a fact that I do have a table called SiteVisitors that is part of the Marketing schema
Below is my code:[
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Can the user spotty see that schema? Have you connected to the default schema for that user?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19670
    
  18

You're used to MySQL, aren't you?

In MS SQL Server you have two options if you want to work with tables/views from another database:
- include the schema. For example, "Select LastName,FirstName from Marketing.dbo.SiteVisitors" if the schema for this table/view is "dbo"
- omit the schema to use the default; the two dots are still required though. For example, "Select LastName,FirstName from Marketing..SiteVisitors"

I use the latter most of the time.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Mbulelo Jadula
Greenhorn

Joined: Apr 23, 2010
Posts: 3
Thanks for repsonding Paul.
Yes when connecting via the MS SQL Managment Studio with the user spotty,I can view the table.What's odd is that even if I change the connection string to use the 'sa' account I still encounter the same error. I don't have this issue when connencting to a none named instance.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19670
    
  18

Is my post invisible or something? It shows exactly how to solve this.
Mbulelo Jadula
Greenhorn

Joined: Apr 23, 2010
Posts: 3
Hi Rob

Thanks for the reply and willigness to assist. I don't know why but when i posted my reply your response was not visible. I have tried your suggestion and I am still getting the same error.
In fact with the 'sa' account it says the previous error but if i try with the spotty user Login failed.I know this has nothing to do with the password because I have verified this over and over that this user has access via Management Studio.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


In fact with the 'sa' account it says the previous error but if i try with the spotty user Login failed

Presumably there is no user associated with the login spotty in the database Test, and the login does not have DBA rights. If you chec the security for your user and login you should see what is missing.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Can't return data from MSSQL 2008 named instance