Basic question about "Invalid object name" error on table

In some cases, when referring to objects in the database, you must precede the name with the owner name - e.g. "objectowner.NumgenCreateStatus" especially is the object was created by someone other than "dbo".

Try reading that table using Query Analyzer.  Make the connection to QA using the same username and password that you are using from VC.

Thanks for the ideas.

esmoreno, the table/object is owned by "dbo" so I assume it was created by "dbo"?

Scott, I tried Query Analyzer using the same login (my own username/domain) as from VC, and got essentially the same result:
Query: "SELECT * FROM NumgenCreateStatus ORDER BY ProdRun DESC"

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'NumgenCreateStatus'.

As you can see it's a very simple query as well.  In Query Analyzer, I can see NumgenCreateStatus as a User Table, and can expand it, etc.  Here's a question.  I only have the DB set up as a System DSN, not a User DSN.  But so do other people, for whom this all works.  Should that matter at all?

Also, make sure you are addressing the object from the right database

Now you know for sure that the object really does not exist, at least not with owner dbo.

Try this query in QA:

SELECT name, xtype, uid AS [owner id], USER_NAME(uid) AS [owner name]
FROM sysobjects
WHERE name LIKE '%NumgenCreate%'

to verify that the object exists (or not), owner is "dbo" (or not).

If that doesn't turn up anything, expand the search (maybe the table name is just spelled differently):


SELECT name, xtype, uid AS [owner id], USER_NAME(uid) AS [owner name]
FROM sysobjects
WHERE name LIKE 'N%'
ORDER BY name

Thanks for the help.

jgsrikanth, good thing to check.  My connection string is "Data Source=SecretNumbers" which is also the DB listed in the ODBC Data Source Administrator / System DSN tab as a System Data Source.

But in Query Analyzer (and Enterprise Manager), the DB is not bumpy case-- it's "secretnumbers".  The .MDF file in the SQL Server data area is "SecretNumbers.mdf" however.  Does any of this matter?

Scott, the query you provided comes back empty:

name                                                                                                                             xtype owner id owner name                                                                                                                      
-------------------------------------------------------------------------------------------------------------------------------- ----- -------- --------------------------------------------------------------------------------------------------------------------------------

(0 row(s) affected)

I guess I'm really confused.  If it doesn't exist, then why does QA list it as a User Table?

Scott, that second query came back empty as well:

name                                                                                                                             xtype owner id owner name                                                                                                                      
-------------------------------------------------------------------------------------------------------------------------------- ----- -------- --------------------------------------------------------------------------------------------------------------------------------

(0 row(s) affected)

About to try all lower case on the DB name, everywhere.

Same result (invalid object name) with the connection string "Data Source=secretnumbers", System DSN / System Data Source = "secretnumbers", DB file name = "secretNumbers.mdf".

It's quite a feeling knowing I'm really going to feel stupid soon, but don't yet know when!

Make sure the userid / login you are using to make the connection has access/permission to the db and table(s) in question.  If not, SQL makes it look as if they dont exist.

do you have a case sensitive SQL installation?

use the following command in the query windows

exec master..sp_helpsort

Thanks everyone.

Scott, good point-- I made sure the userid included the domain (because that is how the app logs onto the DB), and using Enterprise Mgr I looked at my user under enabled everything I could for that user, but I still got the same result.

jgsrikanth, the output from that query tells me I have a case-insensitive install (thanks for that tidbit):

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

-------------

OK, first stupid thing... in QA I "Changed Database..." to NumgenCreateStatus, and then the query in question worked!  But why won't the application work?  What steps does it need to perform in addition to the following?

class CNumgenApp : public CWinApp
{
public:
.
.
      CString m_connection;

      // ADO
      _ConnectionPtr      m_pConnection;
.
.
}
.
.      
BOOL CNumgenApp::InitInstance()
{
.
.
m_connection = "Data Source=secretnumbers";
.
.
try
{
      m_pConnection.CreateInstance(__uuidof(Connection));

      m_pConnection->ConnectionTimeout = 300; // 5 minute timeout
      m_pConnection->CommandTimeout = 300;      // 5 minute timeout

      m_pConnection->Open(m_connection.AllocSysString(),"","",adModeUnknown);
                  
      pRecordset.CreateInstance(__uuidof(Recordset));
      pRecordset->CursorLocation = adUseClient;

      // THIS CALL FAILS
        pRecordset->Open((const _variant_t)"SELECT * FROM NumgenCreateStatus ORDER BY ProdRun DESC",
                        m_pConnection.GetInterfacePtr(),
                        adOpenDynamic,
                        adLockOptimistic,
                        adCmdText);
.
.
}
.
.
}