Perform the following steps to query using ZappySys Drivers inside SQL Server (T-SQL Query)
Search for ZappySys Data Gateway in Start menu (or go to Start > ZappySys > ODBC PowerPack > Data Gateway ).
Launch ZappySys Data Gateway Configuration Utility.
Go to Users Tab and create new user (Check Admin option if user can access any Data source you create in the next step).
Go to Data Sources Tab and click Add to create new Data Source.
Enter data source name and select Type (i.e. ODBC if you need to read data from DSN).
Click on Edit link under Settings column and select ODBC DSN.
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
Check Enabled option
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)
Open SSMS (SQL Server Management Studio) and connect to SQL Server
Navigate to Server Objects > Linked Servers. Right click and click [New Linked Server...]
Enter linked server name (e.g. ZENDESK_API). In the server type select other data source.
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
Select [SQL Native Client] from the provider dropdown.
Enter catalog name as (This is same name as Data source name from Data gateway Service Config UI).
Now click on Security Tab.
Select option which says [Be made using this security context].
Specify same userid and password which you configured for ZappySys Data Gateway.
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
--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
@server = N'ZENDESK_API' --Linked server name (this will be used in OPENQUERY sql
, @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
@rmtsrvname=N'ZENDESK_API' --linked server name
, @rmtuser=N'tdsuser' --enter your Gateway user name
, @rmtpassword='tds123' --enter your Gateway user's password
--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;
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 $')
--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'')')
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]