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)

[solved] Docker and Windows 10 hyper-v Containers, byLinuxVM’ failed to start.. . Virtual machine ‘MobyLinuxVM’ could not be started because the hypervisor is not running

If you are like me trying to run windows Docker containers in a hyper-v vm windows 10 guest on a windows 10 host, you might have come across above issue, we can fix this issue in two steps.

  • First make sure you are running on most up to date windows 10, prerequisite for OS requirements is to have Windows 10 Anniversary Update.
  • Second enable nested virtualization, why do we need to do that ?

nesteddiagram2

In this case, Hyper-V has been configured to expose virtualization extensions to its guest VM. A guest VM can take advantage of this, and install its own hypervisor. It can then run its own guest VMs, more info visit msdn blog

So how to enable nested virtualization,

first turn off the vm, run below command on the host OS, I would say the root OS.

Set-VMProcessor -VMName <VMName> -ExposeVirtualizationExtensions $true

Enable networking for nested virtual machine, can be done in two ways Mac spoofing or NAT mode.

will go with Mac Spoofing, to enable MAC spoofing, run below command on host os.

Get-VMNetworkAdapter -VMName <VMName> | Set-VMNetworkAdapter -MacAddressSpoofing On

Make sure the guest OS has enough memory to run MobyLinuxVm docker container to initialize, I would say at least 4 gigs.

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}

Compress Database backups on SQL Express Edition

As we know Microsoft does not support compression for SQL Server Express but time to time we use express edition database engine when all you need is small database that might not even grow more than 1 gig or 5 gigs.

Prep:

we need 7z zip program its an opensource software will help us compress the files, download the latest version 64 bit (http://www.7-zip.org/)

Enable xp_cmdShell ( we are going to use xp_cmdshell to execute the batch command to compress or delete files)

-- To allow advanced options to be changed.  
EXEC sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  
-- To enable the feature.  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO  

TSQL Script( Where Magic happens)

How does script work:  it first looks at mypath and lists all .bak files and folders it loops each of these files using a cursor and execute the 7zip command to compress the .bak to .zip and cleanuphrs parameter will clean the backup zip files older than specified number of hours.

/*
##Script to Compress the backups in sql express using 7 zip 
##Author: Akhil 
##Date:09/19/2016
##Rev:1 initial Setup
##Rev:2 fix the date add to negitive 
##Rev:3 Remove xp_delete_file instead use batch command to remove old files.
*/

--parameter
declare @myPath varchar(4000) =  'R:\mssql\userdb';
declare @apath varchar(1000) = '';
declare @aextn varchar(10)= 'zip';
declare @7zexe varchar(1000)= 'C:\Program Files\7-Zip\7z.exe'
declare @cleanupdays int = 4;
declare @cleanupdate datetime;

IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (
	id int IDENTITY(1,1)
	,subdirectory nvarchar(512)
	--,filename 
	,depth int
	,isfile bit
	, ParentDirectory int
	,flag tinyint default(0));

-- top level directory
INSERT #DirectoryTree (subdirectory,depth,isfile)
	VALUES (@myPath,0,0);
-- all the rest under top level
INSERT #DirectoryTree (subdirectory,depth,isfile)
	EXEC master.sys.xp_dirtree @myPath,0,1;


UPDATE #DirectoryTree
	SET ParentDirectory = (
		SELECT MAX(Id) FROM #DirectoryTree
		WHERE Depth = d.Depth - 1 AND Id < d.Id	)
FROM #DirectoryTree d;

-- SEE all with full paths
declare @archivefiles cursor ;
declare @bfile sysname;
declare @afile sysname;
declare @conataner sysname;
declare @result int;
declare @cmd varchar(8000);

--Get all the files 

set @archivefiles = cursor for
WITH dirs AS (
	 SELECT
		 Id,subdirectory,depth,isfile,ParentDirectory,flag
		 , CAST (null AS NVARCHAR(MAX)) AS container
		 , CAST([subdirectory] AS NVARCHAR(MAX)) AS dpath
		 FROM #DirectoryTree
		 WHERE ParentDirectory IS NULL 
	 UNION ALL
	 SELECT
		 d.Id,d.subdirectory,d.depth,d.isfile,d.ParentDirectory,d.flag
		 , dpath as container
		 , dpath +'\'+d.[subdirectory]  
	 FROM #DirectoryTree AS d
	 INNER JOIN dirs ON  d.ParentDirectory = dirs.id
)
SELECT dpath,case when @apath is null or len(@apath) = 0 then replace(dpath,'.bak','.'+@aextn) 
 else @apath+replace(subdirectory,'.bak','.'+@aextn)  end as afile
    FROM dirs 
-- Dir style ordering
where isfile = 1 and subdirectory like '%.bak'


print('Archive Process')

open @archivefiles	

fetch next from @archivefiles into @bfile,@afile
while @@FETCH_STATUS = 0
begin 
print (@bfile)
print (@afile)
set @cmd = '""'+@7zexe+'" a "'+@afile+'" "'+@bfile+'"'+' -sdel'+'"'
print('Archive Command'+@cmd)
exec @result =  xp_cmdshell @cmd
print(@result)
fetch next from @archivefiles into @bfile,@afile
end

print('Clean up')

set @cmd = '"forfiles -p "'+@myPath+'" -s -m *.'+@aextn+' /D -'+convert(varchar,@cleanupdays)+' /C "cmd /c del @path"'
print('Delete Command '+@cmd)
exec @result =  xp_cmdshell @cmd
print(@result)

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