Bug Report T141620
Visible to All Users

"Invalid object name '#temp01'" when using a stored procedure with temporary tables as an Xtrareport data source

created 11 years ago

I have made a stored procedure on a MS Sql server. It runs on the server and I can make a AspxGridview with it. I run the wizard to make the sqldatasource for the AspxGridView, No problem with that.

Now I want to make a Xtrareport with a chart with data from the same stored procedure. When I run the data source wizard "add report data source"
I choose for the same connectionstring I did for the Gridview, same stored procedure etc but I am not able to finish the wizard. It comes with the error Invalid object name #temp01.

When I change my stored procedure by removing the temporary tables , I can finish the wizard , and show the chart in my report. When I put my temporary tables back in the stored procedure I get the same error 'invalid object name' when I run the report.

How can I avoid this error? Why is the connection for a aspxgridview datasource different from a xtrareport datasource ?

Comments (3)
DevExpress Support Team 11 years ago

    Hello Gerd,

    Would you please provide us with a database backup, so we will be able to recreate this situation and do our best to fix it?

      I made a small example . In the rar file is a database dump Sql server 2008, and a solution creating a aspxgrid view with a proc with a temp table and a report with a proc without temp table. I am not able to use the proc with the temp table in the report

      DevExpress Support Team 11 years ago

        Hi,

        I have managed to reproduce this behavior. I am forwarding this issue to our developers for further processing. You will receive an automatic message once the status of this issue is changed.

        Answers approved by DevExpress Support

        created 11 years ago (modified 11 years ago)

        Hi,

        We have examined this behavior and come to the conclusion that this issue is not directly related to our components. When temporary tables are used in the Stored Procedure, the .NET data provider fails to properly retrieve the schema.

        In order to use stored procedures containing temporary tables, you have to turn off FMTONLY in a never executed statement. Thus, the temporary tables can be read by the calling application. For example:

        SQL
        ALTER PROCEDURE [dbo].[sip_TempTable] -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; IF 1=0 BEGIN SET FMTONLY OFF END Select * Into #T141620 From ST_Orders Select * From #T141620 END

        I also suggest that you review the following topic that contains more detailed information on this solution:
        http://www.sqlservercentral.com/Forums/Topic969503-338-1.aspx

          Show previous comments (7)
          AP AP
          Afsaneh Poorbakhtiar 9 years ago

            Hi Ingvar
            I did so and succeed, thanks

            DevExpress Support Team 9 years ago

              You are welcome, Afsaneh!

              PT PT
              Pradeep Tiwari 5 6 years ago

                Thanks worked for me also.

                Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

                Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.