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.