I am attempting to sort some data using temporary tables in SQL Server 2000. The data is a set of time-stamped pressure meter rate-of-change readings (Delta), for which I want to find the statistically significant drops between particular time limits.

Specifically my queries are:

1. Sort for the rows which fall between the time limits, and for the meters I am interested in, and select into #PDropTemp:

SELECT [TagIndex],[Timestamp],[Delta] AS [Drop]
INTO #PDropTemp
FROM [<original table>]
WHERE ([Timestamp] > @MinTime)
AND ([Timestamp] <@MaxTime)
AND (ValQuality = 192)
AND ((TagIndex = 19) OR (TagIndex = 59))

2. Sort for those drops which are greater (ie more negative) than 3 times the standard deviation for that particular meter, and also where the average for the meter is not zero. Selected into #PDropFiltered

SELECT T1.TagIndex,T1.[Timestamp],T1.[Drop]
INTO #PDropFiltered
FROM #PDropTemp_DM_AggregatedPressure T1
WHERE (T1.[Drop] < -3 *
    (
    SELECT STDEV(T2.[Drop])
    FROM #PDropTemp_DM_AggregatedPressure T2
    WHERE (T2.TagIndex = T1.TagIndex)
    ))
AND (
    (
    SELECT AVG(T3.[Drop])
    FROM #PDropTemp_DM_AggregatedPressure T3
    WHERE (T3.TagIndex = T1.TagIndex)
    ) <> 0)
ORDER BY T1.[Drop]

3. After doing this for potentially a number of different source tables I finally get everything from #PDropFiltered:

SELECT * FROM #PDropFiltered

[I could probably omit the last query, and select the middle query directly into memory, but nevermind...]

Executing these queries in code throws an exception telling me that #PDropTemp is an invalid object name. What is the problem? Is it the fact that I am doing a nested query?

Some other relevant points are:

1. I keep the same connection open throughout, so I assumed that the temporary tables would not be thrown away between queries.
2. These queries work fine in query analyzer.
3. If I replace these queries with simple select queries from Northwind it works fine in code.
4. If I use public temporary tables, ie ##PDropTemp it works fine. There shouldn't be an issue for concurrency because there is only one 'user' at a time, but I'm a little unclear about when public temporary tables get dropped - they are still there in tempdb after I close the connection.

Can anyone help?

Oops - forget to do some editing - wherever you see #PDropTemp_DM_AggregatedPressure it should actually read #PDropTemp. I thought it would make it easier to read...

eadred,

I don't have the solution to your problem, but I can tell you what's causing it (I've been trying to solve this myself for a few hours!). It is the use of the temp table. ADO.Net is unable to get the schema for the query. This is also why it works when you use the public temp table:- because the schema still exists when ADO looks for it.

If I find a solution, I'll let you know.

Chris

There are two secrets to success in this world:
1. Never tell everything you know

OK, I've come up with a work-round (which is probably better coding anyway). Instead of using a temp table, declare a table variable to hold the data. e.g.
This doesn't seem to upset ADO and allows you do all the normal neat things with datasets etc.

HTH,

Chris

There are two secrets to success in this world:
1. Never tell everything you know

Hi Chris,

I perform my queries using three separate SqlCommand objects (and across a couple of function calls, since there's a bit of polymorphism involved), not all in one batch query or sproc.

How can I still use a table variable as a temporary storage location in this case?

Thanks

Eadred