PowerShell – SQL Server SMO Drop table in Database

In one of my previous blog, I mention about issue I found with SQL Server 2014 SMO not supporting the *.TruncateData() method. So, while working on one of my company SQL Server migration I need my script to cleanup some tables and I ended up doing a quick script to drop a selected list of tables.

Before we have the script, we need to create the list of tables. Basically, I had previously create two PowerShell objects and use the compare-object cmdlet to identify only the same tables found in both objects.

Then I can proceed to drop tables from the list object labeled $ToDelete:

## - Create compare results of list of tables:
$list = compare-object -ReferenceObject $newSAP -DifferenceObject $oldSAP -IncludeEqual;

## - Save list for future reference:
$list | Where{ $_.sideindicator -eq '==' } | out-file c:\temp\SAP_tablesToDelete.csv;
$list | Where{ $_.sideindicator -eq '=>' } | out-file c:\temp\SAP_tablesMissing.csv;

## - Only get the list of tables to be deleted:
$toDelete = $list | Where{ $_.sideindicator -eq '==' } | Select-Object InputObject;

## - Using SMO steps to work with tables:
$SQLServerInstanceName = 'TSQLDSP01'; $SQLServerDatabasename = 'dgSAP';

## - Connect to SQLServer instance and get table name list:
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;
$SQLSrvObj = new-object('Microsoft.SqlServer.Management.Smo.Server') $SQLServerInstanceName;
$tables = $SQLSrvObj.Databases[$SQLServerDatabaseName].tables;

## - Step to go through tables list and the drop tables:
$global:cnt = 1; [array]$results = $null;
$results = foreach ($t in $tables)
{
## - Loop through tables to be drop:
foreach ($i in $toDelete)
{
## - Chekc each table $t in the $i selected list:
if ($t.name -eq $i.InputObject)
{
## - Drop table process:
## -> $t.Drop();
$r = "[$($global:cnt.ToString('000'))] - Sql $($t.name) = Object $($i.InputObject) removed"
Write-Verbose $r -Verbose;
$r; $global:cnt++
};
};

## - Refresh tables object without dropped tablename:
$tables.Refresh();
};

This *script will assist in the third-party application to recreate the tables meeting for our migration.

*Note: Just in case, I commented out the line having the $t.Drop() method.

PowerShell – SQL Server 2014 SMO TruncateData() Workaround

As I was still puzzle why the SMO *.TruncateData() was missing in SQL Server 2014, I needed to find a quick workaround to continue with my data migration.

Of course, here comes T-SQL scripting to the rescue:


Truncate Table databasename.schema.tablename
GO

Basically, subtitute the SMO .TruncateData() with few lines of T-SQL statement, and have PowerShell run the code against SQL Server. The script is shown below:

## ----------------------------------------- ##
## - Using SMO steps to work with tables:
$SQLServerInstanceName = 'TSQLDSP01'; $global:SQLServerDatabasename = 'devMaxText';
$global:DbSchema = 'dbo

[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;
$SQLSrvObj = new-object('Microsoft.SqlServer.Management.Smo.Server') $SQLServerInstanceName;
$tables = $SQLSrvObj.Databases[$global:SQLServerDatabaseName].tables;

$global:cnt = 1;
foreach ($t in $tables.name)
{
$tsqry = @"
Truncate Table $($global:SQLServerDatabasename).$($global:DbSchema).$($t)
Go
"@;
#$SQLSrvObj.Databases[$SQLServerDatabaseName].ExecuteNonQuery($tsqry);
Write-Verbose "[$($global:cnt.ToString("0000"))]Truncate Table dgSAP_Old.dbo.$($t) Process" -Verbose;
$global:cnt++;
};

This *script will assist in the third-party application to load date back to the tables for our migration.

*Note: Just in case, I commented out the line having the .ExecuteNonQuery() method.

Linux Sky Application for “Skype for Business”

Yes! As started to spend time learning Linux I found this Linux application that allows me to use my Office365 account and share my desktop to other users using “Skype for Business”. I find this very interesting now that I could use this application to setup online presentations right off my Linux system.

sky01

Here’s the link to check it out: https://tel.red/linux.php

But, definitely is oriented for business organization that can afford the paying an annual fee of $45/per user activation. In the meantime, you can test this application which is limited a 2 minute limit on each call or screen share. They will send you a key to use within 24 hours.

Another missing feature, if you compare it to “Sky for Business”, is the session recording capability.  I mean, still is a great Linux app that will connect to you Skype members.

I can tell that their technical support will answer questions promptly and their email response was very good. They can also help and discuss any arrangement in order to get you going.

For now, their application only support domain users. But, I was told, that support for non-domain users will be available soon.

Installation was a challenge for me. Just because I’m not a Linux expert but I manage the figured out what was needed to complete the installation on my Ubuntu 16.04.1 desktop. They offered any Sky installation for various Linux distro.

Check out the images!  I think this product has potential for connecting Windows and Linux users.

ubuntusky

skylinux2_2016-09-11_19-56-21

I’m hoping to be able to at least do one of my Florida PowerShell User Group meeting with this product.

VS Code running PowerShell – Terminal session 3/3

Here’s the final blog. I’m going to show the third way to execute PowerShell:
1. VS Code – PowerShell Extension and Debug feature.
2. VS Code – Code Runner Extensions for PowerShell.
3. VS Code – Terminal session.
mslinux

 

VS Code – Terminal session

In Windows, we are configuring the VS Code “Integrated Terminal” to instead of executing Windows Cmd shell or Linux Bash, to use PowerShell Console.Then again, this is a quick change in the user “settings.json” in your script working folder.
vscodeps3_01_2016-09-23
Notice, by using the two forward slashes ” // … “, I commented out the original line that will execute Windows PowerShell.

 

After the change is made and restarted VS Code, use either the menu option “View | Integrated Terminal“, or just use key shortcut ” Ctrl + ` ” to open PowerShell.
vscodeps3_02_2016-09-23

 

In Linux, the *Terminal line change from “terminal.integrated.shell.windows” to “terminal.integrated.shell.linux“.  Of course, the PowerShell path change to “/opt/microsoft/powershell/6.0.0-alpha.10/powershell“.
vscodeps3_03_2016-09-23

 

*Note: Bug in VS Code Linux using Integrated Terminal, it won’t scroll up/down.
vscodeterminalbug
 ** Workaround for Scrolling Up/Down: Use Crtl-Shift-Up or Ctrl-Shift-Down.

 

Bonus on Extensions

These three blogs give you an insight on how to customized your VS Code editor.  So, every installed extension you can customized it on you “settings.json” file.  Where you find these settings? Look at the Default Settings (which are read-only) then look for the extension section.  You can then copy/paste the line you need into your custom “settings.json” and “launch.json” file.
Now, keep an eye in upcoming release of VS Code because it will come with a PowerShell integrated which is something everyone waiting.

VS Code running PowerShell – Code Runner Extensions 2/3

As, I recently went back to try using VS Code in Linux, I’m going to show the second way to execute PowerShell:
1. VS Code – PowerShell Extension and Debug feature.
2. VS Code – Code Runner Extensions for PowerShell.
3. VS Code – Terminal session.
powershell_linux

VS Code – Code Runner Extensions

We need to proceed to install the “Code Runner” Extension. Take a look at this extension information which can be use with many other script languages.
vscodeps2_01_2016-09-22
In Linux, to execute PowerShell using “Code Runner“Extension:
1. open the script.
2. Right-click anywhere in the script area.
3. Then, select “Run Code” to execute.
vscodeps2_03_2016-09-22
That’s it!  All script results will be display in the “Output” pane below the code.
vscodeps2_04_2016-09-22
Now, in Windows OS, Windows PowerShell is the default executable path to run the script. If you want to change from Windows PowerShell to use the PowerShell Open Source then you need the change the executable path line in “Code Runner” settings.
Here’s a sample view of “Code Runner” modified code in the user “settings.json” file:
 vscodeps2_02_2016-09-22
This change in ‘ “powershell”: …” line will point the PowerShell Open Source:
vscodeps2_05_2016-09-22
Notice, by using the two forward slashes ” // …“, I commented out,the original line that will execute Windows PowerShell.
Next blog post, I’m going to cover the third way to execute PowerShell – “VS Code – Terminal Session“.

SQL Server 2014 missing SMO .TruncateData() method

Hum! I just found out that in SQL Server 2014 (SP2 installed), while migrating from SQL Server 2005, one of my PowerShell script (I’ve been using for a long time) that uses SMO to truncate tables. But, when running it against a SQL Server 2014 database, I’m getting an error:

“..this property is not available on SQL Server 2014.”

For mi surprise, I ran the same PowerShell script against SQL Server 2016 and it works fine.

Here’s a sample function that does a Truncate to all my tables in a database:

function Clear-DatabaseTable
{
[CmdletBinding()]
param (
[string]$SQLServerInstanceName,
[string]$SQLServerDatabasename
)

[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;
$SQLSrvObj = new-object('Microsoft.SqlServer.Management.Smo.Server') $SQLServerInstanceName;
$tables = $SQLSrvObj.Databases[$SQLServerDatabaseName].tables;

## Database tables rowcounts before truncate:
$tables | Select @{ Label = "TruncatedTable"; Expression = { $_.Schema + "." + $_.Name }; }, `
@{ Label = "PrevRowCount"; Expression = { $_.rowcount }; } | FT -auto

$ReadThis = `
"**********************************************************************`r`n " `
+ "Do you really want to Truncate all tables in $SourcedbName ? Y/N `r`n" `
+ "**********************************************************************`r`n ";
$Cont = Read-Host $ReadThis;
[console]::ResetColor();

if ($Cont.ToUpper() -eq 'Y')
{
foreach ($t in $tables)
{
Write-Verbose "Truncating Table $($t)" -Verbose;
$t.TruncateData();
};
};
};

Load this function into you session and then run the following command:

Clear-DatabaseTable -SQLServerInstanceName 'MTRINIDADLT2\MSSQL2K16A' `
-SQLServerDatabasename 'devMaxTest';

The results against SQL Server 2016 were successful:

sqltruncatedata_01_2016-09-21_9-21-18

But, when running against SQL Server 2014, I get the error:

sqltruncatedata_02_2016-09-21_9-21-18

I logged this issue under SQL Server UserVoice: https://manage.uservoice.com/forums/108777-database-manager-for-sql-azure-feature-suggestions/suggestions/16286755-sql-server-2014-is-missing-smo-truncatedata-met

Please Vote on it!

VS Code running PowerShell – Debug 1/3

I recently went back to try using VS Code. Now that I’m fully invested with PowerShell Open Source in Ubuntu Linux 16.04.1 Desktop.  During trails and errors I found at least three ways to execute PowerShell:

1. VS Code – PowerShell Extension and Debug feature.
2. VS Code – Code Runner Extensions for PowerShell.
3. VS Code – Terminal session.

VS Code Extensions

First, we need to add a couple of extensions:
1. PowerShell
2. Code Runner

In below image, we are installing Python Extensions.  In Linux, having Python installed will be of great benefit.

powershellandpythonextensions

These are a most-to-have extensions to work with PowerShell. We are going to use the Debug feature.

Using VS Code Debug

First, we are going to use VS Code debug option to run PowerShell Out-Of-The-Box. This way we can be use debug to execute and step thru the PowerShell script code.

Open the folder were the scripts are going to be stored. The first time using the Debug it will ask to create the “launch.json” which is needed in order to execute and debug the script.  Accept the default values as you can change them later if needed.

launchjson

By the default, in Windows OS, it will execute Windows PowerShell ver 5.x.  In Linux, it will run the PowerShell Open Source. Keep in mind, I’m on a Linux system.

Next, is to create a new “settings.json” file, go to VS Code menu click on “File | Preferences | User Settings“.  In order to execute PowerShell Open Source, which is in a different folder, we need to create a “settings.json” file with the following code:

settingsjson_01

So, you’ll ended up with two *.json files in your script folder.  Then, you can customized these file to work with your script code when it gets debug.

setlaunchjson_04

Bonus Note: On a Windows System, if you want to customize VS Code to use PowerShell v6.0.0-alpha.x, just add the following line in the “settings.json” file:

changepowershelexepath

Next blog post, I’m going to cover “VS Code – Code Runner extension

PowerShell for every system is becoming a reality!

Powershell_2016-08-18_11-29-36

Yes! Now, PowerShell will be available CrossPlatform to help any system automation need. As a Linux newbie, I’m excited about this announcement.

https://msdn.microsoft.com/en-us/powershell/mt173057.aspx

So, if you have Linux Systems such as Ubuntu, CentOS, and, even for Mac OS X 10.11.

Check the link: https://github.com/PowerShell/PowerShell

Just go and get it. But, keep in mind, this is a work in progress.

This is PowerShell Core only, Alpha Version 6.0.0.9 and there’s a lot of work to do. Bugs and feedback are been submitted as the community are contributing for it success.

This version is also available for Windows 10 / Server 2016 and Windows 8.1 / Server 2012 R2. You can have it side-by-side with the current version of PowerShell.

WinPoshAlpha_2016-08-18_10-22-33

Linux Installation Steps

Just follow the instruction provided in the GitHub PowerShell page, look for your Linux version, and follow the link provided for downloading installation package: https://github.com/PowerShell/PowerShell/blob/master/docs/installation/linux.md#ubuntu-1604

In my case, I took the setup for Ubuntu 16.04:

1. Open a Terminal session.

2. run the following three commands:
$ sudo apt-get install libunwind8 libicu55
$ cd Downloads
$ sudo dpkg -i powershell_6.0.0-alpha.9-1ubuntu1.16.04.1_amd64.deb

The first lines installs two dependencies needed before installing the package.  Then, changing the folder to “Downloads”.

For those new to Linux, keep in mind, all command line sample is case-sensitive or it will not run.

Run PowerShell

After the installation is completed then you are ready to run PowerShell from any of the Linux Terminal applications.

  1. Open the “Terminal” application
  2. Then, type “powershell” (all in lowercase).

PoshLinux

And, you try to run some cmdlet straight from Linux. But, are still some limitations, and there are some parameter that won’t work.  Remember! This is an Alpha version.
PoshLinuxGetHelpErr_07
You can’t build a remote PSSession (yet) and any use of the parameter “-computername ” will be limited to Linux systems.
GetprocessLinuxErr_01

So, a word of caution! if you try to use a cmdlet from Windows to access a Linux system, your Windows PowerShell session will crash.

GetprocessLinuxErr_02
All these issues will be handle soon.

Linux – Creating an Alias to call ‘powershell’

The following Linux commands will help to create the alias to call ‘powershell’. This way so you don’t type the whole name.  The alias will be ‘Ps’ because there already is an existing one call ‘ps’.

Below are all the steps needed to create the alias:
1. Create a linux commands file by opening the gedit app.
$ sudo gedit Posh.sh
Add line -> powershell

2. After saving the file go and add permission.
$ sudo chdmod 755 Posh.sh

3. Need to make it Alias permanent and available when opening the Terminal app.
$ sudo gedit /etc/bash.bashrc
Add line -> alias Ps=/home/maxt/Posh.sh

4. This reset bash.
$ bash

5. Testing the new alias
$ Ps

Now, you just type ‘Ps’ (case-sensitive) to open PowerShell in Linux. This way you’ll have the flexibility of using PowerShell when needed.

Linux PowerShell Editor – VS Code

In Linux, open Visual Studio Code:
VSCODE2016-08-18_11-10-03

From with in VS Code, you can open the “Terminal” session by using the hotkey combination “ctrl” +”\” +” ` “.  So, at the $ prompt you can either type ‘powershell‘ or our new alias ‘Ps‘.

You can make your VS Code Terminal session or load PowerShell automatically by doing the following steps: Click “File -> Preferences -> User Settings”.
Then, on the opened Settings.json file and change to the following lines:

** for Linux **
LinuxVSCode_2016-08-18_11-22-10

** for Windows **
WinVSCode_2016-08-18_11-22-10

Remember to safe the file and you are set to work with PowerShell to build scripts.
This is an Awesome Time to learn PowerShell!

 

 

Important PowerShell Links To Remember

Powershell_2016-07-28_12-37-40

Just a reminder!  Here are some PowerShell resource link to save.

Microsoft just recently announce the PowerShell “Windows Management Framework 5.1 Preview“. Check the PowerShell Team Blog:

Announcing Windows Management Framework (WMF) 5.1 Preview

In addition, if you encounter any PowerShell bugs and want to provide any suggestions, below is the link you can use:

*note: Keep in mind. WMF 5.1 Preview is not supported in production environments.

More resource links:

Windows PowerShell Home Page
https://msdn.microsoft.com/powershell

Windows Management Framework 5.1 Preview
https://www.microsoft.com/en-us/download/details.aspx?id=53347

Don’t forget to check WMF 5.1 Release Notes
https://msdn.microsoft.com/en-us/powershell/wmf/5.1/release-notes

SAPIEN Technologies ‘PowerShell Studio 2016’ – The premier editor and tool-making environment for Windows PowerShell
https://www.sapien.com/software/powershell_studio

And forgot!!  For everyone who love to work with PowerShell and SQL Server:

Go and keep learning about PowerShell!!

Microsoft SSMS July 2016 Hotfix is Available now

Go and get it.  Of course, No SQLServer PowerShell update on this hotfix. But you’ll need this update.

MSSMS_02_2016-07-15_14-32-33

Read more on this link for more information: https://msdn.microsoft.com/en-us/library/mt238290.aspx

If you are still interested of see what’s new in this July Edition of SSMS, check the following Microsoft Blog site:
https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/30/sql-powershell-july-2016-update/

MSSMS_01_2016-07-15_14-32-33