PowerShell Core useful cross-platform variables

In PowerShell Core, there are three useful variable you can use in a cross-platform scripts. Then, the script can have the necessary logic to response based on the environment it is running.

Use the cmdlet Get-Variable to find them, and keep in mind, these variables are not found in Windows PowerShell 5.x.

Get-Variable Is*

Although, the results will display four variable, but let’s pay attention to three of them. Below are the variables with their default values:

IsLinux                            False
IsOSX                              False
IsWindows                    True

These three variables can help in identifying which Operating System the script are been executed.  This way just adding the necessary logic, in order to take the correct action.

For example, you can want to execute a Python code on different systems.

In Windows, if Anaconda (Python 3.6) was installed then Python executable is located at: C:\ProgramData\Anaconda3\python.exe

In Ubuntu Linux, Anaconda (Python 3.6) may be found on user Home folder, then it can be located at:  /home/username/anaconda3/bin/python

Of course, Python location may vary if different versions are installed, and/or installation default location was change.

Sample Cross-platform Code Snippet

Here’s a simple script code logic to accomplished cross-platform behavior.  In my environment the sample code was executes in a Windows system.

if ($IsLinux -eq $true)
{
Write-Host "Executing in Linux - $($psversionTable.platform.ToString())" -ForegroundColor Green;
#Linux Python path:
if ((Test-Path '/home/maxt/anaconda3/bin/python') -eq $true)
{
Write-Host "Linux Python Path Validated" -ForegroundColor Yellow;
}
}
else
{
if ($IsWindows -eq $true)
{
Write-Host "Executing in Windows - $($psversionTable.platform.ToString())" -ForegroundColor Green;
#Windows Python path:
if ((Test-Path 'C:\ProgramData\Anaconda3\python.exe') -eq $true)
{
Write-Host "Windows Python Path Validated" -ForegroundColor Yellow;
}
}
else
{
Write-Host "Can't execute under this OS!";
};
};

Go ahead and try the same code in Linux PowerShell Core. Of course, this code can be improved, but let’s leave it to your creativity. Start simple and grow!

By the way, did you know you can use SAPIEN Technologies PrimalScript to execute PowerShell Core scripts. Yes! It is possible!

Check the following SAPIEN Blog post about it: https://www.sapien.com/blog/2017/09/15/using-powershell-core-in-primalscript/

Posted in Bash, PowerShell, Ubuntu, Windows 10 | Comments Off on PowerShell Core useful cross-platform variables

SAPIEN PowerShell Tools at Orlando Microsoft Ignite Conference 2017

As I started a new role working for SAPIEN Technologies as their Technology Evangelist in September, I had the opportunity to be working with them a the Microsoft Ignite Conference in Orlando.

Greatly appreciate this opportunity and the chance the meet everyone interested in PowerShell as well as our SAPIEN PowerShell Tools at the event.

Feel free to reach out, keep asking about our product, product services, and must important, give us feedback on how to make it better.

Don’t forget to check out our blog posts, support forums, YouTube videos, and specially the “Information Center” under the following link: https://www.sapien.com/support

 

 

Posted in Bash, Linux Bash, PowerShell, Ubuntu, Windows 10 | Comments Off on SAPIEN PowerShell Tools at Orlando Microsoft Ignite Conference 2017

PowerShell Core Stable SQL Server SMO Assemblies and DataRow objects

Yes! Just recently I downloaded the latest SQL Server SMO assemblies that can be use with PowerShell Core in both Linux and Windows. You can find them in Github under Microsoft SqlToolsService. But, you’ll need to extract only the necessary DLL’s before you can start creating your PowerShell Core SMO scripts. There’s no installation program, as this is installed manually.

There’s one requirement I would suggest to do. Download and install .NET Core 2.0.
To download click this link: https://www.microsoft.com/net/download/core

Manual SMO Installation

The latest SqlToolsService version can be found at this Github link: https://github.com/Microsoft/sqltoolsservice/releases
I’m currently using is V1.1.0-alpha.31.

Just download the file for the OS you’re working:

1. In Windows, download the zip file “Microsoft.SqlTools.ServiceLayer-win-x64-netcoreapp2.0.zip
a. Open the zip file.
b. In the zip app, select only the following dll’s:

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Dmf.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.Management.SmoMetadataProvider.dll
Microsoft.SqlServer.Management.SqlScriptPublishModel.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoExtended.dll
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.SqlParser.dll
NetCoreGlobalization.dll

c. Extract all selected dll’s into your *PowerShell Core Beta folder “C:\Program Files\PowerShell\6.0.0-beta.x”.

2. In Ubuntu Linux, download the tar file “Microsoft.SqlTools.ServiceLayer-ubuntu16-x64-netcoreapp2.0.tar.gz“.
a. To open the file, use either Desktop Nautilus, or use the command-line tar command.
b. In your home folder, create a folder for the dll’s you’re going to extract (for example: mkdir sqltoolsservice).
c. In the tar app, Select only the following dll’s:

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Dmf.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.Management.SmoMetadataProvider.dll
Microsoft.SqlServer.Management.SqlScriptPublishModel.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoExtended.dll
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.SqlParser.dll
NetCoreGlobalization.dll

d. Extract the files into the folder you created.

Definitely, using the GUI tar or zip application seems better as you can use the Ctrl key to individually highlight the files to extract.

*Note: Keep in mind, when you add these dll’s into the PowerShell Core folder, uninstalling PowerShell beta won’t removed them. You must manually delete them and the folder.

Verifying SMO Works

In order to test SQLServer Management Objects working with PowerShell Core, we are going to use the following PowerShell Core script snippet:

# - Windows Hack:
cd 'C:\Program Files\PowerShell\6.0.0-beta.x'

# - Linux Hack:
cd /home/username/SqlToolsServices

# - Loading necessary SMO Assemblies:
$Assem = ("Microsoft.SqlServer.Management.Sdk.Sfc", `
"Microsoft.SqlServer.Smo", `
"Microsoft.SqlServer.ConnectionInfo",
"Microsoft.SqlServer.SqlEnum");
Add-Type -AssemblyName $Assem

# - Prepare variables for connection strings to SQL Server using SQL Authentication:
$SQLServerInstanceName = 'Sql01,1451';
$SQLUserName = 'sauser'; $sqlPwd = '$MyPwd99!';

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

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

## - SMO sample 2
## -> To execute T-SQL Query:

# - Prepare query string variable:
$SqlQuery = "SP_WHO2";

# - Execute T-SQL Query:
[array]$result = $SQLSrvObj.Databases['master'].ExecuteWithResults($SqlQuery);

# - Display T-SQL Query results:
$result.tables.Rows | Select-object -first 10 $_ | Format-Table -AutoSize;

When executing the code both Windows and Linux, make sure you are in the folder you installed the dll’s files or it won’t execute.

In Window, in PowerShell Core console stays in folder: “C:\Program Files\PowerShell\6.0.0-beta.x”.

In Ubuntu Linux, in PowerShell Core console, change directory to “SqlToolsService”.

The above script will verify your manual installation of the SMO dll’s in PowerShell Core was successful. Now, you can use SMO in PowerShell Core in both Linux and Windows. And, most important, the previous issue I describe in my previous blog post “PowerShell Core – Getting SQL Server using ADO.NET Data provider” about the DataRow object has been cleared. So, there’s no need for adding code to fix the object to display data columns and values correctly.

Please, go ahead the give it a try! It’s great that now we can use PowerShell Core in Linux to create .NET object we can use and take advantage of this technology.

Posted in Bash, Linux Bash, PowerShell, Ubuntu, Windows 10 | Comments Off on PowerShell Core Stable SQL Server SMO Assemblies and DataRow objects

FLPSUG goes live at Keiser University Port St. Lucie

Yes, its finally happening! Thanks to Leslie Haviland (Director of Student Services), Dewan Persaud (Program Chair Information Technology), and staff to help me setting this meeting at their Port St. Lucie location.

Everyone is welcome to attend no matter what’s your skill level. I’m hoping that this will be first of many upcoming meetings as this technology is finally On-Demand in the industry. Keep in mind, PowerShell is also available Open Source running on Linux and Mac OS’s.

Most important! Is never too late to start learning about PowerShell.

Please, come over or register at: bit.ly/2u6unrs

Event Address:
Keiser University – Port St. Lucie
9400 SW Discovery Way
(Room 106)
Port St. Lucie, FL 34987

Hope to see you all there!

Posted in Bash, Linux Bash, PowerShell, Ubuntu, Windows 10 | Comments Off on FLPSUG goes live at Keiser University Port St. Lucie

PowerShell Core – Getting SQL Server using ADO.NET Data provider

If you’re interested in using PowerShell Core in Linux, as well in Windows, to connect and execute T-SQL queries then the “System.Data” namespace is available. This contains .NET classes necessary to help you access and manage data from SQL Server, as well as different ADO.NET source providers.  Although, this is not a substitute for SMO, you can use it to execute T-SQL queries, Stored-Procedures, and even DMV’s.

There’s a catch!

Nothing is perfect yet! Using the .NET Core version of System.Data, there’s a known issue with the datarow class. It seems it’s building the data results as string list of values without the column information.

But, there’s always a way to make thing work adding some extra code to work around this issue and reconstruct the data the way we want.

So, give it time! It will be fix. But, for now, below is the workaround.

Sample Code Workaround

The following code, using .NET Core System.Data, will connect to a SQL Server (Windows or Linux) using SQL Server Authentication, execute a T-SQL query, extract the data and rebuild the .NET PSObject. At the end, this PowerShell .NET object will contain the datarow object with Columns and Data values.

1. Prepare connection string and execute query

## - Prepare connection string and execute query:
$Global:getSvr = "mtrinidadlt2,1437";
$Global:con = "server=$Global:getSvr;database=Master;Integrated Security=false;User ID=sa;" + 'Password=$Adm1n!';
$sda = New-Object System.Data.SqlClient.SqlDataAdapter ("SP_WHo2", $global:con)
$sdt = New-Object System.Data.DataTable
$sda.fill($sdt) | Out-Null

2. Dissect query result to identify column and data:

## - Dissect query result to identify column and data:
## - =================================================

## - Initializing varialbles:
$global:rowcolumnheading = $null;

## - Prepare columns:
$global:cnt1 = 1;
foreach ($colname in $sdt.columns.ColumnName)
{
if ($global:cnt1 -ne $sdt.columns.ColumnName.count)
{
[string]$global:rowcolumnheading += $colname + ',';
$global:cnt1++
}
else
{
[string]$global:rowcolumnheading += $colname;
}

}; $global:rowcolumnheading;

[array]$global:dataobject = $null; [string]$dataitem = $null;
$global:dataobject = $global:rowcolumnheading;

## - Load data:
$global:cnt2 = 1;
foreach ($item in $sdt.rows.table[0].rows.itemarray)
{
if ($global:cnt2 -ne $sdt.rows.table[0].rows[0].itemarray.count)
{
try
{
[string]$dataitem += $item.Trim() + ',';
$global:cnt2++;
}
catch
{
#-> Ignore Error and keep counter working:
[string]$dataitem += '-' + ',';
$global:cnt2++;
}
}
else
{
try
{
[string]$dataitem += $item.Trim();
$global:dataobject += $dataitem;
$dataitem = $null; $cnt2 = 1;
}
catch
{
#-> Ignore Error and keep counter working:
[string]$dataitem += '-';
$global:dataobject += $dataitem;
$dataitem = $null; $cnt2 = 1;
}
};
};

3. Finally, properly build .NET data object with column/row:

## - Build csv to rebuild data column/row object: (Windows)
$global:dataobject | Out-File c:/temp/data.csv
$data = import-csv c:/temp/data.csv

4. (Optional) Export fixed data object to CSV file:

## - Export to a true CSV format file:
$data | Export-Csv -Path c:/temp/nonSMO_Data.csv -NoTypeInformation -Encoding UTF8

Sample results of the PowerShell Core script:

Basically, step 3 and 4 is the work around code in order to build the Datarow objects properly. I’m using the comma ‘,’ as a field delimiter and, when the dataitem has a NULL value, I’m replacing it with a dash ‘-‘.

About SMO in Linux

I’m sad to say that the SMO in Linux broke with the recent PowerShell Core Beta 3. But, the issue has been reported and eventually SMO in Linux will bounce back. If you want to play with it, you can have multiple version of PowerShell Core installed side-by-side in Windows. This issue will be corrected soon.

Posted in PowerShell | Comments Off on PowerShell Core – Getting SQL Server using ADO.NET Data provider

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: https://www.eventbrite.com/e/florida-powershell-user-group-monthly-meeting-july-2017-tickets-36113308879?ref=estw

I hope you can joined me in this exciting session!

Posted in Azure, Bash, Linux Bash, PowerShell, SQL Server, Ubuntu, VS Code, Windows 10, Windows 2012 | Comments Off on FLPSUG – Next Online meeting July 26th 2017

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
https://register.gotowebinar.com/register/9138484624537412611

Once again Thanks to everyone!

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

Posted in Azure, Bash, PowerShell, SQL Server, Ubuntu, VS Code, Windows 10, Windows 2012 | Comments Off on South Florida SQLSaturday – Working with SQL Server in Linux session

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: https://github.com/Microsoft/sqltoolsservice 

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

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Dmf.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.Management.SmoMetadataProvider.dll
Microsoft.SqlServer.Management.SqlScriptPublishModel.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoExtended.dll
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.SqlParser.dll

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“: https://www.microsoft.com/net/core/preview#windowscmd
https://www.microsoft.com/net/core/preview#linuxubuntu

Now, because we are working with PowerShell Core, don’t forget to install the latest build found at:
https://github.com/PowerShell/PowerShell/releases

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: microsoft.sqltools.servicelayer-win-x64-netcoreapp2.0.zip

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 https://github.com/Microsoft/sqltoolsservice/releases/download/v1.0.0-alpha.34/microsoft.sqltools.credentials-ubuntu16-x64-netcoreapp2.0.tar.gz

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:

[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo")

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 = (
"Microsoft.SqlServer.Management.Sdk.Sfc",
"Microsoft.SqlServer.Smo",
"Microsoft.SqlServer.ConnectionInfo"
); 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.

Conclusion

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: https://azure.microsoft.com/en-us/
Github: https://github.com/
Ubuntu: https://www.ubuntu.com/
Microsoft Windows Bash Shell: https://msdn.microsoft.com/en-us/commandline/wsl/about
Microsoft Academy: https://mva.microsoft.com/
Microsoft Channel 9: https://channel9.msdn.com/
Microsoft MVP Blog: https://blogs.msdn.microsoft.com/mvpawardprogram/
Microsoft SQL Server Docs: https://docs.microsoft.com/en-us/sql/sql-server/sql-server-technical-documentation
Microsoft PowerShell Blog: https://blogs.msdn.microsoft.com/powershell/

Posted in Azure, Bash, Linux Bash, PowerShell, SQL Server, Ubuntu, Windows 10, Windows 2012 | Comments Off on Using Linux SQL Server SMO in PowerShell Core

Issues with VS Code “PowerShell Integrated” feature

As VS Code continues to evolve, we need to keep in mind that technology keep changing fast. Some of us on Windows Insider Fast Ring get the opportunity to test drive the latest build of Windows 10.

But there are times when these upgrades with break something in one of our installed applications. Just don’t despair, as there’s always a workaround!

In the last Windows Insider Build 16193, did break in VS Code (and the VS Code Insider) editor as their new feature “PowerShell Integrated” won’t work. Microsoft knows about it an it will be fix down the line. This issue will impact both Windows PowerShell, and PowerShell Core.

This issue is only on Windows 10 Build 16193 including WSL (Windows 10 Bash), and will be corrected soon.  In Windows 10 Bash console, PowerShell Core will get hung!

But, don’t worry! If you installed Ubuntu Desktop in Windows 10 Bash, then you can use PowerShell without any issues.

VS Code Workaround for “PowerShell Integrated”

If you want some information about this issue, feel free to checkout the following *link:
https://github.com/PowerShell/vscode-powershell/issues/742#issuecomment-301915916

*Note: Thanks to David Wilson (https://github.com/daviwil) for providing the workaround for VS Code.

In the link above, you’ll find the workaround to fix the issue. Basically, is creating the profile for VS Code (“Microsoft.VSCode_profile.ps1“) and adding the following line:

File: Microsoft.VSCode_profile.ps1
[System.Console]::OutputEncoding = [System.Text.Encoding]::ASCII
Write-Host “PowerShell version X.x.x loaded”

Where are these VS Code profile files been stored at in Windows?

Keep in mind. These profile files you need to create them at the following locations:

1. For Windows PowerShell – C:\Users\mtrinidad\Documents\WindowsPowerShell

2. For PowerShell Core – C:\Users\mtrinidad\Documents\PowerShell

3. In Linux, for standard profile.ps1 file – /opt/microsoft/powershell/6.0.0-beta.1 

In order for the profile to be use in VS Code, we need to add the following line in the “setting.json” file: “powershell.enableProfileLoading”: true

What other PowerShell tools you can use in VS Code?

Use “Code Runner” extension.  Then, from the menu look for “Preferences | Settings” which will open the “settings.json” you can configure which PowerShell version you want to use with the following lines:

1. For Windows PowerShell use:
“powershell”: “powershell.exe -ExecutionPolicy ByPass -File”,

2. For PowerShell Core:
“powershell”: “\”C:\\Program Files\\PowerShell\\6.0.0-beta.1\\powershell.exe\” -ExecutionPolicy ByPass -File”,

To execute a PowerShell script using Code Runner extension, just right-click and select “Run Code“. Then, all PowerShell results will be display under the “Output” section.

Changing “PowerShell Integrated” Terminal behavior

The normal behaviour when using the “PowerShell Integrated” is that you can highlight a few lines or execute the whole script and the results will be display in the “Terminal” section.

So, Yes! You can change the Terminal Integrated behavior to run other type of console: Bash, DOS, and even PowerShell version X as a standalone host. Just look at under the Settings Preferences “Settings.json” file by copy/paste the following line:

  • For Dos – “terminal.integrated.shell.windows”: “cmd.exe”,
  • For PowerShell Core – “terminal.integrated.shell.windows”: “C:\\Program Files\\PowerShell\\6.0.0-beta.1\\powershell.exe”,
  • For Windows PowerShell – “terminal.integrated.shell.windows”: “powershell.exe”,
  • For Linux Bash – “terminal.integrated.shell.linux”: “bash”,
  • For Linux powershell – “terminal.integrated.shell.linux”: “powershell”,

Sample image “Settings.json”:

Go ahead! Give it a try and experiment.

Check Out Github

If you’re interested in contributing, providing feedback and helping with the development of PowerShell Core, don’t forget to check out Github: https://github.com/PowerShell/PowerShell

Always remember!
* For issues, bugs, and feedbacks with Windows PowerShell, use the following link at “Windows PowerShell UserVoice“: https://windowsserver.uservoice.com/forums/301869-powershell/category/148044-powershell-engine

* For issues, bugs, and feedbacks with PowerShell Core, use Github: https://github.com/PowerShell/PowerShell/issues

Posted in PowerShell, VS Code, Windows 10 | Comments Off on Issues with VS Code “PowerShell Integrated” feature

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: https://www.continuum.io/downloads

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 https://repo.continuum.io/archive/Anaconda3-4.3.1-Linux-x86_64.sh

3. Run the installation command:

$ bash Anaconda3-4.3.1-Linux-x86_64.sh

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/libmsodbcsql-13.1.so.6.0' : 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
reconfigure

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:")
print(sys.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])]
trecord.append(datarow)
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):
print(rec);

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;

mainloop()
'@;

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: http://tinyurl.com/lmuquxu
* Check What’s new about SQL Server 2017? https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2017
* Getting started in SQL Server on Linux: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-get-started-tutorial
* Download Anaconda: https://www.continuum.io/downloads

Posted in Bash, Linux Bash, PowerShell, SQL Server, Ubuntu, Windows 10 | Comments Off on PowerShell, and SQL Server Working with Anaconda