PLATFORMS SOLUTIONS BLOGS CONTACT

/administration/linkedserver




LINKED SERVER


Enzo Server emulates all the necessary protocols to support Linked Server connections from a SQL Server database. This allows developers to communicate with APIs and remote resources directly from within a database and from SQL Agent Jobs.

Configure Linked Server


To create a new Linked Server from an existing SQL Server instance, expand Server Objects->Linked Servers, right-click and choose New Linked Server.



In the New Linked Server screen, enter the server name and port where Enzo Server is listening on. In this example Enzo Server is running on the same server than SQL Server and is listening on port 9550; as a result the server name for Enzo is LOCALHOST,9550.

Because Enzo Server emulates the SQL Server protocol, select SQL Server for the server type.



Under the security window, select the last option Be made with the following security context and enter an existing Enzo Server login/password.

You can specify any login name that is defined as an Enzo Login; however it is important to note that the default configuration settings will be loaded when the connection is made. Please refer to the Configuration Settings section for more information.



Under the server options window, make sure to set the values as shown in this screenshot.

The RPC settings are required, and Distributed Transactions must be disabled.




Execute Commands Through a Linked Server Connection


Assuming you have created a linked server to Enzo called localhost,9550 and you are connected to a SQL Server database, you can run the following command:


SELECT * FROM [localhost,9550].bsc.sharepoint.usStatesTest

By leveraging Linked Server you can also call Enzo adapters from Stored Procedures, Views, Triggers, Functions and even from SQL Server Agent jobs.


This example shows you how to create a stored procedure that gets data from two SharePoint Online lists in real-time, saves the output into in-memory tables, and returns a single data set that joins both tables.

To improve performance, the queries could be executed against cached data instead.

Enzo Server does not support JOIN operations in the current release. As a result you must bring data into a SQL Server environment to join the records locally.


CREATE PROCEDURE GetMyList
AS

DECLARE @data as table (ID int, Title nvarchar(255), LastName nvarchar(255), State nvarchar(255))
DECLARE @states as table (ID int,Title nvarchar(255), StateCode nvarchar(255))

-- Get test user records from SharePoint Online
INSERT INTO @data EXEC [localhost,9550].bsc.SharePoint.GetListItemsEx 
	'Enzo Test', 'ID,Title,Last Name,State' 
-- Get states records from SharePoint Online
INSERT INTO @states EXEC [localhost,9550].bsc.SharePoint.GetListItemsEx 
	'US States', 'ID,Title,State Code'

SELECT D.Title, D.LastName, S.Title as State 
FROM @data D LEFT JOIN @states S ON D.State = S.Id

GO







601 21st St Suite 300
Vero Beach, FL 32960
United States

(561) 921-8669
info@enzounified.com
terms of service
privacy policy

PLATFORM

ENZO SERVER
ENZO DATAZEN

SOLUTIONS

SOLUTIONS OVERVIEW
INTEGRATION
SaaS
CLOUD ANALYTICS

RESOURCES

DOWNLOAD
BLOGS & VIDEOS
IN THE NEWS
ENZO ADAPTERS
ONLINE DOCUMENTATION
TCO CALCULATOR

COMPANY

LEADERSHIP TEAM
PARTNERS


© 2023 - Enzo Unified