SSMS Version 17.4 no more SQLPS Module

It was just a matter of time, as it was already mention in previous SSMS (SQL Server Management Studio) documentation that SQLPS module was going to be deprecated and replace with the new SQLServer module.

See SSMS download information at:

After SSMS Version 17.4 was release back in December, SQLPS module is no longer available. So, if you try to use the “Start PowerShell” from any of the database object, you’ll get the message “No SQL Server cmdlets found…” popup message.

New SQLServer PowerShell Module

But, no worries! Both the SSMS link and the popup message tell you where to get the new *SQLServer PowerShell module as is a separate installation from the PowerShell Gallery.

PowerShell Gallery SQLServer PowerShell Module, Get here:

One thing to point out, this module is only meant to be use on Windows PowerShell.

In other words, it will not work in PSCore6.

Housekeeping Needed

Now, there’s the chance previous SSMS installations have left the older SQLPS PowerShell Module in the system.

As is shown in the previous image, the variable $env:PSModulePath contains the path to the existing SQLPS module(s).

Either, remove the path manually using PowerShell, or thru the GUI System “Environment Variable“.

Or better yet, if you’re using SAPIEN Technologies, Inc. “PowerShell Studioproduct, the n use the Cache Editor feature to manage your existing PowerShell Modules. Check out the blog post and video about this feature at:

Video featuring PowerShell Studio Cache Editor

Option for PSCore

The only way to use PSCore6 to work with SQLServer cross-platform, is using the SMO (SQLServer Management Objects) for .NETCore, which is available in NuGet. For more information in how to install it, check my blog post at:

The only downside, you need to create the script from scratch. There’s plenty of documentation about SMO to overcome this hurdle. Most important, you are  sharpen your PowerShell scripting skills.

Don’t forget that before install any artifacts from PowerShell Gallery, NuGet, or Chocolatey the console shell need to be open “as an Administrator“.

Be Bold!! Learn PowerShell Core!!

PSCore6 – Nuget Microsoft.SqlServer.SqlManagementObjects latest Package (v140.17218.0) Broken

This is the SMO (SqlServer Management Objects) package use to create PSCore6 scripts to connect and manage SQL Server on Windows, Linux, and Azure.

But today, I found out the latest version “140.17218.0″ is broken. I had to rolled back to use an older version “140.17199.0” to get it to work again.

You can find the information about this package in this link:

This NuGet SMO package version is built on .NETCore 2.0 for PSCore6, and will not install in Windows PowerShell.

Installing SMO Package

To *install the previous SMO package version “140.17199.0“, use the following command:

Install-Package Microsoft.SqlServer.SqlManagementObjects -RequiredVersion '140.17199.0'

*Note: Need to install as an Administrator.

If  the newer SMO version “140.17218.0” is installed then it will not connect. There are no errors, or failures displayed.  (See image)

This issue has been reported to NuGet SMO owners and hopefully will be resolved soon.

Testing SMO in PSCore6

Here’s the PSCore6 script for SMO testing. The script will work in both Windows and Linux.

## - Help find and save the location of the SMO dll's in a PowerShell variable:
$smopath = `
Join-Path ((Get-Package Microsoft.SqlServer.SqlManagementObjects).Source `
| Split-Path) (Join-Path lib netcoreapp2.0)

# Add types to load SMO Assemblies only:
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.Smo.dll)
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.ConnectionInfo.dll)

## - Prepare connection and credential strings for SQL Server:
## - (Connection to Windows SQL Server multi-instance sample)
$SQLServerInstanceName = 'System01,1451'; $SQLUserName = 'sa'; $sqlPwd = '$Mypwd01!';

## - Turn ON below for Linux:
## - (Connection to Linux SQL Server multi-instance sample)
# $SQLServerInstanceName = 'LinuxSystem02'; $SQLUserName = 'sa'; $sqlPwd = '$Mypwd01!';

## - Prepare connection passing credentials to SQL Server:
$SQLSrvConn = New-Object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($SQLServerInstanceName, $SQLUserName, $SqlPwd);
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn);

## - SMO Get SQL Server Info:
$SQLSrvObj.Information `
| Select-Object parent, platform, product, productlevel, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;

## - End of Code

Most Important

In order for this to work, NuGet needs to be installed first. The following *code block will help to check if it’s already installed. And, if not, then it will install NuGet in PSCore6.

# Register NuGet package source, if needed
# The package source may not be available on some systems (e.g. Linux)
if (-not (Get-PackageSource | Where-Object{$_.Name -eq 'Nuget'}))
Register-PackageSource -Name Nuget -ProviderName NuGet -Location
Write-Host "NuGet Already Exist! No Need to install."

*Note: Thanks to the SMO guys for providing this code block to get me started testing.

Also, if you already installed the buggy NuGet SMO version, remember to use the following command to uninstall the package:

uninstall-package Microsoft.SqlServer.SqlManagementObjects

I’m hoping this blog post will help in any way.

Be Bold!! Learn PowerShell Core!!

Streamlining SQL Server Management Objects (SMO) in PowerShell Core

I’ve been recently posting about getting SQL Server Management Objects (SMO) Framework to work in PowerShell Core in both Windows and Linux Systems. So, here’s the revised blog post as the method has kept improving. This way you can start creating some cross-platform SMO PowerShell Core scripts in your environments.

It will works the following way:
1. Windows connecting to Windows SQL Server.
2. Windows connecting to Linux SQL Server.
3. Linux connecting to Linux SQL Server.
4. *Linux connecting to Windows SQL Server.

*Note: Any issues with firewall connecting from Linux to Windows, can be solved by creating the inbound rule for Linux in Windows Firewall.

How to get the SMO for PowerShell Core?

It’s easy! You can get it from NuGet Gallery using PowerShell Core Console. Just make sure you open PowerShell Core as an Administrator to avoid any installation issues.

You could use the following one-liners to find and install the recent SMO package. The following “if-else” code snippet can execute in either Windows or Linux PowerShell Core console.

# Register NuGet package source, if needed
# The package source may not be available on some systems (e.g. Linux)
if (-not (Get-PackageSource | Where-Object{$_.Name -eq 'Nuget'}))
Register-PackageSource -Name Nuget -ProviderName NuGet -Location
Write-Host "NuGet Already Exist! No Need to install."

Installing SMO from NuGet Gallery

After we verified NuGet Package Management is already installed in our system, then we can proceed in Find/Install “SQL Server Management Objects (SMO) Framework“. The current version is “140.17199.0”.

Execute the following one-liner by using the Find-Package to make sure is available. Then, do the install-package command

## - Check that the NuGet feed is available and has the SMO package:
Find-Package -Name Microsoft.SqlServer.SqlManagementObjects

## - Install latest SMO package from NuGet:
Install-Package -Name Microsoft.sqlserver.SqlManagementObjects -Scope CurrentUser

## - Next Line Confirmed Installation:
Get-Package Microsoft.SqlServer.SqlManagementObjects

As of today (November 6th, 2017), the current version of Microsoft.SqlServer.SqlManagementObjects is 140.17199.0. And, it can be installed on either Windows and Linux systems from NuGet.

For more NuGet information about the SMO package, click on the following link:

Locating SMO Assemblies and connect to SQL Server

In order to use SMO in PowerShell, we need to know where they are installed. The next one-liner gets the NuGet location to build the path of the SMO installed assemblies.

## - Help find and save the location of the SMO dll's in a PowerShell variable:
$smopath = Join-Path ((Get-Package Microsoft.SqlServer.SqlManagementObjects).Source `
| Split-Path) (Join-Path lib netcoreapp2.0)

The SMO path is saved. We are ready to load the needed SMO assemblies, to connect and work with SQL Server. The code snippet below will load the SMO assemblies, connect to SQL Server providing necessary credentials:

# Add types to load SMO Assemblies only:
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.Smo.dll)
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.ConnectionInfo.dll)

## - Prepare connection and credential strings for SQL Server:
## - (Connection to Linux SQL Server single instance sample)
$SQLServerInstanceName = 'lxSql00'; $SQLUserName = 'sa'; $sqlPwd = '$Pswrd1!';

## - (Connection to Windows SQL Server multi-instance sample)
$SQLServerInstanceName = 'winSql01,1450'; $SQLUserName = 'sa'; $sqlPwd = '$Pswrd1!';

## - Prepare connection passing credentials to SQL Server:
$SQLSrvConn = New-Object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($SQLServerInstanceName, $SQLUserName, $SqlPwd);
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn);

In the previous code sample, I included some variance in providing the SQl Server instance for cross-platform use:
1. In Linux, either using the “SqlServername” or, “IP-Address“.
2. In Windows, either using the “SqlServerName“, or “IP-Address“, or in the case of mutliple instance “SqlServerName,Port“.

Now that the SMO connection to the SQL Server has been established, then we can continue to explore our options using .NET SMO Framework. The Code snippet below shows how to display some of the SQL Server SMO information .NET properties:

## - SMO Get SQL Server Info:

$SQLSrvObj.Information `
| Select-Object parent, platform, product, productlevel, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;

## - End of Code

More SMO Options…

There are additioanl sources providing SMO dll’s:
1.NuGet SMO:
2.GitHub SQLToolService:
3.Installing SQL Server mssql-scripter(Python-based):

I’ve been using GitHub “SqlToolsService” for some time now and it works closs-platform. At the same time, I’ve been keeping it up-to-date:


I dare to say! Using .NET SQL Server Management Objects (SMO) Framework, let you be flexible adding control over your scripting. Keep in mind, this is well documented in Microsoft MSDN site:

My number one choice is to use NuGet Package Management. Although, you can play around with the GitHub SqlToolsService version as it gets frequent updates. The trick in using the GitHub version, is to add the path to where the Dll’s are stored and you’re good to go.

Just Dare to Experiment! Keep learning PowerShell!

Special Thanks to Microsoft: Matteo Taveggia and  David Shiflet for providing me with Nuget PowerShell code piece. I just change it a little!

FLPSUG – Next Online meeting July 26th 2017

I’m working on getting a meeting with Keiser University to allow me to host my Florida PowerShell User Group Monthly meetings at their Port St. Lucie Campus location.  But, in the meantime, I setup July’s Online meeting for Wednesday 26th at 6:30pm (EST).

This month topic:

Working with SQL Server for Linux Cross-Platform

You’re welcome to explore the latest build of SQL Server for Linux, including everything you need to install and connect to SQL Server. He will also look into the tools that are available to use from Linux and / or Windows. Maximo will provide samples on querying for database information using Python/Java and PowerShell between two environments. This will be a demo intensive session you will not want to miss!

To register, click on the following Eventbrite link:

I hope you can joined me in this exciting session!

South Florida SQLSaturday – Working with SQL Server in Linux session

First! Thanks to the organizers, and specially the attendees as they waited patiently to the attend my last session of the day “Working with SQL Server for Linux Cross-Platform”. It proves to be good. Love their interaction and the will to embrace technology.

As, I almost didn’t make it to the event, due to car problem, I one my coworker gave me ride to the event at Nova Southeastern University. I missed giving the early session “SQL Server working with PowerShell and Python” so I ended up merging both sessions into one.

For my surprise, the last session went better than I expected. I ran everything from Azure Cloud which work like a charm, and the attendee were awesome.

Both presentation and all demo scripts were uploaded to the SQLSaturday #627 event site. I hope you all take advantage of the resource link I provided.

In the demo it’s interesting we covered the following on PowerShell Core, Python 3.6 (Anaconda), and SQL Server 2017 (Linux):

* In Window 10, using SSMS v17.1 connecting to SQL Server 2017 in Linux
* In Linux, connect to a Windows Shared folders
* In Windows, using SSMS to restore a Windows Database into Linux SQL Server.
* Sample script using Python tk (Gui) w/pyodbc (SQL connector), and PowerShell displaying PowerShell object in a Gridview.
* Using SMO in Linux with PowerShell.

And, we did covered a lot in a short time.

By the way, I will be giving the same session at IDERA’s Geek Synch webinar, on July 12th, at 11:00am CT/12:00pm ET:

Geek Sync – I Working with SQL Server for Linux Cross-Platform

Once again Thanks to everyone!

I’ll see you all in Orlando SQLSaturday #678 on October 7th!

Using Linux SQL Server SMO in PowerShell Core

Yes! It’s possible. Here’s the information in how to set it up and start doing some PowerShell scripting. But, first understand that everything posted here is still a Work-In-Progress. And, the good news, it’s all Open Source.

I hope you find the following information essential as there’s no really any instruction in how to install these components. So, let’s get started!

Where To Get It!

The Microsoft SQL Tools Service is a set of API that provided SQL Server Data Management capabilities on all system cross-platforms. It provide a small set for SMO dll’s enough to get started.

You can download the file from following Github link: 

Here’s the list of available SMO DLL’s currently include in the “SqlToolsService – ServiceLayer” file:


Keep in mind, this list will continue to grow and we hopefully expect more SMO DLL’s added.

Installation pre-requisites

In my case, I got various systems setup: Windows and Ubuntu 16.04. So, I make sure I download correct *zip or *tar.gz file

As, pre-requisite, you will needed to have already installed *”.NET Core 2.0 Preview 1” for the SQL Service Tools to work and remember this need to be installed in all systems.

Just in case, here’s the link to download “.NET Core 2.0 Preview 1“:

Now, because we are working with PowerShell Core, don’t forget to install the latest build found at:

Windows Installation

You need to get the file from the latest release. At the time I writing this blog, it’s Pre-release “v1.0.0-alpha.34 – .Net Core 2.0 build“.

To make *”Sql Tools Services” to work in PowerShell Core, I had to extract all content in the file into the “C:\Program Files\PowerShell\6.0.0-Beta.x” folder. Remember, this will replace any existing DLL’s on that folder.

*Caution: This steps should be done on a test machine as there’s always a possibility that it could PowerShell Core DLL’s.

Don’t forget that all these components are still in development but this should stopped us from trying and even contributing.

The file you’ll need to download for Windows is:

Please, for now ignore the *microsoft.sqltools.credentials*.  If you install the Credentials DLL’s in the PowerShell Beta folder, PowerShell will not work.

Linux Installation

Now, for Linux is a different story as there’s no need to add the DLL’s in the PowerShell Core folder. You need to get the file from the latest release. At the time I writing this blog, it’s Pre-release “v1.0.0-alpha.34 – .Net Core 2.0 build“.

I would recommend doing the following steps in the Bash Console:

1. At your /home/user-name location, create the sqltoolsservice folder:

maxt@MyUbuntu01:~$ mkdir sqltoolsservice

2. Change directory and Download the file for Ubuntu:

maxt@MyUbuntu01:~$ cd sqltoolsservice/
maxt@MyUbuntu01:~/sqltoolsservice$ wget

3. Continue extract the *tar.gz into the folder:

maxt@MyUbuntu01:~$ tar -xzvf microsoft.sqltools.credentials-ubuntu16-x64-netcoreapp2.0.tar.gz

That’s it for Linux. Now, you are ready to work with SMO and PowerShell.

Testing SMO in PowerShell Core

This is changing my way I script SMO in PowerShell. As my normal way I’ve been scripting SMO in PowerShell doesn’t work in PowerShell Core. Basically, a few more lines need to be added and now I will use the Add-Type to get the SMO assemblies loaded.

Loading SMO Assemblies

The first step is to load the SMO assemblies needed to start working with SQL Server. So, the following line is finally depricated and won’t work:


The old method I’ve been using for a long time will failed because is expecting the “Property Login …” to be set.

The updated way, has been replaced by the Add-Type with the following essential three assemblies:

## - Loadind SQL Server SMO assemblied needed:
$Assem = (
); Add-Type -AssemblyName $Assem;

The above assemblies are required in order to work since SQL Server SMO 2012 and greater. You can have limited use when connecting to SQL Servers version 2005, and possibly 2000.

Prepare connection parameters for Windows Systems

In Windows systems, we use ‘Integrated Authentication‘. But, here’s where things change a bit since SQL Server 2012 SMO. You will need to prepare the connection parameters, and set the *.UseIntegratedSecurity property to ‘true‘ (the default is ‘false‘). At the same time, you’ll need to set the password to ‘null’ in order to connect successfull.

## - Prepare connection strings and connect to a Windows SQL Server:
$SQLServerInstanceName = 'sqlsvrinst01,1439';
$SQLUserName = 'winUsername';
$SQLSrvConn = new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($SQLServerInstanceName, $SQLUserName, $null);
$SQLSrvConn.UseIntegratedSecurity = $true;
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn)

Now, you can query the PowerShell Object $SQLSrvObj.

## - Query PowerShell Object:
$SQLSrvObj.Information `
| Select-Object parent, platform, product, productlevel, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;

Prepare connection parameters for Linux Systems

For Linux systems, we use ‘SQL Authentication’. Here we add the SQL User password, then passing the value to the SqlConnectionInfo class.  And, the *.UseIntegratedSecurity property by the default is ‘false‘.

## - Prepare connection strings and connect to a Linux SQL Server:
$SQLServerInstanceName = 'sqlsvrinst01,1439';
$SQLUserName = 'sqluser01'; $sqlPwd = '$usrpwd01!';
$SQLSrvConn = new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($SQLServerInstanceName, $SQLUserName, $SqlPwd)
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn)

Again, you can proceed to query the PowerShell Object $SQLSrvObj.

## - Query PowerShell Object:
$SQLSrvObj.Information `
| Select-Object parent, platform, product, productlevel, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;

Please notice in the above image, the Windows 10 Insider Build 16215 Bash Console is running PowerShell Core. This list insider release made it possible for PowerShell Core to be functional again.


As we can see, this opens new opportunities to build cross-platform PowerShell scripts solutions working with SQL Servers in Linux, Windows, and others.

This is very exciting to start experiencing first hand these upcoming changes. I can’t deny that’s it’s challenging but you can’t turn down an opportunity to gain more skills.

Please, take advantage and subscribe to Microsoft Azure. Build, test, and start deploying solutions. Don’t be afraid to be creative. We all learn thru trial and errors!

This is a good time to keep up with what’s going on with technology.

Additional References:

Microsoft Azure:
Microsoft Windows Bash Shell:
Microsoft Academy:
Microsoft Channel 9:
Microsoft MVP Blog:
Microsoft SQL Server Docs:
Microsoft PowerShell Blog:

PowerShell, and SQL Server Working with Anaconda

On my previous blog “PowerShell – Working with Python and SQL Server“, I show how to install Python 3.5 so we can be build python scripts to connecting to SQL Server and use them with PowerShell.

Now, since the release of SQL Server 2017 and the integration of Anaconda (ie. Python 3.6), we need to know what it takes to successfully install Anaconda on your developer system(s) both Windows and Linux.

Installing Anaconda in Windows

In Windows the installation is simply done through the SQL Server 2017 setup process. During the SQL Server installation process, select the “Machine Learning Services (In-Database)” option and this will automatically install both “R” and *”Anaconda” on your system.

*Note: Installing Anaconda (Python 3.6) will redirect any previous version of Python to version 3.6. So, you may need to manually revert back to use older version.

Installing Anaconda in Linux (Ubuntu)

There are few more steps to complete the installation on *Linux. First, verify which is the latest version available by going to the following link:

Then follow these steps in bash console:

1. Change directory to where you want to store the installation file:

$ cd Downloads

2. The “curl” command for the latest version available:

$ curl -O

3. Run the installation command:

$ bash

4. Enter “Yes” to Accept the license agreement.

6. Then, you can select the location where Anaconda will be installed. The default is the user home folder.

5. Add the Anacona path to user profile in the “.bashrc” file by answering “Yes” and this will force to open Python on version 3.6.

6. Finally, to activate Anaconda, type the following command:

$ source ~/.bashrc

If you want to use any previous version, then you’ll need to manually type the PythonX.x executable. Try the following commands to open other versions of python previously installed in Ubuntu: python3.5, python2, or python2.7.

*Note: These steps can be applied to WSL Windows 10 Bash.

Using “update-alternatives” Linux Command

You could also setup the “update alternatives” command to swapt between the different versions of Python. This command need to be executed under super-user privilege “sudo su“.

Below is the series of commands use with “update-alternatives“:

##-> Install python for 'update-alternatives' command use:
$ sudo su
# update-alternatives --list python # will not display python

##-> To setup to use different versions:
# update-alternatives --install /usr/bin/python python /usr/bin/python2.7 5
# update-alternatives --install /usr/bin/python python /usr/bin/python3.5 1
# update-alternatives --install /usr/bin/python python /home/Username/anaconda3/bin/python3.6 2

##-> To list all installed pythons:
# update-alternatives --list python

##-> To change Python version, then select which version
# update-alternatives --config python

##-> You can use the --remove parameter to get rid of any lines added:
# update-alternatives --remove python /usr/bin/python3.5

Remember, in Ubuntu Linux, the system default version of Python is 2.7.

It would be a bad routine, when using the “update-alternatives” command, to change back to the default version as all running scripts during the system updates will need run on Python 2.7.

Additional Package for SQL Server

During the Anaconda installation, you’ll notice that it will load lots of python packages for data science and including “tk” which provide the ability to create GUI applications.

But, there’s one package missing, “pyodbc” will be needed in order to create python scripts to connect with SQL Server.

I did install PYODBC in both Windows and Linux, run the following command at the console:

conda install pyodbc

Then, to test this package was loaded, open *python and type:

import pyodbc
## - Connect to database:
cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=MTRINIDADLT2,51417;DATABASE=master;UID=sa;PWD=$SqlPwd01!')
cursor = cnxn.cursor()

Unfortunately, in Ubuntu Linux, the connection string will fail giving the following error:

cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=MTRINIDADLT2,51417;DATABASE=master;UID=sa;PWD=$SqlPwd01!')
Traceback (most recent call last):
File "", line 1, in
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql/lib64/' : file not found (0) (SQLDriverConnect)")

Strangely enough, this error is only on Ubuntu Linux and not Windows installation. So, Python 3.6 will work on Windows to build your scripts to work with SQL Server while Microsoft and/or Anaconda figured this one out.

*Note: This sample connection string to SQL Server is done thru SQL Server Authentication.

Configuring Anaconda in SQL Server 2017

This is only available in SQL Server 2017 and SQL Server Management Studio v17 with the feature of integrating Anaconda (Python 3.6) with SQL Server is to be able to execute the python script(s) from SQL Server Stored-Procedure.

The following steps need to be complete to enable SQL Server to execute Python scripts as an external script from SSMS SQL Query or within a stored-procedure.

1. Execute the following T-SQL command:

sp_configure 'external scripts enabled', 1

2. Then, SQL Server Service will need to be restarted for the changes to take place.

3. Proceed to execute a python script from SSMS SQL Query panel:

execute sp_execute_external_script
@language = N'python',
@script = N'
import sys
print("Hello SQLServer, I am Python Version:")

Unfortunately, I haven’t been successful to run the SSMS SQL query connected to a SQL Server on Linux. So, apparently there’s still a limitation in Linux.

What with PowerShell!

So the main purpose of integrating Anaconda (Python 3.6) with SQL Server is to be able to execute the script from SQL Server Stored-Procedure. But, one of Anaconda installed packages is ‘tk‘.

The ‘tk‘ package allows you to create GUI application in Python. This opens opens opportunities to develope and integrating some solution with PowerShell. For example, PowerShell v6 Alpha doesn’t have the Out-GridView command available yet.

So, here’s a raw with limited functionality of a python Out-GridView look-a-like. The following sample code will access some data from SQL Server, use PowerShell to manipulate the information, and then use Python ‘tk’ component to display it in a GUI datagrid.

$runpy = @'
import pyodbc
from tkinter import *

cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=MTRINIDADLT2,1738;DATABASE=master;UID=sa;PWD=$Adm1n!')
cursor = cnxn.cursor()

#Execute T-SQL Query:
trecord = []
tsql = 'SELECT Name, Location, Hire, HireDate FROM SampleDB1.dbo.HolidayEmployees;'
if cursor.execute(tsql):
row = cursor.fetchone()
while row:
datarow = [str(row[0]),str(row[1]),str(row[2]),str(row[3])]
row = cursor.fetchone()

## - list to screen list of data and will get number of rows in the list:
i = 0;
for i, rec in enumerate(trecord):

for i, rec in enumerate(trecord):
col = 0;
for c in rec:
Label(text=c, relief=RIDGE, width=15).grid(row=i, column=col)
col = col + 1;


python -c $runpy;

As you can image, there’s a lot of room to grow for integrating technologies such as PowerShell and Python. Just be creative!

Additional Tips

1. To edit, or commented out, the Anaconda Path, in the .bashrc file:

$ sudo gedit ~/.bashrc


2. To find out all installed packages in Anaconda, use the following command:

$ conda list

3. Upgrading Anaconda to newer version:

## - Windows:
conda update --prefix 'C:\Program Files\Anaconda3' anaconda
## - Linux:
$ conda update anaconda

Additional Resources

* Don’t forget to check out Microsoft Data Amp Technical Sessions at:
* Check What’s new about SQL Server 2017?
* Getting started in SQL Server on Linux:
* Download Anaconda:

PowerShell – SQL Server Management Studio v17 is Ready!

Yes! The SQL Server Management Studio  Version 17 is available for download for the purpose of managing the new SQL Server 2017, Azure SQL Databases and Azure SQL Data Warehouse. At the same time this version won’t prevent you from working with older SQL Server such as SQL Server 2000 and SQL Server 2005 but there will be some features that won’t work. Please know the limitations!

To download the new SQL Server Management Studio V17, click on the following link:

Now there are a few things, you should be aware. This version depends on Visual Studio 2015 with the update KB3165756 installed. I ended up crashing SSMS when I made the mistake of uninstalling Visual Studio 2015 to make some room for the Visual Studio Preview.

Here’s the SSMS error message that will popup and made my application useless.

But, no worries! In order to correct the issue, I did the following steps:

1. Uninstall SSMS
2. Restart machine (Windows 10 Insider Build 16179)
3. In my case, I installed the Visual Studio 2015 Shell only.
4. Search and installed the Visual Studio 2015 Update – KB3165756.
5. Restart machine.
6. Just to make sure, open Visual Studio 2015 and verify the update was installed.
7. Download SQL Server Management Studio V17:
8. Install and restart system.
9. Open SSMS v17 and verify there are no errors.

Apparently, there are strong dependencies in SSMS with Visual Studio 2015.

Now, there are a few changes from previous SSMS Release Candidates in regards with PowerShell SQL Server commands. As the new PowerShell “SQLServer” module is no longer included with SSMS v17. But, you still get the SQLPS module.

The following PowerShell one-liners to check for the modules installed:

gcm -Module SQLServer -CommandType Cmdlet | Select-object -first 10 name
gcm -Module SQLPS -CommandType Cmdlet | Select -first 10 name

In order to installed the PowerShell “SQLServer” module, you’ll need to downloaded from “PowerShell Gallery”. Follow the instructions from the following link:

One requirement for using PowerShell “SQLServer” module is to have PowerShell Version 5.0 or greater.

Install-module -Name SqlServer -Scope CurrentUser -AllowClobber

Make sure that after you complete the installation, close and open another PowerShell session.

Now, you are ready to work with both SSMS v17 and the new PowerShell “SQLServer” module.


PowerShell, SQL Server, and Linux Oh My!

The South Florida Code Camp 2017 is ON! Come and join me in this event on Saturday, March 11th 2017 all day event.

Check out the event speaking agenda:

I will be presenting the following 3 sessions:

1. 09:50am – 11:00amWorking with SQL Server for Linux Cross-platform:
I will be showing the latest build of SQL Server vNext for Linux. Everything you need to install and connect to SQL Server. Then, what tools are available to use from either Linux and Windows. Sample on querying for database information using Python and PowerShell between two environments. This will be a demo intensive session.

2. 11:10am – 12:20pm Using Windows 10 Bash with PowerShell Open Source:
We’ll be taking a tour into Windows 10 Bash Linux subsystem. I’ll be sharing some tips on how to work with Bash, and the workaround to make PowerShell in side BASH. This is the perfect environment to start learning about Linux while you work in windows. We’ll be take advantage of how-to use cross-platform Open source product(s). All this and more from Windows 10.

3. 01:20pm – 02:30pm Using PowerShell Open Source across multiple Platforms:
PowerShell is Open Source now! Come see how you could use PowerShell cross-platform between Windows and Linux sharing scripts to assist in admin task automation. I’ll be walking on how to make this work with existing tools. Also, interacting with SQL Server for Linux.

To register go to the following link:

Can you run PowerShell in SQL Server Management Studio? YES!

Just to be clear!! You can run PowerShell in SQL Server Management Studio.

Not only you can run PowerShell, but you can create scheduled jobs in SQL Agent to run PowerShell scripts. This has been available since SQL Server 2008 (before R2). And, this is a lot better now, as each version are finally providing more “SQL PowerShell cmdlets” to manage your SQL Server in their *”SQLPS” PowerShell module.

*Note: SQLPS Module was introduce with SQL Server 2008.

Nowadays, Thanks to both Aaron Nelson, Christy LeMaire, and Rob Sewell who have contribute to the success of provide new enhancements to SQL Server PowerShell (SQLPS) cmdlets. Check out their tools:

DBA Tools “best practices and instance migration module” link:
DBA Reports “free, fun” link:

By the way, DBA Reports is owned by Rob Sewell – @sqldbawithabeard. Great Work!!

So, YES! You can run PowerShell from SQL Server Management Studio.

How to run PowerShell?

You can Right-Click on most of the SQL Server objects under “Object Explorer” and look for “Start PowerShell“.

This will open the PowerShell prompt and you are ready to start your adhoc scripting.

Keep in mind, on the latest version of SQL Server (< 2012), the SQL PowerShell module (SQLPS) is loaded and already available. This will create a SQL Server Drive connecting (in this case) to your local instance installation or whichever instance you’re connecting to.

Notice, in my case, the above image will open a PowerShell prompt and is using PowerShell version 5.1 which is part of my Windows 10. The same will be true on earlier OS version of PowerShell. The “Start PowerShell” will open the current PowerShell version installed on that machine.

Another thing to understand, although you already have a set of available cmdlets to manage your SQL Server, you can still expand and build more script with the use SMO (SQL Server Management Objects). So, the possibilities to build your own solutions are endless.

All SMO .NET assemblies are loaded into your system when installing SSMS.

About SQLPS been removed

To be clear! Documentation states that SQLPS “Utility” (sqlps.exe) will be removed in the future. But, the SQLPS PowerShell module will still be available. (See reference link)

This is why you rather use the normal PowerShell console and start using the SQLPS module. Keep in mind, that since PowerShell 3.0, all existing installed modules are automatically loaded and ready to use in your PowerShell session.

How do I get SQL PowerShell?

Simple! SQL PowerShell comes included when SQL Server Management Studio(SSMS) is installed. For sometime ago SSMS (SSMS 2012) has been available to install separately (free-of-charge). As a matter of fact, you could install three separate SQL Server Features components without the need of installing SSMS and start scripting against your SQL engine.

The following link shows both latest version of SSMS (16.5.3) and the preview SSMS for SQL Server vNext (RC 17) can be found here:

Bonus – No need for SSMS GUI nor the SQL Engine

Sometimes there’s no need to install a SQL instance, nor SSMS GUI but only the necessary components installed in order to run and scheduled some SQL PowerShell scripts in Windows Server Task Scheduler. I had this scenario on a **server with no SQL engine but needed to run some scheduled SQL PowerShell scripts. Only 3 components are needed:

(Below content extracted from Microsoft link (Install section) : )

Microsoft® Windows PowerShell Extensions for Microsoft SQL Server® 2016
The Microsoft Windows PowerShell Extensions for SQL Server includes a provider and a set of cmdlets that enable administrators and developers to build PowerShell scripts for managing instances of SQL Server. The SQL Server PowerShell Provider delivers a simple mechanism for navigating SQL Server instances that is similar to file system paths. PowerShell scripts can then use the SQL Server Management Objects to administer the instances. The SQL Server cmdlets support operations such as executing Transact-SQL scripts or evaluating SQL Server policies.

Filename: X86 and x64 Package (PowerShellTools.msi)

– Microsoft® SQL Server® 2016 Shared Management Objects
SQL Server Management Objects (SMO) is a .NET Framework object model that enables software developers to create client-side applications to manage and administer SQL Server objects and services.

Note: Microsoft SQL Server Management Objects requires – Microsoft SQL Server System CLR Types, that is available on this page.
Filename: X86 and x64 Package (SharedManagementObjects.msi)

– Microsoft® System CLR Types for Microsoft SQL Server® 2016
The SQL Server System CLR Types package contains the components implementing the geometry, geography, and hierarchy id types in SQL Server. This component can be installed separately from the server to allow client applications to use these types outside of the server.

Filename: X86 and x64 Package (SQLSysClrTypes.msi)

**Note: This can apply to desktop/laptop is you don’t want to install the whole SQL Server CD. As long as, you have remote connection to a SQL Server system, then you just start building scripts. You will save some disk space too.