PowerShell – Page 23 – Max Trinidad

Passing Parameter to a PowerShell executable – Part 1

Posted on August 18, 2011September 6, 2011 by admin

Creating a PowerShell Executable with Parameter(s)

Recently, I’ve been creating some PowerShell script, and intergrating it into my SSIS solution. You may ask, but how?? Simple, if you a product such as SAPIEN’s PrimalForms (or, PrimalScript), you can create an executable out of your script. Yes!! You can create an executable file of your PowerShell script. But, not until recently, I didn’t need to pass parameter(s) my compiled version of my script(s).

Let’s start with some very basic sample of how you create a PowerShell executable script that will accept parameters and display the results on the PowerShell Console prompt.

First the Sample Script, my of “HelloWorld” executable -“HelloWorldExec.ps1″

[sourcecode language=”powershell”]
#========================================================================
# Created with: SAPIEN Technologies, Inc., PrimalForms 2011 v2.0.9
# Created on: 8/17/2011 6:08 PM
# Created by: Max Trinidad
# Organization: ISO Group, Inc.
# Filename: HelloWorldExec.ps1
#========================================================================

Param([string] $firstName, [string] $lastName)
Write-Host "Hello Work!! I’m $firstName $lastName" `
-BackgroundColor Black -ForegroundColor Yellow

#end of script
[/sourcecode]

Now, we need to compiled this script using “Script Packager” under the “Export” tab in PrimalForms 2011.

We are going to select to build the executable for a “WIN32 Command Line” because this script is consider a Console Application and there’s no windows form in it. We click in the “Build” button to cerate the EXE. This exe file will be built in the same folder where your script are stored.

CompileScript2 — Make sure to select WIN32 or WIN64 Command line option

Let’s open my PowerShell Console, change directory to where my executable is located, and run it. Test the executable in both DOS and PowerShell command prompt:

HelloWorldExec — Testing PowerShell executable in both DOS & PowerShell Console

Good! As you can see this is a very simple script. Basically, this script doesn’t need to be a function in order to use the Param() keyword to accept two input arguments: $firstName, and $lastName.

Now, we are ready to build another executable so we can use it on our next blog Part-2 “PowerShell Integration to a SSIS solution“.

For more information about SAPIEN’s PrimalForms 2011, click on the link: http://www.sapien.com/software/primalforms

Great SQL Saturday South Florida Event – PowerShell included!!

Posted on August 14, 2011August 14, 2011 by admin

Yes!! It was a great event with over 400 people showing up, and only two PowerShell Sessions included. Thanks to all SQL Saturday organizers for all the efforts in putting this events together. I’m honor to be part of it. Thanks Scott and Herve!!

Well, here’s both presentations: Session 1 – Let’s Talk About SQL PowerShell, Session 2 – Extendign your T-SQL with PowerShell, and more samples:

Many THANKS!! to all who attended my sessions, and I know that you all were asking for more at the end. This is just an example that there’s a lot to talk about PowerShell and every little time to do it. But, don’t worry!

I’m in the process of setting up some livemeeting event every on the third week of the month at 11:30am(EST). It will a “PowerShell Code Snippet” 3o minutes demo with the purpose of showing block of code by myself and our PowerShell community. Hopefully, this will speed up your learning by reusing these block of code.

Stay Tuned to this blog site for more information.

Just forgot… Congrats to all the winners how found marking on the flyers I provided. I gaveaway 2 SAPIEN PowerShell V2 ebooks (X), 2 PluralSight One Month Training Subscription(PL), and 2 Diskeeper Pro Premier Defrag software for desktop (D).

Happy PowerShelling!!

SQL Server SMO loading Assemblies Gotcha’s

Posted on July 29, 2011July 29, 2011 by admin

One thing I’ve seen most DBA’s blogging about SQL Server and PowerShell is the use of the V1 way to load the SMO Assemblies using:

[void][reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”);

And, there’s a reason for still using this method, which I will show in a minute. Now, PowerShell Version 2.0, introduce a new command “Add-Type” use to simplify loading the assemblies:

Add-Type -AssemblyName “Microsoft.SqlServer.Smo”;

And, works Great! Except, when you’re working with different versions of SQL Server on the same box. You will experience some issues loading the SMO Assembly using the “Add-Type”:

Here’s the error message:

PS [3] > Add-Type -AssemblyName “Microsoft.SqlServer.Smo”;
Add-Type : Could not load file or assembly ‘Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken
=89845dcd8080cc91′ or one of its dependencies. The system cannot find the file specified.
At line:1 char:9
+ Add-Type <<<< -AssemblyName “Microsoft.SqlServer.Smo”;
+ CategoryInfo : NotSpecified: (:) [Add-Type], FileNotFoundException
+ FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.PowerShell.Commands.AddTypeCommand

I’m sure, many if you may have seen this error, and that’s why people sometime go back to use the old faithful V1 format.

Now, you need to be careful because you might be loading the wrong SMO assemblies to use against your database. But, for most cases, I sure it’s OK to load the latest one because it can handle older versions. This is why you most test all your scripts before putting them in your production environment.

Well, the old and faithful V1 “ [reflection.assembly]::Load… ” to load your SMO assemblies work because it grabs the latest version from your windows “Assembly” folder. Here’s an example:

In my environment I do have SQL Server Express, SQL Server 2008 R2 Developer, and SQL Server Denali. So, when I use V1 way to load my SMO, you will notice in the PSConsole that it’s loading the assembly from SQL Server SMO version 11.0.0.0. Notice that using the V2 command “Add-Type”, was trying to find 9.0.242.0 and fail. Even, if this error might be due to a registry issue, this is no the assembly I want to load.

Now, can we still use V2 “Add-Type” command to load the correct version of this assemblies. Hell Yea!!

Here is how:

1. You need to confirm the full path and filename of the SMO DLL, which is located in the “C:\Windows\Assembly” folder.

2. It helps to load the latest SMO assembly using V1 way, then use the following .NET line to list the location of that assembly:
[system.appdomain]::CurrentDomain.GetAssemblies() | Select location

get_SMOassemLoc — Check PowerShell session loaded Assemblies

3. Now, look for your loaded SMO assembly location, then highlight and press ‘Enter’ to copy the line:
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll

4. Use the V2 “Add-Type” command with the “-Path” parameter, and paste (right-click mouse) to add the copied SMO full path. This value must be a string so use quotes around it. Don’t press enter yet!!

Sample line copied but change version to “10.0.0.0”:
“C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll”

In order to load a different version, you need to manually change the version number. In my case, I need to change from 11.0.0.0 to use my SQL Server SMO version 10.0.0.0. This is why you need to check for your multiple SMO version already installed in your system under you Windows Assembly folder.

WindowsSMOassem — Windows loaded assemblies

Finally, press enter on the following command will load the SMO assembly for SQL Server 2008/R2: (this is a one-liner)

Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll”

I know it’s a long line. Remember, you just type it one time and then copy/paste to another script.

Don’t forget to save this line, then open a new PSConsole session, and run it.

Here’s screen shot how is done:

LoadSMOassem10 — New Powershell Session and loaded SMO 2k8 Assemblies

Keep in mind, if you’re wondering what assemblies are loaded during your PowerShell session, then use the following one-liner:

[system.appdomain]::CurrentDomain.GetAssemblies() | Select location

To test your SMO loaded successfully use the following SMO lines to connect to you local SQL instance:

$MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’)

$MySQL.Information.version

Happy PowerShelling!!

IT Camp Saturday 2011 – Let’s Talk About PowerShell

Posted on July 25, 2011July 29, 2011 by admin

Apologies to all waiting for this session to be posted. Finally, I was able to upload my presentation to my SkyDrive account and make it available to all attendees.

Thanks to all who participated in this great IT Camp Event.

Here’s the link:

Please, you can contact me if you have any questions:

Twitter: @MaxTrinidad, via Email at: MaxT@putittogether.net

Using Get-TSQLTableRecordCount dual syntax

Posted on July 5, 2011March 4, 2012 by admin

Here’s one of the function that’s part of my recent SQLDevTools Module – “Get-TSQLTableRecordCount” which is a good example of a multi-purpose command. The main purpose of this function is to assist in displaying the record count of all tables in the database. But, at the same time it can assist you to provide a list of all users databases.

Usage #1 – To show all databases:

In case that you don’t remember the database name in your SQL Server instance, this command give you the ability to list all databases. Here’s an example:

PS> Get-TSQLTableRecordCount -SQLInstanceName MAX-PCWIN1 -ShowAllDatabases
Result:

Processing SQLServer Instance: MAX-PCWIN1, DatabaseName: ALL

SQLServerName DatabaseName
————- ————
[MAX-PCWIN1] AdventureWorks
[MAX-PCWIN1] AdventureWorks2008R2
[MAX-PCWIN1] Developer
[MAX-PCWIN1] ReportServer
[MAX-PCWIN1] ReportServerTempDB

Usage #2 – To Display all tables record counts in the selected Database:

Now you know the database name and you’re ready to use the command to get the record counts fo all your tables. Here’s an example:

PS> Get-TSQLTableRecordCount -SQLInstanceName MAX-PCWIN1 -DatabaseName Developer
Result:

Processing SQLServer Instance: MAX-PCWIN1, DatabaseName: Developer

Parent      DisplayName                    RowCount DataSpaceUsed
——      ———–                    ——– ————-
[Developer] dbo.DatabaseDiskStats                 0             0
[Developer] dbo.Employees                         9           240
[Developer] dbo.ServersToCheckDiskStats           0             0
[Developer] dbo.SurfSpotTable                     4             8
[Developer] Production.ProductDescription       762           144
[Developer] Production.ProductPhoto             101          2208
[Developer] Purchasing.PurchaseOrderDetail        0             0
[Developer] Sales.SalesPerson                     0             0

Use PowerShell with C# to get any file ‘ContentType’

Posted on June 21, 2011 by admin

Here’s an example of how you can use a piece of C# code to create an Static method for getting a file ‘ContentType’. It just took me a few minutes to look for the sample C# code in the internet that would give me the expected result.

I modify the C# code from this blog ( http://codeasp.net/blogs/raghav_khunger/microsoft-net/531/how-to-get-content-type-of-a-file-in-c ) to create my own “”GetContentType” static method in my PowerShell script.

[sourcecode language=”powershell”]

$CSharpCode = @"
using System;
using Microsoft.Win32;
public class Win32file
{
public static string GetContentType(string getfileName)
{
string contentType = "application/octetstream";
string ext = System.IO.Path.GetExtension(getfileName).ToLower();

RegistryKey registryKey = Registry.ClassesRoot.OpenSubKey(ext);

if (registryKey != null && registryKey.GetValue("Content Type") != null)

contentType = registryKey.GetValue("Content Type").ToString();

return contentType;
}
}
"@
Add-Type -TypeDefinition $CSharpCode

[/sourcecode]

Usage: [Win32file]::GetContentType(“C:\Temp\GraphicImage.png“)

Result: image/png

Now, this piece of code serve a purpose, and I’m going to be shown it in my next blog post on “How to import a Document into a table BLOB field?”. YES!! Using PowereShell and much easier than using SSIS.

Stay tuned!

Quick Export SQL Data to a Excel CSV file…

Posted on June 17, 2011June 17, 2011 by admin

Here’s a quick way to get data off on of your SQL Server table and dump it to a CSV file. Only 4 steps:

1. Make sure you are in SQLPS console, or have the community SQLPS (or SQLPSv2) module loaded ( ie. Import-Module SQLPS ) in either your PowerShell Console or ISE.

2. Have you query ready, then save it into a PowerShell variable:

$sql = “SELECT * FROM [AdventureWorks].[Production].[Location]”

3. Next one-liner will build the PowerShell object and exported to a *CSV file: (Execute in localhost only. Use -ServerInstance with the -database parameter if is needed t0 execute query)

Invoke-Sqlcmd -query $sql | Export-Csv -Path c:\temp\excelfile.csv -NoTypeInformation

4. Last line will open the file in Excel:

ii c:\temp\excelfile.csv

This surely beats going to SSIS for quick results!

For more information about the “Invoke-SQLcmd” use the help in PowerShell:

Help Invoke-SQLcmd -detailed

FLPSUG Next Livemeeting – July 13th 6:00PM

Posted on June 15, 2011July 5, 2011 by admin

Florida PowerShell User Group Virtual Meeting.

Next livemeeting, Wednesday July 13th, starting at 6:00PM (45min-1hr), I will be presenting about “Customizing your PowerShell Environment”. Here’s the link if you want to join me:

https://www.livemeeting.com/cc/mvp/join?id=9GGSBP&role=attend&pw=cb*GD587c

Download Presentation here: <will be provided later> (rename *txt file to *zip)

Just released SQLDevTools Module (Alpha 0.1.0)

Posted on June 6, 2011June 6, 2011 by admin

I’m excited to release this new version of SQLDevTools today Monday June 6th. Here’s list of the updates:

##- SQLDevTools Alpha 0.1.0 Released 06/06/2011:
##-
##- Functions fixed;
##   1. Get-TSQLTableRecordCount dual syntax issue not working properly.
##  2. Write-TSQLScriptIndex parameters ‘DropIndexOnly” and ‘CreateIndexOnly’ was mispelled.
##  3. Get-TSQLTableTriggers not work, add modify_date field as last_modified.

## – Updated two function to include rename of destination table: (need to all help)
## 1. Write-TSQLScriptDFConstraint – Help updated
## 2. Write-TSQLScriptInsertSelect – Help updated

## – Add two new functions to change destination table name in the PS Object: (Help included)
##  1. Rename-TSQLScriptedTableName
##  2. Rename-TSQLScriptedTableIndexes
##  3. Get-TSQLUserDefinedDataTypes
##  4. Get-TSQLXmlSchemaCollections
##   5. Get-TSQLUserDefinedFunctions
##   6. Write-TSQLScriptXmlSchemaCollections
##  7. Write-TSQLScriptUserDefinedFunctions
##  8. Write-TSQLScriptUserDefinedDataTypes

Next release -> will include Views, User Stored-Procedures, and Database triggers.

Basically, this completes the basic need to script out most object needed to rebuild a table, or series of tables. It takes into account “Identity”, Computed, and User Defined objects. Also, I included the ability to alter(rename) your scripted destination tablename, and index objects.

For testing these functions, I’ve use Microsoft “AdventureWorks” which is full of scripting surprises. Keep in mind, I’ve use a mix of both SMO and embed-TSQL that assist in generating the results.

Here’s a teaser sample using new function to script User Defined Data Types:

[sourcecode language=”powershell”]
## – Setting variables:
$SQLInstanceName = "YourSQLinstanceName"
$SourcedbName = "AdventureWorks"
$DestinationdbName = "Developer"

## – Sample – Write a UserDefinedDataTypes script, and display results in console:
Write-TSQLScriptUserDefinedDataTypes -SQLInstanceName $SQLInstanceName -DatabaseName $SourcedbName `
-DestinationdbName $DestinationdbName -DropUDDTOnly
Write-TSQLScriptUserDefinedDataTypes -SQLInstanceName $SQLInstanceName -DatabaseName $SourcedbName `
-DestinationdbName $DestinationdbName -CreateUDDTOnly

## Save PS Object to a file, and then view it:
[string] $ScriptToFile = Write-TSQLScriptUserDefinedDataTypes -SQLInstanceName $SQLInstanceName -DatabaseName $SourcedbName `
-DestinationdbName $DestinationdbName
$ScriptToFile | Out-File c:\Temp\SQLScript01.sql
ii c:\Temp\SQLScript01.sql
[/sourcecode]

SampleWUDDT — Sample executing Write-TSQLScriptUserDefinedDataTypes

Stay Tuned!!

Preview to my new set of SQL Developer PowerShell functions

Posted on May 25, 2011 by admin

Here’s a preview on what I’ve been doing for work. I create about 12 new functions to assist me in automating a task to copy table(s) from one database to another. Then, this process will also rebuild the data, indexes, and constraints from the scripts extracted during this process.

All these functions are using embedded T-SQL scripts to assist in the scripting of the SQL Table objects. Finally, I will be ending having a module to load all my functions when is needed.

Just recently, I updated two on the main functions I posted in the Microsoft Script Repository:

1. “Function Write-SQLCreateTable” – Updated – 05/25/2011: Added three new switch parameters so we can create individual scripts for:DropTableOnly, CreateTableOnly, and TurnOffidentity.

2. “Function Write-SQLInsertSelect” – Updated – 05/25/2011: Add ability to detect if there’s an Identilty Column so it can add the “Set Identity_Insert …” line. And, Add set-location go back to your “Home Drive” when the process completed.

These two updated functions require you have the SQLPS module or in won’t work. That’s why I include to do both Import and Remove of SQLPS module. Also, these functions will not script out Indexes, and Constraints.

In my new SQLDevTools Module, all functions will not import/remove the SQLPS module. It will included functions to script out Indexes, Check Constraints, Foreign Keys and even Triggers. Functions name are change slightly, and a test functions script is included to assist in building rebuilding a table in another database. More functions will be added to this module. I will make it available to the community through CodePlex when is ready.

Here’s a preview snapshot of my new functions: