Temporary Table in Stored Proc

18 posts, 0 answers
  1. Susan
    Susan avatar
    137 posts
    Member since:
    Jul 2007

    Posted 10 Sep 2008 Link to this post

    I have been using the reporting with the SqlDataAdapter that points to a stored procedure. I have had no problems up until now. the stored procedure i'm trying to use, uses temporary tables inside it. it still returns a resultset just as if i used the select statement with a 'real' table. when i try and select the stored proc with the sqldataadpater, i get this message:

    invalid object name #tempteams (which is the name of the temporary table within the stored proc).

    any suggestions to get around this?

    thanks,
    susan
  2. Felizardo Francisco
    Felizardo Francisco avatar
    4 posts
    Member since:
    May 2008

    Posted 18 Sep 2008 Link to this post

    I am having the same problem.

    Solutions anyone?
  3. DevCraft banner
  4. Felizardo Francisco
    Felizardo Francisco avatar
    4 posts
    Member since:
    May 2008

    Posted 18 Sep 2008 Link to this post

    I found a work around.
    I created a 2nd stored procedure and put the following in it:

    SELECT

    * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec database.dbo.sp_name') AS tbl


    Replace database.dbo.sp_name with your particular sp name.

  5. Susan
    Susan avatar
    137 posts
    Member since:
    Jul 2007

    Posted 21 Sep 2008 Link to this post

    Felizardo,
    Thanks for sharing your solution. I did create another stored proc like you suggested. Since I am not very familiry with the OPENROWSET, but I don't believe you can use parameters. my original stored procedure has 2 parameters that i pass into it.

    do you know if i can do this?

    Can someone from Telerik address this - letting us know if there is a way to use temporary tables inside of our stored procedures?

    thanks,
    susan
  6. Felizardo Francisco
    Felizardo Francisco avatar
    4 posts
    Member since:
    May 2008

    Posted 21 Sep 2008 Link to this post

    Susan,

    I tested it with a parameter. It works.
    In the following example, I passed "Sales" as a parameter to TestSP.

    SELECT

    * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec donottest.dbo.TestSP "Sales" ') AS tbl




  7. Susan
    Susan avatar
    137 posts
    Member since:
    Jul 2007

    Posted 21 Sep 2008 Link to this post

    Thanks for the reply Felizardo. I tried your suggestion, but can't get it to work. In addition, the parameters are passed in as @param1 and @param2. I tried

    SELECT

    a.*

    FROM

    OPENROWSET('SQLOLEDB','SEPPC';'scwebaccess';'scwebpassword',

    'SET FMTONLY OFF EXEC rpt_seasonschedulebyteam @param1 @param2 ') AS


    But get an error with the above as well. Any other thoughts?

    Thanks Again,
    Susan

  8. Felizardo Francisco
    Felizardo Francisco avatar
    4 posts
    Member since:
    May 2008

    Posted 23 Sep 2008 Link to this post

    Where are you getting the error? at what point? and what is the error message?

    You should open a support ticket for it.

  9. Susan
    Susan avatar
    137 posts
    Member since:
    Jul 2007

    Posted 29 Sep 2008 Link to this post

    this was a limitation of the sqldataadapter - thanks for steering me in the right direction. what i did was to use my stored procedure with the temporary table to populate a dataset in my code-behind. i then attached the dataset to the report via the datasource.

    everything is working great.

    thanks,
    susan
  10. AkAlan
    AkAlan avatar
    121 posts
    Member since:
    Jun 2009

    Posted 17 Feb 2010 Link to this post

    Has anyone been able to come up with a better work around on using sp's with temp tables. Susan, can you provide a sample of your code behind, I have never used that method but need to get a report out that wasa made using temp tables. Thanks.
  11. Susan
    Susan avatar
    137 posts
    Member since:
    Jul 2007

    Posted 23 Feb 2010 Link to this post

    First, create a dataset (dsNbrGamesDblHeaders). Call your stored procedure and fill the dataset. Then just point the reports datasource to the dataset. You will not be able to see the fields in the report itself. you will just need to code them as =fields.somefield name (will not be able to 'pick' them inside the designed).

    Hope it helps!
    Susan

     

     

    Dim objDA As New SqlDataAdapter

     

     

    Dim ScheduleDAL as New ScheduleDAL

     

    objDA = ScheduleDAL.NbrGamesDoubleHeaders(seasonKey)

     

    '-- This is the DataSet created at Design Time

     

     

     

     

     

    Dim myDS As New dsNbrGamesDblHeaders

     

    myDS.Tables(

    "NbrGamesDblHeaders").Clear()

     

    myDS.EnforceConstraints =

    False

     

     

     

     

    objDA.Fill(myDS,

    "NbrGamesDblHeaders")

     

     

    Me.ReportViewer2.Report = New rptNbrGamesDblHeaders

     

     

    Dim report1 As rptNbrGamesDblHeaders = CType(me.ReportViewer2.Report,rptNbrGamesDblHeaders)

     

    report1.DataSource = myDS

  12. Brook
    Brook avatar
    2 posts
    Member since:
    Mar 2007

    Posted 09 Apr 2010 Link to this post

    I think I have a bettter solution.  Note this is a bug with the Microsoft Data Set Designer and not Telerik Reporting. 

    Apparently the DS Designer has an issue with reading the Metadata if you use temp table in your stored proc e.g. #temptablename resutling in the graphical user interface for adding a Table Adapter using a stored procedure becoming un-usable and forcing you to use a hand coded work around as Susan noted above or re-write your entire stored proc.  Not sure about you, but I use these tools to reduce my work not increase it.  The idea of hand coding my data set connections AND my report takes the fun out of my day.

    I found this little trick that will get the Data Set Designer to read your metadata from your stored proc and allow you to use the Graphical Designers rather than hand coding everything.

    Add this in the beginning section of your stored proc, you may have to remove the tableadapter from the design surface after you add this for it to show.  I added mine right after my AS statement and before my declaration and temptable definitions.

    IF 1=0 BEGIN
        SET FMTONLY OFF
    END

    Since the condition of 1=0 will never execute no worries on actually setting FMTONLY to OFF.
    Bizarre, but the designer surface will now recognize your metadata and allow you to use them via the graphical interface of T-Reporting just as it should.  Apparently this little feature has been around since for quite some time and must fall low on Microsoft's list of things to fix.

    I found the details for this fix in this thread on Microsoft: http://social.msdn.microsoft.com/forums/en-US/vbgeneral/thread/2e8b86eb-9c3d-439f-a904-b457ad9c0a2c/ 

    Hope this helps others...  Happy Reporting
    Brook
  13. Eric Moore
    Eric Moore avatar
    25 posts
    Member since:
    Mar 2010

    Posted 10 Nov 2010 Link to this post

    That worked great, thanks!
  14. Glenn
    Glenn avatar
    4 posts
    Member since:
    Apr 2011

    Posted 01 Jun 2011 Link to this post

    See this post for the easy fix:

    http://www.telerik.com/community/forums/reporting/telerik-reporting/error-while-report-designing.aspx#1577288

    SET FMTONLY OFF

    Simply add the following line to your stored procedure - saved me a heap of pain and doesn't require changing server config like the answers below.
  15. Muhammad
    Muhammad avatar
    10 posts
    Member since:
    Aug 2012

    Posted 10 Aug 2012 Link to this post

    Great, putting
    SET FMTONLY OFF
    in the very start of your stored procedure works. See the snapshot.
  16. Mangesh
    Mangesh avatar
    16 posts
    Member since:
    Sep 2011

    Posted 22 Mar 2013 Link to this post

    Hi,

    I was having the same issue but according to specify into this forum,
    I've added following code in my sp too.
    IF 1=0 BEGIN
        SET FMTONLY OFF
    END

    And right after that I've successfully bind fields through report wizard steps.
     
    But now I'm facing some more and very important issues 
    1) In Designer, In Edit Expression Dialogue, After Clicking on Fields options, db fields are not showing up. (see attach image1.png)
        However, I'm able to see all the columns in Data Explorer (see attach image2.png)
    2) Maybe, because of that Even Reports are not able to generate at run time.(see attach image3.png) , However, data is present in the db. I executed the sp in sql datasource wizard and it gave me all the necessary data. (see attach image4.png)

    This is only happening for this report due to creating temporary table in sp. Rest of the reports are working fine which doesn't use temporary tables.

    Is there any workaround to this couple of issues?

    Regards,
    Mangesh


  17. Stef
    Admin
    Stef avatar
    3036 posts

    Posted 27 Mar 2013 Link to this post

    Hi Mangesh,

    We cannot reproduce the described behavior. Please check whether your report's DataSource property (or Table.DataSource as it is not clear from the screenshot) is set to the SqlDataSource component. You can find attached a sample report illustrating the needed settings to use stored procedures containing temp tables.

    Further details on how to reproduce your issue will be much appreciated.

    Regards,
    Stef
    the Telerik team

    Telerik Reporting Q1 2013 available for download with impressive new visualizations. Download today from your account.

  18. Ken Lewis
    Ken Lewis avatar
    28 posts
    Member since:
    Sep 2009

    Posted 12 Jun 2013 Link to this post

    Brook,

    Thanks for the post, that saved me!

    Ken
  19. Rowan
    Rowan avatar
    1 posts
    Member since:
    Oct 2013

    Posted 17 Oct 2013 Link to this post

    Thanks Brook, very good finding. Great help for me
Back to Top
DevCraft banner