SQL Server Performance

Temp Tables in Reporting Service

Discussion in 'SQL Server Reporting Services' started by mheinrich, Feb 12, 2004.

  1. mheinrich New Member

    Hello,

    I am tring to create a report from a sp. In the sp I am selecting into a temp table.
    example: select * from pubs into #tempTable

    This is the error I recieve from report service:
    There is an error in the query. Invalid object name '#tempTable'.

    Any Ideas?

    Thank You,

    Matt
  2. jasper_smith New Member

    When the Report Designer tries to get metadata about the stored procedures resultset this will fail because it runs the stored procedure with SET FMTONLY ON. In a development environment you have choice of using a normal table to simulate the structure of the temporary table (this has to exist and cannot itself be created in the procedure) or manually add the fields of the dataset to the fields toolbar by right clicking on it and choosing Add. Becasue SET FMTONLY ON returns only metadata and does not actually execute the procedure, the temp table created in the procedure will not exist and it will fail to return the metadata.


    HTH

    Jasper Smith
  3. tudor_ali New Member

    quote:Originally posted by mheinrich

    Hello,

    I am tring to create a report from a sp. In the sp I am selecting into a temp table.
    example: select * from pubs into #tempTable

    This is the error I recieve from report service:
    There is an error in the query. Invalid object name '#tempTable'.

    Any Ideas?

    Thank You,

    Matt

    Use a variable instead of a temp table.
  4. unclebiguns New Member

    If you run the stored procedure in the Data tab it will retrieve the fields and no longer give you the error.[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Jack Corbett, DBA<br />Nexfor Fraser Papers
  5. knarf New Member

    You have to create #temp table before you can insert / access

    try something like this
    ============================

    CREATE PROCEDURE usp_partselector
    @PartType as varchar(20)
    AS
    -- CREATE TEMP TABLE
    create #temp (part varchar(10), qty int, cost real)
    -- INSERT RECORDS
    insert into #temp
    select part, qty, cost from source table
    Where partgroup = @PartType

    -- GET RECORDS FROM TEMP TABLE
    select * from #temp

    GO
    ========================================

Share This Page