Some time in the past I wrote a weblog publish about Azure SQL Information Warehouse and Energy BI which I defined set up a brand new occasion of Azure SQL Information Warehouse and visualise your Azure SQL DW information in Energy BI. On this publish I clarify question an Azure SQL DW in SSMS and Visible Studio.
Prior the most recent launch of SQL Server Administration Studio (SSMS) 2016, the one accessible device for querying an Azure SQL Information Warehouse was SQL Server Information Instruments (SSDT) for Visible Studio 2013 or 2015. Right here is how you should use SSDT 2015 to question an Azure SQL Information Warehouse:
-
Enter “Server Title”
-
In case 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 Information Warehouse you created earlier than. Be sure 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” sort to “Sql Server Authentication”
-
Enter “Consumer Title” and “Password”
-
Choose the Azure SQL Information Warehouse then click on “Join”
-
You efficiently related to the “Azure SQL Information Warehouse”
-
Broaden the Server from “SQL Server Object Explorer” pane
-
Broaden “Databases” then increase the database
-
Broaden “Tables”
-
Now you can see all your information warehouse tables
-
Write a T-SQL question and click on on “Execute” button (
) or press “Ctrl + Shift + E”
- You may as well proper click on on an object from Object Explorer and script the article.
Word: Drag and drop from SQL Object Explorer to question editor is NOT supported in Visible Studio.
I guess you hate the “Ctrl + Shift + E” key mixture as a lot as I do. To alter the above key mixture:
-
Click on “Instruments” menu
-
Click on “Choices”
-
Broaden “Setting” then click on “Keyboard”
-
On the “Present instructions containing” sort “execute”
-
Choose “SQL.TSqlEditorExecuteQuery” from the checklist
-
Click on within the “Press shortcut keys” field then press “F5”
-
Click on “Assign”
-
You may see the present command which are at present utilizing “F5” as shortcut
-
In case you’re comfortable to set “F5” as SQL question execution shortcut click on OK
In earlier model of SSMS (2008, 2012, 2014, 2016 CTP3) you could possibly connect with an occasion of Azure SQL Information Warehouse, you could possibly additionally execute T-SQL queries after you related to the database, however, it wasn’t absolutely practical. As an example, 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 choice.”
and also you get the next error in SSMS 2016 CTP3:
“’HAS_PERMS_BY_NAME’ just isn’t a acknowledged built-in perform title. (Microsoft SQL Server, Error: 104162)”
One other instance of points utilizing prior variations of SSMS with Azure SQL Information Warehouse is that they have been unable to indicate the database tables in “Object Explorer” pane which suggests 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 present 13.0.15600.2) we are able to simply connect with an Azure SQL Information Warehouse, modify the objects, execute queries and see the tables in Object Explorer. Principally, we are able to do so much 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 sort”
-
Enter Server title
-
Choose “SQL Server Authentication” for “Authentication”
-
Enter Login and Password then click on OK
-
From Object Explorer, increase databases, increase an present information warehouse, then increase “Tables”
Word: Examine this out to study extra about Azure Information Warehouse distribution strategies.
- Once we open a brand new question editor we get the next warning:
- Click on an choice of your alternative 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 may as well proper click on on an object from Object Explorer and script the article.
Associated
Supply hyperlink
