SQL Server Performance

How to execute a stored procedure with temp tables

Discussion in 'SQL Server 2005 Integration Services' started by GopalMynam, May 22, 2007.

  1. GopalMynam New Member

    Hi,

    Server A --- SQL Server 2000
    Server B --- SQL Server 2005

    I need to move data from source A to Source B using SSIS package in server B.

    I have a stored procedure in server A, in which I used private temp tables for processing data.

    I want to execute the SP through OLE DB source (which is part of data pump).

    I am getting error "invalid object name #temp1".

    Can someone help me in overcoming this problem.

    Thanks,
    GMYNAM
    GopalMynam, May 22, 2007
    #2

  2. dineshasanka Moderator

    You should set the RetainSameConnection property of the connection to True.

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
    dineshasanka, May 22, 2007
    #2

  3. satya Moderator

    satya, May 23, 2007
    #2

Share This Page