Temporary Table in Stored Proc in Reporting General Discussions

Susan

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

Felizardo Francisco

4 posts

Member since:
May 2008

Posted 18 Sep 2008 Link to this post

I am having the same problem.

Solutions anyone?

Felizardo Francisco

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.

Susan

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

Felizardo Francisco

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

Susan

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 a

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

Thanks Again,
Susan

Felizardo Francisco

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.

Susan

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

AkAlan

118 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.

Susan

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

Brook

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

Eric Moore

25 posts

Member since:
Mar 2010

Posted 10 Nov 2010 Link to this post

That worked great, thanks!

Glenn

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.

Muhammad

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.

query.png

Mangesh

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

Stef

Admin

3610 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.

TempTables.zip

Ken Lewis

28 posts

Member since:
Sep 2009

Posted 12 Jun 2013 Link to this post

Brook,

Thanks for the post, that saved me!

Ken

Rowan

1 posts

Member since:
Oct 2013

Posted 17 Oct 2013 Link to this post

Thanks Brook, very good finding. Great help for me

Dan

12 posts

Member since:
Jul 2011

Posted 01 May 2017 in reply to Brook Link to this post

This worked great. Thank you!

Samuel

2 posts

Member since:
Sep 2017

Posted 05 Sep 2017 Link to this post

I had the same problem, which was solved using Common Table Expression (CTE). The following code example:

; WITH TempTable

AT

(

- -Your query here

)

Select * from TempTable

I hope I have helped.

Sincerely,

Samuel Mesquita.

Vijayan

3 posts

Member since:
Aug 2014

Posted 03 Oct 2017 in reply to Felizardo Francisco Link to this post

In order to use stored procedures containing temporary tables, you have to turn off FMTONLY in a seemingly never executed statement. Thus the data schema of temporary tables can be read by the SqlDataSource Wizard as each state of IF-ELSE statements is evaluated when the SQL query is run by the SqlDataSource Wizard. The check will be skipped when the same SQL query is used by calling application at run-time.

IF 1=0 BEGIN
SET FMTONLY OFF
END

Bryon

9 posts

Member since:
Jan 2015

Posted 29 Dec 2017 in reply to Brook Link to this post

This is not working for me... I get a time out error when I try to execute the stored procedure when configuring the sqldatasource... :(

error after turning variable off.png

Samuel

2 posts

Member since:
Sep 2017

Posted 30 Dec 2017 in reply to Bryon Link to this post

Your Problem is timeout exception. You try to increase the connection time. You look in sqlDataSource the CommandTime property, Then increase this value. Usually, this value is setting with the 30 value. Increase to 300, remembering that these values are given in seconds.

Sincerely,

Samuel Mesquita.

Bryon

9 posts

Member since:
Jan 2015

Posted 30 Dec 2017 in reply to Samuel Link to this post

I did bump it up, but gave the same error atabout the same time lapse.Thank you for your quick response.