It has been a long time since I dealt with this issue in our environment, but it seems like changing the job to a SQL step using xp_cmdshell made it...

Openquery returns the same error.

When connecting directly to the server where the global temp table was created, I can query it, but I cannot query over the linked server.

Using the linked server, I can...

Is it possible to query from a global temp table from another server via linked server?

When I use

SELECT * from linked_server.db.owner.##GlobalTemp

I get a message:

Invalid object name ##GlobalTemp

Did you ever find a solution to this problem?  We are having the same issue with the same frustrations and no common threads other than it is a cmdexec job...

Default operation would be to have this run serially.  The VBScript would wait for the DTS package to complete before executing the next command.  Right??

One possible solution: 

Rather than inserting directly to the table, create a table on the destination with the same structure and import to that table.  You can think of this...

Use the Microsoft OLE DB Provider for ODBC Driver and the DSN is the same as the data source from the ODBC driver.  The other fields are blank.

On the security...

We have had success using the linked server connected to an ODBC driver as suggested by the original poster.

We use the openquery syntax as suggested above and access multiple libraries...

Thanks!  I knew it had to be there somewhere.

Might be possible to create the large procs independently and then conditionally execute them with dynamic sql

If you have a datetime field in the dataset you are updating, you can do a query to the table and check for today's date using an execute SQL task. ...

You might also try running manually on the server.  It could be a missing driver or drive mapping or something simple like that.  When you run on your local machine,...

You might be able to update sysdatabases table in the master database with a new name and then try dropping it using the new name.

Good Luck!

This sounds like a legacy minded solution.  While it would probably work, when working with a database, recovery from abends *should* be painless if well written due to transaction logging...