AzureDataStudio Blog posts contents page

AzureDataStudio Blog series

1) Azure Data Studio and SQL Saturdays
2) ADS Keyboard Short cuts – part 1
3) ADS Keyboard Shortcut and Name drop
4) ADS Server and Switching Profiles   – Setting up ability to switch in different dashboards, user profiles
5) Azure Data Studio – Database Status Dashboards – Steps to create a dashboard and clever trick to run a script across different SQL versions with different syntax
6) ADS Extensions – Brief intro to extensions
7) SQL Saturday Belgium – My 1st SQL European SQL Saturday experience
8) ADS Error switching in PowerShell– with switching to PowerShell Kernel in Notebooks and fix
9) Azure Data Studio Notebooks Markup– Annotating notebooks, headers, lists, and formatting, with a clever hack to get around text alignment issues
10)  Azure Data Studio – wow more time saving – Searching for servers or adding in new servers
11) Leeds and Manchester Data Platform Meetup AzureDataStudio – Trip over the Pennines and another great feature to compare clipboard and the active window
12) Azure Data Studio -The gift that keeps on giving – Even easier filtering for files
13) Less AzureDataStudio more Yorkshire– Trip to Haworth
14) Getting the git repository- AzureDataStudio. And an offer that I might regret this one! Maybe not  – link to the repository
15) This contents page.
16) Azure Studio formatting TSQL – easy formatting of SQL text
17) Azure Data Studio Snippets
18) Azure Data Studio my snippets – list of TSQL files and link to the snippets sql.json file
19) Azure Data Studio Notebook Charts – charting in Notebooks
20) Azure Data Studio – Working remotely, setting up ADS to run without installation
21) Azure Data Studio – Run book– How I utilised Notebooks for a SQL upgrade/migration
22) Azure Data Studio Data Scripter– Ability to script tables to insert statements and specify where clauses
23) Azure Data Studio– More short cut keys – duplicating lines, compare clipboard to the active window
24)
25)
26)
27)
28)
29)
30)
31)

Posted in Azure Data Studio, Azure Data Studio Extensions, Dataplatform, Docker, Markup, SQL Notebooks | Leave a comment

Getting the git repository- AzureDataStudio. And an offer that I might regret this one! Maybe not (14/31)

I have stored the GitHub Presentation in the following location

https://github.com/martincroft/SQLPresentations.git

So_how_difficult_can_it_be

I quite like presenting the same talk at multiple locations (turns out they have been cancelled since I drafted this), but we need to look after each other so that’s cool as well.

I’ve had a weekend off and been away, it’s crazy how difficult this is to blog daily and the guys who do it all the time are amazing,

I’ve still managed it but its not what I wanted it to be. I’ve learned a lot and hopefully shared a lot because that’s what it was really about

I’ve come up with a crazy idea as I sit in a hotel in Bronte country (and had a few drinks most of the afternoon) lets find someone to share anything I know. I’m not biging myself up, but I’d like to think I’ve got something to share and maybe someone is interested who knows 🤔

Posted in Azure Data Studio, Productivity | Leave a comment

Less AzureDataStudio more Yorkshire (13/31)

Its day 13 and Friday funny that, as probably explains what I’m about to get to shortly. I’m on a weekend break with the wife in the lovely North Yorkshire village of Haworth.

I’ve just checked in at the sleeping house at the bottom of the famous cobbled high street. Below is the view from the hotel.

Just switched the laptop on for todays blog to discover I have little charge and no charger. So any posts will need to be done from my phone.

Azuredata studio thoughts of the day, the previous two nights were presenting at two different usergroups and if was interesting to see how many people havent used AzureDataStudio but hopefully a few will now have a play.

Last night in Leeds and previous night in Manchester few interesting questions came out, one around can you get dashboards to auto refresh, which would make them quite a useful feature. So far tried to find a short cut key for refresh with out much luck and fudging the sql script called to have a loop and a wait for but to no avail,but it’s interesting the ideas people are coming up with to customise the ADS experience.

Also the hotel room toilet seems to be twinned with a toilet in Africa

,whatever next

Posted in Azure Data Studio, Yorkshire | Leave a comment

Azure Data Studio -The gift that keeps on giving (day 12/31)

I thought I had come up with a clever way of searching in Azure Data Studio to search for the filename of a SQL or PowerShell file using a PowerShell script that read the file in reversed the file, and added the file name to the end of the file and reserved it back so basically adding the name of the file to the top with a comment like:-

#My SQL Script.sql #SQL

I can now search on the file name then I discovered if type Ctrl+P and just type the name, I can find the file name with no clever Powershell shenanigans.

I love this app. I am still finding stuff out using day today.

Then a couple of days ago I discovered if you start typing in the pane with all your scripts it can filter on what you type, the example below I type CMD and find all files with CMD in the title. I knew it did this in the PowerShell section but hadn’t realised it did it here!

search

I had started to think blogging 31 times in a month would be a challenge and I imagine it will be, but I keep finding out new features.

This blog was finished off on the way to Leeds Dataplatform with @Ianpike both of doing techie stuff ( he’s finishing his presentation just saying)

Posted in Azure Data Studio, Tips & Tricks | Tagged , | Leave a comment

Leeds and Manchester Data Platform Meetup AzureDataStudio (Day 11/31)

The Manchester Data-platform User group is run by a bunch of great people and we rotate around for various meetups. It’s my turn this month which is bad planning as a Liverpool fan who’s champions league 2nd leg game is on (while I will be presenting or sorting out the remote 2nd speaker) and its also a different talk to one I’ve prepared for SQL Saturday.

I’m actually looking forward to this as I can concentrate on the really nice features and ignore the ones that mean I still go back to SSMS. Even though I need to cram it all into 30 minutes

Summary
More of a why than a what, I will show you why you should be using Azure Data Studio rather than SQL Server Management studio. This is a condensed session from a larger session with some great features that just can’t be found in SSMS. The positives on why you need to install it and start using it today. How it increased my productivity and it’s only going to get better. No experience is necessary but will give you some thoughts on how it can make your data experience all the better.

Azure Data Studio bit of the day

Another really useful and nifty short-cut key Ctl+K C to compare the open file with contents of the clipboard

So take the scenario you cut and paste some code into a window and change a few lines, bits of code, hit the combo Ctrl+K then C you get what’s different, I can see this being quite useful and even used it to compare two result sets today at work to see if they were the same from a complex string.

As it turns out I went to the pub to be disappointed by the result and ended up finishing the blog on the train, I need to get a few of these done and scheduled! but I did manage to get a beer from the user group to take home with me. #SQLCommunity

It was a great meet up with 3 different speakers @IanPike and @MarkDataGuy and myself and going home later than planned and we’re all doing it again tomorrow at @DataLeeds. 

Posted in Azure Data Studio, Beers, Dataplatform | Leave a comment

Azure Data Studio – wow more time saving

I worried I would not have content for 31 blog posts, however, I keep finding more new stuff daily.

I am working in an environment with a few hundred SQL servers and managing the servers list is a pain.  I tend to connect to the query window and its arduous, to then go add a new connection (even if its already there) to locate and connect to the manage pain.

So I spend a bit of time working through what several of the shortcut keys do by assigning F6 as that’s not mapped to anything. Finding a key combination that isn’t mapped can be an issue, for example, I used Shift+I for formatting SQL code until I wanted to upper case i (Doh!)

Anyhow map F6 to

Search: Servers

msssql.seachservers

This then brings a popup box with a search, type in the server name and bingo it brings up the server. I did find it the server wasn’t in the list nothing happened so if you map ctrl+F6 you can add a new server so covers both bases.

shortcut search

Posted in Azure Data Studio, Tips & Tricks | Leave a comment

Azure Data Studio Notebooks Markup (9/31)

Notebooks are great ways to add code and documentation to SQL scripts and PowerShell scripts to add clarity and increase productivity (if you add the syntax you don’t need to google it each time you need to change it slightly!.

Headings

Heading are started with # symbol as shown below

Headings Resulting in the following in the notebookheadings2

Bold and underlining and special section

You can make words bold, or italic or combination of both and add special section using the following syntax

bold

Which results in the following in a notebook

bold2

Emphasis

You can also highlight text

emphasise

Which results as following

emphasise2

Bullet Points

bullet points

Resulting in the following 3 levels of bullet points

bulletpoints

Formatting

One thing I did note, that even though I had tabbed or added spaced to the Markup, the actual format came as without the spaces or tabs and everything was aligned left

spaces1

This is not ideal as everything aligned left will start to look a mess.

This is where this website became quite useful, it has a load of different types of spaces,  you can click on a button copy the paste the one I used was the “em space” I then pasted into the notebook and add them in as space getting.

Much nicer.

spaces2

Lists

You can have ordered lists by adding a . after a number, these will allow 3 levels as shown below.

lists

If you notice below, notice item 55. shows as 4. once you start a list it doesn’t matter what numbers you have in the markup they will be sequential starting at the first value, if I had started with 11, next would have been 12 and so on.

lists2

 

Posted in Azure Data Studio, Markup, Tips & Tricks | Tagged , | Leave a comment

ADS Error switching Kernel in PowerShell Notebook (8/31)

When switching or attempting to switch Kernel, you get a prompt to install Python on clicking yes, it errors with the following message

Could not start session: Installing Notebook dependencies failed with error:
Process exited with with error code: 1. StdErr Output: WARNING: Retrying (Retry(total=4,
connect=None, read=None, redirect=None, status=None)) after connection broken by
‘SSLError(SSLError(1, ‘[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed
(_ssl.c:841)’),)’: /simple/jupyter/ WARNING: Retrying (Retry(total=3, connect=None,
read=None, redirect=None, status=None)) after connection broken by ‘SSLError(SSLError(1,
‘[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:841)’),)’
: /simple/jupyter/ WARNING: Retrying (Retry(total=2, connect=None, read=None,
redirect=None, status=None)) after connection broken by ‘SSLError(SSLError(1, ‘
[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:841)’),)’:
/simple/jupyter/ WARNING: Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None))
after connection broken by ‘SSLError(SSLError(1, ‘[SSL: CERTIFICATE_VERIFY_FA…

This can be down to proxy/vpn issue in a corporate environment

  1. https://go.microsoft.com/fwlink/?linkid=2109051
  2. Extract the zip file to C:\python-3.6.6-win-x64-0.0.1-offline\0.0.1
  3. open CMD and connect to C:\python-3.6.6-win-x64-0.0.1-offline\0.0.1
  4. run C:\python-3.6.6-win-x64-0.0.1-offline\0.0.1\installLocalPythonPackages.bat C:\python-3.6.6-win-x64-0.0.1-offline\0.0.1
  5. Open up user setting.json file located in C:\Users\Username\AppData\Roaming\azuredatastudio\User\settings.json or through setting
  6. Open up settings and find notebook.pythonPath and set to C:\python-3.6.6-win-x64-0.0.1-offline\
  7. Search for notebook.useExistingPython and check its set to false (unticked)
  8. Restart ADS

This was an issue I came across using in a corporate environment, the Github pages for AzureDataStudio have a wealth of useful information, this was one such item I came across the fix for, unfortunately, there are a lot of issues (and lots closed) so not always easy to find what you are looking for so re-blogged this.

This was located from https://github.com/microsoft/azuredatastudio/issues/8204

 

Posted in Uncategorized | Leave a comment

SQL Saturday Belgium 7/31

I thought today’s post should reflect SQL Saturday Belgium, my first time in Belgium. My first-time Presenting outside the UK.

I managed to find the venue fine, but I did nearly lose my phone along the way! Walked along Mechelen river, think its a river, they have a purpose-built path along the side which has decking, turns out very wet and slippy decking. Taking the following photo having over the side to get better shot I slipped nearly ending up with the phone in the water.

 

The talk I like to think was well received, how can you ultimately know, no one walked out and a few people said it was good. I also had a few people speak to me afterward asking more questions always a good sign. And a few things I can look further into (future posts) I made the mistake of not finishing this prior to the hospitality of the Belgium guys running the event, but the 31 days of March need to continue!

on a final note, Belgium beer is ridiculously strong.  I loved every minute of it and met some amazing SQL people #SQLfamily.

Posted in Azure Data Studio, SQLSaturday | Leave a comment

ADS Extensions (6/31)

Day 6 and I’ve arrived in Belgium for SQL Saturday tomorrow. I’m wondering the stupidly now of saying or doing 31 blog posts on ADS, a talk at SQL Saturday Belgium and SQL Saturday Iceland in March and the Organising and speaking at the Manchester DataPlatform User group next Wednesday and Leeds DataPlatform UG next Thursday. Anyway I digress, today’s post is an introduction to one of the cool ADS features:-

Extensions.

To quote Spiderman’s uncle “with great power comes great responsibility”, Azure Data Studio Extensions offer great power from the vanilla ADS and start making it a useful tool over SSMS or using with SSMS. There will be several blog posts covering extensions as there are a lot of them and they add much-needed productivity.

Extensions are VSIX files that can be either downloaded and installed by clicking on the ellipsis (three dots) and selecting Install from VSIX and some can be installed directly from within ADS.

01 Extensions instllation

The search extension box can show various elements using @ symbol to show the various options

02 Search extensions

The built-in themes you can’t amend (we’ll you can change the setting but there are easier ways) For example you can edit the Theme setting.

03 builtin themes

I’ll expand on Extensions in future posts

I’m in a hotel in Mechelen finishing this off , thinking I probably should get more prepared and also thinking why do Belgium’s make beer that’s 9%. Tomorrow is SQL Saturday Belgium ;).

Posted in Azure Data Studio, Uncategorized