Hello world!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!

Posted in Uncategorized | Leave a comment

Changing to a new blog site – “The PowerShell Front”…

Yes, the time has come to move forward and try a new blog site.

I have decided to use WordPress for my new blog site: The PowerShell Front: http://www.maxtblog.com/

So, this is my last blog post and I will try to move all my old blog later.

Thanks again and see you soon at “The PowerShell Front”

:)

Posted in Uncategorized | Leave a comment

Building MySQLActivity Windows solution…

Here’s another proof-of-concept using what it’s available on your SQL Server and building a windows solution.  In my previous blog, I create a PowerShell script that would allow me to query my SQL Servers with my customized version of the SP_Who2n but just to realized later that it won’t work on SQL Servers 2000.  So, I decided to evolved my script to a Windows solution using SAPIEN technologies “Visual PowerShell 2011”.

This solution will accomplished the following:

1. Able to access all instances names of SQL Server without the need of SMO using .NET Framework.

2. Will display the SQL Server version information.

3. You have the ability to sort any of the columns.

4. Solution will provide instant ability to query for quick viewing.

5. Future feature could be added later to manage SPID’s.

6. This solution assume you have Windows Trusted Authentication.

This is just a starting SQL Activity solution:

Now, in the script tab you will add your code.  In your ‘Designer’ tab, when you double-click on the ‘Button’ control, it will take you to the script tab section and into the $button_Click={… } Script-block function.  This way you just all the code:

On my button code I accomplished two things:

1. Identify SQL Server 2000 so in can pick the correct version of my customized version of SP_Who2.

2. Then, after finding the SQL Server SP-Who2 version run the SQL script, and display the results on the datagrid.

All the .NET code to execute the T-SQL script against SQL Server are non_SMO.  So, there’s no need to install the SQL Server Management Objects.  Here’s a sample snippet of the code use to help build the combo-box with the SQL Server name and their instances:

# – Non_SMO to build the “SQLServername\InstanceName” and convert them to a strings arrays

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

[Array] $Global:s
$Global:s = foreach($i in $SQLInst) {$i.SQLname}

 

And, here’s a code snippet to run a T-SQL query and save the results to a variable:

# – Non_SMO to run a T-SQL query and saving the results to later use.

$SQLqry = "Select @@Version as SQLversion"
$con = "server=$getSvr;database=Master;Integrated Security=sspi"
$sda = New-Object System.Data.SqlClient.SqlDataAdapter ($SQLqry, $con)
$sdt = New-Object System.Data.DataTable
$sda.fill($sdt) | Out-Null   
$tdata = $sdt

 

Now, when using “Visual PowerShell 2011”, you take advantage of intelli-sense to speed your development.  I like the fact that, to populate my datagrid component with the result of my SP_Who2 query, I just needed to add one line of code:

$datagrid1.DataSource = $tdata

I’m ready to build a “MySQLActivity.exe’ solution.

So, as you can see, the possibility to keep improving your PowerShell scripting to a Windows solution gives the ability to be more productive.  I will be adding more feature to this solution.  It you want to test drive it as an executable, contact me on twitter.

Keep PowerShelling!!

Posted in Uncategorized | Leave a comment

SAPIEN – Visual PowerShell 2011 is coming soon….

If you haven’t use SAPIEN PrimalForms 2009 yet then start now, or you can wait until their new Visual Studio 2011 comes out.  Either way this product is so good that will keep you head spinning to find excuses to create new Windows solution for PowerShell scripts.  From including combo-boxes, and creating code snippets to deploying PowerShell executables (*exe) solution.  Yes, this is my favorite feature, you can create executables from your scripts!!

Now, with the upcoming “Visual PowerShell 2011” with new features and components: RSS feed, recently opened files/projects, File Browser, database browser, access to snippets, PS console and there’s section were you can “ADD” Assemblies (hum!!).  Giving you the look of a more Visual Studio-like Editor.

Also, if you continue to install other SAPIEN product then it will displayed them under “installed products”.

But, which version are we looking here??  I’m currently test-driving version 0.0.3.0 Alpha.  Yes!! This is the Alpha version I was given permission to blog about it and I’m loving it.  So, any bug? Yes, but its functional enough to build Windows solutions.

I will be blogging more about “Visual PowerShell 2011” so you can all appreciate the potential of this great product.

I love PowerShell!!

Posted in Uncategorized | Leave a comment

PowerShell “dir” Get-ChildItem – a very simple useful cmdlet…

Lately, I’ve been involved in working with historical data, creating and moving SQL databases backups in my developers environment.  So, in one of my new scripts I started using the .NET Framework “System.IO.DirectoryInfo($YourCurrentFolder)” to get access to the folders both methods and properties.

So, what kind of properties and methods I’m talking about?  Here’s a few:

Methods
1. *.getDirectories()
2. *.getFiles()
Properties
1. name
2. basename
3. fullname
4. extension

 

First, we need to start building our .NET collection of folder objects.  In this case, I’m are trying to find all of my SQL backups on my C: drive, which have the file extension of *.bak.  I’m creating a variable to save my results.

So, I starting working .NET System.IO.Directory namespace:

$Rootname = ‘c:\'; $dbak = New-Object System.IO.DirectoryInfo($Rootname);

$found_baks = $dbak.getfiles(“*.bak”,”AllDirectories”)            # -updated thx to Shay

This few .NET liners will give me a list of all directories with all my *.bak files. 

So, going back to using PowerShell cmdlets I’m using “ Dir “ to search for my *.bak files:  dir C:\ –recurse –filter *.bak  or the unix-like version ‘ ls c: -r -fi *.bak

It started to give me some results but it also started to display some errors during the search process.  The error is due to the Operating System protecting some of the system files areas.

This error will not stop the execution of the cmdlet.  But, if you want to stop the error message,  then use the parameter “ –ea SilentlyContinue “ to skip it.

dir C:\ –recurse –filter *.bak –ea SilentlyContinue

We run a clean search process, save the results into a variable, then identify and check all the *.bak files locations. Select only to view Name with DirectoryName.  Remember, to access you variable information, use the “gm” (get-member) cmdlet using the parameter “ -MemberType property ” to display the Property objects.

Now, we got the list and proceed to properly identify your SQL backups location and later decide what to do with these files.  You can decide to export the information in the variable to an output file: *.txt or *.csv for later use.

Simple and useful!!  Happy PowerShelling!!

Posted in Uncategorized | Leave a comment

My Customized SP_Who2 with PowerShell…

This is to show a Proof-of-Concept on how SQL Server tools can be use with PowerShell V2. 

In this case, I like the way I can use SQL Server 2008 PS cmdlet “ Invoke-SQLCmd to collect information off my SQL engine giving me the ability to transform the results in different ways.   Keep in mind, when you save the result of your query to a variable, it will contain a snapshot of the data at that point in time.  My only purpose in creating a customized version of SP_Who2, is to be able to run my script from my desktop and get the information quickly displayed to my screen and/or generate some kind of reports if necessary.

Getting started:

Here’s brief example of saving the results of a SQL Stored-Procedure to a variable and displaying the results on the console:

The following line “ $dbs | gm –MemberType property ”, will give you a list of the property holding the data you have access to generate the output.  Because of the T-SQL“SP_Databases” stored-procedure generates a three column results, we just display all values by typing “ $dbs ” and pressing enter.  If you want to exclude the ‘REMARK” column then you use the following one-liner: “ $dbs | Select Database_Name, Database_Size “.

Now that you have the variable $dbs containing your results, this data can be queried and displayed in many other ways.  Here’s some PowerShell V2 cmdlets:

1. Out-file

2. ConvertTo-Csv

3. ConvertTo-Html

4. ConvertTo-Xml

5. Out-Gridview

Here’s a One-liner example of displaying the $dbs variable results to an HTML page:

My customized SP_Who2 Stored-Procedure:

In order to use “Invoke-SQLCmd” cmdlet in PowerShell V2, you need to load the SQL Server 2008 snapins and providers into either your PowerShell and/or ISE profile. 

Here’s two links that can provide you information on how-to accomplish updating these files:

1. http://blogs.msdn.com/b/mwories/archive/2008/06/14/sql2008_5f00_powershell.aspx

2. http://msdn.microsoft.com/en-us/library/cc281962.aspx

Now, I was very excited that I can execute T-SQL queries and manipulate the results in many different ways.  So, I tackle the SP_Who2 stored-procedure so I can build some PowerShell functions with it.  But, I was surprise to find an unexpected error when executing it using Invoke-SQLCmd.  

Here’s the error:

Error: Invoke-Sqlcmd : A column named ‘SPID’ already belongs to this DataTable.

My cmdlet is complaining there’s already a property column named ‘SPID’ and it can’t generate the datatable.   So, I went back to use my SSMS (SQL Server Management Studio) to run the SP_Who2 and verify the result sets to find the two identical columns:

So, been a hacker-by-need, I looked into the SP_Who2 T-SQL code (both SQL2K5/2K8) and identify the lines I causing the duplicate name ‘SPID’, changed it to ‘SPID2’.  Then, I can save this code inside my PowerShell script in a string variable using the Here-String ( @” .text_here. “@).   Beside, changing the lines I realized that there might be some differences between SP_Who2 SQL 2K5 and 2K8. 

Here’s the differences, only one line:

1. In SQL Server 2005 SP_Who2, line #118:  “ from master.dbo.sysprocesses with (nolock) ”.

2. In SQL Server 2008 SP_Who2, line #118: “ from sys.sysprocesses_ex with (nolock) “.

I kept the SP_Who2 code from SQL Server 2008 and finish with my custom changes inside a Here-String variable $SQLqry, then we add three more lines to get this Scripts going and Let’s call it *get-SQLActivity.ps1”. 

*Note: This script is using SQL Server 2008 PowerShell “Invoke-SQLCmd” and assuming Windows Authentication.

# ==============================================================================================
# Microsoft PowerShell Source File -- Created with SAPIEN Technologies PrimalScript 2009
# NAME: get-sqlActivity.ps1
# AUTHOR: Max Trinidad , PutItTogether.net
# DATE  : 6/25/2010
# 
# COMMENT: This is a customized version of SP_Who2 use in a PowerShell script to assist in 
# monitoring users activities. When using the $Global:varname will allow you to work with the   
# values pull from the custmomized T-SQL SP_Who2 script.
# ==============================================================================================

# - Create Global variable so you can have access to the data after the PSscripts executes
$Global:Sp_Who2 = $null;

# - Customized T-SQL SP_Who2 
$SQLqry = @"
--======================================================================
-- SQL Source File -- Created with SAPIEN Technologies PrimalScript 2009
-- NAME: SP_Who2_Custom.sql
-- AUTHOR: Max Trinidad , PutItTogether
-- DATE  : 6/24/2010
--
-- COMMENT: Customized SP_WHO2 but the onli change was to rename the dup
-- 'SPID' column to 'SPID2'.
--======================================================================

USE [Master]
GO
/****** Object:  StoredProcedure [sys].[sp_who2]    Script Date: 06/24/2010 09:46:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Alter procedure [sys].[sp_who2]  --- 1995/11/03 10:16
    
Declare    
	@loginame      nvarchar(128) --sysname = NULL

--as

set nocount on

declare
    @retcode         int

declare
    @sidlow         varbinary(85)
   ,@sidhigh        varbinary(85)
   ,@sid1           varbinary(85)
   ,@spidlow         int
   ,@spidhigh        int

declare
    @charMaxLenLoginName      varchar(6)
   ,@charMaxLenDBName         varchar(6)
   ,@charMaxLenCPUTime        varchar(10)
   ,@charMaxLenDiskIO         varchar(10)
   ,@charMaxLenHostName       varchar(10)
   ,@charMaxLenProgramName    varchar(10)
   ,@charMaxLenLastBatch      varchar(10)
   ,@charMaxLenCommand        varchar(10)

declare
    @charsidlow              varchar(85)
   ,@charsidhigh             varchar(85)
   ,@charspidlow              varchar(11)
   ,@charspidhigh             varchar(11)

-- defaults

select @retcode         = 0      -- 0=good ,1=bad.
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))

select
    @spidlow         = 0
   ,@spidhigh        = 32767

--------------------------------------------------------------
IF (@loginame IS     NULL)  --Simple default to all LoginNames.
      GOTO LABEL_17PARM1EDITED

-- select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from sys.syslogins where loginname = @loginame)
	select @sid1 = sid from sys.syslogins where loginname = @loginame

IF (@sid1 IS NOT NULL)  --Parm is a recognized login name.
   begin
   select @sidlow  = suser_sid(@loginame)
         ,@sidhigh = suser_sid(@loginame)
   GOTO LABEL_17PARM1EDITED
   end

--------

IF (lower(@loginame collate Latin1_General_CI_AS) IN ('active'))  --Special action, not sleeping.
   begin
   select @loginame = lower(@loginame collate Latin1_General_CI_AS)
   GOTO LABEL_17PARM1EDITED
   end

--------

IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0)  --Is a number.
   begin
   select
             @spidlow   = convert(int, @loginame)
            ,@spidhigh  = convert(int, @loginame)
   GOTO LABEL_17PARM1EDITED
   end

--------

raiserror(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN


LABEL_17PARM1EDITED:


--------------------  Capture consistent sysprocesses.  -------------------

select

  spid
 ,status
 ,sid
 ,hostname
 ,program_name
 ,cmd
 ,cpu
 ,physical_io
 ,blocked
 ,dbid
 ,convert(sysname, rtrim(loginame))
        as loginname
 ,spid as 'spid_sort'

 ,  substring( convert(varchar,last_batch,111) ,6  ,5 ) + ' '
  + substring( convert(varchar,last_batch,113) ,13 ,8 )
       as 'last_batch_char'
 ,request_id

      into    #tb1_sysprocesses
      --from sys.sysprocesses_ex with (nolock)
      from sys.sysprocesses with (nolock)


if @@error <> 0
	begin
		select @retcode = @@error
		GOTO LABEL_86RETURN
	end

--------Screen out any rows?

if (@loginame in ('active'))
   delete #tb1_sysprocesses
         where   lower(status)  = 'sleeping'
         and     upper(cmd)    in (
                     'AWAITING COMMAND'
                    ,'LAZY WRITER'
                    ,'CHECKPOINT SLEEP'
                                  )

         and     blocked       = 0



--------Prepare to dynamically optimize column widths.


select
    @charsidlow     = convert(varchar(85),@sidlow)
   ,@charsidhigh    = convert(varchar(85),@sidhigh)
   ,@charspidlow     = convert(varchar,@spidlow)
   ,@charspidhigh    = convert(varchar,@spidhigh)



select
             @charMaxLenLoginName =
                  convert( varchar
                  ,isnull( max( datalength(loginname)) ,5)
                         )

            ,@charMaxLenDBName    =
                  convert( varchar
                  ,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)
                         )

            ,@charMaxLenCPUTime   =
                  convert( varchar
                  ,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)
                         )

            ,@charMaxLenDiskIO    =
                  convert( varchar
                  ,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)
                         )

            ,@charMaxLenCommand  =
                  convert( varchar
                  ,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)
                         )

            ,@charMaxLenHostName  =
                  convert( varchar
                  ,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)
                         )

            ,@charMaxLenProgramName =
                  convert( varchar
                  ,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)
                         )

            ,@charMaxLenLastBatch =
                  convert( varchar
                  ,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9)
                         )
      from
             #tb1_sysprocesses
      where
             spid >= @spidlow
      and    spid <= @spidhigh



--------Output the report.


EXEC(
'
SET nocount off

select
             SPID          --= convert(char(5),spid)

            ,Status        =
                  CASE lower(status)
                     When ''sleeping'' Then lower(status)
                     Else                   upper(status)
                  END

            ,Login         = substring(loginname,1,' + @charMaxLenLoginName + ')

            ,HostName      =
                  CASE hostname
                     When Null  Then ''  .''
                     When '' '' Then ''  .''
                     Else    substring(hostname,1,' + @charMaxLenHostName + ')
                  END

            ,BlkBy         =
                  CASE               isnull(convert(char(5),blocked),''0'')
                     When ''0'' Then ''  .''
                     Else            isnull(convert(char(5),blocked),''0'')
                  END

    ,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,'
            + @charMaxLenDBName + ')
            ,Command = substring(cmd,1,' + @charMaxLenCommand + ')

            ,CPUTime = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
            ,DiskIO  = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')

            ,LastBatch = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')

            ,ProgramName   = substring(program_name,1,' + @charMaxLenProgramName + ')
             ,SPID2  = convert(char(5),spid)  --Handy extra for right-scrolling users.
             ,REQUESTID = convert(char(5),request_id)
      from
             #tb1_sysprocesses  --Usually DB qualification is needed in exec().
      where
             spid >= ' + @charspidlow  + '
      and    spid <= ' + @charspidhigh + '

      -- (Seems always auto sorted.)   order by spid_sort

SET nocount on
'
)


LABEL_86RETURN:


if (object_id('tempdb..#tb1_sysprocesses') is not null)
            drop table #tb1_sysprocesses

--return @retcode -- sp_who2
--Select @retcode
"@

# - Enter the SQL Server or SQL Server\Instancename
$Svrname = Read-Host "Enter Servername: ";

# - Building the User Activity variable
$Global:SP_Who2 = Invoke-SQLcmd -ServerInstance $Svrname -database master -query $SQLqry

# - Display results to Out-Gridview
$Global:SP_Who2 | Out-GridView -Title $Svrname

 

At the end, this script will display the result in a grid view windows box.  Let’s test this script:

Now we have a PowerShell SQL Server Activity Monitor script that you can run from your desktop against any of your SQL Server on the network.

AND!!!!, because I’m using the Global scope on my output variable (“ $Global:SP_Who2 “), after you execute the script, you can get to this variable to generate new custom results. 

Check this out!! Let’s run the script.  So, after the script have created the gridview, we are going back and get a different results from our variable selecting only want the first 10 lines and with the SPID >= 50:

$SP_Who2 | ? {$_.SPID -GE ’50’} | select -first 10 SPID, Status, Login, HostName, BlkBy, DBName, ProgramName, LastBatch | ft -auto

So, the variable is still available if you want to save the results in a different format.

Well, that it for now.   AGAIN, this is Proof-Of-Concept on what you can do with such a powerful tool like PowerShell in combination with SQL Server Administration.

I love PowerShell and SQL Server working together!!

Disclaimer: Use this code at your own risk

Posted in PowerShell | 2 Comments

New PowerShell Book is coming for 2011 – “Sams Teach Yourself PowerShell V2 in 24 hours”

Big Announcement!! – (6/23/2010)

Yes!!  I just signed the agreement to start writing a new book for Pearson Publishers – "Sams Teach Yourself PowerShell V2 in 24 hours".
I’m hoping hit the shelves sometime around the first quarter of 2011. 

I’m very excited to start this new project and join my others book author colleges.  I know it’s going to be a big challenge.  But, this is AWESOME!!!
So, stay tuned and watch my upcoming blogs.

Posted in Uncategorized | Leave a comment