VS Code working with SQL Server

VS Code editor works great and specially when using it in cross-platform environment. So, I decided to try using it connect to one of my SQL Server instance. To configure VS Code editor is simple. Make sure to check my previous blog post for information.


Next we need to look which SQL extension are available. In this case I decided to use “vscode-mssql” extension:


After installing, we need to customized their setting by creating connection(s) to our SQL Server. We do this by opening VS Code “User Preferences” and under “Default Settings.json” we search for the “vscode-mssql” settings to be copied over to our working folder “settings.json” file.


I configure two database connections, which you can choose by pressing Ctrl-Shift-E before it execute the SQL Script.


The result from the SQL Script will be displayed on the right side of the editor.


One thing to notice, on VS Code version 1.5.3, there’s an extension called “vscode-icons” which gives nice file icons to the working files.



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:

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

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)
Write-Verbose "[$($global:cnt.ToString("0000"))]Truncate Table dgSAP_Old.dbo.$($t) Process" -Verbose;

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.


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.



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.


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.
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.


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“.


*Note: Bug in VS Code Linux using Integrated Terminal, it won’t scroll up/down.
 ** 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.

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.
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.
That’s it!  All script results will be display in the “Output” pane below the code.
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:
This change in ‘ “powershell”: …” line will point the PowerShell Open Source:
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
param (

[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;

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

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:


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


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.


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.


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:


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.


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:


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