Monday, November 12, 2018

Load and run custom T-SQL scripts in SSMS via Snippets

If you work with SQL Server on a daily basis, it is very likely you have a lot of custom scripts you have to execute frequently, maybe you have stored them on a folder and you open them manually as you need them, or have saved them on a solution or project file, maybe you execute a custom .bat or PowerShell file to load them when you open SSMS...

Every method has its pros and cons, and on this post, I will show you a new method to load your custom scripts on any open query window on SSMS via Snippets.

What is a Snippet?


According to Microsoft Documentation:
Code snippets are small blocks of reusable code that can be inserted in a code file using a context menu command or a combination of hotkeys. They typically contain commonly-used code blocks such as try-finally or if-else blocks, but they can be used to insert entire classes or methods.

In short words is custom code (T-SQL Scripts for us) that you use a lot and you want to have always available to use.
In SSMS there are a lot of code snippets available by default, that you can use right away, let us see one of them in action, we will use a snippet to insert a create table template:


You can see how easy is to load the code in any existing window, even if this already has code on it, so now let us create one with our custom code.


Creating a custom Snippet


These files are stored in XML format with a .snippet extension, you can find the official documentation here.

The basic template is this (you can find the file on my GitHub):


<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
 <CodeSnippet Format="1.0.0">
 <Header>
 <Title>Your Snippet title</Title>
        <Shortcut></Shortcut>
 <Description>Your snippet description</Description>
 <Author>Author of the snippet</Author>
 <SnippetTypes>
  <SnippetType>Expansion</SnippetType>
 </SnippetTypes>
 </Header>
 <Snippet>
  <Declarations>
   <Literal>
    <ID>Variable1</ID>
    <ToolTip>Variable1 description</ToolTip>
    <Default>variable1 default value</Default>
   </Literal>
  </Declarations>
  <Code Language="SQL">
<![CDATA[
SELECT $Variable1$
]]>
  </Code>
 </Snippet>
 </CodeSnippet>
</CodeSnippets>

Explaining it:

  • You need to insert your custom code inside the <![CDATA[]]> label. 
  • If you have variables, you must enclose them in $ characters ($Variable1$ for our template, and then declaring the variable with the name you use inside the <Declarations> section.
  • Then declare the Script name, description, and author in the <Header> section.
  • Save it with the .snippet extension.
I have a simple script that I use a lot to see the elapsed and remaining time for any session on SQL server, so let us use it for this example, the query is the following (Note: I didn't make this script, but I was not able to find the author, so if you know him/her, please let me know so I can give the proper credit):


SELECT 
 r.session_id,
 r.command,
 CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],
 CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
 CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
 CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
 CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
 CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
 CASE WHEN r.statement_end_offset = -1 
 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))AS [SQL Text]
FROM sys.dm_exec_requests r WHERE command LIKE  '%%' --Text of the Session you want to monitor, like BACKUP or DBCC


For this script, our variable will be the text in the like statement, so after using the snippet template, our code should look like this:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
 <CodeSnippet Format="1.0.0">
 <Header>
 <Title>Running Queries ETA</Title>
        <Shortcut></Shortcut>
 <Description>Display estimated completed time for any given query</Description>
 <Author>SQLGuatemala.com</Author>
 <SnippetTypes>
  <SnippetType>Expansion</SnippetType>
 </SnippetTypes>
 </Header>
 <Snippet>
  <Declarations>
   <Literal>
    <ID>YourCommand</ID>
    <ToolTip>Command to look for</ToolTip>
    <Default>BACKUP</Default>
   </Literal>
  </Declarations>
  <Code Language="SQL">
   <![CDATA[
SELECT 
 r.session_id,
 r.command,
 CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],
 CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
 CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
 CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
 CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
 CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
 CASE WHEN r.statement_end_offset = -1 
 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))AS [SQL Text]
FROM sys.dm_exec_requests r WHERE command LIKE  '%$YourCommand$%'
]]>
  </Code>
 </Snippet>
 </CodeSnippet>
</CodeSnippets>


We save it with the .snippet extension to a folder in our computer, the next step is to load it to SSMS.


Loading the Snippet into SSMS


Open SSMS and go to Tools> Code Snippets Manager



The following window will open. If you want to load a complete snippet folder, click on Add, or if you want to load just one snippet, click on Import.



For this example we will load the entire folder where we store the snippet template and the one we created in the previous step, so we click on Add, and then locate the snippet folder:



We can see that the custom Snippets folder has been created and the 2 snippets are loaded.



After that, we can use the custom code snippets on any window we want.

Loading our Custom Snippet


Now you can load your custom code in any SSMS open query window, we will use it to know how much time is taking a DBCC command we are running on the other window:




For more information on T-SQL snippets, visit this link.

Thursday, November 8, 2018

Enable Machine Learning Services on SQL Server

R Services (SQL Server 2016) or Machine Learning Services (2017 and 2019 CTP) provide you with the ability to perform data analysis from the database itself using T-SQL.

You can learn a little more about what you can do in the SQL Server blog.

On this post, I will show you how to setup and configure it so you can start using it right away in your applications and reports.

To install it

You have to choose it from instance features on your SQL server setup window, you then choose the languages you want to work (R, Python)



Note: if your computer does not have access to the internet, you will have to download two packages separately and then locate them in the setup window.

Continue the setup as usual and finish it.

After you run the setup, please make sure that SQL Server LauchPad service is running for the instance you have installed the Machine Learning Services.




I recommend you to patch the instance with the latest update available in case you encounter any issue with the service startup.


To configure it


Just connect to the instance where the Machine Learning Services was added, using your favorite tool, and run the following T-SQL to enable the code execution:


EXEC sp_configure  'external scripts enabled', 1
RECONFIGURE WITH OVERRIDE

You must see the following output:



After that, you have to restart the SQL Server service for it to work.

our last step is just to test it...


Testing the script execution


To execute scripts you must use the sp_execute_external_script procedure, you can find the official documentation here.

This is the most basic command I can think you can execute to see if everything is ok, displaying the license information of the R language:


EXEC sp_execute_external_script  
@language =N'R',
@script= N'license()'

Note that the first execution after a service restart will take some time to complete, but after waiting you should see the license information in the Messages window:


And now... a simple example with real data


For this example, we will take Application.Countries table from WideWorldImporters test database and display it sorted by population in descendent order with an R script, running this T-SQL:



USE WideWorldImporters
GO


EXEC sp_execute_external_script  @language =N'R',
@script=N'
dsTest<- InputDataSet
OutputDataSet <- dsTest[order(-dsTest$LatestRecordedPopulation),]
',
@input_data_1 =N'
SELECT 
 CountryName,
 FormalName,
 LatestRecordedPopulation,
 Continent
FROM [Application].[Countries]
WHERE Region = ''Americas'''
WITH RESULT SETS 
 (
  ( 
  [CountryName] [nvarchar](60) NOT NULL,
  [FormalName] [nvarchar](60) NOT NULL,
  [LatestRecordedPopulation] [bigint] NULL,
  [Continent] [nvarchar](30) NOT NULL
  )
 );
GO


If you have the database installed and run it, you can see the output with the data sorted from the R script:



You have learned how to execute simple R scripts against SQL server tables from the database engine, so now you can make more complex examples and do your own data analysis scripts.

For more in-depth information visit this link.