sql-database-connect-query-vscode.md

title	description	metacanonical	keywords	services	documentationcenter	author	manager	editor	ms.assetid	ms.service	ms.custom	ms.workload	ms.tgt_pltfrm	ms.devlang	ms.topic	ms.date	ms.author
VS Code: Connect and query data in Azure SQL Database | Microsoft Docs	Learn how to connect to SQL Database on Azure by using Visual Studio Code. Then, run Transact-SQL (T-SQL) statements to query and edit data.		connect to sql database	sql-database		CarlRabeler	jhubbard		676bd799-a571-4bb8-848b-fb1720007866	sql-database	quick start manage	data-management	na	na	hero-article	04/17/2017	carlrab

Azure SQL Database: Use Visual Studio Code to connect and query data

Visual Studio Code is a graphical code editor for Linux, macOS, and Windows that supports extensions, including the mssql extension for querying Microsoft SQL Server, Azure SQL Database, and SQL Data Warehouse. This quick start demonstrates how to use Visual Studio Code to connect to an Azure SQL database, and then use Transact-SQL statements to query, insert, update, and delete data in the database.

This quick start uses as its starting point the resources created in one of these quick starts:

• Create DB - Portal
• Create DB - CLI

Before you start, make sure you have installed the newest version of Visual Studio Code and loaded the mssql extension. For installation guidance for the mssql extension, see Install VS Code and see mssql for Visual Studio Code.

Configure VS Code

Mac OS

For macOS, you need to install OpenSSL which is a prerequiste for DotNet Core that mssql extention uses. Open your terminal and enter the following commands to install brew and OpenSSL.

ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
brew update
brew install openssl
mkdir -p /usr/local/lib
ln -s /usr/local/opt/openssl/lib/libcrypto.1.0.0.dylib /usr/local/lib/
ln -s /usr/local/opt/openssl/lib/libssl.1.0.0.dylib /usr/local/lib/

Linux (Ubuntu)

No special configuration needed.

Windows

No special configuration needed.

Get connection information

Get the connection information needed to connect to the Azure SQL database. You will need the fully qualified server name, database name, and login information in the next procedures.

1. Log in to the Azure portal.

2. Select SQL Databases from the left-hand menu, and click your database on the SQL databases page.

3. On the Overview page for your database, review the fully qualified server name as shown in the following image. You can hover over the server name to bring up the Click to copy option.

   

4. If you have forgotten the login information for your Azure SQL Database server, navigate to the SQL Database server page to view the server admin name and, if necessary, reset the password.

Set language mode to SQL

Set the language mode is set to SQL in Visual Studio Code to enable mssql commands and T-SQL IntelliSense.

1. Open a new Visual Studio Code window.

2. Click Plain Text in the lower right-hand corner of the status bar.

3. In the Select language mode drop-down menu that opens, type SQL, and then press ENTER to set the language mode to SQL.

   

Connect to your database in the SQL Database logical server

Use Visual Studio Code to establish a connection to your Azure SQL Database server.

Important

Before continuing, make sure that you have your server, database, and login information ready. Once you begin entering the connection profile information, if you change your focus from Visual Studio Code, you have to restart creating the connection profile.

1. In VS Code, press CTRL+SHIFT+P (or F1) to open the Command Palette.

2. Type sqlcon and press ENTER.

3. Press ENTER to select Create Connection Profile. This creates a connection profile for your SQL Server instance.

4. Follow the prompts to specify the connection properties for the new connection profile. After specifying each value, press ENTER to continue.

   The following table describes the Connection Profile properties.

   Setting	Description
   Server name	Enter your fully qualified server name, such as mynewserver20170313.database.windows.net
   Database name	Enter your database name, such as mySampleDatabase
   Authentication	Select SQL Login
   User name	Enter your server admin account
   Password (SQL Login)	Enter the password for your server admin account
   Save Password?	Select Yes or No
   [Optional] Enter a name for this profile	Enter a connection profile name, such as mySampleDatabase.

5. Press the ESC key to close the info message that informs you that the profile is created and connected.

6. Verify your connection in the status bar.

   

Query data

Use the following code to query for the top 20 products by category using the SELECT Transact-SQL statement.

1. In the Editor window, enter the following query in the empty query window:

   SELECT pc.Name as CategoryName, p.name as ProductName
   FROM [SalesLT].[ProductCategory] pc
   JOIN [SalesLT].[Product] p
   ON pc.productcategoryid = p.productcategoryid;

2. Press CTRL+SHIFT+E to retrieve data from the Product and ProductCategory tables.

   

Insert data

Use the following code to insert a new product into the SalesLT.Product table using the INSERT Transact-SQL statement.

1. In the Editor window, delete the previous query and enter the following query:

   INSERT INTO [SalesLT].[Product]
           ( [Name]
           , [ProductNumber]
           , [Color]
           , [ProductCategoryID]
    	   , [StandardCost]
    	   , [ListPrice]
    	   , [SellStartDate]
    	   )
     VALUES
           ('myNewProduct'
           ,123456789
           ,'NewColor'
           ,1
    	   ,100
    	   ,100
    	   ,GETDATE() );

2. Press CTRL+SHIFT+E to insert a new row in the Product table.

Update data

Use the following code to update the new product that you previously added using the UPDATE Transact-SQL statement.

1. In the Editor window, delete the previous query and enter the following query:

UPDATE [SalesLT].[Product]
SET [ListPrice] = 125
WHERE Name = 'myNewProduct';

2. Press CTRL+SHIFT+E to update the specified row in the Product table.

Delete data

Use the following code to delete the new product that you previously added using the DELETE Transact-SQL statement.

1. In the Editor window, delete the previous query and enter the following query:

   DELETE FROM [SalesLT].[Product]
   WHERE Name = 'myNewProduct';

2. Press CTRL+SHIFT+E to delete the specified row in the Product table.

Next steps

• To connect and query using SQL Server Management Studio, see Connect and query with SSMS
• To connect and query using .NET, see Connect and query with .NET.
• To connect and query using PHP, see Connect and query with PHP.
• To connect and query using Node.js, see Connect and query with Node.js.
• To connect and query using Java, see Connect and query with Java.
• To connect and query using Python, see Connect and query with Python.
• To connect and query using Ruby, see Connect and query with Ruby.