All Collections
Apps, Tools & Integrations
Using the SQL Connector to query Layer data in SQL Server
Using the SQL Connector to query Layer data in SQL Server

Generate useful reporting by joining your Layer data to other data sources with our SQL Server Connector

Nick Kewney avatar
Written by Nick Kewney
Updated over a week ago

The SQL Connector is a Layer extension for SQL Server which allows users with database experience query data available on our API with local data.

This allows customers to extend & customise their Layer reporting set, expose data to customers via their own applications, and much more.

Getting Started

In order to use the SQL Server Connector for Layer data, you'll need to be running SQL Server 2016 or newer. You can download the developer edition for free in order to start exploring your Layer data today.

Once you've got a SQL Server instance set up, download the SQL Connector here.

Next, right click SQLServerAPI.zip and extract to a directory, e.g. C:\temp\SQLTools

Run the install script below in SQL Server Management Studio and change all references to TempDB to a database on your server

/* =============================== */
/* Begin One-Time Install Script */
/* =============================== */

USE TestDB -- Replace this with your DB
GO
sp_configure 'clr enabled', 1 -- The common language runtime integration feature is off by default, and must be enabled in order to use this component
RECONFIGURE

GO

ALTER DATABASE TESTDB -- Replace this with your DB
SET TRUSTWORTHY ON
GO

DROP PROCEDURE IF EXISTS [Layer_SQLServerAPI_APIGet]
DROP ASSEMBLY IF EXISTS [Layer.SQLServerAPI]
DROP ASSEMBLY IF EXISTS [Newtonsoft.Json]
DROP ASSEMBLY IF EXISTS [System.Runtime.Serialization]
GO
CREATE ASSEMBLY [System.Runtime.Serialization] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.Serialization.dll' WITH PERMISSION_SET = UNSAFE
GO
CREATE ASSEMBLY [Newtonsoft.Json] AUTHORIZATION dbo FROM N'C:\temp\SQLTools\Newtonsoft.Json.dll' WITH PERMISSION_SET = UNSAFE

GO

CREATE ASSEMBLY [Layer.SQLServerAPI] AUTHORIZATION dbo FROM N'C:\temp\SQLTools\Layer.SQLServerAPI.dll' WITH PERMISSION_SET = UNSAFE
GO
CREATE PROCEDURE [dbo].[Layer_SQLServerAPI_APIGet] @URL NVARCHAR(MAX) NULL
,@Headers NVARCHAR(MAX) NULL

AS

EXTERNAL NAME [Layer.SQLServerAPI].[StoredProcedures].[APIGet]
GO
/* ==================== */ /* End Install Script */ /* ==================== */

If you extracted to a different directory to the one above, change references of c:\Temp\SQLTools to the directory you copied the contents of sqlserverapi.zip to

Run the install script below to register the scripts on your SQL Server instance

Grab your API tokens from The Layer API

Add these to the sample below and start writing SQL API queries within your own environment:

--------------------------------------------------------------
-- Author       Nick Kewney [[email protected]]
-- Created      06/07/2020
-- Purpose      Sample script for Layer SQL API Connector
-- Copyright © 2020, Layer Systems Ltd, All Rights Reserved

--------------------------------------------------------------
/* ========================= */ /* Begin API Query Example */ /* ========================= */
use TestDB
-- Grab the report URL from the custom report & modify the querystring parameters if required
DECLARE @apiUrl VARCHAR(max) = 'https://webapi.thelayer.com/api/CustomReport/Get?reportId=report_guid&DateFrom=01/06/2020&DateTo=03/07/2020&RawJsonResponse=true&TableIndex=0'
DECLARE @apiHeaders NVARCHAR(max) = '[
{
"Name": "Token1",
"Value" :"xxx"
},{
"Name": "Token2",
"Value" :"xxx"
}]';
DECLARE @response AS TABLE ([json] NVARCHAR(max))
INSERT INTO @response
EXEC [dbo].[Layer_SQLServerAPI_APIGet] @apiUrl, @apiHeaders
SELECT * FROM OPENJSON((SELECT [json] FROM @response)) WITH (
[Action] NVARCHAR(max) '$."Action"',
[Sales Order Reference] NVARCHAR(max) '$."Sales Order Reference"',
[Service Description] NVARCHAR(max) '$."Service Description"') a
/* ======================= */
/* End API Query Example */ /* ======================= */

Did this answer your question?