DTS Import Wizard Problem

Question

DTS Import Wizard Problem

Scott Thornton-407727

SSCrazy

Points: 2271
More actions
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?

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

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

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 1

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.

Scott Thornton-407727 SSCrazy Points: 2271 More actions · Answer 2

thats no so good for multiple calls to the proc.

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

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 3

Koji Matsumura

SSCrazy

Points: 2241

June 25, 2007 at 8:26 pm

#715077

Please post your code.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 4

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

Scott Thornton-407727 SSCrazy Points: 2271 More actions · Answer 5

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.