View the earlier version.Version 7.10


BizTalk360 provides a platform to manage SQL queries within the application. There are a set of pre-loaded queries that come as an out-of-the-box functionality. In addition, BizTalk360 allows users (with the adequate permissions) to manage the existing SQL queries and add/edit/delete their own queries.


Quick Links


Managing The Pre-Loaded Queries

To edit a pre-loaded query, follow the steps as shown below in exact order. For example, let us consider the query "All Disabled Receive Locations [BT360DOC01\BT360DOC01: BizTalkMgmtDb]". This query will fetch the list of disabled receive locations from the BizTalkMgmtDb. Lets try to modify the query to retrieve the list of all receive locations from the BizTalkMgmtDb.

  1. Log in to the BizTalk360 application
  2. Click 'Operations' tab in the Navigation panel
  3. Click the expand button against 'Data Access' tab and select 'Secure SQL Queries' link
  4. In the Secure SQL Queries screen: 
    • Select the pre-stored query "All Disabled Receive Locations [

      BT360DOC01\BT360DOC01

      : BizTalkMgmtDb]" from the drop down
    • The query will execute and the results will be displayed in the Query Results pane
  5. Once the query results are generated, click the >Build Query Expression. This will open the Query Details pane
  6. Modify the query (in this case remove the last 2 lines; WHERE RL.[Disabled] = -1) as per the requirement
  7. Click Execute Query to generate the new query results for the modified query information
If you enter a wrong query while modifying the information, BizTalk360 will throw an exception error message while executing the query.


Click the sliders to view the process of managing the pre-loaded queries.


secure sql queries home select sql queries in biztalk360 monitor sql queries in biztalk360 edit sql queries in biztalk360 sql results table in biztalk360


Adding A New Secure SQL Query

BizTalk360 allows users to build secure SQL queries that are more appropriate to the organization's business in addition to the pre-loaded queries that are made available out-of-the-box.

Note: You can only create SELECT queries in BizTalk360. Any INSERT or UPDATE queries will be rejected.


Let's take an example of adding a new query that will fetch the Spool Size of the BizTalkMgmtDb. This will involve a SELECT statement that will retrieve the value of 'SpoolSize' from the message box database. Follow the steps as shown below to add a new query:

  1. Log in to the BizTalk360 application
  2.  Click 'Operations' tab in the Navigation panel
  3. Click the expand button against 'Data Access' tab and select 'Secure SQL Queries' link
  4. In the Secure SQL Queries screen, click the New Query icon. This will pop open the Secure SQL Query Details page
  5. In the Secure SQL Query Details page:
    • Friendly Name - Enter a friendly name for the Secure SQL query that can be easily understood by users Ex. Calculate Spool Size
    • SQL Instance - Enter the name of the SQL instance (In this case, BT360DOC01)
    • Database - Enter the name of the message box database (In this case, BizTalkMgmtDb)
    • Enter the SQL Query - Type the query into the space provided
  6. Click Save to save the query
If you enter a wrong query while adding a new query, BizTalk360 will throw an error message while saving the query.
BizTalk360 allows the users to open up to 4 tabs at a time to execute multiple queries. To open a new tab, click the + button. To close the tabs individually, click the * button next to the tab name. To close all the tabs in one click, click the X button next to the + button.


Click the sliders to view the process of adding a new secure SQL query.


sql query in biztalk360 new sql query in biztalk360 enter sql query in biztalk360 save new sql queries in biztalk360 saving new sql queries in biztalk360


Editing A Secure SQL Query

BizTalk360 allows users to make modifications to the pre-loaded queries and custom queries added by the users. Users can modify the query information according to their requirements. Follow the steps as shown below to edit a query:

  1. Log in to the BizTalk360 application
  2. Click 'Operations' tab in the Navigation panel
  3. Click the expand button against 'Data Access' tab and select 'Secure SQL Queries' link
  4. In the Secure SQL Queries screen, click the Edit Query icon. This will pop open the Secure SQL Query Details page
  5. In the Secure SQL Query Details page, you can modify the value for the following fields as per the requirement:
    • Friendly Name - Enter a friendly name for the Secure SQL query that can be easily understood by users Ex. Calculate Spool Size
    • SQL Instance - Enter the name of the SQL instance (In this case, BT360DOC01)
    • Database - Enter the name of the message box database (In this case, BizTalkMgmtDb)
    • Enter the SQL Query - Type the query into the space provided
  6. Click Save to save the query


Click the sliders to view the process of editing a secure SQL query.


sql queries home edit sql queries in biztalk360 sql query edit in biztalk360 edit sql qery


Deleting A Secure SQL query

To delete a query, follow the steps as shown below:

  1. Log in to the BizTalk360 application
  2.  Click 'Operations' tab in the Navigation panel
  3. Click the expand button against 'Data Access' tab and select 'Secure SQL Queries' link
  4. In the Secure SQL Queries screen, click the Delete Query icon. This will pop open the "Confirm Delete" message box with the message as "Are you sure, you want to delete this query? This query may be used by other users as well". Click Yes to delete the secure SQL query
  5. The query will no longer be available in the drop down
You cannot delete the pre-loaded queries from the drop down list. Only secure SQL queries entered by users can be deleted. 


Click the sliders to view the process of deleting a query (user created one).


sql query home in biztalk360 delete sql query in biztalk360 deleting sql qery deleted sql query in biztalk360 delete secure sql query in biztalk360