none
SSRS 2008, can I use <exec sp> and <Select> combo in dataset query pane for DataSet

    Question

  • Hi, I'm trying to use this combo for my dataset: i.e. call sp and then use table resulting from this sp, and it give me an error

    <procedure or function has too many arguments specified> while generating report on Preview, but runs OK from query designer, I'm totaly lost.

    Can it be done??


    create table #temp (c1....c2)
    insert #temp
    exec sp_1000_Get_Mir  
    
    select c1, c2 from #temp

    Tuesday, February 25, 2014 8:23 AM

Answers

  •  Djallal my original construction works OK even from SQL Designer in ssrs, not talking about SSMS.

    You can insert output from exec sp if target table is pre defined. It just doesn't work for Preview in ssrs.

    Thansk, Visakh

    in other words I can compine exec sp and direct sql  with text option in ssrs, I tried to use #temp but in my case each dataset = new transaction (I have this option ON for dataset1), so #temp table is not visible for other datasets.

    So I finished to do sp1, sp2, sp3,... for each of dataset, even the main portion for each is the same, hope it will work still fast, will it use cache, because Exec Plans will be the same..

    Thanks

    Tuesday, February 25, 2014 8:41 PM

All replies

  • Hi,

    You can create a tmp Table and  select rows from it.

    But, you can not insert rows from a Stored procedure (SP). For this, create a new SP with a temp table as an in put parameter, fill it and return your table as an output. 

    Sts


    Dj's | Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    Tuesday, February 25, 2014 5:40 PM
  • You need to use it like below if you want to select subset of columns from sp output

    http://beyondrelational.com/modules/2/blogs/70/posts/10812/select-columns-from-exec-procedurename-is-this-possible.aspx

    But I would still recommend using a wrapper procedure inside it create a temp table with structure some as your sp output and then select required columns from it

    ie like below

    CREATE PROC WrapperProc
    AS
    create table #temp (c1....c2)
    insert #temp
    exec sp_1000_Get_Mir  
    
    select c1, c2 from #temp
    go

    Then call the WrapperProc from your report


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, February 25, 2014 5:52 PM
  •  Djallal my original construction works OK even from SQL Designer in ssrs, not talking about SSMS.

    You can insert output from exec sp if target table is pre defined. It just doesn't work for Preview in ssrs.

    Thansk, Visakh

    in other words I can compine exec sp and direct sql  with text option in ssrs, I tried to use #temp but in my case each dataset = new transaction (I have this option ON for dataset1), so #temp table is not visible for other datasets.

    So I finished to do sp1, sp2, sp3,... for each of dataset, even the main portion for each is the same, hope it will work still fast, will it use cache, because Exec Plans will be the same..

    Thanks

    Tuesday, February 25, 2014 8:41 PM