0

I'm running into a very strange error with temp tables and some SQL stored procedures. For the past couple days, the only error I've been able to get is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." After raising the threshold for a timeout in C# (from 30 seconds to 120 seconds), I'm now getting this error:

"Invalid column name 'CoverageLimit'. Invalid column name 'Difference_Options'. Invalid column name 'CoverageLimit'. Invalid column name 'CoverageLimit'. Invalid column name 'CoverageLimit'. Invalid column name 'CoverageLimit'. Invalid column name 'CoverageLimit'. Invalid column name 'CoverageLimit'. Invalid column name 'Difference_Options'. Invalid column name 'Difference_Options'. Invalid column name 'CoverageLimit'."

However, these columns do not exist in the temp table referenced in the stored procedure called in the function that the stack trace is hitting.

CREATE TABLE [dbo].[#TEMP]
(
    FormEndorsementID [int] NOT NULL,
    [Restriction] bit,
    [Description] nvarchar(3000),
    [Type] nvarchar(255),
    [QQ_Reference] nvarchar(255),
    [Values] nvarchar(255)
) ON [PRIMARY]

I was quite confused by this, so I executed a query to find any instances of CoverageLimit or Difference_Options in stored procedures, and ended up finding this temp table in two completely different stored procedure:

CREATE TABLE [dbo].[#TEMP]
(
    OptionID [int] IDENTITY(1,1) NOT NULL,
    CoverageLimit int,
    Difference_Options int
) ON [PRIMARY]

(Note: I wasn't the one who set up this database schema. It is in severe need of cleanup/updating.)

Now, could there be an issue of some kind of scope? The strangest part about this is that we have this same application running on a different server with the same databases, and it's not encountering this error.

Thanks!

DWright
  • 9,258
  • 4
  • 36
  • 53
user1888527
  • 543
  • 1
  • 7
  • 12
  • 1
    Any chance they are on a regular table that you are dealing with? Perhaps through the use of triggers? – Dave.Gugg May 13 '15 at 17:52
  • 3
    You may want to start by naming your temp tables something other than TEMP. Use meaningful object names. – mjw May 13 '15 at 17:54
  • @Dave.Gugg I first searched all tables to see if those columns existed, and only found them in the two stored procedures. – user1888527 May 13 '15 at 17:55
  • 2
    If the same user is somehow running both procs at the same time, Proc2 will attempt to use the #Temp table from Proc1. – Tab Alleman May 13 '15 at 17:56

1 Answers1

4

This whole mess could have and should have been avoided if you only used meaningful names for you temporary tables. My guess is that for some reason your server could not drop one of the temporary tables and that is what is causing your error.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • 2
    It is bad advice to suggest using table variables instead of temp tables whenever possible. They are for different purposes and are each helpful when used correctly. – Dave.Gugg May 13 '15 at 17:57
  • 2
    @Dave.Gugg To be fair, `table-valued parameters` <> `table variables`. – Aaron Bertrand May 13 '15 at 18:08
  • 1
    That's true, but from my understanding `table-valued parameters` get the same bad cardinality estimates as table variables, so I'm still thinking that is still not good advice. – Dave.Gugg May 13 '15 at 18:16
  • Thanks, point taken. I would have added this link to my answer but using a cellphone makes pasting a mess. To the OP, [Read this.](http://stackoverflow.com/questions/11857789/when-should-i-use-a-table-variable-vs-temporary-table-in-sql-server) – Zohar Peled May 13 '15 at 19:39
  • @Dave.Gugg you can improve the estimate problem with TF 2453 ([I blogged about it here](http://sqlperformance.com/2014/06/t-sql-queries/table-variable-perf-fix)). Anyway, I thought the suggestion was made to pass the rows in instead of creating a #temp table and populating it inside the procedure, which would be better in many scenarios. It doesn't seem like the OP is having performance issues based on estimates - we don't even know if the #temp table has more than one row. A lot of the myths about `#temp` vs. `@table` [can be dispelled here](http://dba.stackexchange.com/q/16385/1186). – Aaron Bertrand May 13 '15 at 19:44