none
Problem with INSERT - Invalid object name - table that appears in database

    Question

  • CREATE DATABASE MyDatabase;

    OK, then

    USE MyDatabase;        

    CREATE TABLE orders

    (id INT IDENTITY(1,1) PRIMARY KEY,

    customer VARCHAR(50),

    day_of_order DATETIME,

    product VARCHAR(50),

    quantity INT);

    OK.

    but when I do this:

    USE MyDatabase;

    INSERT INTO orders
    (customer,day_of_order,product, quantity)
    VALUES('Tizag','8/1/08','Pen',4);

    I get error message:   Invalid object name 'orders'

    and the column names are Invalid.

    The table 'orders' appears in the Object Explorer in MyDatabase as dbo.orders

    I tried    INSERT INTO dbo.orders

    Get error message:      Invalid object name 'dbo.orders'

     What is the problem?

     

     

    Monday, October 10, 2011 1:04 AM

Answers

  • Today I tried to replicate the problem.

    Delete the database. Do everything the same way I did yesterday - create database, add table 'orders', and insert values for one row.

    I get no error messages.

    Does it make a difference if I'm drinking coffee?

     

    Monday, October 10, 2011 4:53 PM

All replies

  • You can confirm existance of a table using following query

    SELECT OBJECT_ID('dbo.Orders','U')

    if output is not NULL then your orders table already exists

    now, regarding the errror message 'invalid object name'

    can you make sure that

    a) you are connectiong to the correct database and

    b)  in Management Studio Edit -> IntelliSense ->Refresh Local Cache and/OR

          right click the database, and click "refresh"


    • Edited by Chirag Shah Monday, October 10, 2011 1:33 AM
    Monday, October 10, 2011 1:33 AM
  • Thanks for your reply.

    >>Confirm existence of table:

    USE MyDatabase;
    SELECT OBJECT_ID('dbo.Orders','U')

    Results: 1 2105058535

    If I enter a db with no tables,
    USE test99;
    SELECT OBJECT_ID('dbo.Orders','U')

    Results: 1 NULL

    So the table does exist.
    ====

    >>(a) Can you make sure that you are connectiong to the correct database...

    When I enter
    USE MyDatabase, after keying My, I get intellisense, and I select MyDatabase.
    So I'm using what SQL tells me is available.

    >>(b) in Management Studio Edit -> IntelliSense ->Refresh Local Cache and/OR
    right click the database, and click "refresh"

    My Edit does not have Intellisense option. 
    but I have refreshed the database often, and it shows 'orders' in Tables.

    Help, About:
    Microsoft SQL Server Management Studio 10.50.1617.0
    Microsoft Data Access Components (MDAC) 6.1.7601.17514
    Microsoft MSXML 3.0 4.0 5.0 6.0
    Microsoft Internet Explorer 9.0.8112.16421
    Microsoft .NET Framework 2.0.50727.5446
    Operating System 6.1.7601

    ====
    I tried this:
    drop table orders;
    Get msg: Cannot drop the table 'orders', because it does not exist or you do not have permission.

    I executed this (even tho error msg appears) and the table was deleted!

    It does not appear in Object Explorer.

    USE MyDatabase;
    SELECT OBJECT_ID('dbo.Orders','U')
    now shows NULL.

    Create the table again.
    Try the Insert again.
    Get same error messages, but execute it.
    Message: 1 row affected.

    Therefore, in both cases (drop table, and insert) the error messages seem to be false.
    Have you seen this before?

    ps. Another example: If I Select top 1000 rows, it completes it, but reports error messages on all columns (invalid) and Invalid object name for the table.

    SELECT TOP 1000 [id]
          ,[customer]
          ,[day_of_order]
          ,[product]
          ,[quantity]
      FROM [MyDatabase].[dbo].[orders]

     

    • Edited by gs99 Monday, October 10, 2011 2:56 AM
    Monday, October 10, 2011 2:37 AM
  • just checking you have all permission on that table including view definition

    Monday, October 10, 2011 3:16 AM
  • Hi,
    Check this URL same point discussed here.
    http://social.msdn.microsoft.com/Forums/en-GB/sqlgetstarted/thread/5e760744-442b-44c7-8116-77136e12a898
    Shatrughna.
    Monday, October 10, 2011 7:41 AM
  • just checking you have all permission on that table including view definition


    If I didn't have permission, SQL would not proceed to execute the query, correct?
    Monday, October 10, 2011 1:26 PM
  • Today I tried to replicate the problem.

    Delete the database. Do everything the same way I did yesterday - create database, add table 'orders', and insert values for one row.

    I get no error messages.

    Does it make a difference if I'm drinking coffee?

     

    Monday, October 10, 2011 4:53 PM