Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Nov 2007
    Posts
    19

    Unhappy Unanswered: Can't 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,

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    probably you are executing your query in the context of some other database, not the one you created yesterday.

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    The error seems to suggest that the object that you referenced doesn't exist in the schema.

    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Nov 2007
    Posts
    19
    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,

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Nov 2007
    Posts
    19
    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".

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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:

    Code:
    select * from tblTXMup
    If you still receive the same message, try the following and let me know what the result is.

    Code:
    select
      *
    from
      information_schema.tables
    wehre
      table_name = 'tblTXMup'
    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Nov 2007
    Posts
    19
    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?

  9. #9
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  10. #10
    Join Date
    Nov 2007
    Posts
    19
    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.

  11. #11
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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.

    Code:
    create table tblTXMup (id int);
    Regards,
    Last edited by r123456; 11-16-07 at 05:19.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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.
    Code:
    SELECT * FROM myTable
    --equivalent to
    SELECT * FROM dbo.myTable
    Try running the following and let me know if you get any results
    Code:
    SELECT *
    FROM   sysobjects
    WHERE  type IN ('U', 'V')
    AND    name LIKE '%tblTXMup%'
    George
    Home | Blog

  13. #13
    Join Date
    Nov 2007
    Posts
    19
    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.

  14. #14
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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:
    Code:
    select db_name()
    Do you get the expected result?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •