Can't return data from MSSQL 2008 named instance

[Mbulelo Jadula]

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:[

  String connectionUrl = "jdbc:sqlserver://localhost:1433;instanceName=Ventra/2008" +
           "databaseName=Test;user=spotty;password=dotty;";
           Connection con = DriverManager.getConnection(connectionUrl);
           PreparedStatement statement=con.prepareStatement("Select LastName,FirstName from Marketing.SiteVisitors");
           ResultSet result=statement.executeQuery();
               while(result.next()){
                   System.out.println(result.getString("LastName")+ " "+result.getString("FirstName"));
     } 
}  

[Paul Sturrock]

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

[Rob Spoor]

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.

[Mbulelo Jadula]

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]

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

[Mbulelo Jadula]

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]

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.