Azure DevOps Connector - Using Stored Procs

T-SQL is a programming language used for managing and querying data in Microsoft SQL Server databases. We provides tools for data integration, automation, and connectivity.

If you want to use T-SQL with ZappySys, you can use our Connector, which allows you to connect to SQL Server databases and execute T-SQL queries from Azure DevOps.

Create Data Source in ZappySys Data Gateway based on ZappySys API Driver

Download and install ZappySys ODBC PowerPack.
Search for gateway in start menu and Open ZappySys Data Gateway:
Go to Users Tab to add our first Gateway user. Click Add; we will give it a name tdsuser and enter password you like to give. Check Admin option and click OK to save. We will use these details later when we create linked server:
Now we are ready to add a data source. Click Add, give data source a name (Copy this name somewhere, we will need it later) and then select Native - ZappySys API Driver. Finally, click OK. And it will create the Data Set for it and open the ZS driver UI.

AzureDevOpsDSN
When the Configuration window appears give your data source a name if you haven't done that already, then select "Azure DevOps" from the list of Popular Connectors. If "Azure DevOps" is not present in the list, then click "Search Online" and download it. Then set the path to the location where you downloaded it. Finally, click Continue >> to proceed with configuring the DSN:

AzureDevOpsDSN

Azure DevOps

Now it's time to configure the Connection Manager. Select Authentication Type, e.g. Token Authentication. Then select API Base URL (in most cases, the default one is the right one). More info is available in the Authentication section.

OAuth [OAuth]

Steps to get Azure DevOps Credentials
Connecting to your Azure DevOps data requires you to authenticate your REST API access. Follow the instructions below:

Go to https://app.vsaex.visualstudio.com/app/register to register your app.
Fill in your application and company's information as required, and then select the scopes that your application needs. This should typically be Project and team (read and write) and Work items (read and write).

Your selected scopes when registering your app must match the scopes you enter here on the connector screen. If they don't match, the connector will not be able to work with your Azure DevOps account!

If you need further information about the scopes used in Azure DevOps, or need to see what to enter into the connector screen to match up with your selected scopes, visit https://learn.microsoft.com/en-us/azure/devops/integrate/get-started/authentication/oauth?view=azure-devops.
Select Create Application and then the Application Settings page will be displayed.
Record the application settings that are displayed for us to use later, especially the App ID and the Client Secret.
Visit https://aex.dev.azure.com. Once you are logged in, record your organization name in the URL that is displayed on the page. For example, if your organization is called "acmeinc", the URL on the page would be acmeinc.visualstudio.com.
Back at the connector screen, enter the App ID that was recorded in step 4 into the Client Id (App ID) field.
Enter the client secret that was recorded in step 4 into the Client Secret field. In order to edit the text in this field, select the ellipses (...) button that appears when the textbox is clicked and edit the client secret with the dialog box that appears.
Enter the organization that was recorded in step 5 into the Organization name or Id for url field.
Click Generate Token. If proper authentication occurs, you will see a notice saying so. You can click Yes to save a backup file of your generated tokens.
Select the project you want to connect to by default from the Default Project (Choose after Generating Token) field.
Select the Security tab.
Enter https://auditservice.dev.azure.com,https://almsearch.dev.azure.com into the Additional Trusted Domains field.
Select the Test Connection button at the bottom of the window to verify proper connectivity with your Azure DevOps account.
If the connection test succeeds, select OK.

Fill in all required parameters and set optional parameters if needed:

AzureDevOpsDSN

Azure DevOps

OAuth [OAuth]

https://dev.azure.com

Required Parameters
Client Id (App ID)	Fill in the parameter...
Client Secret	Fill in the parameter...
Organization name or Id for url (e.g. mycompany)	Fill in the parameter...
Return URL	Fill in the parameter...
Scope	Fill in the parameter...
API Version	Fill in the parameter...
Optional Parameters
Default Project Name (Choose after Generating Token)	Fill in the parameter...
RetryMode	Fill in the parameter...
RetryStatusCodeList	Fill in the parameter...
RetryCountMax	Fill in the parameter...
RetryMultiplyWaitTime	Fill in the parameter...

Personal Access Token (PAT) [Http]

Steps to get Azure DevOps Credentials
To connect to Azure DevOps using a Personal Access Token (PAT), you must first create a valid PAT:

Go to your Azure DevOps home page: https://dev.azure.com/{your organization name}.
On the home page, select the User Settings menu dropdown (looks like a person icon with a small gear over it), and then select Personal access tokens.
Select the New Token button.
Enter a name for the new personal access token.
Select the appropriate organization for the new personal access token.
Select the expiration date for the new personal access token.
Select the scopes for this token. (You may be restricted from creating full-scoped PATs. If so, your Azure DevOps administrator in Azure AD has enabled a policy which limits you to a specific custom defined set of scopes.)
Select the Create button.
The new Personal Access Token (PAT) is created and displayed. Copy and save this token, since it is as sensitive as a password.
Visit https://aex.dev.azure.com. Once you are logged in, record your organization name in the URL that is displayed on the page. For example, if your organization is called "acmeinc", the URL on the page would be acmeinc.visualstudio.com.
Back at the connector screen, enter the token you recorded from step 9 into the Personal Access Token (PAT) field.
Enter the organization that was recorded in step 10 into the Organization name or Id for url field.
Enter the name or Id of the project you want to connect to by default in the Default Project (Choose after above fields) field.
Select the Security tab.
Enter https://auditservice.dev.azure.com,https://almsearch.dev.azure.com,https://analytics.dev.azure.com into the Additional Trusted Domains field.
Select the Test Connection button at the bottom of the window to verify proper connectivity with your Azure DevOps account.
If the connection test succeeds, select OK.

Fill in all required parameters and set optional parameters if needed:

AzureDevOpsDSN

Azure DevOps

Personal Access Token (PAT) [Http]

https://dev.azure.com

Required Parameters
Personal Access Token (PAT)	Fill in the parameter...
Organization name or Id for url	Fill in the parameter...
API Version	Fill in the parameter...
Optional Parameters
AuthScheme	Fill in the parameter...
AuthHeader	Fill in the parameter...
UserName	Fill in the parameter...
Default Project Name (Choose after Generating Token)	Fill in the parameter...
RetryMode	Fill in the parameter...
RetryStatusCodeList	Fill in the parameter...
RetryCountMax	Fill in the parameter...
RetryMultiplyWaitTime	Fill in the parameter...

Once the data source has been configured, you can preview data. Select the Preview tab and use settings similar to the following to preview data:
Click OK to finish creating the data source.

Read data in SQL Server from the ZappySys Data Gateway

To read the data in SQL Server the first thing you have to do is create a Linked Server. Go to SQL Server Management Studio and configure it in a similar way:
Then click on Security option and configure username we created in ZappySys Data Gateway in one of the previous steps:
Optional: Under the Server Options, Enable RPC and RPC Out and Disable Promotion of Distributed Transactions(MSDTC).

You need to enable RPC Out if you plan to use EXEC(...) AT [MY_LINKED_SERVER_NAME] rather than OPENQUERY.
If don't enabled it, you will encounter the Server 'MY_LINKED_SERVER_NAME' is not configured for RPC error.

Query Example:
```
EXEC('Select * from Products') AT [MY_LINKED_SERVER_NAME]
```
If you plan to use 'INSERT INTO...EXEC(....) AT [MY_LINKED_SERVER_NAME]' in that case you need to Disable Promotion of Distributed Transactions(MSDTC).
If don't disabled it, you will encounter the The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "MY_LINKED_SERVER_NAME" was unable to begin a distributed transaction. error.

Query Example:
```
Insert Into dbo.Products
 EXEC('Select * from Products') AT [MY_LINKED_SERVER_NAME]
```
Finally, open a new query and execute a query we saved in one of the previous steps:
```
SELECT * FROM OPENQUERY([MY_LINKED_SERVER_NAME], 'SELECT * FROM Products');
```

Create Linked Server using Code

In previous section you saw how to create a Linked Server from UI. You can do similar action by code too (see below). Run below script after changing necessary parameters. Assuming your Data Source name on ZappySys Data Gateway UI is 'AzureDevOpsDSN'


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

    //Replace YOUR_GATEWAY_USER, YOUR_GATEWAY_PASSWORD
    //Replace localhost with IP/Machine name if ZappySys Gateway Running on different machine other than SQL Server
    //Replace Port 5000 if you configured gateway on a different port


    --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 if you dont want to buy Paid version https://www.microsoft.com/en-us/sql-server/sql-server-editions-express

    --Uncomment below if you like to drop linked server if it already exists
    --EXEC master.dbo.sp_dropserver @server=N'LS_AzureDevOpsDSN', @droplogins='droplogins'

    --3. Create new linked server
    
    EXEC master.dbo.sp_addlinkedserver
      @server = N'LS_AzureDevOpsDSN'  --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'AzureDevOpsDSN' --Data source name you gave on Gateway service settings

    --4. Attach gateway login with linked server

    EXEC master.dbo.sp_addlinkedsrvlogin
      @rmtsrvname=N'LS_AzureDevOpsDSN'  --linked server name
    , @useself=N'False'
    , @locallogin=NULL
    , @rmtuser=N'YOUR_GATEWAY_USER' --enter your Gateway user name
    , @rmtpassword='YOUR_GATEWAY_PASSWORD'  --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 'LS_AzureDevOpsDSN', 'rpc', true;
    EXEC sp_serveroption 'LS_AzureDevOpsDSN', 'rpc out', true;

    --Disable MSDTC - Below needed to support INSERT INTO from EXEC AT statement
    EXEC sp_serveroption 'LS_AzureDevOpsDSN', '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 'LS_AzureDevOpsDSN', 'query timeout', 1200;
    GO

Create Custom Store Procedure in ZappySys Driver

You can create procedures to encapsulate custom logic and then only pass handful parameters rather than long SQL to execute your API call.

Steps to create Custom Store Procedure in ZappySys Driver. You can insert Placeholders anywhere inside Procedure Body. Read more about placeholders here

Go to Custom Objects Tab and Click on Add button and Select Add Procedure:
Enter the desired Procedure name and click on OK:
Select the created Store Procedure and write the your desired store procedure and Save it and it will create the custom store procedure in the ZappySys Driver:
Here is an example stored procedure for ZappySys Driver. You can insert Placeholders anywhere inside Procedure Body. Read more about placeholders here
```
CREATE PROCEDURE [usp_get_orders]
    @fromdate = '<<yyyy-MM-dd,FUN_TODAY>>'
 AS
    SELECT * FROM Orders where OrderDate >= '<@fromdate>';
```
That's it now go to Preview Tab and Execute your Store Procedure using Exec Command. In this example it will extract the orders from the date 1996-01-01:
```
Exec usp_get_orders '1996-01-01';
```
Let's generate the SQL Server Query Code to make the API call using store procedure. Go to Code Generator Tab, select language as SQL Server and click on Generate button the generate the code.
As we already created the linked server for this Data Source, in that you just need to copy the Select Query and need to use the linked server name which we have apply on the place of [MY_API_SERVICE] placeholder.
```
SELECT * FROM OPENQUERY([MY_API_SERVICE], 'EXEC usp_get_orders @fromdate=''1996-07-30''')
```
Now go to SQL served and execute that query and it will make the API call using store procedure and provide you the response.