Category: Uncategorized

Post in Twitter:

From @SQLvariant: Pretty Cool: Microsoft SQL Server Code-Named “Denali” CTP3 Release Noteshttp://social.technet.microsoft.com/wiki/contents/articles/3711.aspx #SQLDenali  (07/12/2011 – updated release note link)

(Updated: 07/09/2011 – Microsoft decided to remove the link to this Release Notes.)

From me (@MaxTrinidad): #SQLDenali #PowerShell – Make sure to read the release notes on PowerShell stuff: 1.1, 1.5, 2.4, 5.8, 5.9, 5.11.1, 5.11.3, & 8.1. Awesome!!

From me (@MaxTrinidad): #SQLDenali CTP3 comes with the long awaited SQLPS module… HURRAY!!

Yes!! Read the “Microsoft SQL Server Code-Named “Denali” CTP3 Release Notes”: http://social.technet.microsoft.com/wiki/contents/articles/3711.aspx (What a teaser??)

About the SQLPS Module.  Finally, Microsoft have provided us with a PowerShell Module that will load all available SQL Server PowerShell commands (SQLPS).   But, keep in mind, that this module only load the command use with the SQL Database Engine.  You will still need to manually load the Master Data Services commands, plus whatever they will give us.

So,  for now, it’s a waiting game to see SQL Server Denali CTP3 come out.

WE ARE ALL WAITING!!

Here’s a good example of how useful is SMO in your SQL Server Developement environment.  As I the task repopulating data in a quarterly basis, I need to clear all my staging tables in databases before my import processes.   OK! I could just create a T-SQL query with all my truncate lines.  But, this will force me to open SSMS, file open my T-SQL file, and then execute the query.  Or, I can create simple PowerShell function so I can run from my PowerShell Console, or ISE editor when it’s needed.

In my previous blog, I showed the Get-TSQLTableRecordCount function which allow me to either list all my databases, or display a list of row count for all the tables in the database.  I can use this function to verify that my PowerShell table truncate function is working.

So, when using SMO, how many lines of code it will take me to create this function?  About 11 line of code.   But truely, the heart of this code is only 3 lines, and rest if only adding the necessary code to it a function.

Here’s the basic 3 lines that make the truncate work:

[sourcecode language=”powershell”]
$MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $SQLInstanceName
$Mytables = $MySQL.Databases[$DatabaseName].tables
foreach($table in $Mytables){$table.TruncateData()}
[/sourcecode]

Of course, you need to provide both the SQL Server instance, and the database name

Note: Be careful with this function because is meant to truncate all tables in the given Database.

I went back to SSMS to graphically confirm that all data was removed, but I found out that the database size was still showing.  I knew I had to incorporate the SMO Shrink() method for the database after doing the SMO TruncateData() method on each of the tables.

Here’s an image showing the database size after the truncating all tables:

So, I added the following line after the truncate tables:

[sourcecode language=”powershell”]
$MySQL.Databases[$DatabaseName].Shrink(0,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]’TruncateOnly’)
[/sourcecode]

Image after truncate and running the SMO Shrink() method:

Putting everything together into a basic function performing your tables truncate and database shrink process:

[sourcecode language=”powershell”]
Function Clear-TSQLTruncateAllTables{
[CmdletBinding()]
Param(
[Parameter(Mandatory=$True, Position=0)] [ValidateScript({$_ -ne $null})] [string] $SQLInstanceName,
[Parameter(Mandatory=$True, Position=1)] [ValidateScript({$_ -ne $null})] [string] $DatabaseName
)
$MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $SQLInstanceName
$Mytables = $MySQL.Databases[$DatabaseName].tables
foreach($table in $Mytables){$table.TruncateData()}
$MySQL.Databases[$DatabaseName].Shrink(0,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]’TruncateOnly’)
}
[/sourcecode]

This is just a teaser code so you can see the potential and benefit in understanding SMO in PowerShell.  I will be incorporating a more elaborate version of this function into my SQLDevTools Module available in CodePlex in the next release.

For more information on this SMO DatabaseShrink Method, here’s some links: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.shrink(v=SQL.110).aspx and http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.shrinkmethod(v=SQL.110).aspx

This script hold a total of 4 functions to help setup your missing PowerShell User profiles which are not created when installing Window Management Framework, nor setup during then new Windows 7/2008R2 Operating Systems.

Get this script at the Microsoft Technet Script Gallery: http://gallery.technet.microsoft.com/scriptcenter/717b8ca4-391d-4fef-87fc-153c133606c7

You’ll have the following functions available to setup all your profiles:

1. Set-PSUserProfile – Will create a blank Windows PowerShell User profile.
2. Set-PSISEProfile – Will create a blank Windows PowerShell ISE User profile.
3. Get-PSUserProfile – Will display or edit the Windows PowerShell User profile.
4. Get-PSISEProfile – Will display or edit the Windows PowerShell ISE User Profile.

Also, all four commands use the parameter “-OpenWithEditor” in case you want to
make changes to any of the profile files.  All functions have aliases.

To load all functions use the following command:

PS Z:\YourScriptLocation>. .\ Beginner_PSProfile_Helper_functions.ps1

Use the help command to get information about any of the 4 functions:

PS> Help Get-PSUserProfile -Detailed

See folowing images:

Recognition
To Michael Simmon which his blog gave the idea to create these functions: http://www.ilovepowershell.com/how-to-create-a-powershell-profile/

I hope you’ll find it useful.

First, at today PowerShell Deep Dive sessions, Jeffrey Snover (Microsoft Father of PowerShell) has tweeted:

“ANNOUNCING: #PowerShell Licensed under the Community Promise http://tinyurl.com/3d5vcf3 WHOOOOT!!”

Then, SAPIEN Technologies tweeted: (Check their blog!!)

“New: : It’s All New! http://www.sapien.com/blog/2011/04/18/its-all-new/”

Finally, PowerShell MVP – Jaykul, has release in codeplex – Show-UI is the merger of PowerBoots and WPK.

“The new url is official http://showui.codeplex.com/ #PowerBoots #PowerShell #WPK #WPF #GUI #Threading”

There’s lot to learn and no time to waste.  Happy Scripting!!

 

I created the function “Get-dbTableRecordCount” to get both the tables record and data space used from a selected table.  A the same time, if you can’t remember the databasename, then you can use the parameter ” -ShowAllDatabases Y ” to only list all databases from the provided SQL Server Instance name.

Download “Get-dbTableRecordCount” Function script at the MS Script Gallery at: http://gallery.technet.microsoft.com/scriptcenter/c606e406-af61-4552-b686-2c9dc17ab06e

A few requirements will remain:

1. Must have either SQL Server 2008 (or above) installed, or the have the free version of SQL Server Management Studio 2008 R2 (no SQL Engine) installed.

2. And, to download either,  or both of the SQLPS modules.  Instructions are provided with the description of the function in the MS Script Gallery.

This functions will have the following 3 parameters:

1. ” -SQLInstanceName “ – This is mandatory entry. Enter your SQL Server instance name.

2. ” -DatabaseName “ – Optional but required with for the “-SQLInstanceName” parameter.

3. ” -ShowAllDatabses “ – Optional but only requiered with the “-SQLInstanceName” parameter and not with “-DatabaseName”.

An Alias name is provided: “gtrc”

Also, remember you can use “help”, or “get-help” commands to read documentation about this function.

Sample pics: (show databases only)

I just posted a new PowerShell function to help get information about your SQL Server(s).  Following Don Jones recent post about “Inventory SQL Server Versions on Multiple Computers” this is just to show you, if you have SQL Server 2008 (R2 or above), then you can use SMO which is already installed with SQL.

Check out *”Get-MSSQLVersion” at Script Gallery: http://gallery.technet.microsoft.com/scriptcenter/5f763a6e-23bf-46be-a837-13e5005acb72

Please, make sure to meet the requirements to use this function.  More information in the Script Gallery post.

Now, this new function include three cmdlet parameters that will affect your results.

• ” -SQLServerOption ” – This parameter will allow you to: 1) Individually get version information about one, or a series of SQL Server, or 2) using the ” -SQLServerOption all ” parameter, will get you a list of all SQL Servers on the network.
• ” -DateTimeStamped ” – *This parameter only accepts Y, any other character(s) will be ignored. This will results in writting the start and end date/time for running this command.
• ” -HideBlank ” – This parameter only accepts Y, any other character(s) will be ignored.  This will ignored all blanks items selected with the ” -SQLServerOption ALL” because   you may not have permission to access the SQL Instance.

By the way, I automatically included some SQL Server counter to let you know how many server(s) it found and the how many are listed in the results.

*Note: I included the Date/Time stamped is because this function will take some time to complete. 

Sample picture:

Practical Use: – The following line will create a text file with your information:   

PS> gmsv -SQLServerOption ALL | Out-File c:\MyFolder\MySQLVersionInfo.txt

Also, you can save the results to an object, then create a csv file.  Here’s the starting point for your CSV:

Go ahead, and complete the task…  I’m Just Saying!!

 Have fun Scripting!!

I love the PowerShell collaboration in the IT community.  One of my twitter followers reminded me about the “Format-Table” command  that allows you to group your collection using the parameter “-GroupBy” which work beautifully.  There’s no need to add the “ForEach” command.  I trully forgot to the check for this parameter. 

This change in my script will saves be  a few more lines of code.and here’s the final result.  Here’s the script:

[sourcecode language=”powershell” 1=”wraplines"false"”]
## – Stored T-SQL modified script into a string variable
$sqlQuery = @"
SELECT
s.name+’.’+OBJECT_NAME(c.OBJECT_ID) as SchemaTableName
,c.name AS ColumnName
,SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TypeName
,c.max_length
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
JOIN sys.tables as t2 on t2.object_id = c.object_id
JOIN sys.schemas as s on s.schema_id = t2.schema_id
ORDER BY c.OBJECT_ID;
"@

## – Load the SQLPS module for Denali ( for 2008/200R2 is SQLPS)
Import-Module SQLPSv2
$SavedResults1 = Invoke-SQLCmd -ServerInstance "ISO-DESKTOP-65" -Database "ReportServer" -Query $sqlQuery
$SavedResults1 | ft -auto -GroupBy SchemaTableName
[/sourcecode]

And, here’s the sample PowerShell Console result:

This is a good example of how PowerShell can keep simplifying your script as yiou continue to grow.

Especial THANKS to David Moravec (http://www.powershell.cz) for tweet to giving me the tip to enhance this script.   Now, you can download both versions:

<>

OK!  Calling all Florida West and Central Coast IT professionals.  The first Florida ITCampSaturday is coming to Tampa, Florida on Saturday March 19th.  It’s an all day free event you don’t want to miss.

Topics can be anything applicable to SysAdmins or IT Pros including: SharePoint, System Center, SQL Server, PowerShell, Active Directory, or Windows 2008 R2 and Windows 7.

Call for Speakers is Open Now…  Here’s the link to register your session(s): http://itcampsaturday.com/tampa/2011/01/13/call-for-speakers-open/

Don’t be left out!  Register now at: http://itcampsaturday.com/tampa/

Have fun and learn more!