SQL04151 Warning on UPDATE STATISTICS on temporary table

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

 

 

 

Answer 1

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 [e-mail] 

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

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

Answer 3

I have reported this as a bug on Connect.

Feedback ID: 565084

--
Regards,
Neale NOON

 

Answer 4

Hi Neale,

Thanks for your valuable feedback.

---

Figo Fei
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact [e-mail] 

Please remember to mark the replies as answers if they help and unmark them if they provide no help.