Post reply

displaying the result set coming from a temp table in a stored proc

  • September 3, 2004 at 2:57 pm

    #109234

    Hello:

    I have a stored procedure which does a whole lot of computation and stores the result in a temp table (#).

    In the final step before i return a return code of 0 , i return a result set which is basically a select statement on the temp table like so ;

    select f1, f2, f3

    from #myTable

    however in the reporting service designer the fields are not recognised automatically even though i am able to run the procedure in the data tab. Bottom line how do i display the result of a stored proc in reporting services if the result set is coming from a temp table.

    We have several such stored procs that are being called in Excel and i would like to migrate them into Reporting Services, but this seems to be a bottleneck. If Excel can do it why not Reporting Services ?

    Regards

    -srinivas krishnaswamy

  • September 6, 2004 at 8:00 am

    This was removed by the editor as SPAM

  • September 6, 2004 at 11:46 pm

    #521906

    How are you initially creating the temp table?

    CREATE TABLE #myTable (...)

    or

    SELECT ...

    INTO #myTable

    I've had some problems with the SELECT ... INTO #temp before. Try explicitly declaring the table if you're not already

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 7, 2004 at 8:13 am

    #521966

    I create the table using create table #mytable(..). Then u update some rows in it and then return the results using select f1,f2, from #mytable

  • September 8, 2004 at 2:28 am

    #522064

    In Report Designer you will need to use the Generic Query Designer when working with queries that use temp tables.

    HTH,

    Regards.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply