Temporary Table in Stored Proc

[Susan]

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]

I am having the same problem.

Solutions anyone?

[Felizardo Francisco]

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]

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]

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]

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

[JASON]

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.

[Felizardo Francisco]

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

You should open a support ticket for it.

[Susan]

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]

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]

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]

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]

That worked great, thanks!

[Glenn]

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]

Great, putting
SET FMTONLY OFF
in the very start of your stored procedure works. See the snapshot.

[Mangesh]

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]

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

[Ken Lewis]

Brook,

Thanks for the post, that saved me!

Ken

[Rowan]

Thanks Brook, very good finding. Great help for me

[Dan]

This worked great.  Thank you!

[Samuel]

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]

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]

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

[Samuel]

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]

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