locked
Should this be a warning?

    Frage

  • I'm getting the following warning from my datadude project:

    Warning 1 TSD3028: The following dependencies are missing from your database project: [Variable|Column] svc.#tmp.EVT_COMPLETED. Your database application might fail at runtime when [Procedure] svc.usp_GetWorkOrder is executed. C:\TFSProjects\Chevron\Minerva\src\Solutions\Chevron.UpstreamSolutions.SqlServer.MinervaRepository\Schema Objects\Stored Procedures\svc.usp_GetWorkOrder.proc.sql 141 29 Chevron.UpstreamSolutions.SqlServer.MinervaRepository

    As you can see its complaining because #tmp is not part of my datadude project. of course its not - its a temp table.

     

    Shouldn't datadude be clever enough to realise its a temp table and then not raise this warning?

     

    78 of 88 warnings in my project currently are caused by this. Its quite annoying.

    -Jamie

     

    Donnerstag, 18. Januar 2007 21:04

Antworten

  • Hi Jamie,

    The problem you mentioned is actually a bug in our code.  Thank you for finding this issue and we had fixed it in current bits.  For time being, you can fully qualify #tmp with dbo.#tmp in from clause, or when you create the temp table using "svc" schema, instead of "dbo" schema, that will let those annoying warnings go away.

    Thanks,

    Liangxiao

    Dienstag, 6. März 2007 23:08

Alle Antworten

  • Jamie -

    That does seem odd. I'll see if I can scare up someone to look into it.

    thanks,

     

    Montag, 22. Januar 2007 19:13
    Besitzer
  • Thanks Steven. If you could make sure that a reply comes up here I would appreciate it.

    -Jamie

     

    Montag, 22. Januar 2007 21:03
  • Hi Jamie,

    For global temp table, since we can not understand the schema inside the table, we do raise some warnings if we resolve a select column to a column on this temp table.  Since we don't know if the column exists or not, just warn user that if the column does not exist, it might cause runtime error.

    Thanks,

    Liangxiao

    Montag, 22. Januar 2007 23:49
  •  Liangxiao Zhu - MSFT wrote:

    Hi Jamie,

    For global temp table, since we can not understand the schema inside the table, we do raise some warnings if we resolve a select column to a column on this temp table.  Since we don't know if the column exists or not, just warn user that if the column does not exist, it might cause runtime error.

    Thanks,

    Liangxiao

     

    I'm not sure I agree with this behaviour. With temp tables I think you have to trust the user somewhat. Better that than a DBPro project that returns tens of unnecassary warnings (like mine currently does).

    Perhaps you could raise a different warning if it is about a column  temp table, that way we could suppress it in the project properties.

    -Jamie

     

    Dienstag, 23. Januar 2007 00:00
  • Jamie,

    Is the a local or global temp table?

    Is the temp table created inside the scope of the block of code referencing the temp table?

    If not is not defined in scope and not global; is this called in a nested fashion?

    I have a hard time extracting the real issue from your post since it has no clear repro, sorry.

    -GertD

    Dienstag, 6. Februar 2007 07:25
  • Also warning 3026 is specfic to global temp tables.

    -GertD

    Dienstag, 6. Februar 2007 07:45
  •  Gert Drapers - MSFT wrote:

    Jamie,

    Is the a local or global temp table?

    Is the temp table created inside the scope of the block of code referencing the temp table?

    If not is not defined in scope and not global; is this called in a nested fashion?

    I have a hard time extracting the real issue from your post since it has no clear repro, sorry.

    -GertD

     

    Hi Gert,

    I realise I should have been more descriptive. Sorry about that.

     

    The sproc is basically like this:

    CREATE PROC MyProc AS

    BEGIN

      CREATE TABLE #tmp AS <column-list>

      INSERT INTO #tmp SELECT ... FROM ...

      SELECT <column-list> FROM #tmp

    END

    That's obviously very simplified but you get the idea. Its very simple. And in answer to your question, its a local temp table.

     

    Below, I have pasted the actual code that is producing this warning.

        1 CREATE proc [svc].[usp_GetWorkOrders]

        2 (@pMRC nvarchar(15))

        3 AS

        4 /*

        5 Created By : **********

        6 CreatedDate : 11/19/2006

        7 

        8 Parameters : (1) @pmrc : Filter criteria for EVT_MRC

        9 

       10 Purpose     : Gets all the WorkOrders based on the filter criteia.

       11 

       12 */

       13 

       14 SET NOCOUNT ON

       15 DECLARE @StrSQL NVARCHAR(4000)

       16 SELECT @StrSQL  = N'SELECT * FROM OPENQUERY(D7i, ''SELECT EVT_CODE,

       17 EVT_ACTION,

       18 EVT_SCHBRKD,

       19 EVT_CAUSE,

       20 EVT_COMPLETED,

       21 EVT_COSTCODE,

       22 decode(EVT_CREATED,null,sysdate) EVT_CREATED,

       23 EVT_PERSON,

       24 EVT_MRC,

       25 EVT_DOWNTIMEHRS,

       26 EVT_BOELOSS,

       27 decode(EVT_DUE,null,sysdate) EVT_DUE,

       28 EVT_DURATION,

       29 EVT_OBJECT,

       30 EVT_EQDOWN,

       31 EVT_FAILURE,

       32 EVT_FREQ,

       33 EVT_JOBTYPE,

       34 EVT_PERIODUOM,

       35 EVT_PERMITREQD,

       36 EVT_PRIORITY,

       37 EVT_REQM,

       38 EVT_SCHEDGRP,

       39 EVT_STATUS,

       40 decode(EVT_TARGET,null,sysdate) EVT_TARGET,

       41 decode(EVT_UPDATED,null,sysdate) EVT_UPDATED,

       42 EVT_CLASS,

       43 decode(EVT_DATE,null,sysdate) EVT_DATE,

       44 EVT_DESC,

       45 EVT_PARENT,

       46 EVT_TYPE FROM SJV_MINERVA.EVENTS '

       47 

       48 Declare @FilterClause nvarchar(200)

       49 Select @FilterClause=N''

       50 IF (@pMRC<>'')

       51     SELECT @FilterClause = @FilterClause + N' WHERE EVT_MRC = ''''' + @pMRC + ''''

       52 

       53 IF (@FilterClause<>'')

       54     Select @strSQL = @strSQL + @FilterClause + N''''')'

       55 ELSE

       56     Select @strSQL = @strSQL + ''')'

       57 

       58 --Select @strSQL

       59 

       60 --WHERE EVT_MRC=''''' + @mrc + ''''''')'

       61 --first create the #tmp table

       62 

       63 CREATE TABLE [dbo].[#tmp](

       64     [EVT_CODE] [varchar](120)  NOT NULL,

       65     [EVT_ACTION] [varchar](32) NULL,

       66     [EVT_SCHBRKD] [varchar](160) NULL,

       67     [EVT_CAUSE] [varchar](32) NULL,

       68     [EVT_COMPLETED] [datetime] NULL,

       69     [EVT_COSTCODE] [varchar](120) NULL,

       70     [EVT_CREATED] [datetime] NULL,

       71     [EVT_PERSON] [varchar](60) NULL,

       72     [EVT_MRC] [varchar](60) NOT NULL,

       73     [EVT_DOWNTIMEHRS] [numeric](38,0) NULL,

       74     [EVT_BOELOSS] [varchar](160) NULL,

       75     [EVT_DUE] [datetime] NULL,

       76     [EVT_DURATION] [numeric](8, 0) NOT NULL,

       77     [EVT_OBJECT] [varchar](120) NULL,

       78     [EVT_EQDOWN] [varchar](160) NULL,

       79     [EVT_FAILURE] [varchar](32) NULL,

       80     [EVT_FREQ] [numeric](8, 0) NULL,

       81     [EVT_JOBTYPE] [varchar](32) NULL,

       82     [EVT_PERIODUOM] [varchar](40) NULL,

       83     [EVT_PERMITREQD] [varchar](160) NULL,

       84     [EVT_PRIORITY] [varchar](32) NULL,

       85     [EVT_REQM] [varchar](32) NULL,

       86     [EVT_SCHEDGRP] [varchar](120) NULL,

       87     [EVT_STATUS] [varchar](16) NOT NULL,

       88     [EVT_TARGET] [datetime] NULL,

       89     [EVT_UPDATED] [datetime] NULL,

       90     [EVT_CLASS] [varchar](32) NULL,

       91     [EVT_DATE] [datetime] NULL,

       92     [EVT_DESC] [varchar](320) NOT NULL,

       93     [EVT_PARENT] [varchar](120) NULL,

       94     [EVT_TYPE] [varchar](16) NOT NULL

       95 )

       96 

       97 INSERT INTO #tmp

       98 execute svc.usp_GetData @StrSql

       99 

      100 select

      101     EVT_CODE    As WorkOrderID,

      102     EVT_ACTION    As ActionCode,

      103     EVT_SCHBRKD As BreakInScheduleCode,

      104     EVT_CAUSE As ReasonType,

      105     EVT_COMPLETED As CompletedDate,

      106     EVT_COSTCODE As FinancialReferenceID,

      107     EVT_CREATED As RequestDate,

      108     EVT_PERSON As CrewID,

      109     EVT_MRC As OrganizationID,

      110     EVT_DOWNTIMEHRS As DowntimeHrs,

      111     EVT_BOELOSS As ProductionLoss,

      112     EVT_DUE As WorkOrderDueDate,

      113     EVT_DURATION As WorkDuration,

      114     --EVT_OBJECT As ,

      115     '' As FacilityID,

      116     EVT_EQDOWN As EquipmentStatus,

      117     EVT_FAILURE As FailureID,

      118     EVT_FREQ As WorkFrequency,

      119     EVT_JOBTYPE As WorkOrderJobTypeID,

      120     EVT_PERIODUOM As PeriodUOM,

      121     EVT_PERMITREQD As PermitRequiredCode,

      122     EVT_PRIORITY As WorkOrderPriorityID,

      123     EVT_REQM As ProblemCode,

      124     EVT_SCHEDGRP As WorkOrderScheduleGroupID,

      125     EVT_STATUS As WorkOrderStatusID,

      126     EVT_TARGET As TargetDate,

      127     EVT_UPDATED As UpdatedDate,

      128     EVT_CLASS As WorkOrderClassID,

      129     EVT_DATE As WorkOrderDate,

      130     EVT_DESC As WorkOrderDescription,

      131     EVT_PARENT As ParentWorkOrderID,

      132     EVT_TYPE As WorkOrderTypeID

      133 

      134  from #tmp

      135 

      136 

      137 drop table #tmp

      138 

      139 

     

    And here's the warning:

    Warning 75 TSD3028: The following dependencies are missing from your database project: [Variable|Column] svc.#tmp.EVT_DESC. Your database application might fail at runtime when [Procedure] svc.usp_GetWorkOrders is executed. C:\TFSProjects\Minerva\src\Solutions\Chevron.UpstreamSolutions.SqlServer.MinervaRepository\Schema Objects\Stored Procedures\svc.usp_GetWorkOrders.proc.sql 130 14 UpstreamSolutions.SqlServer.MinervaRepository

    Hope that helps.

    -Jamie

     

     

    Dienstag, 6. Februar 2007 22:52
  • I have to agree, this behavior is very, VERY annoying.  I'm getting over 400 warnings, and about 380 of them are because of temp table usage.  These warnings are, imho, utterly bogus, and it makes finding the real warnings very difficult.  I don't want to suppress this warning entirely, because it found a few valid errors.  These are the ones that weren't about temp tables. 

    It needs to be possible to just surpress the temp-table based warnings of this kind.  Either that, or make it smart enough to see a temp table is being declared and figure out the column names (which might be a bit much for a 1.0 product).

     

     

    Freitag, 23. Februar 2007 08:17
  • Hi Jamie,

    The problem you mentioned is actually a bug in our code.  Thank you for finding this issue and we had fixed it in current bits.  For time being, you can fully qualify #tmp with dbo.#tmp in from clause, or when you create the temp table using "svc" schema, instead of "dbo" schema, that will let those annoying warnings go away.

    Thanks,

    Liangxiao

    Dienstag, 6. März 2007 23:08
  •  Liangxiao Zhu - MSFT wrote:

    Hi Jamie,

    The problem you mentioned is actually a bug in our code.  Thank you for finding this issue and we had fixed it in current bits.  For time being, you can fully qualify #tmp with dbo.#tmp in from clause, or when you create the temp table using "svc" schema, instead of "dbo" schema, that will let those annoying warnings go away.

    Thanks,

    Liangxiao

     

    Hey cool. Thanks Liangxiao, that's great news!

     

    -Jamie

     

    Dienstag, 6. März 2007 23:18
  • Hi Gert,

     

    When you have such warnings on a stored proc with global temp tables - it seems that VSTS4DBPro will not allow you to create unit tests for the stored proc from the schema view ( the right click menu item for Create Unit Tests is grayed out) - is this by design? If so I assume that a unit test method still can be created manually for the stored proc?

    Freitag, 20. April 2007 20:32
  • Have you seen this fixed in the CTP?  I found a similar warning with select * into #temptable still in the CTP.  I have a thread listing various incorrect warnings and links to bug reports here:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1578947&SiteID=1

    I also think the incorrect warnings are very distracting from real problems.  Today I spent a few hours going through 100+ warnings and trying to sort them into
    Mittwoch, 9. Mai 2007 16:21
  • Hi Sideout

     

    For Jamie's original temp table issue, it was fixed in the CTP release.  For the issue you reported, it is a bug in "select * into", we are looking into the bug.  For time being, to work around this, if you expand your "*" to column names, those warning will go away.

     

    Thanks,

    Liangxiao

     

    Freitag, 11. Mai 2007 00:19