This is a migrated thread and some comments may be shown as answers.

Temporary Table in Stored Proc

23 Answers 1305 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
Susan
Top achievements
Rank 1
Susan asked on 10 Sep 2008, 10:58 PM
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

23 Answers, 1 is accepted

Sort by
0
Felizardo Francisco
Top achievements
Rank 1
answered on 18 Sep 2008, 06:20 PM
I am having the same problem.

Solutions anyone?
0
Felizardo Francisco
Top achievements
Rank 1
answered on 18 Sep 2008, 07:01 PM
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.

0
Susan
Top achievements
Rank 1
answered on 21 Sep 2008, 09:03 PM
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
0
Felizardo Francisco
Top achievements
Rank 1
answered on 21 Sep 2008, 10:05 PM
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




0
Susan
Top achievements
Rank 1
answered on 22 Sep 2008, 12:15 AM
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

JASON
Top achievements
Rank 1
commented on 31 Oct 2023, 08:38 AM

I do as this, but my MSSQL shows as follow:

SELECT a.*
FROM
OPENROWSET('SQLOLEDB','SEPPC';'scwebaccess';'scwebpassword',
'set fmtonly off exec dbo.rpt_RMN_Sidebar_Right @Login_Id @Security_List @Report_Date ') AS a;

Error Info: Msg 15281, Level 16, State 1, Procedure rpt_RMN_Sidebar_Right_Test, Line 20 [Batch Start Line 16]
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

0
Felizardo Francisco
Top achievements
Rank 1
answered on 23 Sep 2008, 02:09 PM
Where are you getting the error? at what point? and what is the error message?

You should open a support ticket for it.

0
Susan
Top achievements
Rank 1
answered on 29 Sep 2008, 10:33 PM
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
0
AkAlan
Top achievements
Rank 2
answered on 18 Feb 2010, 01:31 AM
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.
0
Susan
Top achievements
Rank 1
answered on 23 Feb 2010, 03:18 PM

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

0
Brook
Top achievements
Rank 2
answered on 09 Apr 2010, 09:45 PM
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
0
Eric Moore
Top achievements
Rank 1
answered on 10 Nov 2010, 10:13 PM
That worked great, thanks!
0
Glenn
Top achievements
Rank 1
answered on 01 Jun 2011, 08:12 AM
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.
0
Muhammad
Top achievements
Rank 1
answered on 10 Aug 2012, 09:26 AM
Great, putting
SET FMTONLY OFF
in the very start of your stored procedure works. See the snapshot.
0
Mangesh
Top achievements
Rank 1
answered on 23 Mar 2013, 12:19 AM
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


0
Stef
Telerik team
answered on 27 Mar 2013, 04:18 PM
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.

0
Ken Lewis
Top achievements
Rank 1
answered on 13 Jun 2013, 12:34 AM
Brook,

Thanks for the post, that saved me!

Ken
0
Rowan
Top achievements
Rank 1
answered on 17 Oct 2013, 10:10 PM
Thanks Brook, very good finding. Great help for me
0
Dan
Top achievements
Rank 1
answered on 01 May 2017, 12:59 PM
This worked great.  Thank you!
0
Samuel
Top achievements
Rank 1
answered on 05 Sep 2017, 01:29 PM

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.

0
Vijayan
Top achievements
Rank 1
answered on 03 Oct 2017, 12:04 PM

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

 

0
Bryon
Top achievements
Rank 2
answered on 29 Dec 2017, 10:23 PM
This is not working for me... I get a time out error when I try to execute the stored procedure when configuring the sqldatasource... :(
0
Samuel
Top achievements
Rank 1
answered on 30 Dec 2017, 12:19 PM

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.


0
Bryon
Top achievements
Rank 2
answered on 30 Dec 2017, 04:34 PM
I did bump it up, but gave the same error atabout the same time lapse.Thank you for your quick response.
Tags
General Discussions
Asked by
Susan
Top achievements
Rank 1
Answers by
Felizardo Francisco
Top achievements
Rank 1
Susan
Top achievements
Rank 1
AkAlan
Top achievements
Rank 2
Brook
Top achievements
Rank 2
Eric Moore
Top achievements
Rank 1
Glenn
Top achievements
Rank 1
Muhammad
Top achievements
Rank 1
Mangesh
Top achievements
Rank 1
Stef
Telerik team
Ken Lewis
Top achievements
Rank 1
Rowan
Top achievements
Rank 1
Dan
Top achievements
Rank 1
Samuel
Top achievements
Rank 1
Vijayan
Top achievements
Rank 1
Bryon
Top achievements
Rank 2
Share this question
or