connect variable to an openquery to a linked server on a join

1EXEC sp_addlinkedserver @server='SQL2005', @srvproduct='', @provider='SQLNCLI', @datasrc='SERVER1\SQL2005'

1--Script : EXEC AT Command
2
3--Script 1 : Create a linked server 
4EXEC sp_addlinkedserver 'FARAWAYSERVER', 'SQL Server'
5
6--Script 2 : Execute a simple SELECT statement on the linked server
7EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer') AT [FARAWAYSERVER];
8GO
9
10--Script 3 : Executing multiple SELECT statements on linked server and getting multiple resultsets
11EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer;
12SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.CustomerAddress;') AT [FARAWAYSERVER];
13GO
14
15--Script 4 : Execute a SELECT statement on linked serer and pass two arguments at dynamically
16EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer 
17WHERE CustomerID = ? AND LastName = ?', 10, 'Garza') AT [FARAWAYSERVER];
18GO
19
20--Script 5 : Execute a SELECT statement on linked serer and pass two arguments at dynamically
21--by using variables
22DECLARE @CustomerID AS INT
23DECLARE @LastName AS VARCHAR(100)
24SET @CustomerID = 10
25SET @LastName = 'Garza'
26EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer 
27WHERE CustomerID = ? AND LastName = ?', @CustomerID, @LastName) AT [FARAWAYSERVER];
28GO
29
30--Script 6 : Execute a DDL statement on linked server
31EXEC (
32'USE TempDB
33IF OBJECT_ID(''dbo.Table1'') IS NOT NULL
34DROP TABLE dbo.Table1
35CREATE TABLE dbo.Table1
36(
37Column1 INT
38)' ) AT [FARAWAYSERVER];
39
40--Script 7 : Once you are done with your testing, clean up created objects
41EXEC (
42'USE TempDB
43IF OBJECT_ID(''dbo.Table1'') IS NOT NULL
44DROP TABLE dbo.Table1'
45) AT [FARAWAYSERVER];
46EXEC sp_dropserver 'FARAWAYSERVER'

1exec sp_serveroption @server='myserver', @optname='rpc', @optvalue='true'
2exec sp_serveroption @server='myserver', @optname='rpc out', @optvalue='true'

1DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)SET @LinkedServer = 'MyLinkedServer'SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''SET @TSQL = 'SELECT au_lname, au_id FROM pubs..authors'')' EXEC (@OPENQUERY+@TSQL)