I have a work flow as followed but haven't been had any success after the 1st step. 1. run a query to create a global temp table from ServerA.databaseX 2. pull the data from the global temp table to the ServerB.databaseY I think the problem is that after the step1 excuted, the session closes and the global temp table gets deleted. So when the step 2 tries to access the global temp table it's no longer there. I tried to pull the data using the direct query method (no temp table) but it took too long - more than 45 minutes. If i have to do the above steps manually, it wouldn't take more than 15 minutes. I dont have access to create a local table in the destination server/database. Is there a way to keep the session alive so i can pull data from the global temp table. I appreciate any help you can provide.

You could create a new staging database xxx1 on the ServerA. Set recovery model to simple. Create a permanent table zt01 in the database xxx1. Truncate that table zt01 before inserting a new data on the SeverA. Insert new data into table ServerA.xxx1.zt01. Transfer data from ServerA.xxx1.zt01 into ServerB.DatabaseY. It could be easily done via SSIS.Sergei

What is the error you are getting? Why do you need the temp table on Server A? What populates this table? I would dump all the data off the table on ServerA to a file then upload this file to Server B.Arthur My Blog