ADS: Number on the Explorer Icon

When you open Azure Data Studio you will see a tool bar on the left side of the screen.  On this bar you will see a number of icons.  The image below is what you might see.  From time to time you might see a number on the lower right of the icon.

In the example, there are two icons that have number over them.  The bottom one is related to extensions and it is telling you there are updates to extensions you have installed.  In this case there are two.

The top number is the focus of this post.  The icon is for the Explorer. When you click on the icon you will see a screen pop up. At the top of this screen you will see “Open Editors”. Similar to below.

These are files that you have opened, made a change to and have not saved.  As you open files and make changes, this number will increment.  It will also increment if you open a new query window and make a change to it.

Although Azure Data Studio can persist your unsaved documents, it is still a best practice to save your work as you go.

Thanks for visiting my blog!!!

 

SYS.DM_EXEC_SESSIONS and System Processes

Being able to gather information about current sessions has for many years been an important part of a DBA’s responsibilities when troubleshooting a performance issue.  The type of information that we need includes the SPID, host, database name, login name and application being used.  While all this is important, at times system processes need to be identified or even excluded from the results.

Over the years, DBAs have developed their own technics for gathering information about current processes. These have included sp_who, sp_who2 and a query pulling data from the sys.processes table. SYS.DM_EXEC_SESSIONS is a DMV that came out more recently, SQL Server 2008.  I have read many articles and posts over the years that have said any SPID with an ID of 50 or less should be considered system related.  While this is true, there are some processes that will have an ID greater than 50 and also be a system process. SYS.DM_EXEC_SESSIONS allows us to easily identify system processes.

Sp_who and Sp_who2 are stored procedures I have used many times over the years. I usually use sp_who2 rather than sp_who.  Sp_who2 returns more information than sp_who. If you look at the image below, you will see two data sets.  The top one is sp_who and the bottom is sp_who2. These additional columns is why I prefer sp_who2.

Sys.sysprocesses is also a great resource   However is does not include a column that will allow us to determine if it is a system process or not.  Below are the columns in sys.sysprocesses.

SYS.DM_EXEC_SESSIONS is a view that was included starting with SQL Server 2008.  This view returns much, if not all the same information as the above. However, it also returns a great deal of additional information.  One of the columns returned is “is_user_process”.  This is a bit column that if set to 0 is a system process and if set to 1, is a user process.
SELECT     session_id
        , login_time 
        , is_user_process
        , login_name
        , host_name
FROM SYS.DM_EXEC_SESSIONS
If you run the above query, you will see a result set similar to what is below.  Is_user_process is the third column.  The rows in the red box are above session_id of 50 or more.  The items in the green box are user processes, as
identified in the is_user_process column.

This is a great view to use.  Some thing to keep in mind, it is a view and one of the tables it pulls from is sys.sysprocesses.  Here is a link to Microsoft’s documentation on this view.

Thanks for visiting my blog!!!

ADS: Simple Data Scripter

Moving data in a table from a production server to a lower environment such as a development server can be done a number of different ways.  These include backup\restore, using SSIS to move the data, import\export wizard and scripting the data in SQL Server Management Studio.  Azure Data Studio by itself doesn’t have a way to script the data in a table and it does not have the import\export wizard.  This is where the Table Data Scripter extension comes in handy.

Like most extensions for Azure Data Studio, this extension is also in a preview state.  As of the date of this post, it is currently at version 0.1.1 and is published by Sean Price. The documentation for this extension can be found here, Simple Data Scripter. In the documentation there are a few limitations identified

      • Not all data types are supported, specifically the binary datatypes like text, binary and varbinary
      • Has only been tested with SQL Server
      • Does not work on a view

Some extensions will add options under Preferences.  This extension does not add any options.  Given that, there is limited ability to configure this, which is OK, not really much of a need to do any configuration.

Just as a review, let’s take a look at how we can script the data in SQL Server Management Studio.  After right clicking on the database and going to Tasks…Generate Scripts…  When you do, a wizard will pop up and then after a few clicks on next, you will arrive at the screen below.  The box highlights the “Types of data to script” option.  With this there are three options, Data Only, Schema and Data and finally Schema Only.

When right clicking on a database in Azure Data Studio you will see limited options none of which are related to scripting data.  This is also true when right clicking on a table in the database.  Here are the options we see by right clicking on a table, as before, none of which are related to scripting data.

In order to script the data in Azure Data Studio we will need to install the Simple Data Scripter extension.  This is a pretty straight forward and simple install.  You will need to download the .vsix file.  You can get to the GitHub to download the file from the documentation for the extension in ADS.  You can install it under the File menu.  While you will not be prompted to restart ADS when you install it, I usually like to restart ADS anyway.  In this case, the extension doesn’t move to the installed extensions until after a restart.

Now that the extension is installed, you will see a new option when you right click on a table.  This is another potential limitation of the extension, there isn’t an option to script all the data in the database.  Well, at least I haven’t found it yet.  But then again, I am not sure having a simple place to script all the data in one place if the best way to go.

When clicked the following screen will appear at the top Azure Data Studio.  Initially when we look at it our first thoughts might be, what a bad query.  But it does make sense, we are creating a script of all the data in the table.

If you click anywhere, ADS will cancel the query and you will see this pop up in the lower right corner and the above screen will disappear.

If you press enter Azure Data Studio will execute the query and generate the script. You will not see a result set but you will see the script.  It will look similar to below.  Notice the Create Table statement at the top, it is commented out.  The table that is used for the example is named RentalCar.  The script is working with a table named #tempRentalCar.

When the script is finished, the Task window will open up at the bottom of the screen.  The top two are successful executions while the third failed, reason for the failure will be covered shortly.

You may be wondering if the query can be modified.  The answer is yes, however not every type of statement will work.  If you want to add a WHERE clause that works just fine.  However, as the query become more complex it failed more frequently.  I attempted to run a query with a join in it and it failed.  That is the failure in the image above.  The error stated “Query has no results to return”.  However, the query works just fine when running in ADS or SQL Server Management Studio.

Here are some of the statements I have tried.

      • Query with a join – failed
      • Query with a sub-query – failed
      • Query with a WHERE clause and just one table – succeeded

When compared to the expectations, this extension works very well and I can definitely see a use for this.

Thanks for visiting my blog!!

 

ADS: Notebook Text Formatting

Azure Data Studio has a great feature in the form of Notebooks.  A notebook is a document that allows for executable code such as TSQL or Python as well as notes for documentation.  Notebooks can be utilized to help organize things such as DR runbooks and troubleshooting documents.  What I like best about notebooks is that everything needed can be located in a single file, including data.

When working in a Notebook you have two types of cells, text and code.  The focus of this post is how to format the text cell.  Of course text goes into this cell so that part is easy and of course the text can say anything you would like to say.  When we work with text in Word, there is a format tool bar that we can use to make it look like we want it.  The text cells do not have this toolbar.

You might be asking, without the format toolbar, does that mean we can’t format the text?  That answer is no….we can still format the text, we just need to do it slightly different.  Rather than use a toolbar, we need to use characters.

While not all formatting options are available in Notebooks, there are a few commonly used options that are.  These are below as well as a few others we won’t cover in this post.

        • Font Size
        • Font Weight
        • Bullet lists
        • Italics

Here is an example of what your text might look like with formatting.  Notice that there are different size fonts, bold fonts, email address, URL and a small section highlight(blue vertical bar).  We will go over each of these in this post.

First of all, in order to get to the location that allows you to format the text, you simply need to find the text you want to format and double click on it.  When you do, the box to format the data will appear just above it.  Similar to below.

The chart below has an example of each of the font options. The sample code is in the parenthesis.

 

Font Changes in a Notebook

Formatting Applied and Sample code Example
No Formatting
One #

(# Font Changes)

Two #

(## Font Changes)

Three #

(### Font Changes)

Four #, adds bold

(#### Font Changes)

Five #, adds bold

(##### Font Changes)

Six # – Makes font tiny and bold

(###### Font Changes)

Seven # – no impact at all, prints the # signs

(####### Font Changes)

Italics

The above chart shows how to change the size and weight of the text.  Well, what if I want the text to be italics.  To do this we will use the *, well actually a pair of them. One will be placed at the start of the text you want to be italics and the other will be placed at the end.

Like this:   *Font Changes*

This is what it will look like in a Notebook

If you want a larger font you can combine characters.

Using both the # and *, we can get a larger font as well as italics.

This is what it would look like:  # *Font Changes*

The image above shows a much larger font and italics.

URL

If you want to include a URL to a web site you can easily do this with out the use of any special characters, such as a # or *.  When you do, the text to the URL appear just as you type it.  For example, if I want to include a link to my blog I could type, www.davebland.com.  When I do, that is exactly how it will appear in the text, www.davebland.com.

However, what if I want different text to appear, let’s say I want “Dave Bland’s blog” to appear in the text rather than www.davebland.com.  This is also pretty easy.  You will need to utilize the square brackets, [] and a pair of parenthesis, ().

The square brackets will hold the text I want to appear, while the URL will be placed between the ().

Like this:

[Dave Bland’s Blog](https://www.davebland.com)
Here is an example of both.

 

Bold

We already talked about how you can use the # to get a bold font.  But what if I want a word in the middle of the text to be bold and nothing around it.  This is where the * comes in.  We just put two * before and two after the text we want bold.

Like this:

# I what these words to be bold: **Font Changes**
This is what it will look like.  Notice the # at the start, remember this makes the font larger.

 

Block of Text

Azure Data Studio also offers the ability to block off sections of text, similar to the text in the green box  below.  Notice the blue vertical bar and the tinted background.

This is done by placing a > at the start of the line.

This is what it will look like:

You can add more > at the front if you like, this will create blocks in a block.

This is what it will look like:

>>### “Those responsible for sacking the people who have just been sacked, have been sacked.” – What movie is this from?

 

And this is the result:

Bullet Lists

The final format item I would like to cover it a bullet list.  Like all the other format options, this is completed by the use of a special character.  In this case it is the plus sign, +.

This is what it will look like:

+ First Bullet
+ Second Bullet
This is the result:

Put it all together

If you use a number of these in one text box, this is what it might look like.

And this is the result:

There you have it….formatting text in Azure Data Studio notebook without the use of a format toolbar.

Thanks for visiting my blog!!!