none
VS2012 Database Project with SSDT (dec) - #TEMPTABLE generates WARNING SQL 71502.

    Question

  • I am learning Visual Studio 2012's "database project" system, using Visual Studio 2012 with Update 1, plus SSDT.

    I am finding it very good at finding real problems in my database, especially programming errors in stored procedures where someone has removed a field from a database table but didn't go through and verify that all the stored procedures execute without errors. So validation of your .sql scripts via the "build" command in Visual Studio 2012 is very handy. I would hate to abandon it.

    But I also note that whenever a #TEMPTABLE is used in a stored procedure, even when "Enable Extended Transact-SQL verification for common objects" is turned off, I still get "build errors" involving #temptable.field references in stored procedures.

    What steps does the Database Project take to determine the schema of a temporary table? Since my temporary tables by definition don't exist in the main schema, they didn't get into my Database Project when I imported a real production SQL database into Visual Studio via the Import Database option, right after creating the database.

    Should I be creating "#TEMPTABLE.SQL" files and adding them to my project?  Right now the temporary tables are created in my main applications code and since they're temptables, they weren't imported when I imported from my existing database. Since almost all real world applications that I have ever seen use #temptables heavily for data storage, and don't restrict their scope and lifetime to within a single stored procedure there must be a way to let the schema-compiler-thingy know about this kind of thing.  A "pragma" or "include" directive that would tell the compiler how a particular .sql file expects to be called, and what temporary tables may exist and what their schema may be.  (I know that it would be better to not have done this in the first place, and to use explicit table-valued-parameters to stored procedures exclusively, but sometimes it would be nice to use the VS2012 db project to clean up a mess that you already have, and I find its utility in this area limited.)

    Sample error:

    c:\dev\...\dbo\Stored Procedures\xyz.sql(95,96): Warning:  SQL71502: Procedure: [dbo].[proc123] has an unresolved reference to object [#temptable1].[somefield1].

    If there was a way to include a script that defined the temptables in use once, and include it into the various places where it's necessary to know about these if the T-SQL is to be thoroughly validated, that would be fine, and if Turning of Extended Verification did what I think it's supposed to do, then perhaps nothing would be necessary.

    I am not terribly happy about the idea of simply suppressing a warning for the entire source-code-unit (xyz.sql above is not the real name of the unit).  The chief value I see in SSDT + VS2012 DB Projects is the idea that the "Compiler" checks my schema, and turning that off I may as well go back to Notepad++ or just typing stuff into SSMS and hoping it works.

    [I asked this question also on StackOverflow here]


    wp


    Wednesday, March 20, 2013 4:51 PM

Answers

  • Warren,

    If the temp object is created inside the scope of the object referencing it, it should get resolved. If you are passing temp tables between objects, like store procedures, have a dummy create in the second proc using 

    if not exists (select ...)
    begin
       create table #t1 (c1 int not null)
    end

    Should resolve the warning.

    You can also suppress the warning at the file level, by right clicking on the warning in the error list and choosing suppress, or by editing the property grid of the file.


    -GertD @ www.sqlproj.com

    Wednesday, May 29, 2013 5:59 AM
    Moderator

All replies

  • The only solution I've found is to add the (meaningless) code like this where the error occurs.

    --Prevent SSDT db deployment errors for table created in dbo.procEmailStep1
    If object_id('dbo.EmailTempTable') Is Null
    	CREATE TABLE [dbo].[EmailTempTable](
    		[actor_id] [bigint]		NOT NULL,
    		[legacy_ID] [bigint]	NULL,
    		[TYPE] [nvarchar](20)	NULL
    	);
    


    Paul

    Friday, March 22, 2013 12:06 AM
  • Thanks Paul23 - that was a nice workaround.. 

    "WHAT A PATHETIC WAY OF  INTRODUCING SSDT(SICK, SHIT DATA TOOLS) BY MS -- DID NOT EVEN DO BASIC TEST BEFORE RELEASING... "



    HydPhani

    Wednesday, May 22, 2013 12:59 PM
  • Warren,

    If the temp object is created inside the scope of the object referencing it, it should get resolved. If you are passing temp tables between objects, like store procedures, have a dummy create in the second proc using 

    if not exists (select ...)
    begin
       create table #t1 (c1 int not null)
    end

    Should resolve the warning.

    You can also suppress the warning at the file level, by right clicking on the warning in the error list and choosing suppress, or by editing the property grid of the file.


    -GertD @ www.sqlproj.com

    Wednesday, May 29, 2013 5:59 AM
    Moderator