Post reply

DTS Import Wizard Problem

  • June 25, 2007 at 6:41 pm

    #70570

    Hi,

    Trying to import some data from one database to another. The stored procedure I am calling (on a MS SQL 2000 64 bit server) contains a temporary table that causes the following error:

    ---------------------------

    DTS Import/Export Wizard Error

    ---------------------------

    Error Source: Microsoft OLE DB Provider for SQL Server

    Error Description: Invalid object name '#TMP_INVOICE'.

    Context: Error calling GetColumnInfo.  Your provider does not support all the interfaces/methods required by DTS.

    ---------------------------

    OK  

    ---------------------------

    In fact a similair error occurs when creating the package manually:

    ---------------------------

    Package Error

    ---------------------------

    Error Source : Microsoft OLE DB Provider for SQL Server

    Error Description : Invalid object name '#TMP_INVOICE'.

    ---------------------------

    OK  

    ---------------------------

    My guess is that is doesn't like the temp table.

    Is there anyway around this?

  • June 25, 2007 at 7:25 pm

    #715074

    Execute a CREATE TABLE statement to create a "permanent" version of your temp table, insert records into it, then execute a DROP TABLE statement when you're done.

  • June 25, 2007 at 7:30 pm

    #715076

    thats no so good for multiple calls to the proc.

    I am investigation use of the table ar at the moment.

  • June 25, 2007 at 8:26 pm

    #715077

    Please post your code.

  • June 25, 2007 at 10:48 pm

    #715101

    Why do you use a hash table while moving data to other database. is it joining data between 2 or more tables and inserting into the destination database. If so you can use the query itself directly to select and insert the data to the destination database rather than moving to a temp table and moving from there.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • June 25, 2007 at 10:58 pm

    #715105

    The temp table is within a stored procedure I would like to call. its resultset is the data I wish to insert into the other database.

    Because the proc is quite complicated and multiple updates are performed on the #temp resultset.

    I have changed the #temp to a @table var.

    I just get a different error of 'Null pointer exception'

    I don't think mssql2000 64 bit likes these variables much when trying to import into a ms sql2000 32 bit db.

     

     

     

Viewing 6 posts - 1 through 5 (of 5 total)

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