Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

This is a pretty simple SQL query I would assume, but I'm having problems getting it to work.

if (object_id('#InfoTable')is not null) 
Begin
    Drop Table #InfoTable
End

create table #InfoTable (NameOfObject varchar(50), NameOfCounter varchar(50), SampledValue float(30), DayStamp datetime)

insert into #InfoTable(NameOfObject, NameOfCounter, SampledValue, DayStamp)
select vPerformanceRule.ObjectName AS NameOfObject, vPerformanceRule.CounterName AS NameOfCounter, Perf.vPerfRaw.SampleValue AS SampledValue, Perf.vPerfHourly.DateTime AS DayStamp
from vPerformanceRule, vPerformanceRuleInstance, Perf.vPerfHourly, Perf.vPerfRaw
where (ObjectName like 'Logical Disk' and CounterName like '% Free Space' AND SampleValue > 95 AND SampleValue < 100)
order by DayStamp desc

select NameOfObject, NameOfCounter, SampledValue, DayStamp
from #InfoTable


Drop Table #InfoTable

I've tried various other forms of syntax, but no matter what I do, I get these error messages.

Msg 207, Level 16, State 1, Line 10
Invalid column name 'NameOfObject'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'NameOfCounter'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'SampledValue'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'DayStamp'.
Msg 207, Level 16, State 1, Line 22
Invalid column name 'NameOfObject'. 
Msg 207, Level 16, State 1, Line 22
Invalid column name 'NameOfCounter'.
Msg 207, Level 16, State 1, Line 22
Invalid column name 'SampledValue'.
Msg 207, Level 16, State 1, Line 22
Invalid column name 'DayStamp'.

Line 10 is the first 'insert into' line, and line 22 is the second select line. Any ideas?

share|improve this question
    
Any particular DBMS? –  Ignacio Vazquez-Abrams Jun 1 '12 at 15:41

1 Answer 1

up vote 1 down vote accepted

The DROP isn't doing anything unless your database context is TEMPDB, so the #InfoTable probably exists from a prior step with different column names.

Try changing your DROP to:

if (object_id('tempdb..#InfoTable')is not null) 
Begin
    Drop Table #InfoTable
End
share|improve this answer
    
Do I need to add tempdb.. to all the other accesses of the table (when I insert for example) or just in the drops? –  Hoser Jun 1 '12 at 16:33
1  
no, just the Object_ID(), everything else will work without it. –  SqlACID Jun 1 '12 at 17:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.