Month: March 2013

Take a look at my SQL Server SMO article “Using SQL Server Management Objects with PowerShell” in the “SQL Server Pro” April magazine is available now on the internet: http://www.sqlmag.com/article/windows-powershell/sql-server-managment-objects-powershell-144832

Sometime is nice to find simple samples that can make life easy. Here’s a quick sample on how you can use this string manipulation to make it readable. Also, I included a way to dynamically create a PowerShell variable from an existing (it may be useful for someone) just as a proof-of-concept.

In this sample I’m going to create a T-SQL script and display it in my PowerShell console. First, let’s create some variables that will be use to replace some values in our string:

[sourcecode language=”powershell”]

## – Dynamically creating a variable with a value:
$SubstituteVariable = "SQLDatabase";
New-Variable $SubstituteVariable -Value "Developer";

$Tablename = "AddressBook";

[/sourcecode]

The above code will create three PowerShell variable but we are end up using only the $SQLDatabase and the $Tablename in our T-SQL script.

The next few PowerShell code will show you a simple ways to initialize a string variable holding the T-SQL scripts.

1. One-liner sample – All acceptable except when using complex Multi-line T-SQL Scripts.

[sourcecode language=”powershell”]

## [1] – Normal string one liner:
$SQLquery = "Select [firstname], [lastname] from [$SQLDatabase].[dbo].[$Tablename]";
$SQLquery;

[/sourcecode]

2. Using Here-String/Splatting, you can change the above sample and make it a multi-line string. This still is a One-liner, and it use the tab in front of the column names. *note: In PowerShell V3.0 the tab will be ignore but in PowerShell V2.0 it will work OK.

[sourcecode language=”powershell”]

## [2] – Using Here-String/Splatting. This string contains manual tabs done in and editor the it was copy/paste to the console: (bug in V3 console only. ISE works.)

$SQLquery1 = @"
Select
[firstname],
[lastname]
from [$SQLDatabase].[dbo].[$Tablename]
"@;

$SQLquery1;
Write-Host "$SQLquery1" -ForegroundColor ‘Yellow’;

[/sourcecode]

3. The last Here-String sample we are using the .NET ‘-f’ string formatter which will allow you to replace the values for {0} and {1} place holder. In this sample code inside the string i’m hard coding tab as `t (tick symbol plus t) which in this case it works OK in both PowerShell V2 and V3:

[sourcecode language=”powershell”]

## [3] – Using Here-String/Splatting with .NET ‘-f’ formatting:
$SQLquery2 = @"
Select
`t[firstname],
`t[lastname]
from [{0}].[dbo].[{1}]
"@ -f $SQLDatabase, $tablename;

Write-Host "$SQLquery2" -ForegroundColor ‘Cyan’;

[/sourcecode]

4. When using a PowerShell editor (ISE or others) an invalid Here-String variable PowerShell will give an error if you try to use tabs on each line of the code: (can’t use white space in editor with Here-String)

[tab] $x = @”
[tab] Testing
[tab] Testing
[tab] “@;

Basically, Here-String variable works great when you want to store a multi-line string but have some caveats:

1. PowerShell v3 console only. Manual tabs are ignored.
2. When using splatting (@”..”@) the @” can begin in any column position but the terminating “@ ends on the beginning of the new line. See sample #3.
3. When using a PowerShell editor Whitespaces in a Here-String block of code are not allowed.