Stored Proc with temp table issue.

  • Hi everyone.

    I understand that this topic was covered several times on this forum, but I can't seem to find ones matching my issues.... and am hoping to find some solution :(.

    I am trying to use stored proc created for report as a data source and move result into other database table.

    Store proc first generate #temp table, then creates #temp noncluster index, then select statement to call data from #temp table (with multiple joins).

    store proc sample:

    > create table #temp

    > index #temp table

    > select statement from #temp (multiple joins to other table)

    Store proc was developed for report and optimized for data read. However, when I try to use this stored proc as data source, I get 'Invalid Object name #Temp Table' error.

    I did some research, but only thing I see is mostly calling data stored in temp table and using temp table for the source. My case is bit different... temp table is used, but it goes through another multiple joins and returns result.

    Any suggestions?

    Thank you all for reading this!

  • Hi BlackGarlic,

    First, my advice is that you post this to the T-SQL forum because this one on SqlServerCentral is for Integration services (SSIS) which is not as frequently visited as T-SQL, not to mention its for a different topic.

    Some suggestions (by no means am I an expert). But, first, if possible, I would copy/paste the select statement from sproc into another query editor window and execute the create #temp table first. Then I would, piecemeal, execute the other portion of that query up to and including it's first join to the #temp table.

    This should reveal the problem. Perhaps the join to the #temp table has a typo. This is valid initial approach that you may have already taken.

    I would do it and post the results of that investigation on the T-SQL forum.

  • Where are you trying to use the stored procedure as a data source and how are you specifying it?

  • When I run stored proc on ssms, it runs fine. Problem occurs when I try to execute the store proc in SSIS package.

    Stored Proc is being used in Data Flow Task > OLE DB Source. I selected SQL query as source.

    Here is the store proc code sample:

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    alter procedure dbo.usp_GetBill_Info

    @StartDate datetime,

    @EndDate datetime,

    AS

    BEGIN

    SET NOCOUNT ON;

    create table #ID

    (

    ID nvarchar(100),

    TID nvarchar(100)

    )

    insert into #EID (ID, TEID)

    select a1.ID, a2.TID

    FROM CustID AS a1

    inner JOIN TransactionHeader AS a2

    where PV1.DischargeDtTm between @StartDate AND @EndDate2

    create NONCLUSTERED INDEX #idx_ID on #TID(ID) include (TID)

    -- Start Report

    select

    #ID.ID

    ,ID.TID

    ,Account_Status = V1.AccountStatus

    ,Quantity=f.[Quantity]

    ,Amount = cast(fin.[Amount] as decimal(38, 2))

    ,Service_Date = fin.[ServiceDate]

    ,fin.BatchDate

    from #ID

    INNER JOIN Account as v1

    on #ID.ID = v1.ID

    INNER JOIN financeData AS fin

    on #ID.TID = fin.TID

    drop table #EID

    end

    go

  • My SSIS is rusty, how are you invoking the procedure in SSIS?

  • create NONCLUSTERED INDEX #idx_ID on #TID(ID) include (TID)

    hmmm. the create index is for a temp table I don't see referenced anywhere else (I see #ID and #EID)

    Can you remove the create index statement from the sproc and try rerunning the bids package.

  • index portion of code is wrong. It's actually

    create NONCLUSTERED INDEX #idx_ID on #ID(ID) include (TID).

    But I will run it again without index portion.

    I an using following sql code in OLE DB Source:

    exec dbo.usp_GetBill_Info @StartDate =?, @EndDate=?

  • BlackGarlic (5/26/2013)


    index portion of code is wrong. It's actually

    create NONCLUSTERED INDEX #idx_ID on #ID(ID) include (TID).

    But I will run it again without index portion.

    I an using following sql code in OLE DB Source:

    exec dbo.usp_GetBill_Info @StartDate =?, @EndDate=?

    And you have defined the parameters and the appropriate variables in the OLE DB component?

    I am trying to do something similar using SSIS on my system at home, just not with your proc.

  • You seem to create a temp table called #ID, then INSERT and SELECT from #EID, which is not defined. Have you missed out a section from your code?

    Can you possibly post the full text of the error message?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I'm not saying you can't execute a sproc as a sql statement but I've never done it. Why don't you just extract the sql out of the sproc for the time being, minus the index and add back pieces one at a time. You can re-parameterize at the very end.

  • @Phil

    Actual code has #ID. I made mistake while I was copying the code.

    This is the error I am getting:

    Exception from HRESULT: 0xC0202009

    Error at Data Flow Task [OLE DB Source [18]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

    An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0"

    Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".

    @KoldCoffee

    Yes. I am in process of rewriting report query logic to avoid temp table. But I would still like know/learn how to utilize this kind of store proc because we have lot of report written with temp table...

    Thanks!

  • Before you eliminate the #temp table, take a look at this link and see if it's ideas help solve the #temp table issue.

    http://www.sql-server-performance.com/2013/temp-tables-ssis/

    As far as the error message, I will tell you that SSIS is notorious for giving messages that lead you nowhere. So, they are of use to a marginal extent, as in CLUEs, but often misleading.

  • U can try out with ##temp tables that is (global temp table) use this only u can not find with #temp.

    Moreover, u can create physical temp table in tempdb. that is same as #temp table (but u need to create and drop it manually)

  • This is a fairly common issue with SSIS (at least in versions before SSIS 2012 - I haven't yet tested this on 2012). When using a stored proc as a data source, if that sproc uses temp tables at all, chances are you're going to have some issues using it in SSIS. To make a long story short, SSIS will try to validate the upstream data prior to execution, and if the temp table doesn't yet exist, that validation will often fail.

    There are a few workarounds to this, none of which are very pretty. The easiest solution is usually to replace the temp table with a physical staging table (one that is persisted in the database), and just trunc/reload this staging table in the sproc.

    If you can't change the sproc, you might consider executing the sproc in an Execute SQL Task and load those results into a staging table directly by doing an INSERT INTO <staging table> EXEC <sproc name>.

    Hope this helps...

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • I got this problem finally resolved. So the trick was in the beginning of the stored proc, I just had to declare all column values like following:

    if 1=2

    begin

    select

    CAST(NULL AS nvarchar(60)) Account,

    CAST(NULL AS datetime) OrderDate,

    What a pain!

    Thank you all for your tips and suggestions!

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply