T-SQL / Linked Server
PreviousNext

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 Data Sources Tab and click Add to create new Data Source.
  5. Enter data source name and select Type (i.e. ODBC if you need to read data from DSN).
  6. Click on Edit link under Settings column and select ODBC DSN.
  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

USE master;
GO
IF EXISTS ( SELECT * FROM sysservers WHERE srvname = 'ZENDESK_API' )
	 exec sp_dropserver 'ZENDESK_API', 'droplogins'; 

IF NOT EXISTS ( SELECT * FROM sysservers WHERE srvname = 'ZENDESK_API' )
BEGIN
 
 EXEC master.dbo.sp_addlinkedserver 
	@server = N'ZENDESK_API', 
	@srvproduct=N'', 
	@provider=N'SQLNCLI', --OR SQLNCLI1 for 2012
	@datasrc=N'localhost,5000', /*if gateway service is installed on a different machine then change localhost to IP or name of remote server */
	@catalog=N'yourdatasourcename'

 EXEC master.dbo.sp_addlinkedsrvlogin 
	@rmtsrvname=N'ZENDESK_API',
	@useself=N'False',
	@locallogin=NULL,
	@rmtuser=N'tdsuser',
	@rmtpassword='tds123'	
END


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 '') .

select * from OPENQUERY(YourLinkedServerName , 'SELECT * FROM value' )



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



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

Articles

Click here to see all articles for [T-SQL (SQL Server)] category
How to Make PayPal API Call in ODBC / SQL Server (T-SQL)

How to Make PayPal API Call in ODBC / SQL Server (T-SQL)

Introduction In our previous article we saw how to call PayPal API in SSIS. Now in this article, we will see How to Make PayPal API Call in ODBC from the SQL Server. This blog mainly focuses on ODBC approach but steps mentioned to call PayPal APIs can be useful for any developer regardless of which […]


Import SAP S/4HANA OData Service Data Into Sql Server via ODBC Driver

Import SAP S/4HANA OData Service Data Into Sql Server via ODBC Driver

Introduction In our previous blog we saw how to read JIRA data in SQL Server. Now let’s learn how to Import SAP S/4HANA OData Service Data Into Sql Server. SAP S/4HANA provides OData REST API interface to access data in your application using HTTP Protocol. We will use ODBC XML Driver to read SAP data and load […]


Import Bing Ads data into SQL Server (Performance Reports)

Import Bing Ads data into SQL Server (Performance Reports)

Introduction In our previous blog post we saw how to import REST / SOAP API in SQL Server. Using same concepts let’s look at how to import Bing Ads data into SQL Server. We will explore many techniques to call Bing Ads API and learn how to automate data extraction without doing any ETL. You […]


How to import Amazon MWS data into SQL Server (T-SQL)

How to import Amazon MWS data into SQL Server (T-SQL)

Introduction In our previous blog post we saw how to import rest API in SQL Server. Using same concepts lets look at how to import Amazon MWS Data into SQL Server. We will explore many techniques to call Amazon MWS API and learn how to automate Amazon MWS data extraction without doing any ETL. We […]


Import Google Analytics data into SQL Server / Reporting / ETL

Import Google Analytics data into SQL Server / Reporting / ETL

Introduction In our previous article we saw how to read Google Analytics data using SSIS. But what if you don’t use SSIS and you still like to Import Google Analytics data into SQL Server or Read inside other Reporting / ETL Apps (e.g. Excel, Power BI, MS Access … and many more). Not all BI […]


Read data from QuickBooks Online into SQL Server via ODBC Driver

Read data from QuickBooks Online into SQL Server via ODBC Driver

Introduction QuickBooks Online is a well-known Cloud-based Accounting Software. In this post, you will learn how to implement QuickBooks Online API Integration with SQL Server or any other RDBMS (e.g. Oracle, MySQL, Postgresql) using ODBC in few clicks. We will use ODBC JSON / REST API Driver to read data from QuickBooks Online and Load into SQL Server / other targets (Using OAuth […]


Insert, Update and Delete JIRA Issue in SQL Server

Insert, Update and Delete JIRA Issue in SQL Server

Introduction In our previous blog we saw how to read JIRA data in SQL Server. In this blog, we will learn how to Insert, Update and Delete Issue in JIRA using ZappySys JSON Driver. We will also read data from SQL Server data table in stored-procedure and insert JIRA issues in bulk, along with few other […]


Push data into a Power BI dataset from SQL Server

Push data into a Power BI dataset from SQL Server

Introduction In this article, you will learn how to push data into a Power BI dataset from SQL Server. Power BI is a Microsoft reporting product that can get data from virtually any source and display it nicely in a report or a dashboard. Among its dataset types, it has a streaming dataset into which […]


How to import REST API in SQL Server (Call JSON / XML SOAP Service)

How to import REST API in SQL Server (Call JSON / XML SOAP Service)

Introduction In this article, you will see few Examples to import REST API in SQL Server Table (Call JSON / XML SOAP API).  You will also learn how to load JSON Files into SQL Server Table using T-SQL Stored procedure code.  So let’s get ready for fun 🙂 JSON File format and REST API (i.e. RESTful […]



Copyrights reserved. ZappySys LLC.