Some time in the past I wrote a weblog submit about Azure SQL Knowledge Warehouse and Energy BI which I defined find out how to set up a brand new occasion of Azure SQL Knowledge Warehouse and find out how to visualise your Azure SQL DW information in Energy BI. On this submit I clarify find out how to question an Azure SQL DW in SSMS and Visible Studio.
Prior the most recent launch of SQL Server Administration Studio (SSMS) 2016, the one obtainable device for querying an Azure SQL Knowledge Warehouse was SQL Server Knowledge Instruments (SSDT) for Visible Studio 2013 or 2015. Right here is how you need to use SSDT 2015 to question an Azure SQL Knowledge Warehouse:
Enter “Server Title”
Should you don’t recall server title then open an online browser and log into Azure portal
Click on “SQL databases”
Click on any desired Azure SQL Knowledge Warehouse you created earlier than. Ensure that the database is “On-line”
Hover over the server title then copy it by clicking on “Copy” icon
Change to Visible Studio and paste the server title
Change “Authentication” kind to “Sql Server Authentication”
Enter “Person Title” and “Password”
Choose the Azure SQL Knowledge Warehouse then click on “Join”
You efficiently related to the “Azure SQL Knowledge Warehouse”
Broaden the Server from “SQL Server Object Explorer” pane
Broaden “Databases” then increase the database
Broaden “Tables”
Now you can see your entire information warehouse tables
- Write a T-SQL question and click on on “Execute” button (
) or press “Ctrl + Shift + E”
- You too can proper click on on an object from Object Explorer and script the item.
Word: Drag and drop from SQL Object Explorer to question editor is NOT supported in Visible Studio.
I wager you hate the “Ctrl + Shift + E” key mixture as a lot as I do. To vary the above key mixture:
Click on “Instruments” menu
- Click on “Choices”
Broaden “Setting” then click on “Keyboard”
On the “Present instructions containing” kind “execute”
Choose “SQL.TSqlEditorExecuteQuery” from the listing
Click on within the “Press shortcut keys” field then press “F5”
Click on “Assign”
You possibly can see the present command which might be at the moment utilizing “F5” as shortcut
- Should you’re pleased to set “F5” as SQL question execution shortcut click on OK
In earlier model of SSMS (2008, 2012, 2014, 2016 CTP3) you might connect with an occasion of Azure SQL Knowledge Warehouse, you might additionally execute T-SQL queries after you related to the database, however, it wasn’t totally practical. As an illustration, everytime you open a brand new question editor you get the next message in SSMS 2008, 2012 and 2014:
“Unable to use connection settings. The detailed error message is: ‘QueryGovernorCostLimit’ just isn’t a acknowledged possibility.”
and also you get the next error in SSMS 2016 CTP3:
“’HAS_PERMS_BY_NAME’ just isn’t a acknowledged built-in operate title. (Microsoft SQL Server, Error: 104162)”
One other instance of points utilizing prior variations of SSMS with Azure SQL Knowledge Warehouse is that they had been unable to indicate the database tables in “Object Explorer” pane which implies you couldn’t proper click on and script the tables.
The excellent news is that with the brand new launch of SQL Server Administration Studio 2016 (at the moment 13.0.15600.2) we will simply connect with an Azure SQL Knowledge Warehouse, modify the objects, execute queries and see the tables in Object Explorer. Mainly, we will do rather a lot in Azure SQL DW with SSMS as we used to do with any regular SQL databases.
Open SSMS 2016
Click on “Join” from Object Explorer
Choose “Database Engine” for “Server kind”
Enter Server title
Choose “SQL Server Authentication” for “Authentication”
Enter Login and Password then click on OK
From Object Explorer, increase databases, increase an current information warehouse, then increase “Tables”
Word: Examine this out to be taught extra about Azure Knowledge Warehouse distribution strategies.
- Once we open a brand new question editor we get the next warning:
- Click on an possibility of your selection then write your SQL script and execute it usually
And, after all, you may drag and drop objects from the Object Explorer to the question editor.
You too can proper click on on an object from Object Explorer and script the item.
