If my understanding is correct, everybody who has access to a remote server has the ability to make temp tables on that remote server. However, this does not necessarily endow a person with the rights necessary to export that data.
This has been the case where I work for a while. Most of the time, the queries I run are small enough in results to allow me to simply copy and paste the data where needed. For larger files, I have struggled a bit more. My first work-around was to link the remote server to my local server, and run my queries from the local server, depositing the files on the local server rather than the remote server’s temp folder. This did produce correct results, but running the queries this way appeared to take quite a bit of time.
I wanted a way to transfer global temp tables from the remote server to the local one, but when I attempted to do this using a Select Into query, SQL Server always interpreted the “##” as pointing to the local temp tables rather than the remote ones. Such queries would result in the following message:
“Database ‘TempDB’ ignored, referencing object in tempdb.”
Well, today I found the solution over at StackOverflow. User Ravi provided the answer in 2014, and the answer was edited by scsimon on June 2, 2016:
The query this person provided was:
SELECT * FROM OPENQUERY(linkedServerName, ‘SELECT * FROM ##temp’)
So – I tried it out. In my case, I wanted to take the ##OpioidList file off the remote server, and I wanted to make it into a table in my local database (not the tempdb.)
First, my remote server was already linked through my localDB- we’ll call it “secretservername” here. If you don’t know whether your remote server is linked to your local one, you can check in Server Objects –> Linked Servers.
Next, I made a query on my local server:
SELECT * INTO OpioidList
FROM OPENQUERY(secretservername, ‘SELECT * FROM ##OpioidList’)
This worked like a dream – creating the table in my “LocalServer” database as a non-temporary file. From there I was able to export it to a flat file to provide to someone in a different department.