sql server openquery from varchar code example

Example 1: CONNECT VARIABLE TO AN OPENQUERY TO A LINKED SERVER ON A JOIN

--Script : EXEC AT Command

--Script 1 : Create a linked server 
EXEC sp_addlinkedserver 'FARAWAYSERVER', 'SQL Server'

--Script 2 : Execute a simple SELECT statement on the linked server
EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer') AT [FARAWAYSERVER];
GO

--Script 3 : Executing multiple SELECT statements on linked server and getting multiple resultsets
EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer;
SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.CustomerAddress;') AT [FARAWAYSERVER];
GO

--Script 4 : Execute a SELECT statement on linked serer and pass two arguments at dynamically
EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer 
WHERE CustomerID = ? AND LastName = ?', 10, 'Garza') AT [FARAWAYSERVER];
GO

--Script 5 : Execute a SELECT statement on linked serer and pass two arguments at dynamically
--by using variables
DECLARE @CustomerID AS INT
DECLARE @LastName AS VARCHAR(100)
SET @CustomerID = 10
SET @LastName = 'Garza'
EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer 
WHERE CustomerID = ? AND LastName = ?', @CustomerID, @LastName) AT [FARAWAYSERVER];
GO

--Script 6 : Execute a DDL statement on linked server
EXEC (
'USE TempDB
IF OBJECT_ID(''dbo.Table1'') IS NOT NULL
DROP TABLE dbo.Table1
CREATE TABLE dbo.Table1
(
Column1 INT
)' ) AT [FARAWAYSERVER];

--Script 7 : Once you are done with your testing, clean up created objects
EXEC (
'USE TempDB
IF OBJECT_ID(''dbo.Table1'') IS NOT NULL
DROP TABLE dbo.Table1'
) AT [FARAWAYSERVER];
EXEC sp_dropserver 'FARAWAYSERVER'

Example 2: CONNECT VARIABLE TO AN OPENQUERY TO A LINKED SERVER ON A JOIN

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