Looking back at SQL Server ‘DTUtil.exe’ and PowerShell

Thanks to a SQL Server college that send me an email about a problem he was having understanding how I was using DTUtil in PowerShell and it was giving him the following error message:

The term ‘Create-My-New-Table’ is not recognized as the name of a cmdlet, function, script file, or operable program. C
heck the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:117
+ dtutil /FILE “C:\temp\Store MySSIS Package\Create-My-New-Table.dtsx” /DestS MAX-PCWIN1 /copy SQL;Create-My-New-Table
<<<<
    + CategoryInfo          : ObjectNotFound: (Create-My-New-Table:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

I was able to duplicate the error and after a couple of hours I realized that I failed to mention one critical piece of information.  In my previous blog series about running the DTutil.exe tool in PowerShell, I forgot to mention that you can’t run DTutil.exe in the PowerShell console.  This is one of a few *exe commands that was meant to run under DOS Shell.

That’s the reason why I came up with a PowerShell script that will call/execute a batch file with passing parameters in order to use the DTUtil.exe tool.

Now, to help clear some of the issues I found when trying to run this command with parameters:

1. As I verfiy, DTutil.exe will need to run in a batch file (*.bat) in order to be able to run in PowerShell.   Here’s the sample error message if you try to run DTUtil dos command under the PowerShell Console:

image

2. Don’t use IP address. In my test it didn’t work and in the Microsoft documentation it is asking for “servername”.  So, use the SQL “Servername” or “Servername\Instance”.   My test fail when using my local machine IP address:

image

3. Then, in the “.. /Copy SQL; ” . Don’t leave a space between the “ SQL; ” and the DTS Package name.

4. And, the DTS Package name doesn’t need the extension “*.dtsx”

Here’s example of a valid DTutil command I use to copy my sample DTS Package file to my local SQL Server:

dtutil /FILE “C:\temp\Store MySSIS Package\Create-My-New-Table.dtsx” /DestS MAX-PCWIN1 /copy SQL;Create-My-New-Table

I hope this will help understanding this issues of running this SQL DOS command in PowerShell and take a look at my older 3 part series about “Deploying SSIS Packages” in PowerShell:

Deploying SSIS Packages using DTUtil.exe with PowerShell – Part 1: http://max-pit.spaces.live.com/blog/cns!A034D6A0DDC4E64E!1075.entry

Deploying SSIS Packages using DTUtil.exe with PowerShell – Part 2: http://max-pit.spaces.live.com/blog/cns!A034D6A0DDC4E64E!1102.entry

Deploying SSIS Packages using DTUtil.exe with PowerShell – Part 3: http://max-pit.spaces.live.com/blog/cns!A034D6A0DDC4E64E!1133.entry

I greatly appreciate the emails and I’m glad to be able help in the IT community.

Keep PowerShelling!!

Start using the Help cmdlet in Windows PowerShell

You want to get started with Windows PowerShell?  The answer is at your fingertips.  The truth is, beside all the good books, you got everything you need already included with Windows PowerShell.  Have you use the “Help” cmdlet in PowerShell?  If not, then you are missing some of the most extensive documentation PowerShell have made available since its release.  There’s a LOT of information that can help you get started in understanding and writing PowerShell scripts.

Just type “Help About_*” and press enter:

image_thumb10

As you can see, all this information is available to you.  This will help you understand how to write functions, how to use operators, Aliases, Parameters, Hash Tables, and more.  Don’t be intimidated by all this information.  I recommend you to use “Help” and then you can search on the internet or reading a book for more in depth information.

By the way, this information can be found online under Microsoft TechNet.  Here an example link looking at the Help on “About_Operators”: http://technet.microsoft.com/en-us/library/dd347588.aspx

image_thumb13

So, how many About_* topics can be found under PowerShell V2?  To find out type the following command:

(help about_*).count    (press enter)

image_thumb9

You will find there’s a total of 95 topics.  This is only the “About_*” topics.  So, as you can see,  this information is available at your fingertips.  So, use it and start learning Windows PowerShell.  

Go ahead and try it!!!

Surprise!! SQL Server 2008 R2 has new PowerShell Cmdlets…

Yes!!  I just found out the new version of SQL Server 2008 R2 has new PowerShell Snapin called “MasterDataServicesSnapin” and it will give 7 new cmdlets:

New-MasterDataServicesDatabase – Creates a Master Data Services database.

Get-MasterDataServicesDatabase – Gets information about a Master Data Services database.

Get-MasterDataServicesDatabases – Gets information about Master Data Services databases on a specified instance of SQL Server.

Get-MasterDataServicesDatabaseServerInformation – Gets connection information for a specified instance of SQL Server.

Get-MasterDataServicesIISInformation – Gets information about Internet Information Services (IIS) on the local computer.

Set-MasterDataServicesSystemSetting – Sets the value of a specified system setting in a Master Data Services database.

Get-MasterDataServicesSystemSettings – Gets system settings from a specified Master Data Services database.

Remember! To make these cmdlets available just use the cmdlet to add the pssnapins:

Add-PSSnapIn MasterDataServicesSnapIn

Additional information can be found: http://technet.microsoft.com/en-us/library/ff487033.aspx

Invoke-SQLCmd minor issue running some SQL Stored-Procedures…

First time I notice something strange with “Invoke-SQLCmd”, I was when executing the system Stored-Procedure “SP_Who2” and got the following error message:

PS C:\Users\Max> $sqlWho = Invoke-Sqlcmd “SP_Who2”
Invoke-Sqlcmd : The pipeline has been stopped.
At line:1 char:24
+ $sqlWho = Invoke-Sqlcmd <<<<  “SP_Who2”
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], PipelineStoppedException
    + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Invoke-Sqlcmd : A column named ‘SPID’ already belongs to this DataTable.
At line:1 char:24
+ $sqlWho = Invoke-Sqlcmd <<<<  “SP_Who2”
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], DuplicateNameException
    + FullyQualifiedErrorId : SqlServerError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

PS C:\Users\Max>

I was trying to create a collection of object with the results from my “SP_Who2” and got the error stating “.. A column named ‘SPID’ already belongs to this DataTable..”.   So, I end up scripting out the SP_Who2, finding there are two columns with the same name ‘SPID’ and PowerShell didn’t like it.  I made the change to rename one of the columns to be SPID2 and save the T-SQL script to my PowerShell script file. 

And, the next time the ran my “Invoke-SQLCmd”, I had no problems and got my results so I could manipulate my .NET objects.

So, this is to make you aware that you will experience this minor issues when executing some of the SQL system stored-procedures.  And, for those who wonder… is this a BUG??  I really don’t think so!!  Because, the issue is in some of the system stored-procedures.  Should I submit this issue to the SQL Team to fix all stored-procedures generating columns with the same name?  This could be a major and unnecessary task.  Anyway, you were served!!

Happy PowerShelling!!

QuickBlog: Build a list of SQL Server without SMO…

Here I’m sharing a piece of PowerShell  code snippet which allow you to create a string array of all your SQL Server with their instances.  This code is a .NET namespace and use the enumerator class “SqlDataSourceEnumerator” to give you all available SQL Server instances in your network.

Just go ahead and give it a try:

$SQLInst = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() `
| select @{Expression={if(($_.InstanceName).length -gt 0)`
{$_.ServerName+"\"+$_.InstanceName}else{$_.ServerName}}; Name="SQLName"}
[Array] $MyServers
$MyServers = foreach($i in $SQLInst) {$i.SQLname}

Please, notice I’m using the reverse accent character for line continuation so it’s easy to view.  This is a good code snippet, if you need to provide a script solution that may required to get some SQL information from scheduled task on a server that have no SQL engine installed.

Hint: Now, you may have another way to void typing all your server in your PowerShell script.

Also, take a look at this other blog from Thomas LaRock “SQL RockStar” about “Finding Servers on Your Network” – Excellent material – http://thomaslarock.com/2009/12/finding-servers-on-your-network/  (Thanks to Chad Miller! For providing the link)

Here’s another snippet using above Thomas LaRock blog but I use PowerShell to extend the command “sqlcmd /L”:

$s = sqlcmd /L
[array] $Servers = $null
[array] $Servers = foreach($i in $s){if($i.length -gt 0){$i.Trim()}}
$Servers

Happy PowerShelling!!!

🙂

Technorati Tags: ,

PowerShell Modules – How to create Aliases for my functions?

There’s tons of information about PowerShell Modules on the internet.  Then I realized, in my PowerShell Modules, How do I create Aliases for my functions?  I couldn’t find any information on the internet about Aliases in modules.  So, thanks to Ed Wilson (Microsoft Script Center), he gave me a hint in one of his articles: http://blogs.technet.com/b/heyscriptingguy/archive/2010/02/21/hey-scripting-guy-february-21-2010.aspx

I’m assuming you already know how to create a module *psm1 file. Well, don’t worry!  The following sample script will get you started.  And, if you want to convert a PowerShell script to a Module file, is as simple as copy the *ps1 to *psm1 file.  Basically, that’s it!!

So, before I created my module file, I first created a PowerShell script “HelloWorld.ps1” which contains the following code:

## NAME: HelloWorld.ps1
Function Show-HelloWorld {
 [CmdletBinding()]
   Param($arg)
           Write-Host "Hello World!!.." $arg
};Set-Alias shw Show-HelloWorld -Description "Sample Hello World in module"

After verifying that my function works, then I copy/rename my PowerShell script to PowerShell module file “HelloWorld.psm1” and saved it in my new module folder name “HelloWorld”.

Now, here’s what I found when creating my Aliases for my module:

  1. In the *psm1 file, I could use either: Set-Alias or New-Alias but not both when buidling my manifest.  One catch, I discovered, if I use the ‘-Description’ parameter in either Cmdlets, the function description won’t load with the module.  (its a possible bug)
  2. The last line in the *psm1 file need to have the Cmdlet “Export-ModuleMember” (a shown below) to include the Aliases.
  3. I think is a good practice to named you PS Module script the same way your Module folder.
  4. Create the Module folder name and place the module *psm1 file in it.

Here’s sample script code:

## PS Module Name: HelloWorld.psm1
Function Show-HelloWorld {
 [CmdletBinding()]
   Param($arg)    ?
   Write-Host "Hello World!!.." $arg
};
#-- Set-Alias will work but can't be New-Alias
#Set-Alias shw Show-HelloWorld -Description "Sample Hello World in module"
New-Alias -Name shw -value Show-HelloWorld -Description "Sample Hello World in module"

#-- Need to be included at the end of your *psm1 file.
export-modulemember -alias * -function *

Now, we need to create our new module folder under our users documents “..\WindowsPowerShell\Modules” called “HelloWorld”.   Using our PowerShell console, we need to create our PowerShell manifest *psd1 file using the following line and pressing enter to all other prompts:

New-ModuleManifest -Path 'C:\Users\YourName\Documents\WindowsPowerShell\Modules\HelloWorld\HelloWorld.psd1' -ModuleToProcess 'C:\Users\YourName\Documents\WindowsPowerShell\Modules\HelloWorld\HelloWorld.psm1'
PS C:\Users\Max> New-ModuleManifest -Path 'C:\Users\max\Documents\Win...
' -ModuleToProcess 'C:\Users\max\Documents\WindowsPowerShell\Modules\...

cmdlet New-ModuleManifest at command pipeline position 1
Supply values for the following parameters:
NestedModules[0]:
Author:
CompanyName:
Copyright:
Description:
TypesToProcess[0]:
FormatsToProcess[0]:
RequiredAssemblies[0]:
FileList[0]:

Keep in mind, to start using New-ModuleManifest, you only need to use two parameters: “-path” – location to store your manifest file and “-ModuleToProcess” – location of your module file.

Don’t be afraid to skip the prompts, you can always go back into your recently build manifest file *psd1 and manually filled the information.  After successfully running the “New-ModuleManifest” and created the manifest file “HelloWorld.psd1”, I can proceed to load our new module using “Import-Module HelloWorld”.  If there were any errors, I would need to go back to file my *psm1 module file.

I’m are ready to test the new “HelloWorld” module executing the “Import-Module HelloWorld” im the PowerShell Console:

Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.

PS C:\Users\Max> New-ModuleManifest -Path 'C:\Users\max\Documents\WindowsPowerShell\Modules\HelloWorld\HelloWorld.psd1'
-ModuleToProcess 'C:\Users\max\Documents\WindowsPowerShell\Modules\HelloWorld\HelloWorld.psm1'

cmdlet New-ModuleManifest at command pipeline position 1
Supply values for the following parameters:
NestedModules[0]:
Author:
CompanyName:
Copyright:
Description:
TypesToProcess[0]:
FormatsToProcess[0]:
RequiredAssemblies[0]:
FileList[0]:
PS C:\Users\Max>
PS C:\Users\Max> Import-Module HelloWorld
PS C:\Users\Max> Get-Alias Shw

CommandType     Name                                                Definition
-----------     ----                                                ----------
Alias           shw                                                 Show-HelloWorld

PS C:\Users\Max> shw
Hello World!!..
PS C:\Users\Max> shw Max
Hello World!!.. Max
PS C:\Users\Max>

Now, I can either use my new Cmdlet function “Show-HelloWorld” or just use the Alias “shw” to execute my function.  That’s it!!  I’ve just created, loaded and tested a new module in our PowerShell Console.  There’s room to grow here!! 

Remember, Don’t TAP OUT!!  Happy PowerShelling!!