Azure PowerShell Preview 1.0 is here

Welcome to PowerShell Azure Resource Manager (RM)!

Microsoft has introduce and made available Azure PowerShell Preview 1.0.  Please read all about it in the following Azure Blog site:

Also, feel free to search for it at usign the following link:

Some Interesting links 

Well, within the search there are more interesting blog articles to start doing some PowerShell scripting:

How to install and configure Azure PowerShell:

Using Azure PowerShell with Azure Resource Manager:

Manage Azure SQL Database with PowerShell:

Integrating SQL AlwaysOn with Azure Site Recovery:

Get it at PowerShell Gallery

Now, my question here is, did you found Azure PowerShell 1.0 Preview? Probably not, but in fact, it’s AzureRM 1.0.1 to be installed. Although, I did found Azure version 0.9.11 and, Yes! I did download that one just in case.

To download these new bits from the PowerShell Gallery, check out the “Get Started with the PowerShell Gallery” link:


Search for both “Azure” and AzureRM”.



During my quest to download Azure PowerShell 1.0, I realized that its AzureRM the one I need to download using: “Install-Module AzureRM” followed by “Install-AzureRM -force“.


To start using the AzureRM Cmdlets, just run the “Import-AzureRM” command.


Now, we are ready to play with Azure Resource Manager and Azure SQL databases.   There’s a total of 653 *AzureRM* cmdlets.



Run the following command to list all *AzureRM* commands:

Import-AzureRM; Get-Command *AzureRM*;


In my next blog article I’ll be converting my classic Azure SQL script to use the Azure RM new paradigm.


PowerShell connecting to SQL Server without SMO

Sometimes there will be a need to provide a scripting solution to connect to SQL Server to execute either a TSQL query or Stored-Procedure without the need of installing a SQL Server bits.  Here I’m providing two code snippets that gets the job done without the use of SMO (SQL Server Management Object) class.

Executing a T-SQL Query

Use the System.Data.SqlClient namespace from the .NET Framework Data Provider for SQL Server in order to build functions to execute T-SQL statement(s) and/or SQL Stored-Procedure with PowerShell.

Here’s an example in how to execute a T-SQL statement querying a table in its basic form:

## - Set PowerShell variables:
$ConnectionString = 'server=YourMachineName\MSSQL2K14;database=Master;Integrated

Security=false;User ID=sa;Password=$myPwd!';
$TSQLQuery = "Select * from AdventureWorks2014.dbo.AWBuildVersion;";

## - Connect and Execute Stored-Procedure:
$sda = New-Object System.Data.SqlClient.SqlDataAdapter ($TSQLQuery, $ConnectionString);
$sdt = New-Object System.Data.DataTable;
$sda.fill($sdt) | Out-Null;



Executing a T-SQL Stored-Procedure

Now the code for executing SQL Stored-Procedure will be differect as we are going to use the ‘.sqlCommand‘ class to process the T-SQL Statement to run a custom table update procedure in its basic form:

## - Set PowerShell variables:
$ConnectionString = 'server=YourMachineName\MSSQL2K14;database=Master;Integrated

Security=false;User ID=sa;Password=$myPwd!';
$TSQLQuery = "Exec [AdventureWorks2014].[dbo].[usp_UpdAWBuildVersion];";

## - Connect and Execute Stored-Procedure:
$sqlCon = New-Object Data.SqlClient.SqlConnection;
$sqlCon.ConnectionString = $ConnectionString;
$sqlCmd = New-Object Data.SqlClient.SqlCommand
$sqlCmd.Connection = $sqlCon
$sqlCmd.CommandText = $TSQLQuery;

I use dbForge for SQL Server to create new Stored-Procedure to dynamically increase the version number.


After executing the non-SMO code then the record did change:



Most important, when using the Data.SqlClient.SqlConnection class, the connection need be ‘.Close()‘ after executing the T-SQL command.

Error Handling

As we are connecting to the SQL Server to execute T-SQL Commands, its important to add some error handling routine to trap errors during the connectivity and/or T-SQL Statement(s) processing.  And, we do this by evolving the basic PowerShell code into a function.

Lets create two functions that will accept two parameters: a connection string, and the T-SQL Query.

* Get-TSQLQuery function

function Get-TSQLQuery
Param (
## - Non_SMO Get SQL query:
$sda = New-Object System.Data.SqlClient.SqlDataAdapter ($TSQLQuery,

$sdt = New-Object System.Data.DataTable;
$sda.fill($sdt) | Out-Null;
Write-Verbose "Error executing T-SQL Query: `r`n[$($error[0])]" -Verbose;

To test this function copy/paste the code into your PowerShell console, and the execute the following commands:

## - Set PowerShell variables:
$conn = 'server=MTRINIDADLT2\MSSQL2K14;database=Master;Integrated Security=false;User

$tsql = "Select * from AdventureWorks2014.dbo.AWBuildVersion";

## - Execute the function with its parameters:
(Get-TSQLQuery -ConnectionString $conn -TSQLQuery $tsql) `
| Select-Object `
SystemInformationID, 'Database Version', VersionDate, ModifiedDate `
| Format-Table -AutoSize;


* Execute-TSQLStoredProc function

function Execute-TSQLStoredProc
Param (

## - NON-SMO executing TSQL Stored-Procedure:
$sqlCon = New-Object Data.SqlClient.SqlConnection;
$sqlCon.ConnectionString = $ConnectionString;
$sqlCmd = New-Object Data.SqlClient.SqlCommand;
$sqlCmd.Connection = $sqlCon;
$sqlCmd.CommandText = $TSQLQuery;
Write-Verbose "Error executing T-SQL Stored-Procedure: `r`n[$($error[0])]"

}; $sqlCon.close();


## - Set PowerShell variables:
$Conn = 'server=MTRINIDADLT2\MSSQL2K14;database=Master;Integrated Security=false;User

$tsql = "Exec x[AdventureWorks2014].[dbo].[usp_UpdAWBuildVersion]";

## - Execute the function with its parameters:
Execute-TSQLStoredProc -ConnectionString $conn -TSQLQuery $tsql;


Adding the Try/Catch error handling block helps is trapping and displaying the errors.

This method serves as another alternative to connect to a SQL Server without the need to install an instance on a system.



PowerShell Deep Dive session at Work – a Success!

At TBC Corp. (Tire Kingdom) I scheduled the second PowerShell meeting for September 28th close to noon.  A give a 2hr PowerShell Deep Dive where everyone brought their laptops so they could follow along the session.  It was a total success!  I had 15 people signed up and 30 showed up for this session.


This was a diversified group of people which included: System Administrators(Windows, Linus, iSeries, SAP), Data Center Operators, Business Intelligence Developers, SQL Server Administrator and even some Business Analyst.  The general overview went well. The whole group was very interested in learning about PowerShell.

GettingStartedPowerShell_01_9-28-2015 jpg

GettingStartedPowerShell_02_9-28-2015 jpg

It all happened thanks for the support of all the technologies managers which realized this technology need to be adopted and embraced in their daily responsibilities.  This is opening to new opportunities to improve productivity.

I’m honored to be part of TBC Corp organization to allow me to be an “influencer” and provide guidance to this amazing technology.  I thanks Microsoft MVP program for helping me in accomplishing my goals and keeping up-to-date with technology.

I’m planning to do more TBC PowerShell Monthly meetings so they can easily adopt PowerShell and its amazing new features such as Desired State Configuration.





I’m so excited!!

PowerShell Studio working with Datagrid component

I love working with PowerShell. Yes! I admitted. And, I love using SAPIEN Technology “PowerShell Studio” product and specially when I can develop a productive windows application for our Data Center team to use. This make my and their job easy to handle.

SAPIEN_9-4-2015 10-36-39 AM

I just develope a Windows application to handle our company tables updates which monitor failures and recovery via PowerShell. The SAP 3rd party vendor application our team are using is old and doesn’t give any productive way to handle failures and recovery for these nightly tasks. And, here’s where PowerShell comes to the rescue.

So, one on the main components in building this Windows application is the use of the Datagrid. As PowerShell has a very useful Out-Gridview cmdlet but SAPIEN product gives you the .NET Visual Studio look-a-like feel for developing Windows Application.

The recent enhancements to the Out-GridView cmdlet has made it powerful and very useful command:

1. The use of filtering the data.
2. The use of the ‘-passthru” parameter so you select one or multiple items.
3. The use of the “-title” parameter.

SAPIEN5_9-4-2015 10-36-39 AM

SAPIEN6_9-4-2015 10-36-39 AM

Now, getting back to using a Datagrid in a SAPIEN PowerShell Windows Application. PowerShell Studio give us the ability to build Windows application with PowerShell code. For a person that have never use Visual Studio, the learning curve is short thanks to all the Wizards this product have included to get you started.

SAPIEN2_9-4-2015 10-36-39 AM

SAPIEN3_9-4-2015 10-36-39 AM

Now, sometime ago I posted a blog about creating a Windows application with a datagrid. Here’s the link:

After I posted the above blog articles,my MVP friend June Blender, remind me that SAPIEN had a snippet “ConvertTo-DataTable” which can be use to convert the PowerShell object to be loaded to the Datagrid. Here’s the blog link:

SAPIEN4_9-4-2015 10-36-39 AM

But just recently, while build more applications with Datagrid, I discover that the DateTime value returned in thedatagrid is incomplete and only shows the date.

ConvertTo-DataTable_9-4-2015 8-07-31 AM

So, I went back to my own version that build the object for the datagrid to verify that the datetime values are retained.
Then, I decided to create a function “ConvertTo-DataSetObject” which is a smaller version and seem to work with ‘datetime‘ values loaded to a Datagrid.

ConvertTo-DataSetObject_9-4-2015 8-07-31 AM

Then, I added the function to my User snippet section. Here’s the function “ConvertTo-DataSetObject” code snippet:

function ConvertTo-DataSetObject
<# .SYNOPSIS Convert an object to a dataset object for a datagrid. .DESCRIPTION Convert an object to a dataset object for a datagrid by create a global variable "$global:dsObj". .PARAMETER InputObject This parameter will accept any PowerShell variable. .EXAMPLE PS C:\> ConvertTo-DataSetObject -InputObject $psObject

This function is useful when creating object for a datagrid component.
Param (

#Create DataSet Object
$global:dsObj = New-Object System.Data.DataSet;

#Create DataTable
$global:dtObj = New-Object System.Data.DataTable("PSObjTable");
$dtCols = $global:dtObj.Columns; $dtRows = $global:dtObj.Rows;

## - Populate Columns
foreach ($c in ($InputObject | gm -MemberType '*Property').Name)
$x = 1;
($global:dtObj.Columns.Add().ColumnName = $c);

## - Populate Rows:
foreach ($c in $InputObject)
## Initialize row:
$nwRow = $global:dtObj.NewRow();

## Data
foreach ($c2 in ($InputObject | gm -MemberType '*Property').name)
$nwRow[$c2] = ($c).$c2;
## Add row to object:



Here some additional tips when working with datagrid component using the following:

In the Datagrid properties:
1. Add a descriptive text under Captiontext.
2. Set the ReadOnly to True.

Using code to resize all column before display the datagrid:

$datagrid1.PreferredColumnWidth = '120';
ConvertTo-DataSetObject -InputObject $d;
$datagrid1.SetDataBinding($dsObj, "PSObjTable");

Windows 10, PowerShell, Visual Studio Tools and more

More to learn and education never stops.

Now that Windows 10 has arrived take advantage of the new tools to start developing applications. Check out PowerShell version 5.0 and the new Visual Studio 2015 with its tools.

Take advantage of all the free training provided by both Microsoft people and MVP’s by connecting to either Microsoft Virtual Academy and Channel 9.  There are no excuses, just do it!

Click on the following banners for more information; (its free)




Don’t forget to check out the new Microsoft PowerShell at:

Also, take the opportunity to check out our PowerShell community sites, such as:

All these are GREAT source of information about PowerShell.  Along with it you can find other organizations that provide free registrations to webcasts, such as:

And, for affordable online training check Pluralsight Video Library:

So, as you can see, there’s plenty of resources available to start your education in Microsoft Technologies.

Go ahead and don’t wait any longer!

FLPSUG PowerShell August 2015 special events

A live Florida PowerShell User Group session at PC Professor in West Palm has to be rescheduled to a later date. Topic: “Integrating PowerShell in Visual Studio Community“.


For more information and registration:  (Coming soon)

I’m hoping to provide more live presentation at this location in the future. Stay tuned!!

Also, our online Florida PowerShell User Group meeting is pleased to have PowerShell MVP Trevor Sullivan on August 27th at 7:00pm. Topic: “Microsoft Azure Cloud Automation using PowerShell“.

For more information and registration:

Come and join us on any of these free events!

FLPSUG – “PowerShell SQL Server SMO Simplicity” video available

Recorded Thursday – 05/28/2015 7:00pm
Speaker Bio: Maximo Trinidad, (Microsoft MVP – PowerShell)

Topic: “PowerShell SQL Server SMO Simplicity” –
In this presentation I will be showing how easy it is to use PowerShell the extract information from SQL Server. I will cover creating, discovering, and formattting objects which is part of how you use PowerShell. At the same time, I will show the use of SMO to connect and interact with your SQL Server engine to collect information.

Download presentation script file:

Devart dbForge Studio for SQL Server Management Tool

I’ve been using DevartdbForge Studio for SQL Server mainly for helping documenting some of my legacy databases and to build some T-SQL script to embed in my PowerShell scripts.

This is a Great tool to have in your SQL arsenal. Check it out!


Download free for 30 days evaluation copy:

* Customized Skin
* Database Comparison
* .. Many more interesting features.

Here are some pictures showing what I like about this product:












PowerShell in South SQL Saturday 379 was a Great Success


Once again I’m thankful to the organizers to have me speak at this “Awesome” event.  I appreciate the all whom attend my session meking it a Great Success and they got more.  My “PowerShell with Visual Studio SQL Data Tools” session became also a “SMO Simplicity Recap” session. They got two session in one.



Session highlights

1. Visual Studio Community 2013 is the environment to use for integrated development by including Microsoft and Third-Party tools like:
a. PowerShell Tools for Visual Studio
b. Python Tools for Visual Studio
c. SQL Data Tools – Business Intelligent Developement
d. PowerShell Studio 2015  – call from within Visual Studio
e. PrimalXML 2015 – Call from within Visual Studio
d. And many more can be added…


2. Visual Studio integration with either Team Foundation and Github repositories.

3. A quick dive in XML objects.

4. PowerShell error trapping in integrated solution.


5. PowerShell SMO embedding and executing T-SQL code.


During my presentation I demo for the first time how Visual Studio can trap PowerShell errors from within a SSIS Script Task component. And, everyone dropped their mounth. Beside the fact that you can also run and trap script errors by executing by itself.


In the SMO session, I show how useful the ScriptBlock can be when embedding and running T-SQL code while PowerShell reads one object at a time.

To download my presentation and demo files are all available under South Florida SQLSaturday Schedule page:

Once again, THANKS to everyone for your attendance and support.