locked
unresolved temp table references, TSD04151 RRS feed

  • Question

  • I'm trying to gauge the level of effort in converting from VS2008 SP1 DB Edition to GDR R2.  I'm getting a bizarre set of warnings but only in a few certain cases.  This is a SQL2005 project.

    Some of our procs need to temporarily store data in a table so our practice is to create a a #temp table inside the proc.  FYI, if you use the SELECT...INTO syntax instead, DBPro doesn't like it, but if you declare the table via CREATE TABLE first, then it resolves it correctly ... most of the time.  I've found 2 cases where I still get the TSD04151 warning that it can't resolve the temp table reference.  Here's some sample code.

    First, add this simple table to your project.

    CREATE TABLE dbo.Unit
    ( UnitID uniqueidentifier NOT NULL
    , UnitNameAbrev varchar(100) NOT NULL
    )
    

    When you add this proc, you will see the warnings

    CREATE PROCEDURE [dbo].[ExampleUnresolvedTempTable]
    	@param1 int = 0, 
    	@param2 int
    AS
    
    IF OBJECT_ID('tempdb..#tmpTable') IS NOT NULL
    	DROP TABLE #tmpTable
    CREATE TABLE #tmpTable
    ( UnitID uniqueidentifier NOT NULL
    , UnitName varchar(100) NOT NULL
    )
    
    -- All these statements resolve correctly
    SELECT * FROM #tmpTable
    INSERT #tmpTable (UnitID, UnitName ) VALUES (NEWID(), 'Wrong Name')
    UPDATE #tmpTable SET UnitName = 'Right Name'
    DELETE #tmpTable
    
    --This doesn't resolve
    CREATE INDEX ix_tmpTable_1 ON #tmpTable (UnitName)
    
    --This doesn't resolve
    DELETE dbo.Unit
    OUTPUT DELETED.UnitID, DELETED.UnitNameAbbrev
    INTO #tmpTable(UnitID, UnitName)	
    

    As you can see standard CRUD operations work fine, but creating an index on a #temp table or using it in an OUTPUT statement raises these false warnings.

    Does anyone know how to clear up these warnings, or is it a problem with Visual Studio?


    MCTS:SQL2005
    Wednesday, March 31, 2010 4:10 PM

Answers

  • MKrebs,

    With GDR there are limitations to resolving against temp tables. Global temp tables and indexes on temp ables throw warnings are you note. There are a few others also, but you can suppress these warnings at the file level.

    In 2010 we have more complete handling of temp tables with the exception of session and global tables. Since these are outside the scope of the object using them they are not resolvable.

    Thanks,


    Barclay Hill Program Manager Visual Studio Data Tools (DataDude, DBPro, Database Edition, Database Projects, VS Data Tools) Please mark the responses as the answer if it resolves your question/issue. http://blogs.msdn.com/bahill
    Thursday, April 1, 2010 7:54 PM
    Moderator

All replies

  • MKrebs,

    With GDR there are limitations to resolving against temp tables. Global temp tables and indexes on temp ables throw warnings are you note. There are a few others also, but you can suppress these warnings at the file level.

    In 2010 we have more complete handling of temp tables with the exception of session and global tables. Since these are outside the scope of the object using them they are not resolvable.

    Thanks,


    Barclay Hill Program Manager Visual Studio Data Tools (DataDude, DBPro, Database Edition, Database Projects, VS Data Tools) Please mark the responses as the answer if it resolves your question/issue. http://blogs.msdn.com/bahill
    Thursday, April 1, 2010 7:54 PM
    Moderator
  • Thank you, Barclay, for your answer.  I guess that's what we'll have to do for now.

    The main reason I wanted to avoid suppressing the warning at the file level is that it suppresses it for the entire proc and not just the object I'm validating.  If something else were to happen in the database that resulted in an unresolved reference in this proc, I'd want to know about it, but suppressing the error in the file means I don't know about the problem until a user runs the proc and it causes an error in our application. 

    Additionally, there is no easy way for me to find all the files that have errors suppressed so I can manually check if a recent change broke any of them.

    Just some things to think about for future versions.

    Thank you for an otherwise great product.

     


    MCTS:SQL2005
    Monday, April 5, 2010 1:32 PM