Why queries won't work even though connection with database is ok?

Question

I created database in SQL Server Management Studio. My connection with database is ok, but query I try to execute is causing problems. It throws me "Invalid object name 'tw__towar'". Even though the query is working fine when I execute it in SQL Server Management Studio.

Here is code fragment responsible for connection.

try {
        Class.forName(ssmsDriver);
        connection = DriverManager.getConnection(url, userName, userPassword);
        Log.d(TAG, "onCreate: connected to the database");
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
        Log.e(TAG, "onCreate: class not found", e);
    } catch (SQLException e) {
        e.printStackTrace();
        Log.e(TAG, "onCreate: sql exception", e);
    }

And here is code responsible for executing query. Double underscore intended.

try {
                statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery("SELECT tw_nazwa FROM tw__towar" +
                        " WHERE tw_ean='5903206038608';");
                mItemNameTextView.setText("SUCCESS");   // DELETE THIS LATER

            } catch (SQLException e) {
                e.printStackTrace();
                mItemNameTextView.setText(e.getMessage());  // DELETE THIS LATER
            }

Does anybody have any idea what may be causing problem?

Please give schema name with table name . Here "SELECT tw_nazwa FROM [schema_name].tw__towar" + " WHERE tw_ean='5903206038608';" — Harmandeep Singh Kalsi, Jun 30 '20 at 10:51
@Stultuske error message is "Invalid object name 'tw__towar'" — Arkadiusz, Jun 30 '20 at 11:17
@HarmandeepSinghKalsi Here is screenshot of what I hope is what you asked for: https://postimg.cc/Ty32DZTg — Arkadiusz, Jun 30 '20 at 11:17
Can you run this command in DB? SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']' AS SchemaTable FROM sys.tables — Harmandeep Singh Kalsi, Jun 30 '20 at 11:27
@HarmandeepSinghKalsi Yes, it returned me 4 records. First 3 are my tables, and the last one is [dbo].[sysdiagrams] — Arkadiusz, Jun 30 '20 at 11:37
SchemaTable [dbo].[tw__towar] [dbo].[uz__uzytkownik] [dbo].[tw_termin] [dbo].[sysdiagrams] — Arkadiusz, Jun 30 '20 at 11:44
Okay do write this is your query please ResultSet resultSet = statement.executeQuery("SELECT tw_nazwa FROM dbo.tw__towar" + " WHERE tw_ean='5903206038608' "); — Harmandeep Singh Kalsi, Jun 30 '20 at 12:00
Unfortunately it gives similar result, which is: "Invalid object name 'dbo.tw__towar'". — Arkadiusz, Jun 30 '20 at 12:11
Please check this link once if it helps https://stackoverflow.com/questions/1362531/sql-server-invalid-object-name-but-tables-are-listed-in-ssms-tables-list — Harmandeep Singh Kalsi, Jun 30 '20 at 13:52

score 0 · Accepted Answer · answered Jul 01 '20 at 11:34

I managed to solve this problem, so I'll post answer here for anyone that would have the same issue in the future.

The problem was that I tried to operate on the sa account (system administrator). I could connect to database but it cut off my access to tables.

To solve this problem you need to create new login by:

right-click on "Security" folder in object explorer
select New -> Login
enter your new login
check "SQL Server authentication" and set your password
I unchecked "Enforce password expiration"
Go to "Server Roles" which is on the left side of this window and check "dbcreator"
Click OK, and restart program.
Login with new credentials and check if everything works.

I hope it will help someone in the future.

Why queries won't work even though connection with database is ok?

1 Answers1