Working with Azure Resource Manager Cross-platform with PowerShell Core

This is a brief introduction to connecting to your Azure Cloud subscription using Azure Resource Manager module (AzureRM) in both Windows PowerShell and PowerShell Core. You could install both AzureRM and AzureRM.Netcore modules in the same Windows System. Or, when using Linux, just install the AzureRM.Netcore module.

Azure Resource Manager Installation

In Windows Powershell, use the following command to *install AzureRM (or Azure.Netcore) from the PowerShell Gallery:

## - Install AzureRM from the PowerShell Gallery:
Install-Module -Name AzureRM -Repository PSGallery;

*Note: Keep in mind, the console need to be open with elevated permission or it won’t install the module(s).

In Linux, use “sudo pwsh” to start PowerShell with admin pernmission.

$ sudo pwsh

Then, enter the following PowerShell command to install AzureRM.Netcore module:

## - Install AzureRM for PowerShell Core:
Install-Module -Name-AzureRM.Netcore -Repository PSGallery;

To see the list of installed AzureRM in PowerShell, execute the following command:

1. AzureRM Modules. Using the following command will provide the list of modules *loaded:

## - List of installed AzureRM modules:
Get-Module -ListAvailable AzureRM `
| Select name, version, path;

2. In Linux, AzureRM.Netcore Modules. Using the following command will provide the list of modules loaded:

## - List of installed AzureRM.Netcore modules:
Get-Module -ListAvailable AzureRM.Netcore `
| Select name, version, path;

Get-Module -ListAvailable *.Netcore `
| Select name, version, path;


*Note: If you see multiple installation of the same modules, but different versions, you’ll need to pick the right version. Or, this could lead to experiencing having issues when executing azure commands.

You’ll notice that not all Windows PowerShell AzureRM modules has been ported to PowerShell Core yet.

Connecting Linux to Azure Cloud

Here’s where fun begin! We all know in Windows Systems, for automation purpose, we need to create the a certificate key so you can connect to our Azure subscription. But in Linux, is different!

The following steps will connect a Linux System to an Azure subscription:

1. One time only, I use the following command to login to Azure:

## - Login to Azure:
Login-AzureRmAccount;

Executing this command, will have you do a few things:

1. Ask if you want to “enable data collection” (Y/N). (pick one)

2. At this point the command will pause waiting for you to open the browser.

3. Open link provided (copy/paste): https://aka.ms/devicelogin

4. The proceed to enter the code:

5. Enter you Azure email account and password:

6. At the end, you will be asked to close the page and the command will end executiion connecting to Azure.

Try to execute a few AzureRM commands to make sure the connection was successful:

## - Testin Azure connections:
Get-AzureRMSubscription;

Get-AzureRMVM -ResourceGroupName AZRESOURCEEAST01 -Status;

Saving Connection Profile for Automation

After verifying connectivity to Azure, use the following command to save your Azure Subscription Profile in a *Json file. Just give it a location and save the file. This will speed up the *connection step.

## - Save Azure subscription profile: (one time or as needed)
Save-AzureRmContext -Path "/home/username/AzureSubs/lx_ARMprofile.json";

Next time around connecting to Azure, use the Import command to connect:

Import-AzureRmContext -Path "/home/username/AzureSubs/lx_ARMprofile.json";

These step works in both Windows PowerShell and PowerShell Core.

Word of Caution

Now, during my venture of moving from Azure Classic modules to Azure Resource Manager (AzureRM) module, I ended hitting the wall. The user experience was rough! It was all due to have duplication modules and different versions, installed in mutliples locations.

There’s a chance that you will encounter the same issues: mostly obsolete/deprecated commands and/or parameters no longer valid. Now, specially if you install Visual Studio, the Azure SDK option might be already an older version.

So, what’s the solution? Definitely, a cleanup is necessary. Then, trust the PowerShell Galery repository to get the latest versions. More in this issue in upcoming post.

For now, don’t stop learning PowerShell!

Posted in PowerShell | Comments Off on Working with Azure Resource Manager Cross-platform with PowerShell Core

PowerShell Core 6 Release Candidate is available now

Yes! The Microsoft PowerShell Team has release the PowerShell Core 6 RC to the public this evening 11/17/2017 available for download.  And, now the road to the GA (General Availability) started.

Read all about it on Microsoft PowerShell Team Blog post a the following link: https://blogs.msdn.microsoft.com/powershell/2017/11/17/powershell-core-6-release-candidate/

Notice, when you start PowerShell (pwsh), is giving you a hint to check out PSCpore6-Docs: https://aka.ms/pscore6-docs 

Let’s keep the momentum going!

Posted in PowerShell | Comments Off on PowerShell Core 6 Release Candidate is available now

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 https://www.nuget.org/api/v2
}else{
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: https://www.nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects

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: https://www.nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects/#
2.GitHub SQLToolService: https://github.com/Microsoft/sqltoolsservice
3.Installing SQL Server mssql-scripter(Python-based): https://github.com/Microsoft/sql-xplat-cli

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

Conclusion

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: https://docs.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/sql-server-management-objects-smo-programming-guide

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!

Posted in Bash, Linux Bash, PowerShell, SQL Server, Ubuntu, Windows 10 | Comments Off on Streamlining SQL Server Management Objects (SMO) in PowerShell Core

PowerShell Core – Collecting Information Cross-platform

Now, PowerShell Core has a new look with the new release version Beta.9. At the same time, the executable name has change from “powershell.exe” to “pwsh.exe” (or “pwsh in Linux).

The following example was shown by Microsoft Joey Aiello during the PowerShell Asia Conference in Singapore.  I, kind of, duplicated the steps showing how-to execute one command against two open PowerShell session: one on Linux, and another in Windows.

In my case, I’m on a workgroup environment. So, I have to enable my WinRM and PowerShell Remoting with the following easy steps (just accepting default options):

## - Run quick setup:
winrm quickconfig -force
Enable-PSRemoting -force
.\Install-PowerShellRemoting.ps1

Next, I got to add my trusted hosts in WinRm: adding both IP-Addresses, and system-names. By the way, I didn’t have top manual restart WinRm services.

Set-Item wsman:\localhost\Client\TrustedHosts -Value 10.0.0.19
$curvalue = Get-Item wsman:\localhost\Client\TrustedHosts
Set-Item wsman:\localhost\Client\TrustedHosts -Value "$($curvalue.value), 10.0.0.40"
Get-Item wsman:\localhost\Client\TrustedHosts

Set-Item wsman:\localhost\Client\TrustedHosts -Value "$($curvalue.value), sapien01"
$curvalue = Get-Item wsman:\localhost\Client\TrustedHosts
Set-Item wsman:\localhost\Client\TrustedHosts -Value "$($curvalue.value), earth"
Get-Item wsman:\localhost\Client\TrustedHosts

Next step is to create two open sessions: one on a Linux System and the other to a Windows System. Then verify that both system sessions are opened.

$linuxSession = New-PSSession -HostName venus -UserName maxt
$windowsSession = New-PSSession -ComputerName earth -Credential max_t

Get-PSSession

Please, notice that I’m using the “ssh” protocol to connect to my Linux system, and for Windows I’m using WinRm.

Finally, we can proceed to execute one command against these opened sessions, and see the merged results.

Invoke-Command -Session $linuxSession, $windowsSession -ScriptBlock {Get-Process}

Thanks to Joey Aiello for this “Great!” demo showing the possibilities for cross-platform sample in collecting information between different systems.

Go Bold! With PowerShell Core!!

Posted in PowerShell | Comments Off on PowerShell Core – Collecting Information Cross-platform

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