Remotely run DDL and DML in Synapse from a SQL Managed Instance using Linked Servers

by | Mar 1, 2021 | Technology | 0 comments

This article is contributed. See the original author and article here.

Imagine you have a SQL Managed Instance and a SQL Dedicated Pool. Now imagine that for some reason you need to run a SELECT, INSERT, UPDATE or even a CREATE TABLE on your SQL Dedicated Pool but executed from your SQL Managed Instance. In case you didn’t know, you can do this using Linked Servers.


 


For those unfamiliar with Linked Servers, they enable the instance in which you create them to read data from remote data sources and also to execute commands against remote database servers.


 


You can setup Linked Servers using SSMS or you can use T-SQL as well. Here’s a short example of how to setup a Linked Server from a SQL Managed Instance to Azure Synapse Analytics SQL Dedicated Pool, using T-SQL.


 


First, we need to start by creating the Linked Server on our SQL Managed Instance, and configuring it to point to our SQL Dedicated Pool:


 


 

-- Configure the linked server on your SQL Managed Instance
EXEC sp_addlinkedserver
  @server = 'yourLinkedServer', -- specify here the name you want for your linked server
  @srvproduct = N'',
  @Provider = 'MSOLEDBSQL', -- recommended OLE DB provider
  @datasrc='yourworkspacename.sql.azuresynapse.net', -- add here your SQL Dedicated Pool server name
  @catalog = 'yourDatabase'; -- add here your SQL Dedicated Pool database name as initial catalog (you cannot connect to the master database)

 


After having created the Linked Server, we need to define the credentials with which we want to connect to your SQL Dedicated Pool:


 

-- Add credentials and options to this linked server
EXEC sp_addlinkedsrvlogin
  @rmtsrvname = 'yourLinkedServer',
  @useself = 'false', -- so we can specify below which user and password to connect with
  @rmtuser = 'yourLogin', -- add here your login on your SQL Dedicated Pool
  @rmtpassword = 'yourPassword'; -- add here your password on your SQL Dedicated Pool

 


Lastly, by default Linked Servers are not able to make stored procedure calls so we need to enable a setting:


 

-- Enable RPC to the given server
EXEC sp_serveroption
  @server = 'yourLinkedServer',
  @optname = 'rpc out', 
  @optvalue = true;

 


Once you’ve successfully executed all the previous steps, you can now remotely run T-SQL commands on your SQL Dedicated Pool directly from your SQL Managed Instance.


 


Executing T-SQL via Linked Servers requires we declare the objects we are referencing with a 4-part name in the format of:


 

-- Query the data using 4-part names 
SELECT * FROM yourLinkedServer.yourDatabase.yourSchemaname.yourTablename;

 


However, if you try to run DDL or DML (other than SELECT) using this 4-part name you’ll get error messages as follows:


 

INSERT INTO yourLinkedServer.SampleSQL.dbo.Employee VALUES(1);

 


Msg 46706, Level 16, State 1, Line 29
Cursor support is not an implemented feature for SQL Server Parallel DataWarehousing TDS endpoint.


 

CREATE TABLE yourLinkedServer.SampleSQL.dbo.t1(col1 INT NULL);

 


Msg 117, Level 15, State 1, Line 31
The object name ‘yourLinkedServer.SampleSQL.dbo.t1’ contains more than the maximum number of prefixes. The maximum is 2.


A way to get around this is by running the DDL and DML in the format of:


 

EXEC ('DDL/DML') at yourLinkedServer

 


So for two previous examples, the T-SQL would be:


 

--to create a table
EXEC ('CREATE TABLE dbo.t1(col1 INT NULL)') at yourLinkedServer;

 


 

--to insert a row into the table
EXEC ('INSERT INTO dbo.t1 VALUES(1)') at yourLinkedServer;

 


 


Hope you find this useful!

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment