Visual Studio Database Project - Unresolved Reference to temp table

Question

I have imported my sql server 2005 database into a VS2010 database project. One of my stored procedures contains a statement similar to

INSERT INTO #myTemp...

and Visual Studio gives me a warning such as

SQL04151: Procedure: [dbo].[mySproc] has an unresolved reference to object [#myTemp].

Is there a way of resolving this reference? I'd like to clear as many of the project warnings as possible.

No, in this case the temp table is created by a "parent" stored proc which then calls this one multiple times in a loop :( — Nick, Apr 19 '11 at 11:00

score 11 · Answer 1 · answered Sep 19 '11 at 18:28

I had the same thing, where the parent creates it. Instead of getting rid of the warning by creating the table if it doesn't exist, I want to be able to throw an exception if it doesn't. Putting a CREATE statement after the return statement guarantees that it will never get ran but also clears up the warning.

IF (OBJECT_ID('tempdb..#Foo') is null)
BEGIN
    Raiserror('#Foo doesn''t exist.', 16, 1)
    RETURN
    CREATE TABLE #Foo (foo int) --Here just to get rid of compile warning
END

score 1 · Answer 2 · edited May 23 '17 at 12:34

1

Did you try three part naming as in here (VS 2010 build database project receive SQL04151)

Also you might need to change the db ref to tempdb instead of master. See this article (http://blogs.msdn.com/b/gertd/archive/2009/06/10/system-objects-in-tempdb.aspx). It describes sys objects but temporary tables are stored in tempdb so would exhibit the same behavior.

edited May 23 '17 at 12:34

Community

1
1

answered Apr 12 '11 at 13:11

ktharsis

3,160
1
19
30

score 0 · Answer 3 · answered Apr 18 '11 at 00:52

0

If you know that there's no problem with your code, you can suppress the build warnings. Go to your project's settings and you'll find a tab called Build. There's a field there to add the codes you wish to suppress. I googled and found this page, describing the steps.

answered Apr 18 '11 at 00:52

slimburrok

67
3

4

I don't think suppressing all of the SQL04151 errors is such a great idea. Sure you'll get rid of the temp table messages, but you'll also suppress every other db reference warning... – GordonBy Apr 19 '11 at 10:45
Then use: `#pragma warning disable 4151 /* SOME CODE */ #pragma warning restore 4151` Be sure to add comments as to why you're disabling the warning. – slimburrok Apr 19 '11 at 21:18

score 0 · Answer 4 · answered Apr 19 '11 at 16:12

Obviously, VS does not know that this stored proc is only to be executed from within another stored proc which creates this temp table. One solution would be to add code to the procedure that creates the temp table if necessary

If object_id('tempdb..#MyTemp') Is Null
    Create Table #MyTemp
        (
        ...
        )

If always called from the parent, in theory the above code should never run.

Visual Studio Database Project - Unresolved Reference to temp table

4 Answers4