Help > Programming Languages > T-SQL / Linked Server

Before you can call ZappySys Drivers inside Microsoft SQL Server via T-SQL Code you need to perform following steps

Step-By-Step

For detailed steps check this article

Video Tutorial

Configure ODBC DSN

Click here for steps to create ODBC DSN.

Configure ZappySys Data Gateway Service

Perform the following steps to query using ZappySys Drivers inside SQL Server (T-SQL Query)

1. Search for ZappySys Data Gateway in Start menu (or go to Start > ZappySys > ODBC PowerPack > Data Gateway ).
2. Launch ZappySys Data Gateway Configuration Utility.
3. Go to Users Tab and create new user (Check Admin option if user can access any Data source you create in the next step).


4. Go to the Data Sources Tab and click Add to create new Data Source.
5. Enter a name for the data source and select the Type. (Tip: Choose Native DSN over ODBC DSN whenever possible if you need to read data from a DSN.)
6. Click on the Edit link under the Settings column, then select your newly added DSN to modify the configurations.
   
7. Click on Edit link under Users column and select Users and Permission you like to assign for this data source. For admin user no need to perform this step
8. Check Enabled option
9. Click Save and Restart the service to apply settings.

   See below screen to make sure you select 64-bit System ODBC DSN only. If you created 32-Bit DSN then it may not show up in the DSN drop-down.
   
   
   
   

Create Linked Server Using in SSMS (for OPENQUERY approach in T-SQL)

1. Open SSMS (SQL Server Management Studio) and connect to SQL Server
2. Navigate to Server Objects > Linked Servers. Right click and click [New Linked Server...]
   
3. Enter linked server name (e.g. ZENDESK_API). In the server type select other data source.
4. Enter Data source. This is server name and port for ZappySys Data Gateway Service. For example enter MY_GATEWAY_SERVER1,5000 if you are connecting to Data Gateway Endpoint which is exposed on port 5000. Data Gateway can expose many endpoints for
5. Select [SQL Native Client] from the provider dropdown.
6. Enter catalog name as (This is same name as Data source name from Data gateway Service Config UI).
   
7. Now click on Security Tab.
8. Select option which says [Be made using this security context].
9. Specify same userid and password which you configured for ZappySys Data Gateway.
   
10. Click OK to save Linked Server.

Create Linked Server using code

You can also create linked server by calling system stored procedures like below. Or to generate full script right click on existing Linked Server and click Script Linked Server As > Create To > New Window

    --///////////////////////////////////////////////////////////////////////////////////////
    --Run below code in SSMS to create Linked Server and use ZappySys Drivers in SQL Server
    --///////////////////////////////////////////////////////////////////////////////////////
    USE [master]
    GO

    --1. Configure your gateway service as per this article https://zappysys.com/links?id=10036
    --2. Make sure you have SQL Server Installed. You can download FREE SQL Server Express Edition from here https://www.microsoft.com/en-us/sql-server/sql-server-editions-express

    --EXEC master.dbo.sp_dropserver @server=N'ZENDESK_API', @droplogins='droplogins'

    --3. Create new linked server
    EXEC master.dbo.sp_addlinkedserver
    @server = N'ZENDESK_API'  --Linked server name (this will be used in OPENQUERY sql
    , @srvproduct=N''
    , @provider=N'SQLNCLI11'
    , @datasrc=N'localhost,5000' --//Machine / Port where Gateway service is running
    , @provstr=N'Network Library=DBMSSOCN;'
    , @catalog=N'XMLDriver' --Data source name you gave on Gateway service settings

    --4. Attach gateway login with linked server
    EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname=N'ZENDESK_API'  --linked server name
    , @useself=N'False'
    , @locallogin=NULL
    , @rmtuser=N'tdsuser' --enter your Gateway user name
    , @rmtpassword='tds123'  --enter your Gateway user's password
    GO

    --5. Enable RPC OUT (This is Optional - Only needed if you plan to use EXEC(...) AT YourLinkedServerName rather than OPENQUERY
    EXEC sp_serveroption 'ZENDESK_API', 'rpc', true;
    EXEC sp_serveroption 'ZENDESK_API', 'rpc out', true;
    --Disable MSDTC - Below needed to support INSERT INTO from EXEC AT statement
    EXEC sp_serveroption 'ZENDESK_API', 'remote proc transaction promotion', false;
    --Increase query timeout if query is going to take longer than 10 mins (Default timeout is 600 seconds)
    --EXEC sp_serveroption 'ZENDESK_API', 'query timeout', 1200;
    GO

        

Using linked server using T-SQL

Now its time to use Linked server to execute some queries against your API service. See below examples. Note that single tick (i.e. ' ) inside OPENQUERY SQL must be escaped using double tick (i.e '') .

    --6. Run some sample query (use OPENQUERY) - Advantages >> FROM clause allowed so can join with other tables, SELECT INTO allowed so you can create new table and insert in one step. 
    --Disadvantages = Variables not allowed in SQL so you have to use ugly Dynamic SQL approach to use variables.
    SELECT * FROM OPENQUERY([YourLinkedServerName],'select * from $')


    --OR--
    --6. Dynamic query approach (use EXEC AT) - Advantages >> More than 8000 chars allowed in SQL sent to driver. Also you can use dynamic SQL (build variable)
    --DECLARE @sql nvarchar(max)
    --SET @sql='select col1, col2 from MyTable' --//add dynamic stuff here -- Comment INSERT clause just to execute without loading into table
    --INSERT INTO (col1, col2)
    --EXEC(@sql) AT [ZENDESK_API]

    --You can create table structure using below technique (Use TOP 0 with SELECT INTO)
    --SELECT top 0 col1, col2 into some_table FROM OPENQUERY([ZENDESK_API],'select col1, col2 from $') --this is the quickest way to create new table to match query 
    --(we do not recommend select * for all columns rather than use column names so order is retained. (i.e. select col1, col2). 
    --To generate column list you can Edit Data source on Gateway UI > Go to Preview Tab > Select Table from drop down to auto generate query with column names



    SELECT @cnt=count(*) FROM OPENQUERY([YourLinkedServerName]
    ,'SELECT * FROM ticket WITH (SRC=''https://zappysys.zendesk.com/api/v2/tickets.json'')')



    --dumping result into temp table
    SELECT * INTO #tmp FROM OPENQUERY([YourLinkedServerName]
    , 'SELECT * FROM ticket WITH (SRC=''https://zappysys.zendesk.com/api/v2/tickets.json'')')

Articles

---

Copyrights reserved. ZappySys LLC.