Cant run query today that ran yesterday

I converted an MS Access db to SQL Server 2005 Express yesterday. I used FullConvert Enterprise for the conversion and it worked great. I ran several queries and saved them, and they ran fine. Today, running the same queries, I get this error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tblTXMup'

I googled the message and found someone who had a similar problem and their answer was they were not the dbo. I checked the new database and it was owned by sa so I logged in as sa and got the same error.

Can anyone set me straight so I can get into this db?

Thanks,probably you are executing your query in the context of some other database, not the one you created yesterday.|||The error seems to suggest that the object that you referenced doesn't exist in the schema.

Regards,|||Thanks for looking at this. I only converted the one database so I know it is the right one. I can display the table I am trying to query, so I know I am logged in to the right one. The query was saved from yesterday and it got a result then but throws the error now.

Any other ideas?

Thanks,|||Sure,

Can you execute a query (or if you fancy, a DML statement for a change) against the table. It is important to differentiate between just seeing the table listed as a database object and the ability to execute a statement against it.

Regards,|||I can't execute a query because I get the error above, but I can not only see the table in the left panel of SQL Management Studio Express but can also display the table's data by right clicking on it and selecting "Open Table".|||OK. Something is not right here.

From what you say above, this is in no a security issue. When you right click on a table and click 'Open Table', the underlying event will just execute a SELECT statement against the table.

Therefore, you should be able to execute:

select * from tblTXMup

If you still receive the same message, try the following and let me know what the result is.

select
*
from
information_schema.tables
wehre
table_name = 'tblTXMup'

Regards,|||Robert,

Thanks again for helping. After running your query I got what looked like five column headers as follows: blank, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE

What does that tell us?|||What was the output returned for each query?

For the first, did you receive an error message, an empty result set, or a set of rows? And similarly for the second, did you receive a single row describing the table,or did you just receive the column headers, but no rows?

Regards,|||Thanks, Robert. From the first, that is the one that I started this thread with, and it still throws the same error. In the second, I got no error, did get headers, got no rows. The headers act like buttons that "depress" when clicked on.|||Could you please try to create the table under the same login account that from which you ran the above queries. At this stage, we wish to examine ownership configurations for the table and the databases that your account is accessing.

What may be happening is that you have two sessions open, each accessing a different database. Your first session may display the 'Welcome Page' to SQL Server 2005 management studio. When you browse the object tree, you select the appropriate database and then browse the object. Having selected 'DatabaseA' from the Object List, you see the results that you had expected.

On the other hand your second session (which appears as a new tab) has opened to an SQL Query interface, but the session is connected to a different database by default. This may well be the Master database. Consequently when you run the select query against the master database, SQL Server reports that your table does not exist.

You may just want to check which database your SQL Query interface session is connected to, and ensure that this is indeed the database that contains the table you wish to query. If this session appears to be accessing the correct database, then you can create a simple table from this session with the same name as the one you are trying to query, and note the outcome.

Once you have created this table, check to see that it appears in the database you expected it to, in the Object Browser. Once you have found it, compare this location to that of the original table that you mentioned in you first post, against which you were able to right click and select 'Open Table'.

The following code will create a simple one column table.

create table tblTXMup (id int);

Regards,|||Has it got anything to do with 3 part naming?
Objects that aren't given a 2nd part are assumed to be owned by "dbo"
i.e.

SELECT * FROM myTable
--equivalent to
SELECT * FROM dbo.myTable

Try running the following and let me know if you get any results

SELECT *
FROM sysobjects
WHERE type IN ('U', 'V')
AND name LIKE '%tblTXMup%'|||George, I ran your query and got a number of column headings but no rows returned. Headings started off name, id, xtype, uid, ...

Does that tell us anything?

Thanks for helping.|||Loyd,

Have you performed the steps that I mentioned in my most recent post? They are designed such that by the end, you should almost certainly know the answer to your problem.

The essence of the problem is this: We need to identify the a) where the table is, b) which account owns it, and most importantly, c) which account you are connected as when you are running these queries that we have supplied.|||OK, so you can open the table via enterprise manager, but can not get to it in Query Analyzer. That sums up the problem? From the results of the queries above, I suspect the two tools are looking at different databases. Run this query to see what database you are currently in in Query Analyzer:

select db_name()

Do you get the expected result?|||r123456 and MCrowley you are both on to something. When I created the table it appeared in the master system db. How can I move to or attach to the correct db?|||Exactly what I thought. You had two sessions open, each pointing to different databases.

I would attach a screen shot to explain, but I don't have SQL Server Management Studio installed. There is a drop down list on the tool bar, just above the query windows, which contains the list of databases that you're account has access to. The database name that appears selected in this list, is the one that all statements in the current session will be executed against, unless of course you explicitly define the database name in the query, which evidently you have not done on this occasion.

Once you've selected the session in which you want to run the SQL Query (not the one you used to browse the Object Tree), ensure that the value in this drop down list is the one for which you want your query to be executed against.

Regards,|||One note, putting user tables in the master database is generally a bad thing. Recovering the master database from backup requires a series of steps that include stopping and restarting the whole server several times. This gets to be annoying to anyone else who happens to be using the server at the time. I would suggest either redoing the upgrade to a user database (you can create one through Enterprise Manager easily enough), or exporting the data from master to a user database. The choice will depend on how many foreign keys you have created in there.|||Ah yes. I will second that suggestion. The number of times I have gone to execute a query, only to notice that the database is pointing to Master.

Another suggestion is to create administrator accounts that are a step down in terms of access rights from the DBO account. This is common practice in most organisations as it achieves two major benefits. Firstly, you have accountability for all access to the database if you configure access to SQL Server to be through Windows NT accounts. Secondly, you can ensure that users only have access to the databases that is permitted by your firm's access policy.

Regards,|||Thanks once more for all the assistance. I am finding out what a newbie I am with SQL Server. I have been submitting SQL queries to SQL Server for years, but have not had a great deal of experience with administering it.

I can submit a query by specifying the database, but the dropdown where I could specify a database to connect to is grayed out and I can't access it. What can I do in SQL Server Management Studio Express to get access to that dropdown?

BTW, as far as permissions, I am working on SQL Server on my own development machine, trying to get all the queries working before uploading to the host development site.

Thanks again|||Thanks once more for all the assistance. I am finding out what a newbie I am with SQL Server. I have been submitting SQL queries to SQL Server for years, but have not had a great deal of experience with administering it.

I can submit a query by specifying the database, but the dropdown where I could specify a database to connect to is grayed out and I can't access it. What can I do in SQL Server Management Studio Express to get access to that dropdown?

BTW, as far as permissions, I am working on SQL Server on my own development machine, trying to get all the queries working before uploading to the host development site.

Thanks again

Loyd,

You will need to ensure that you have the set the focus on an SQL Server query session, for it is these sessions that the drop down list applies to. When you want to access the Database name drop down list, first click anywhere in the session window (example: the window where you type the SQL queries) and then the drop down list become available.

It is unlikely that there are other reasons for the drop down list not being available for you to access.

Regards,