Category: SQL Server

Yes!  As many SQL PASS members already know, the next SQL Server CTP1 is available for download (http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9).  Go and get it!

I was able to installed it on my Windows 7 64bit version without a glitch. I had a problem installing in a Windows Server 2008 SP2 Virtual Machine, but I’m sure it because I needed access to the internet.  So, make sure you got internet connectivity.

The new SQL Server CTP1 has SQL Server Management Studio Denali GUI is based on Visual Studio 2010.  It’s Impressive!!

There’s a few things to know:

1. Prior installing SQL Server Denali CTP1, you need the following:

1. Install PowerShell V2 – Look for Windows Management Framework: http://support.microsoft.com/kb/968929

b. .NET Framework 3.5

2. The Get-Command bug is fix and there’s are a few new PowerShell cmdlets:

Add-SqlAvailabilityGroupDatabase Join-SqlAvailabilityGroup New-SqlAvailabilityGroup New-SqlAvailabilityReplica Remove-SqlAvailabilityGroup Resume-SqlAvailabilityGroupDatabase Suspend-SqlAvailabilityGroupDatabase Switch-SqlAvailabilityGroup

Of course, don’t forget to include the new set SQL cmdlets from the “MasterDataServices” snapin, already introduce in SQL Server 2008 R2 (See my blog: http://www.maxtblog.com/index.php/2010/09/surprise-sql-server-2008-r2-has-new-cmdlets/)

I’m very excited about this new product and expect to see more PowerShell cmdlets later in the process.    KUDOS to the Microsoft SQL Server Team!!

I will be blogging more later.  Happy PowerShelling!

Go ahead!   Download my presentation and samples scripts from this weekend Worldwide Online Techday session.  The slide will provide useful information and the scripts are loaded with interesting code you don’t want to miss.

Click on the link to download the file: (Please rename the file to a *.zip to extract content)

Again, Thanks to all the organizers and sponsor’s for having me participate in this event.

Happy PowerShelling!!

Finally, after three years, I’ve seen a lot more DBA’s blogging about their solutions combining T-SQL and PowerShell.  KUDOS To Everyone!!!  One thing PowerShell has bring to the table is the ease of getting things done.  Still,  there’s a learning curve everyone has to overcome.  The truth is, the more you use it, the harder is to let go.  So, here’s some things that might help the DBA’s:

1. Be careful with PowerShell ISE.  It does have a bug that you won’t notice until you may try to do a copy/paste of your code into a different editor or into your PowerShell Console.  And, this it due to the editor saving  as a “UNICODE” and not ASCII.  You will seen your lines of code in one single line when viewing the script on a different editor, such as notepad .  Again, just be careful.

By the way, this bug has been document in Microsoft Connect and there’s a few blogs about a work around: http://www.nivot.org/2010/05/21/PowerShellISEHackingChangeDefaultSaveEncodingToASCII.aspx  http://dougfinke.com/blog/index.php/2009/07/20/how-to-work-around-a-powershell-integrated-scripting-environment-bug/

2. Do you know? If you don’t want to install SQL Engine but want to take advantage to use PowerShell and manage SQL Server, then just install “SQL Server Management Studio 2008 R2” and you can use SQLPS.   Yes! Why adding unnecessary processes to consume your system resources.   Here’s the link: http://www.microsoft.com/downloads/en/details.aspx?familyid=56AD557C-03E6-4369-9C1D-E81B33D8026B&displaylang=en

3. SQL Server 2008 R2 may not have lots of commands.  But, try to use Invoke-SQLCmd,  this is a very powerful cmdlet specially when you learn to use it to extract data using T-SQL and Stored-Procedures that generate results.  Keep in mind, you may export the results to different file format.    Check out this example:

## Make sure SQLPS is loaded in your session$s = Invoke-SQLCmd -database master -Query ‘exec sp_who’ $s | Select spid, status, loginame, cmd, blk | out-gridview

This is a good sample of collecting SQL Servers information into a .NET variable and displaying the results using “Out-GridView”.  Also, you could have use “Out-file” command and save it in your drive.  PowerShell give you lots of options to handle information.  Think out side of the box!

4. Don’t be afraid to learning new technologies, specially .NET Framework.  You are probably getting into it if you’re working building SSIS packages.  PowerShell syntax looks very similar to C# and you can extend scripting with .NET Framework.   A good example would be if you want to add to send an email from your script.   The issue is, you can’t run any of the new PowerShell V2 commands under SQLPS and/or SQL Agent.  You will need to use straight .NET code to by pass this limitation.  SQLPS is PowerShell V1 mini-shell (more info:http://max-pit.spaces.live.com/blog/cns!A034D6A0DDC4E64E!842.entry ).  

Example:

## – This is the .NET Framework way to create a send email step and can be ##use for both PowerShell V1/V2 ## Send email with monitoring results $mailmess = new-object system.net.mail.mailmessage $mailmess.From = “FromUser@Company.com” $mailmess.To.Add(“ToUser@Company.com”) $mailmess.Subject = “Type Any Subject here…” $mailmess.Body = “Type Any Detail here…”; $mailmess.Attachments.Add($Filename) $mailclient = New-Object System.Net.Mail.SmtpClient $mailclient.host = “Your.SMTP.PostOffice” $mailclient.send($mailmess) ## – This is the PowerShell V2 cmdlet to send email. ( will not run under SQLPS.exe Send-MailMessage -from “FromUser@Company.com” -to “ToUser@Company.com”  `     -Cc “CCtoUser@Company.com” `     -subject “Type Any Subject here…” `     -Body “Type Any Detail here…” `     -SmtpServer ‘Your.SMTP.PostOffice’ ## Now, If you are using SQLPS.exe (only) then you need to use the .NET Framework.? ## This applies when running the SQLPS.exe console and/or writting the code in the ## SQL Server Agent PowerShell jobstep. ## You can only run PowerShell V2 scripts in the SQL Server Agent if you include to ## execute PowerShell.exe with the script file.  This way the script is running ## under PowerShell v2 process. ## ##  sample for Job Step: ##  PowerShell -nologo ‘C:\Developer\PSscripts\PSSQLMonitoring_Summary.ps1’ ##

There’s more but I think I will leave some for later.  For those in the Florida area, I will seen you soon at the next SQLSaturday #49 in Orlando and at the SW Florida .NET Developers Group in Fort Myers.

Happy PowerShelling!!

Here’s a quick way you can use PowerShell to script a Stored-Procedure with a few one-liners.  This is in its basic form and you can use this code to build your own functions.  I will be including both an SMO and a SQLPS versions.  This is how it’s done:

Here’s the SMO version: (copy/Paste code)

###  – This is the SMO Version  –  ###
## Load the SMO Assembly V1 version (works in V2)
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null

## Connect to the SQL Server and get the Stored-procedures
$MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) “YourServerName”
$List_storedProc = $MySQL.Databases[“Master”].StoredProcedures

## List the first 10 procedures
$List_storedProc | Select -first 10 schema, name | ft -auto

## Display selected script
($List_storedProc | ? {$_.Name -eq “Sp_Configure”}).script()

## Save the script to a file
($List_storedProc | ? {$_.Name -eq “Sp_Configure”}).script() | Out-File z:\Temp\Scripted_StoredProc.sql

 

In the above section, you have all the necessary SMO code to create a SQL script of a Stored-Procedure.  Just change the database name and you will be able to list all the SQL Stored-Procedures for that database. 

Now, here’s the SQLPS version: (copy/Paste code)

###  – This is the SQLPS Version  –  ###
## – Change directory to the Stored-procedures folder
cd SQLSERVER:\SQL\MAX-PCWIN1\DEFAULT\Databases\master\StoredProcedures

## – Directory list the first 10 Stored-Procedures using the “-force” parameter
dir -force | select -first 10

## – Stored the result in a variable
$s = dir -force

## Display selected script
($s |  ? {$_.Name -eq “Sp_Configure”}).script()

## – save the scripted item to a files
($s |  ? {$_.Name -eq “Sp_Configure”}).script() | Out-File z:\Temp\Scripted_StoredProc.sql

 

Using SQLPS.exe or loading the SQLServer provider in your profile to gain access to your SQL engine components, it gives you a slightly edge because all the needed SMO assemblies gets loaded for you.  Even if you install SQL Server 2008, PowerShell itself will not load these SMO Assemblies, it has to be part of your PowerShell profile, a separate script, or in a module.

In SQLPS, or from the PS Console (or ISE prompt) with the SQLPS module loaded, you can use the change directory “CD” path provided in the sample and change the database name with the instance ( ..\Max-PCwin1\Default\..) to yours ( ..\YourServerName\Default_or_YourInstanceName\.. ).  That’s it!

cd SQLSERVER:\SQL\SrvName\DEFAULT_or_InstName\Databases\master\StoredProcedures

Go ahead and try it!   You will appreciate PowerShell a lot.

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:

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:

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

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

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

🙂