Is there any way to create a temp table on the remote server via OpenQuery?

Question

Post reply

Is there any way to create a temp table on the remote server via OpenQuery?

lindsaywang

SSCrazy

Points: 2569
More actions
March 6, 2011 at 7:54 pm

#236062

Hi,
Is there any way to create a temp table on the remote server via OpenQuery? I want to use one local server to check the data on one remote server is right. in my code, I have to create a temp table, and then use it to join with other table, the query is similar as the following info. To be honest, I only hope get the data from the query in the openquery, but it query will be very slow if we create the temp table in the local server, and join with the table in the remote server. do you have any suggestion? really appreciated if you can share something with me. Thanks a lot.
Thanks
Lindsay
Select * from OPENQUERY(remotelinkedserver,'SET FMTONLY OFF;
select
*
into #temp
from
adw1.dbo.account
select *
from
#temp t
left outer join adw1.dbo.account.employee hr
on t.PersonnelIdentifier = hr.PersonnelIdentifier
where t.CurrentCostCenterName <> hr.CurrentCostCenterName')

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

not with openquery, but the linked server has an EXECUTE AT command that allows you to do some things;

i've created real tables, bu tnot temp tables(except inside a full set of commands...

for example, i think the execute at command, when it is finished, closes it's connection to teh server, and thus a temp table would be destroyed;

EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'

GO

EXECUTE ( 'CREATE TABLE AdventureWorks2008R2.dbo.SalesTbl

(SalesID int, SalesName varchar(10)) ; ' ) AT SeattleSales;

so your execute command could potentially create a table and manipulate it,as long as the commands are semicolon delimited.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

lindsaywang SSCrazy Points: 2569 More actions · Answer 2

Thanks a lot Lowell. It works for me^_^

Thanks

Lindsay

zipman1952 SSC Journeyman Points: 95 More actions · Answer 3

Thanks, Lowell - that's a lot easier than what I've been doing:

declare @sql varchar(4000)

declare @sql2 nvarchar(4000)

declare @targetServerName varchar(100)='targetMachine'

select @sql = 'N''create table [dirlist] ([listrecords] varchar(500)) insert [dirlist] EXEC master.dbo.xp_cmdshell ''''dir e:\'''''''

select @sql2 = 'execute ' + @TargetServerName + '.[master].dbo.sp_executesql ' + @sql

exec (@sql2)

Getting the quotes right can be a real pain..

Lowell SSC Guru Points: 323518 More actions · Answer 4

sweet! glad that worked for you; I'm putting that in my notes that it really works; i wasn't absolutely sure.

yeah, getting the syntax for the dynamic sql part can be a real pain with the quotes, i know.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!