Returning Results from a SQL Stored Procedures that uses temporary tables

[JayeshJ]

Hello All! Any help is appreciated. I have a LabView 7.0 application in which I am using the Database Utilities to connect to a MSDE 2000 server. Instead of doing all of the table selects and grouping in LabView, I have stored procedures in the database. So, I use the EXEC tool to execute my stored procedures and receive result sets. This works great in general. However, I have a more complicated stored procedure which uses temporary tables to hold and massage data before I do my final select statement. When I do this, LabView returns no results, nor yields an error. The stored procedure works fine in the query analyzer.

For reference, I have attached a zip file which contains a sample VI, a UDL, a SQL script to build the tables and stored procedures, and a PDF explaining where I have gotten to. I have a work-around, which is to create real tables just for that procedure, but that is not a good solution.

Thanks!
~ Jayesh

[Khalid]

Have you tried using VIEWs instead of the temp tables?  See if that works. 

-Khalid

[JayeshJ]

I don't believe a View will allow me to do what I like. Let me explain. I have one table which is temperature data over time. The values get inserted about every minute for up to 128 thermocouples. Now we have a live chart on one LabView screen. The end-user would like us to populate the history for the pen when he navigates to the screen, so if he has been collecting for several hours, he can see the trend. It is possible to build a list of data and send it to the chart. But the data must be at the frequency of the chart (i.e. if the chart is displaying values every 5 seconds, the data feed to it must be in 5 second increments). So, my stored procedure grabs a slice of the data from the real table and then build a temporary table of the data in 5 second increments without interpolation.

A view, in my understanding, is just a defined query of a real table. I can't manipulate the data before selecting it for my results.

I hope what I wrote makes sense. Thanks for your ongoing help
Jayesh

[Khalid]

Jayesh,

Sorry; I was actually thinking of TABLE variables (and not VIEWs).  These should be essentially same as using temp-tables. 

-Khalid

[hegb_85]

any ideas regarding this issue? Actually, table variable doesn't work.

[ofahed]

Greetings.

I was just wondering if anybody has found a solution to this problem, since 2 years passed by. 😉

Being not able to use Temptables in a Storeprocedure is quite annoying

was well i was wondering why the print statement in SQL messes up the return of the Select.

Thanks in advanced,

Olli