Post reply

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

  • 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')

  • March 6, 2011 at 7:58 pm

    #1295166

    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!

  • March 6, 2011 at 10:05 pm

    #1295176

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

    Thanks

    Lindsay

  • March 18, 2011 at 7:56 am

    #1300510

    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..

  • March 18, 2011 at 8:32 am

    #1300538

    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!

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

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