July 8, 2003 at 11:17 am
Is it possible to bring the records back to an .asp app from a temp table? If so, how?
July 8, 2003 at 1:10 pm
Works the same as a normal table. Have you been having problems?
Guarddata-
July 8, 2003 at 1:17 pm
Yes I have. Here's what I have going on:
I'm calling a Stored Proc from an .asp app. This is what is in the SP...
Create Table #CashIn (WarrantyNumber varchar(30), Total money)
Insert into #CashIn
select warrnum, sum(war_amt) as Total
from cash
where (bldnum = '9999999')
group by warrnum
select * from #CashIn
Now, when I try and query any records from #CashIn (from inside the .asp app) I get object doesn't exist.
Temp tables is a new concept to me. What am I doing wrong?
Thanks.
July 8, 2003 at 1:23 pm
Sounds like you are using an ADO object to open a file. Try reading the result into a DataSet object instead. If you must read using adoObject.Open, you will need to have a permanent table.
Guarddata
July 8, 2003 at 1:28 pm
Yes, I am using ADO.
How can I cycle(loop) through the records inside the SP itself? Is that possible?
July 8, 2003 at 1:35 pm
You can reference the temporary table the same as a permanent one within the procedure. Your "SELECT * FROM #CashIn" would produce a recordset. Test it by running the stored procedure from Query Analyzer. If you need to deal with the rows one at a time, you could use a CURSOR (see books online) but then you could use the original SQL statement instead of the temporary table.
Still, if the logic of dealing with the output resides in a calling application (like a web page), you will want to use a DataSet object unless you create the ADOCommand object using the SQL statement instead of a table name.
There are many options. You might want to check out an ASP book too.
Guarddata-
July 8, 2003 at 1:41 pm
Thanks Guarddata.
I'll fire up QA and see what type of results I can come up with.
July 9, 2003 at 12:37 am
Temp table will not exist outside of the stored proc when using #temp syntax, try ##temptablename, and use delete table at end of sp.
Read up on cursors and locking mechanisms, have fun.
July 9, 2003 at 2:15 am
If the SP works correctly when calling it from Query Analyzer, the reason that you don't get data back to ASP probably is that the SP also returns status info. Try adding SET NoCount ON
in the top of the SP, and see if that helps.
Hope this helps.
Good luck!
July 9, 2003 at 4:54 am
la4rha,
The "SET NoCount ON" works like a charm! Thanks for the help.
-Al
July 9, 2003 at 1:19 pm
Good call, la4rha. In the past I've had the same problem in the DTS Execute SQL task that returns a dataset...."invalid pointer error" was the error. This was a pain in the butt to figure out; doesn't seem to be a well documented problem.
snootchie bootchies
Signature is NULL
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply