Simple powershell script to Bulk Load csv into a SQL Server table.

#Read csv file 
#open connection to database using bulkcopy
#convert array to data table
#bulkload data into table
#note: column sequence and data types should match

function Get-Type 
{ 
    param($type) 
 
$types = @( 
'System.Boolean', 
'System.Byte[]', 
'System.Byte', 
'System.Char', 
'System.Datetime', 
'System.Decimal', 
'System.Double', 
'System.Guid', 
'System.Int16', 
'System.Int32', 
'System.Int64', 
'System.Single', 
'System.UInt16', 
'System.UInt32', 
'System.UInt64') 
 
    if ( $types -contains $type ) { 
        Write-Output "$type" 
    } 
    else { 
        Write-Output 'System.String' 
         
    } 
}


function Out-DataTable 
{ 
    [CmdletBinding()] 
    param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject) 
 
    Begin 
    { 
        $dt = new-object Data.datatable   
        $First = $true  
    } 
    Process 
    { 
        foreach ($object in $InputObject) 
        { 
            $DR = $DT.NewRow()   
            foreach($property in $object.PsObject.get_properties()) 
            {   
                if ($first) 
                {   
                    $Col =  new-object Data.DataColumn   
                    $Col.ColumnName = $property.Name.ToString()   
                    if ($property.value) 
                    { 
                        if ($property.value -isnot [System.DBNull]) { 
                            $Col.DataType = [System.Type]::GetType("$(Get-Type $property.TypeNameOfValue)") 
                         } 
                    } 
                    $DT.Columns.Add($Col) 
                }   
                if ($property.Gettype().IsArray) { 
                    $DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1 
                }   
               else { 
                    $DR.Item($property.Name) = $property.value 
                } 
            }   
            $DT.Rows.Add($DR)   
            $First = $false 
        } 
    }  
      
    End 
    { 
        Write-Output @(,($dt)) 
    } 
 
} #Out-DataTable

$file="C:\samplecsv.csv"
$dbserver="ENTER DB SERVER NAME"
$database="ENTER DB NAME"
$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=$dbserver;Integrated Security=SSPI;Initial Catalog=$database");
$cn.Open()
$bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn
$bc.BatchSize = 10000;
$bc.BulkCopyTimeout = 1000
$bc.DestinationTableName = "ENTER TABLE NAME WITH SCHEMA NAME"

$data = Import-Csv $file | Out-DataTable

$bc.WriteToServer($data)

List installed SQL Server instances with version on local or remote computer [Powershell]

 

 

 

$MachineName = . # Replace . with server name for a remote computer

$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey(LocalMachine, $MachineName)

$regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL" )

$values = $regkey.GetValueNames()

$values | ForEach-Object {$value = $_ ; $inst = $regKey.GetValue($value); 
			  $path = "SOFTWARE\\Microsoft\\Microsoft SQL Server\\"+$inst+"\\MSSQLServer\\"+"CurrentVersion";
			  #write-host $path; 
			  $version = $reg.OpenSubKey($path).GetValue("CurrentVersion");
                          write-host "Instance" $value;
			  write-host  "Version" $version}

Get Server Lifecycle Info from MS Support into a table

As part of database life cycle management process organizations ensure database is patched and kept up to date to ensure database system is secured from any vulnerabilities and to stay in support life cycle.

Microsoft Support Life cycle provides a consistent and predictable guidelines for product support availability when a product releases and throughout that product’s life. By understanding the product support available, customers are will be able to maximize the management of their IT investments and strategically plan for a successful IT future.

To know the current status of Microsft life cycle is not easy, MS provides an Excel file that contains all the build versions together with their current support life cycle stage for 2005 through the current version is available.(please note as we speak MS ended support for 2005)

So put to together a powershell script that will load the excel file into SQL Server table.

  1. First step download the excel file.
  2. Second create a table and procedure in any Database to hold the SQL Lifecycle data
  3. load the Excel file into the Table.

Link is available on this page.(https://support.microsoft.com/en-us/kb/321185) (http://download.microsoft.com/download/7/C/1/7C1BA242-14B5-48AE-9A99-7CE4FE9DAAF9/SQL%20Server%20Builds%20V3.xlsx)

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SQLServerBuilds](
	[Version] [varchar](100) NOT NULL,
	[BuildNumber] [varchar](100) NOT NULL,
	[KBNumber] [varchar](100) NOT NULL,
	[ServicePackRTM] [varchar](100) NULL,
	[ReleaseDate] [date] NULL,
	[UpdateNumber] [varchar](100) NULL,
	[IncrementalServicing] [varchar](100) NULL,
	[IncludeSecurity] [varchar](1000) NULL,
	[LifeCycleStatus] [varchar](100) NULL,
	[Notes] [varchar](max) NULL,
	[EOS] [date] NULL,
	[inserted_on] [datetime] NOT NULL,
	[inserted_by] [varchar](100) NOT NULL,
 CONSTRAINT [PK_SQLServerBuilds] PRIMARY KEY CLUSTERED 
(
	[Version] ASC,
	[BuildNumber] ASC,
	[KBNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[SQLServerBuilds] ADD  CONSTRAINT [DF_SQLServerBuilds_inserted_on]  DEFAULT (getdate()) FOR [inserted_on]
GO

ALTER TABLE [dbo].[SQLServerBuilds] ADD  CONSTRAINT [DF_SQLServerBuilds_inserted_by]  DEFAULT (suser_sname()) FOR [inserted_by]
GO

  Create  procedure [dbo].[wmsp_CUSQLServerBuilds] (
  @version varchar(100)
  ,@buildNumber varchar(100)
  ,@Kbnumber varchar(100)
  ,@ServicePackRTM varchar(100)
  ,@ReleaseDate varchar(100)
  ,@UpdateNumber varchar(100)
  ,@IncrementalServicing varchar(100)
  ,@IncludeSecurity varchar(1000)
  ,@LifeCycleStatus Varchar(100)
  ,@Notes varchar(100)
  )
  as
  begin 
  declare @eos date;
  set @ReleaseDate = case when  PATINDEX('%[0-9]%',@ReleaseDate)  = 1 and PATINDEX('%[A-Z]%',@ReleaseDate) = 0 then dateadd(dd,convert(int,@ReleaseDate),'12/31/1899 00:00:00') else @ReleaseDate end
  set @eos = case when  PATINDEX('%[0-9]%',@Notes)  = 1 and PATINDEX('%[A-Z]%',@Notes) = 0 then dateadd(dd,convert(int,@Notes),'12/31/1899 00:00:00') else null end
  if not  exists (select 1 from SQLServerBuilds where [version] = @version and BuildNumber = @buildNumber and KBNumber = @Kbnumber)
  Begin 
  Insert into SQLServerBuilds(
       [version]
       ,[BuildNumber]
      ,[KBNumber]
      ,[ServicePackRTM]
      ,[ReleaseDate]
      ,[UpdateNumber]
      ,[IncrementalServicing]
      ,[IncludeSecurity]
      ,[LifeCycleStatus]
      ,[Notes]
      ,EOS)
   Values (
   @version
   ,@buildNumber 
  ,@Kbnumber 
  ,@ServicePackRTM 
  ,@ReleaseDate 
  ,@UpdateNumber 
  ,@IncrementalServicing 
  ,@IncludeSecurity 
  ,@LifeCycleStatus 
  ,@Notes
  ,@eos
  )
  end 
  else 
  begin 
  
  update SQLServerBuilds
  set LifeCycleStatus= @LifeCycleStatus
  ,UpdateNumber = @UpdateNumber
  ,IncrementalServicing = @IncrementalServicing
  ,IncludeSecurity = @IncludeSecurity
  ,Notes =  @Notes
  ,ReleaseDate = @ReleaseDate
  ,ServicePackRTM = @ServicePackRTM
  ,eos = @eos
 where [version] = @version and BuildNumber = @buildNumber and KBNumber = @Kbnumber 
  end 
  
  end    

 

Powershell Script to load the excel

 $srv = "Server Name"
$Dbname = "Database Name"
$filelocation = "Excel File Location"

add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

$cscn = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $srv 


$files = Get-ChildItem $filelocation *.xlsx

foreach ($file in $files) { 
 Get-ExcelSheetInfo $file.FullName  | ForEach-Object {
           $Sheet =$_.Name; 
                  $SQLserver = Import-Excel $file.FullName -Sheet $Sheet; ForEach ($SQL in $SQLserver) {
                       $Buldnumber = $SQL.'Build number'; 
                        $kbnumber = $SQL.'KB number';  
                        $lifecycle = if($SQL.'Lifecycle stage' -EQ $null){$SQL.'Lifecycle status (Service Pack Support End Date) '}else{$SQL.'Lifecycle stage'}	
                        $Updatenumber = $SQL.'Cumulative Update number/Security ID'; 
                        $IncrementalServicing = $SQL.'Incremental Servicing Model';
                        $inlcudeSecurity = $SQL.'Includes latest security fixes?';
	                    [String]$notes =  $SQL.'Notes regarding Lifecycle stage'+$SQL.'Notes regarding Lifecycle stage/ Service Pack support end date'+$SQL.'Lifecycle status (Service Pack Support End Date) ';
	                    if($notes -ne $null){ $notes=$notes.replace("'","''")}
                        $releasedate =  $SQL.'Release Date';
	                    $servicepack = $SQL.'Service Pack/RTM';
                     if($Buldnumber -NE $null){ 
                            $cscn.Databases[$Dbname].ExecuteNonQuery("exec wmsp_cuSQLServerBuilds '$Sheet','$Buldnumber','$kbnumber','$servicepack','$releasedate','$Updatenumber','$IncrementalServicing','$inlcudeSecurity','$lifecycle','$notes'")}
                     }
        }

 

Final Result

sqlserverbuilds

Convert Data Table to HTML Table with PowerShell and more

##function to convert any datatable to html table

function Convert-DatatablHtml ( $dt)

{ 

$html = "<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
    border: 1px solid black;
    border-collapse: collapse;
}
</style>
</head>
<body>
<table border=""1"" 

style=""width:20%"">";
$hmtl +="<tr>"
for($i = 0;$i -lt $dt.Columns.Count;$i++)
{
$html += "<td>"+$dt.Columns[$i].ColumnName+"</td>"  
}
$html +="</tr>"

for($i=0;$i -lt $dt.Rows.Count; $i++)
{
$hmtl +="<tr>"
 for($j=0; $j -lt $dt.Columns.Count; $j++)
 {
  
  $html += "<td>"+$dt.Rows[$i][$j].ToString()+"</td>"
 }
 $html +="</tr>"
}

$html += "</table>
</body>
</html>"

return $html

}

##How it works 

##creating a sample databtale to test
$dt = New-Object "System.Data.DataTable"


$dt.Columns.Add("id",[int]);
$dt.Columns.Add("value",[string]);

$dtnew = $dt.NewRow();
$dtnew.id =1;
$dtnew.value="test";
$dt.Rows.Add($dtnew)
$dtnew = $dt.NewRow();
$dtnew.id =1;
$dtnew.value="test";
$dt.Rows.Add($dtnew)

##Converting data table to html

$h = Convert-DatatablHtml -dt $dt 

$h


##writing html table to a html file 

$h |out-file "c:\temp\test.html"


##Send an email with with formated html 


Send-MailMessage  -to "to@yahoo.com" -SmtpServer "smtpserver" -body $h -Subject test -From "from@yahoo.com" -BodyAsHtml 

Remove old backup files with PowerShell

Note: script looks at when file was last modified and also removes if any file exists in sub folders recursively.

$path="C:\app"
$NumDaysToRetain=3
$extension ="*.bak"

Get-ChildItem -Path $path -Filter $extension  -Recurse  | 
Where-Object{ $_.lastwritetime -lt (get-date).adddays(-$NumDaysToRetain)}  | 
ForEach-Object { Remove-item $_.FullName; write-host "File" $_.FullName "deleted"}

Powershell To Deploy Sql scripts in a Directory

Source Code

[CmdletBinding()]
Param(
  [Parameter(Mandatory=$True,ValueFromPipeline=$true,HelpMessage="Enter Source Instance")]
  [ValidateScript({ IF( $_ -eq $null) 
		{$false }  
	    elseif ($_.ToString().Trim().Length -eq  0 )
		{$false}
	    else {$true}} )]
   [string]$TargetInst,
  [Parameter(Mandatory=$True,ValueFromPipeline=$true,HelpMessage="Enter Source Source")]
  [ValidateScript({ IF( $_ -eq $null) 
		{$false }  
	    elseif ($_.ToString().Trim().Length -eq  0 )
		{$false}
	    else {$true}} )]
   [string]$TargetDb,
   [Parameter(Mandatory=$True,ValueFromPipeline=$true)]
   [string]$Targetdir

)

## Add Assemblies
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, 
PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
Write-host $TargetInst $Targetdb $targetdir
$sourcedbs = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $TargetInst 
$sourcedbs.ConnectionContext.StatementTimeout = 0
$db = $sourcedbs.Databases[$TargetDB]
$files = Get-ChildItem -Path "$Targetdir"  -Recurse  | Where-Object{$_.Name -Like "*.sql"} | select FullName
Foreach($file in $files) 
{
$query= Get-Content $file.FullName | Out-String ; 
	try
	{
		$db.ExecuteNonQuery($query)
		Write-Host $file.FullName "Query Finished"     
    }
      catch [Exception]
 	{

		Write-Host $_.Exception.GetType().FullName, $_.Exception.Message
	}
}

 

How to use it

.\ExecuteSqlscripts.ps1 ServerName DatabaseName Directory

Note: it does a recursive check if there any folders it gets them all as well.

 

Quick tip: Validating powershell parameters

Although powershell offers a great way of validating the parameters using advanced functions we can extend this functionality to make simple scripts robust as they can be

http://technet.microsoft.com/en-us/library/hh847743.aspx

for example here in this snipet we can validate the input string using a pattern and length

1
2
3
4
5
6
7
8
9
 Param
          (
            [parameter(Mandatory=$true)]
            [ValidateLength(5,10)]
            [String]
            $str
          ) 
$str

Above script will make sure input string length is at least 5 charcters upt 10 characters max

validate input
input string test less than 5 charecters script throws an exception

validate input 1

[ValidatePattern(“[A-Z][A-Z][A-Z][A-Z][A-Z][A-Z]”)]

adding this as a validation attriubute this will make sure string only contains letters a to z

 Param
          (
            [parameter(Mandatory=$true)]
            [ValidateLength(5,10)]
	    [ValidatePattern("[A-Z][A-Z][A-Z][A-Z][A-Z][A-Z]")]
            [String]
            $str
          ) 

$str

validate pattern

 

we can also validate from a script this what I am really interested

below is a simple function that checks if a parameter is null or empty if its not empty does it has any whitespaces similar a static method .Net 4 exposes IsNullOrWhiteSpace

 

function Isnullorempty ($str) 
{
IF( $str -eq $null ) 
 {  $true }  
elseif ( $str.ToString().Trim().Length -eq  0 ){ $true}
else {$false}
}

We are going to embed this script in validation script

 Param
          (
            [parameter(Mandatory=$true)]
            [ValidateLength(5,10)]
 	    [ValidateScript({ IF( $_ -eq $null) 
		{$false }  
	    elseif ($_.ToString().Trim().Length -eq  0 )
		{$false}
	    else {$true}} )]
            [String]$str
          ) 

$str

 

If run with blank white sure it throws an error

PS C:\Powershell> .\powershell.ps1 ” ”
C:\Powershell\powershell.ps1 : Cannot validate argument on parameter ‘str’.
The ” IF( $_ -eq $null)
{$false }
elseif ($_.ToString().Trim().Length -eq 0 )
{$false}
else {$true}” validation script for the argument with value ”
” did not return true. Determine why the validation script
failed and then try the command again.
At line:1 char:19
+ .\powershell.ps1 ” ”
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidData: (:) [powershell.ps1], ParameterBind
ingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationError,powershell.ps1

PS C:\Powershell> .\powershell.ps1  test1
test1

PowerShell and TFS integration

Team foundation power tools provide set of cmdlets to manage and deploy TFS content

Pre req

  1. Team Foundation – Team explorer
  2. Team Foundation – Power tools

 

I found some very use full must read basics links before we kick start

http://blogs.technet.com/b/heyscriptingguy/archive/2014/04/21/powershell-and-tfs-the-basics-and-beyond.aspx

http://blogs.technet.com/b/heyscriptingguy/archive/2012/12/28/protect-your-powershell-scripts-with-version-control.aspx

http://mscodingblog.blogspot.com/2014/02/introducing-tfsmanager-some-powershell.html

http://www.sds-consulting.com/tfs-training-webcast-automating-team-foundation-server-powershell-slides-and-code-link

Click to access TFS%20Training%20from%20SDS%20-%20Powershell%20Slides%20and%20Code.pdf

http://sdscon.codeplex.com/releases/view/60931

 

 

How to Fix : Script cannot be loaded because the execution of scripts is disabled on this system.

You can fix this issue in lot of different ways depending on your environment by running below command in powershell

1.

Set-ExecutionPolicy RemoteSigned

RemoteSigned  – all scripts you created yourself will be run, all scripts downloaded from the internet will need to be signed by a trusted publisher

2.

Set-ExecutionPolicy unrestricted

Unrestricted – Executes any script

3.

ExecutionPolicy ByPass -File “Script name”

Bypass a particular script

If you are in controlled or restricted environment

4.

Set-ExecutionPolicy -Scope “CurrentUser” -ExecutionPolicy “RemoteSigned”

or

Set-ExecutionPolicy -Scope “CurrentUser” -ExecutionPolicy “Unrestricted”