SQL Temp Tables

[Steve]

We've been using Telerik Reporting for more than a year and very pleased with the results. 

In one client app, some DB work is done in ASP.NET code which produces a table whose name is passed to Telerik Reporting which then uses it fine and all works great.  We haven't had the need to use stored procedures in the app.  A client now has a need for us to make the normal table into a SQL temporary table within TEMPDB.  Although I've been quite successful in using SQL with our app, I'm not familiar with true SQL temporary tables.

Upon some research, I came across the' '# & '##' table name prefixes and so (maybe naively) I simply added a '##" to the table name which seems to create the correct table, however, the Telerik report fails with:

    An error has occurred while processing Report 'SystemRpt1':
    Invalid object name '##RPTTEMP_1_5'.

Do you have any example modules which show how to use true SQL temporary tables with Telerik Reporting?

We're using:
    MS SQL Server 2005
    ASP.NET 2.0 (in this app)
    VS 2010 (VB)
    IIS7

Much thanks in advance!

[Massimiliano Bassili]

There are quite a few topics on this subject, I suggest you search the forums, prior opening a new one. Here is an example: http://www.telerik.com/community/forums/reporting/telerik-reporting/temporary-table-in-stored-proc.aspx

Cheers!

[Steve]

Thanks Massimiliano,

We've reviewed those threads and they all discuss how to work with SQL temp tables "when using stored procedures" which we are not.

Currently, the operational production app produces a SQL table whose name is passed to the report to process.  All works well with this, however, when we add the # or ## prefix to the table name, it works as expected in the ASP.Net code and the Telerik report fails with the error I showed in my original post.

The relevant code in the Telerik report is:

Private Sub MyReport_NeedDataSource(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.NeedDataSource

        Dim connectionString As String = Me.ReportParameters("SQLConnect").Value

        Dim sql As String = Me.ReportParameters("SQLSelect").Value

        Dim adapter As New SqlDataAdapter(sql, connectionString)

        Dim dataSet As New DataSet()

        adapter.SelectCommand.CommandTimeout = 0

        adapter.Fill(dataSet)

        Me.DataSource = dataSet

    End Sub

The SQLConnect report parameter normally contains something like "SELECT * FROM RPTTable".

We we simply change the RptTable name in our app (and therefore in the report parameter) to "#RPTTable" or "##RPTTable" it fails in the report with the error I reported - yet we can see the new temp table elsewhere in ASP.Net when using the ## global prefix.

The articles you referenced deal with how to address using temp tables when using stored procedures, however, we are not using stored procedures and we're trying to apply that suggested code change to our code without success.

Please advise, thanks in advance.
Steve

[Steve]

Ooops!  When I wrote:  "The SQLConnect report parameter normally contains something like "SELECT * FROM RPTTable".", of course I meant to write the word "SQLSelect" instead of "SQLConnect"...