Post reply

Temp Table results

  • July 8, 2003 at 11:17 am

    #175981

    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

    #464567

    Works the same as a normal table. Have you been having problems?

    Guarddata-

  • July 8, 2003 at 1:17 pm

    #464569

    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

    #464574

    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

    #464576

    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

    #464578

    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

    #464582

    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

    #464619

    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

    #464634

    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

    #464661

    la4rha,

    The "SET NoCount ON" works like a charm! Thanks for the help.

    -Al

  • July 9, 2003 at 1:19 pm

    #464728

    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