locked
SQL04151 Warning on UPDATE STATISTICS on temporary table RRS feed

  • Question

  • In a Visual Studio 2010 SQL Server 2008 Database Project I have a stored procedure that updates statistics on a temporary table with the following form:-

    CREATE TABLE #FOO
    (
      FooID  INTEGER,
      BarID  INTEGER
    )

    INSERT INTO #FOO (FooID, BarID)
    SELECT FooID, BarID FROM dbo.fnFooBar(@FooID, 6, 1)

    CREATE UNIQUE CLUSTERED INDEX #IX_Bar on #FOO (FooID, BarID)
    UPDATE STATISTICS #FOO

    This causes a warning:-

    SQL04151: Procedure: [dbo].[procname] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [BRS].[#FOO] or [dbo].[#FOO]

    Double clicking on the warning opens the procedure script and goes to the line "UPDATE STATISTICS #FOO". The parser does not seem to have any problems with the other references to #FOO (neither the INSERT nor the CREATE INDEX statements) just the UPDATE STATISTICS.

    I've searched Connect and these forums without joy. Is this a known issue? Is there a work-around?

    --
    Regards,
    Neale NOON

     

     

     

    Tuesday, June 1, 2010 7:03 AM

Answers

  • I have reported this as a bug on Connect.

    Feedback ID: 565084

    --
    Regards,
    Neale NOON

     

    • Marked as answer by Figo Fei Friday, June 4, 2010 6:40 AM
    Friday, June 4, 2010 4:49 AM

All replies

  • Hi Neale,

    Please try adding a database reference to the master.dbschema, you can find it at %ProgramFiles%\Microsoft Visual Studio 9.0\VSTSDB\Extensions\SqlServer\2008\DBSchemas.

    In addition, here is a similar case discussed before: http://social.msdn.microsoft.com/Forums/en/vstsdb/thread/a7335bf6-94fe-44df-8791-1f3e7348e971

    Hope it help.

    Thanks.


    Figo Fei
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Wednesday, June 2, 2010 3:30 AM
  • Thanks Figo,

    I already have a reference to master (also tried referencing master as tempdb). In the example above the only warining is on the update statistics line (the other references to temp objects are correctly parsed).

    If I comment out just that one line then the warning goes away.

    If I change it to EXEC('UPDATE STATISTICS #FOO') the warning goes away.

    I suspect that the parser is just having trouble with that one reference scenario. Could this be a bug?

    --
    Regards,
    Neale NOON

    Wednesday, June 2, 2010 3:55 AM
  • I have reported this as a bug on Connect.

    Feedback ID: 565084

    --
    Regards,
    Neale NOON

     

    • Marked as answer by Figo Fei Friday, June 4, 2010 6:40 AM
    Friday, June 4, 2010 4:49 AM
  • Hi Neale,

    Thanks for your valuable feedback.


    Figo Fei
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Friday, June 4, 2010 6:41 AM