Skip to content

Azure SQL Execute Query

Jump to bottom
Adrien Siffermann edited this page Aug 3, 2016 · 10 revisions

With this task, you can execute a SQL query on an Azure SQL Database, using an Azure Resource Manager Service Endpoint. The query will be executed with SQLCMD, and allow uses of variables.

Refer to this link on how to add an Azure Resource Manager Service Endpoint to your VSTS project.

Add the task to your Build or Release Definition

Add-ExecuteSql

You will find the task in the "Deploy" category.

Configuration

Configure-ExecuteSql

General

  • Azure RM Subscription - Specifies the Azure Resource Manager Service Endpoint to use.
  • Type - Type of the SQL script to execute on target: File Path or Inline Script.
  • Script Path - Path of the script to execute. Should be fully qualified path or relative to the default working directory.
  • Inline Script - Path of the script to execute. Should be fully qualified path or relative to the default working directory.
  • Arguments - Arguments passed to the SQLCMD script with the -v option. Should be formatted like Var1="Value2" Var2="Value2". It can then be used with the SQLCMD $(Var1) syntax in the script.

Target

  • Azure SQL Server Name - Azure SQL Server name like, FabrikamSQL.database.windows.net,1433 or FabrikamSQL.database.windows.net.
  • Database Name - Name of the Azure SQL Database.
  • Login - The Azure SQL Server login, with the permissions required for your script.
  • Password - Password for the Azure SQL Server login provided. It can accept variable defined in Build / Release Definitions as $(passwordVariable). You may mark variable type as "secret" to secure it.

Firewall

  • Specify Firewall Rules Using - For the script to execute, the IP Address of the automation agent has to be added to the 'Allowed IP Addresses' in the Azure SQL Server's Firewall. Provide the IP Address range of the automation agents, or select auto-detect (it will find the external IP of your agent).
  • Start IP Address - If you choose 'Allowed IP Addresses', the starting IP Address of the automation agent machine pool like 196.21.30.50.
  • End IP Address - If you choose 'Allowed IP Addresses', the ending IP Address of the automation agent machine pool like 196.21.30.65.
  • Delete Rule After Task Ends - If selected, then after the task ends, the IP Addresses specified here are deleted from the 'Allowed IP Addresses' list of the Azure SQL Server's Firewall.
Clone this wiki locally