Developer42

2015-12-04

SQL Server :: Compare Database Schemas

This is a script to list basic differences in table definitions between two databases.

Background Waffle
When we make database changes in a transactional system, we need to ensure that these won’t adversely affect our BI solution. Normally our developers give us this information when we plan which items to put into a release, so we can liaise with BI; unfortunately the developers machines are down at present due to a disaster at the data center of the company to whom we outsourced development. Since the changes for the release are already in our UAT environment (and only those changes are there), we could determine what would be changing in production by comparing the schemas of the two environments.

My first thought was to use the Database Comparison Tool that comes with Visual Studio. However whilst this does a great job for a developer looking for and resolving differences, it’s not so easy to communicate this information to others (i.e. giving a before and after image of columns side by side. You have to either click on each difference to get a definition of the before and after, then make notes or take screenshots, or you have to generate the DDL, in which case you don’t see the before picture; only the code to amend from the current to the to-be definition.

Instead I realised that I could simply pull the definitions of the tables & columns from SQL, compare these and list the differences; resulting in the code below.
This code can handle where both databases are on the same instance, or where they’re on different instances. For the latter scenario you’d need to create a linked server.
If you frequently compare different combinations of databases and want to avoid amending the instance and catalog names in the code each time you may want to look into views or synonyms (potentially aliases work too if the catalog name is consistent across instances).

Since our BI solution uses replication to get the data from the transactional system, I also included information on which columns were replicated; thus we can filter on those replicated to see just those which BI would be interested in, or can ignore replication to see all differences.

Caveats

  1. We’re not comparing everything; only basic table and column data.
  2. The maxLength value gives length in bytes; not characters (see previous post on how to correct for this).
  3. There are probably other issues; this was a quick fix to resolve an immediate issue which worked for us; there may be issues we’ve not encountered (e.g. maybe the linked server only gives access to a subset of the tables; thus giving false responses)…

Code

set transaction isolation level read uncommitted
go

;with uat as
(
       select t.name TableName
       , c.Name ColumnName
       , ty.name ColType
       , c.max_length ColMaxLength
       , coalesce(c.is_nullable,0) ColIsNullable
       , c.scale ColScale
       , c.[precision] ColPrecision
       , coalesce(c.collation_name,'') ColCollation
       , c.is_replicated
       from [myUatDbServer\uatInstance].[myUatDbCatalog].sys.tables t
       inner join [myUatDbServer\uatInstance].[myUatDbCatalog].sys.columns c
              on c.object_id = t.object_id
       inner join [myUatDbServer\uatInstance].[myUatDbCatalog].sys.types ty
              on ty.system_type_id = c.system_type_id
              and ty.user_type_id = c.user_type_id
)
, prd as
(
       select t.name TableName
       , c.Name ColumnName
       , ty.name ColType
       , c.max_length ColMaxLength
       , coalesce(c.is_nullable,0) ColIsNullable
       , c.scale ColScale
       , c.[precision] ColPrecision
       , coalesce(c.collation_name,'') ColCollation
       , c.is_replicated
       from [myProdDbServer\prodInstance].[myProdDbCatalog].sys.tables t
       inner join [myProdDbServer\prodInstance].[myProdDbCatalog].sys.columns c
              on c.object_id = t.object_id
       inner join [myProdDbServer\prodInstance].[myProdDbCatalog].sys.types ty
              on ty.system_type_id = c.system_type_id
              and ty.user_type_id = c.user_type_id
)
select coalesce(uat.TableName, prd.TableName) TableName
, coalesce(uat.ColumnName, prd.ColumnName) ColumnName
, case
       when prd.TableName is null and not exists (select top 1 1 from prd x where x.TableName = uat.TableName) then 'Add Table'
       when uat.TableName is null and not exists (select top 1 1 from uat x where x.TableName = prd.TableName)  then 'Remove Table'
       when prd.ColumnName is null then 'Add Column'
       when uat.ColumnName is null then 'Remove Column'
       else 'Change Column Definition'
end [AXtion] --our transaction system's Dynamics AX; I'm hilarious :S
, prd.ColType ColTypeFrom
, uat.ColType ColTypeTo
, prd.ColMaxLength ColMaxLengthFrom --not going to fuss about char byte size for now; just want to get a list of changes
, uat.ColMaxLength ColMaxLengthTo --not going to fuss about char byte size for now; just want to get a list of changes
, prd.ColIsNullable ColIsNullableFrom
, uat.ColIsNullable ColIsNullableTo
, prd.ColCollation ColCollationFrom
, uat.ColCollation ColCollationTo
, prd.ColPrecision ColPrecisionFrom
, uat.ColPrecision ColPrecisionTo
, prd.ColScale ColScaleFrom
, uat.ColScale ColScaleTo
, prd.is_replicated PrdIsReplicated --\_these aren't compared; just returned to make it easy to tell what's interesting to BI
, uat.is_replicated UatIsReplicated --/
from uat
full outer join prd
       on prd.TableName = uat.TableName
       and prd.ColumnName = uat.ColumnName
where prd.TableName is null
or uat.TableName is null
or prd.ColumnName is null
or uat.ColumnName is null
or (uat.ColType != prd.ColType)
or (uat.ColMaxLength != prd.ColMaxLength)
or (uat.ColIsNullable != prd.ColIsNullable) 
or (uat.ColCollation != prd.ColCollation) 
or (uat.ColPrecision != prd.ColPrecision) 
or (uat.ColScale != prd.ColScale)
order by coalesce(uat.TableName, prd.TableName) 
, coalesce(uat.ColumnName, prd.ColumnName) 
Advertisements

2015-05-18

PowerShell Script :: Combine-Paths

Filed under: Microsoft, Technology — Tags: , , , , , , , — Developer42 @ 18:34

When you wish to create paths based on various combinations of strings, the following accepts a root plus an array of arrays and returns all combinations:

cls
function combine-paths([string]$root,[array]$paths)
{
    if(($paths -ne $null) -and ($paths.length -gt 0))
    {
        [array]$roots = $paths | select -first 1
        [array]$tail = @($paths | select -skip 1) #@() used to prevent final array from becoming an array of strings instead of a single-element array containing an array of strings
        $roots | %{combine-paths -root (join-path $root $_) -paths $tail}
    }
    else
    {
        $root
    }
}

$x = ('PROD','UAT','SIT'),('SystemA','SystemB','SystemC'),'Interfaces',('In','Out')
combine-paths -root '\\myFileShare\' -paths $x

NB: If you had multiple roots you could do the following:

'c:\','\\somewhere' | %{combine-paths -root $_ -paths $x}

Or amend the function to become a cmdlet taking root from the pipeline to simplify the above further / or add logic to allow the root to be blank/unspecified and to just pass `$_` instead of `(join-path $root $_)` where root is blank/null.
I’ve not done that here in order to keep the code short & simple.

2014-10-13

PowerShell Script :: Get HotFix Info from Local Machine & Web

Filed under: Microsoft, Technology — Tags: , , , , , , , , — Developer42 @ 22:02

In answering a question on Stack Overflow[1], I discovered that the Get-Hotfix cmdlet doesn’t list all hotfixes on your machine. Rather, if the fix is included in a CU, details of the installed CU will be presented, with no apparent way to drill down into the information and check for a specific fix.
So far as I can tell, there are no services / open databases which would allow you to programmatically retrieve this information. The only way currently is to manually head to the CU or fix’s related knowledge base site and read the contents.
As a very rough hack-around, I created a scraping script which will interrogate the MS knowledge-base for information on each hotfix, finding whether it’s a CU or not (based on the hotfix’s description in the page’s title) and if so try to find all related fixes. I only go down one level – to avoid the risk of an infinite loop & save time; should anyone want to go deeper, a caching mechanism could be used to store previous results and thus prevent the risk of a loop and also potentially improve performance.
As with most of the scripts on this site, this is more for me to play with PowerShell than to be of much practical use; but hopefully it could be to someone…

function get-hotfixInfo()
{
	process 
	{
		#$url = "http://support2.microsoft.com/kb/{0}" -f ($_.HotFixId -replace "KB(\d*)",'$1')
		$url = $_.Caption
		if($url -like "*.microsot.com")
		{
			$url = "{0}/{1}" -f $url,($_.HotFixId -replace "(KB)(\d*)",'$1/$2') 
		}
		try
		{
			$response = (Invoke-Webrequest $url -ea stop)
		} catch {
			$response = @{
				ParsedHTML = @{
					Title = "{0}`n`nURL: {1}" -f $error[0].Exception,$url 
				}
			}
		}
		$html = $response.ParsedHTML
		$isCU = $html.title -like "*cumulative*update*"
		$kblets = $null
		if($isCU) #this bit can be even slower than the above, hence only run if we believe we have a CU
		{
			$baseUri = $response.BaseResponse.ResponseURI
			$kblets = $html.getElementsByTagName('a') `
				| ? { ($_.parentNode.tagname -eq 'TD') -and ($_.parentNode.nextsibling.tagname = 'TD') } `
				| ? { $_.className -eq 'KBlink' } `
				| % { New-Object -TypeName PSObject -Prop @{
					Id = "KB{0}" -f $_.innerText
					Uri = (new-object System.URIBuilder($baseUri.scheme,$baseUri.dnssafehost,$baseUri.port,($_.href -replace "about:/(.*),'$1'"))).ToString()
					Title = $_.parentNode.nextsibling.innerText
				}}
		}
		New-Object -TypeName PSObject -Prop @{
			Id = $_.HotFixId
			IsCU = $isCU 
			Title = $html.title
			Source = $_.source
			Description = $_.description
			InstalledBy = $_.installedby
			InstalledOn = $_.installedon
			Uri = "http://support2.microsoft.com/kb/{0}" -f ($_.HotFixId -replace "KB(\d*)",'$1')
			KBlets = $kblets
		}
	}
}

#demos

#get the hotfix id for IE11 CU Feb 2014, and it's component hotfixes
get-hotfix -id kb2909921 | get-hotfixInfo | %{ $_; $_.KBlets | %{ $_; }} | ft -property id,title -autosize

#get the first 2 cumulative update hotfixes
get-hotfix | select -first 2 | get-hotfixInfo | ?{$_.isCU} 


2014-09-26

PowerShell Script :: Get AD Users by Email (Advanced)

Filed under: Microsoft, Technology — Tags: , , , , , , , — Developer42 @ 22:19

Here’s a script I knocked up today.

Auto.ps1 allows me to host the script on a server (or wherever), whilst others can use it by dropping input files into a queue folder, without needing to touch powershell (which may be scary to non-programmers, or may require additional setup or permissions).
ADGetUsersByEmailAdvanced.ps1 gets AD info based on email addresses; without requiring exchange modules, and includes workarounds to cope with missing information.

Auto.ps1

Monitors a folder for new text files. Once found, passes that file to a script to be processed. On completion moves the source file to the same directory & renames to begin with the same timestamp as the generated output file.

$infile = "\\myServer\myShare\Scripts\powershell\ADGetUsersByEmail\in\*.txt"

while (1 -eq 1) {
	#wait for a new file
	while(!(Test-Path $infile)) {Start-Sleep -s 30;}
	write-host "new input file found"
	Get-ChildItem $infile | %{
		$fileTimestamp = "{0:yyyy-MM-dd_HHmmss}" -f (get-date).ToUniversalTime()
		$inputFile = $_.fullname
		$exportFile = "{0}\out\{1}_output.csv" -f $PSScriptRoot,$fileTimestamp
		$inputFileMoved = "{0}\out\{1}_{2}" -f $PSScriptRoot,$fileTimestamp,$_.name
		write-host ("source: {0}" -f $inputFile)
		write-host ("output: {0}" -f $exportFile) 
		write-host ("archive: {0}" -f $inputFileMoved) 
		.\ADGetUsersByEmailAdvanced.ps1 -sourceFile $_.fullname -exportFile $exportFile 
		write-host "processed"
		Move-Item $inputFile $inputFileMoved
		write-host "archived"
	}
}

ADGetUsersByEmailAdvanced.ps1

Given a text file containing a list of email addresses, attempts to resolve those to corresponding AD users, taking advantage of email information in AD where available, then gracefully degrading to more hacky methods. Works its way through a list of domains in case the users are in the same company but on a different domain.

Param(
  [string]$sourceFile
  ,[string]$exportFile 
)
#$sourceFile = '.\sourceEmails.txt'
#$exportFile = ".\output_{0:yyyy-MM-dd_HHmmss}.csv" -f (get-date).ToUniversalTime()
$domains = 'eu','usa','myDomain','anotherDomain' # domain points to the GC; could equally list GC server names here, though this version's more user friendly

#create dummy; allows us to put in values for any unfound items (currently just using null, but we can easily amend if desired)
$adDummy = New-Object –TypeName PSObject –Prop @{
	emailSearched	= $null;
	notFound		= $true;
	sAmAccountName 	= $null;
	fullname		= $null;
	firstname		= $null;
	lastname		= $null;
	cn				= $null;
	countryCode		= $null;
	country			= $null;
	#title			= $null;
	title			= $null;
	department		= $null;
	company			= $null;
	email			= $null;	
	adEmail			= $null;
	proxyEmail		= $null;
}

function RemoveEmailDomain($email) {
  return $email -replace "(\S*)@\S*", '$1'
}
function IsFirstDotLast($name) {
	return $name -like '*.*'
}
function GetFirstName($name) {
	return $name -replace "(\S*?)\.\S*", '$1'
}
function GetLastName($name) {
	return $name -replace "\S*?\.(\S*)", '$1'
}
function GetFirstNamePartial($name) {
	return $name.substring(0,[system.math]::min(3,$name.length))
}
function GetLastNamePartial($name) {
	return $name.substring([system.math]::max($name.length-3,0),[system.math]::min(3,$name.length))
}
function GetADUserByIdentity($id, $domain) {
	#trycatch since erroraction not recognised on this type of command & I don't want error messages polluting my output
	write-host "searching for id '$id' on domain '$domain'"
	try { 
		Get-ADUser -Identity $id -Server $domain -Properties sAmAccountName, displayName, givenName, surname, distinguishedName, countryCode, c, title, department, company, emailAddress, proxyAddresses
	} catch {}
}
function GetADUserFiltered($filter, $domain) {
	Get-ADUser -Filter $filter -Server $domain -Properties sAmAccountName, displayName, givenName, surname, distinguishedName, countryCode, c, title, department, company, emailAddress, proxyAddresses
}
function GetADUserByEmailAddress($email, $domain) {
	write-host "searching for email '$email' on domain '$domain'"
	$filter = {emailAddress -eq $email} 
	GetADUserFiltered $filter $domain
}
function GetADUserByProxyAddress($email, $domain) {
	write-host "searching for proxy '$email' on domain '$domain'"
	$psBugFixSearchUser = "*:$_*"
	$filter = {proxyAddresses -like $psBugFixSearchUser}
	GetADUserFiltered $filter $domain
}
function GetADUserByFullName($name, $domain) {
	$fn = GetFirstName $name
	$ln = GetLastName $name
	write-host "searching for name '$fn', '$ln' on domain '$domain'"
	$filter = {(givenname -eq $fn) -and (surname -eq $ln)}
	GetADUserFiltered $filter $domain
}
function GetADUserByPartialName($name, $domain) {
	$fn = "{0}*" -f (GetFirstNamePartial $name)
	$ln = "*{0}" -f (GetLastNamePartial $name)
	write-host "searching for partial name '$fn', '$ln' on domain '$domain'"
	$filter = {(givenname -like $fn) -and (surname -like $ln)}
	GetADUserFiltered $filter $domain | where { ($_.givenname + $_.surname) -eq $name }
}
function FindBestMatch($email) {
	$result = $null
	#$domains | %{ $result=GetADUserByEmailAddress $email $_; if($result) {return $result;} } #doesn't play as expected
	foreach($domain in $domains) { $result=GetADUserByEmailAddress $email $domain; if($result) {return $result;} }
	foreach($domain in $domains) { $result=GetADUserByProxyAddress $email $domain; if($result) {return $result;} }
	$name = RemoveEmailDomain $email
	foreach($domain in $domains) { $result=GetADUserByIdentity $name $domain; if($result) {return $result;} }
	if(IsFirstDotLast($name)) {
		foreach($domain in $domains) { $result=GetADUserByFullName $name $domain; if($result) {return $result;} }
	} else {
		foreach($domain in $domains) { $result=GetADUserByPartialName $name $domain; if($result) {return $result;} }
	}	
	return $adDummy;
}

#define a function for later use
#get list of emails (ignore blanks)
$emails = (Get-Content $sourceFile) | where{ $_ -gt '' }

#get data from ad and stick it in an csv (or error to console if not found)
$emails | %{ 
	#get ad user by email address
	$adUser = FindBestMatch($_);
	if($adUser.notFound) {
		write-host ":(" -ForegroundColor Red
	} else {
		write-host ":)" -ForegroundColor Green
	}
	#return object replresenting results.
	New-Object –TypeName PSObject –Prop @{
		emailSearched	= $_;
		found			= if($adUser.notFound){$false} else {$true};
		sAmAccountName 	= $adUser.sAmAccountName;
		fullname		= $adUser.displayName;
		firstname		= $adUser.givenname;
		lastname		= $adUser.surname;
		cn				= $adUser.distinguishedName;
		countryCode		= $adUser.countryCode;
		country			= $adUser.c;
		#title			= $adUser.personalTitle;
		title			= $adUser.title;
		department		= $adUser.department;
		company			= $adUser.company;
		adEmail			= $adUser.emailAddress;
		proxyEmail		= [string]$adUser.proxyAddresses; #string joins the array down to a single string value
	}
} | export-csv $exportFile -notype #stick output to file

Script could be improved by allowing auto to kick off jobs so multiple instances of the worker script can be run simultaneously. Also changing the main script to make use of workflows and take advantage of the parallel foreach method should significantly improve it’s performance. However I’m still pretty new to PowerShell, so those steps will have to come later.

2014-09-10

Powershell Script: Monitor Connection Status

Filed under: Technology, Uncategorized — Tags: , , , , , , — Developer42 @ 20:59

This script monitors a connection, reporting when the connection drops and when it’s recovered.
The script monitors by pinging an IP, or by running an HTTP Get against a defined IP/URL.
The URL option’s provided in case run from behind a firewall blocking ICMP.

function Run-ConnectionTest($site, $waitSecs, $firewall)
{
	$state = 'unknown'
	$previousState = 'unknown'

	while($true)
	{
		#get current connection state
		if(Test-MyConnection $site $firewall)
		{
			$state = 'up'
		}
		else
		{
			$state = 'down'
		}
		#report change in state
		if ($state -ne $previousState) 
		{
			$previousState = $state
			$now = Get-Date
			if($state -eq 'up')
			{
				Write-Host -ForegroundColor Green ("{0:yyyy-MM-dd hh:mm:ss}: Connection up!" -f $now)
			}
			else
			{
				Write-Host -ForegroundColor Red ("{0:yyyy-MM-dd hh:mm:ss}: Connection down..." -f $now)
			}
		}
		#wait before checking again
		Start-Sleep -Seconds $waitSecs
	}
}
function Test-MyConnection($site, $firewall)
{
	$result = $false
	if($firewall) #if there's a firewall blocking pings, pinging won't work
	{
		try 
		{ 
			$response = (Invoke-WebRequest -Uri $site)
			#write-host('OK')
			#write-host($response.StatusCode)
			if(($response.StatusCode -ge 200) -and ($response.StatusCode -lt 400)) #treat everything from 200 to 399 as connection successful
			{
				$result = $true
			}
			else #everything outside of 200-399 is treated as a connection issue
			{
				$result = $false
			}
		} 
		catch #all exceptions are treated as connection issues, regardless of http response status code
		{
			#write-host('KO')
			#write-host($_.Exception)
			#$_.Exception.Response.StatusCode.Value__
			$result = $false
		}
	}
	else
	{
		$result = Test-Connection -computer $site -count 1 -quiet
	}
	return $result
}

Clear
#Run-ConnectionTest '8.8.8.8' 5 $false
Run-ConnectionTest 'http://www.google.com' 30 $true

2014-08-22

Powershell Script: Get Server Inventory

Filed under: Microsoft — Tags: , , , , , , , , , , — Developer42 @ 16:36

**Update 2017-02-05**

This script is now available on GitHub: https://github.com/JohnLBevan/PSForInfrastructure/, where it will be kept updated with functionality requests and refactored to make it easier for you to adapt this script to meet your requirements through parameters.


The following script (heavily based on http://www.the-fays.net/blog/?p=334) polls all servers in a given text file, returning information about their disk capacity, # of cpus (sockets & cores), amount of RAM, and OS info.


$serverList = ".\Servers.txt"
$outputCSV = ".\ServerInventory.csv"


$scriptpath = $MyInvocation.MyCommand.Path
$dir = Split-Path $scriptpath
pushd $dir

[System.Collections.ArrayList]$sysCollection = New-Object System.Collections.ArrayList($null)
 
foreach ($server in (Get-Content $serverList))
{
    "Collecting information from $server"
    $totCores=0
 
    try
    {
        [wmi]$sysInfo = get-wmiobject Win32_ComputerSystem -Namespace "root\CIMV2" -ComputerName $server -ErrorAction Stop
        [wmi]$bios = Get-WmiObject Win32_BIOS -Namespace "root\CIMV2" -computername $server
        [wmi]$os = Get-WmiObject Win32_OperatingSystem -Namespace "root\CIMV2" -Computername $server
		#[array]$disks = Get-WmiObject Win32_LogicalDisk -Namespace "root\CIMV2" -Filter DriveType=3 -Computername $server
		[array]$disks = Get-WmiObject Win32_LogicalDisk -Namespace "root\CIMV2" -Computername $server
		[array]$procs = Get-WmiObject Win32_Processor -Namespace "root\CIMV2" -Computername $server
        [array]$mem = Get-WmiObject Win32_PhysicalMemory -Namespace "root\CIMV2" -ComputerName $server
        [array]$nic = Get-WmiObject Win32_NetworkAdapterConfiguration -Namespace "root\CIMV2" -ComputerName $server | where{$_.IPEnabled -eq "True"}
 
        $si = @{
			Server			= [string]$server
			Manufacturer	= [string]$sysInfo.Manufacturer
			Model			= [string]$sysInfo.Model
			TotMem			= "$([string]([System.Math]::Round($sysInfo.TotalPhysicalMemory/1gb,2))) GB"
			BiosDesc		= [string]$bios.Description
			BiosVer			= [string]$bios.SMBIOSBIOSVersion+"."+$bios.SMBIOSMajorVersion+"."+$bios.SMBIOSMinorVersion
			BiosSerial		= [string]$bios.SerialNumber
			OSName			= [string]$os.Name.Substring(0,$os.Name.IndexOf("|") -1)
			Arch			= [string]$os.OSArchitecture
			Processors		= [string]@($procs).count
			Cores			= [string]$procs[0].NumberOfCores
			
		}
		
		$disks | foreach-object {$si."Drive$($_.Name -replace ':', '')"="$([string]([System.Math]::Round($_.Size/1gb,2))) GB"}
    }
    catch [Exception]
    {
        "Error communicating with $server, skipping to next"
        $si = @{
			Server			= [string]$server
			ErrorMessage	= [string]$_.Exception.Message
			ErrorItem		= [string]$_.Exception.ItemName
		}
        Continue
    }
    finally
    {
       [void]$sysCollection.Add((New-Object PSObject -Property $si))   
    }
}
 
$sysCollection `
	| select-object Server,TotMem,OSName,Arch,Processors,Cores,Manufacturer,Model,BiosDesc,BiosVer,BiosSerial,DriveA,DriveB,DriveC,DriveD,DriveE,DriveF,DriveG,DriveH,DriveI,DriveJ,DriveK,DriveL,DriveM,DriveN,DriveO,DriveP,DriveQ,DriveR,DriveS,DriveT,DriveU,DriveV,DriveW,DriveX,DriveY,DriveZ,ErrorMessage,ErrorItem `
	| sort -Property Server `
	| Export-CSV -path $outputCSV -NoTypeInformation    
	

Servers.txt should be held in the same directory as the script, & would look something like this:

MyServer1
MyServer2.fully.qualified.domain.com
YetAnotherServerName
172.24.8.1

Output looks something like this:

"Server","TotMem","OSName","Arch","Processors","Cores","Manufacturer","Model","BiosDesc","BiosVer","BiosSerial","DriveA","DriveB","DriveC","DriveD","DriveE","DriveF","DriveG","DriveH","DriveI","DriveJ","DriveK","DriveL","DriveM","DriveN","DriveO","DriveP","DriveQ","DriveR","DriveS","DriveT","DriveU","DriveV","DriveW","DriveX","DriveY","DriveZ","ErrorMessage","ErrorItem"
"MyServer1","8 GB","Microsoft Windows Server 2003 R2 Enterprise Editio","","4","","HP","ProLiant DL380 G5","Default System BIOS","P56.2.4","CZC8924LQ6      ","","","68.33 GB","0.58 GB","683.5 GB","0 GB","","","","","","","","","","","","","","","","","","","","","",""
"MyServer2.fully.qualified.domain.com","48 GB","Microsoft Windows Server 2012 Datacente","64-bit","8","1","VMware, Inc.","VMware Virtual Platform","PhoenixBIOS 4.0 Release 6.0     ","6.00.2.4","VMware-42 3b 8b d9 d3 92 b3 8e-9a 43 b1 b5 e6 a8 b2 74","0 GB","","31.66 GB","99.87 GB","3.63 GB","","","","","","","99.87 GB","","","","49.87 GB","","","","19.87 GB","","","","","","","",""
"YetAnotherServerName","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))",""
"172.24.8.1","48 GB","Microsoft Windows Server 2012 Datacente","64-bit","8","1","VMware, Inc.","VMware Virtual Platform","PhoenixBIOS 4.0 Release 6.0     ","6.00.2.4","VMware-42 3b 8b d9 d3 92 b3 8e-9a 43 b1 b5 e6 a8 b2 74","0 GB","","31.66 GB","99.87 GB","3.63 GB","","","","","","","99.87 GB","","","","49.87 GB","","","","19.87 GB","","","","","","","",""

2012-08-21

Run VBScript from SQL

Filed under: Microsoft, SQL Server, Technology — Tags: , , , — Developer42 @ 18:13

Though it’s fairly well publicised how to call an existing VBS file from SQL, or how to execute VBScript within a SQL agent, there’s not much info out there on how to have SQL run VBS directly. The code below allows you to manage your scripts in a database table, creating temp files to host and run the scripts as required. The sp_RunSQL stored proc can also be used to run any VBS script; including dynamically generated code.
As always, feedback, suggestions, etc are welcome.

Code:

    use [insert-db-name-here]
    go
    ----use below code to enable commands (required for xp_cmdshell to work)
    --exec sp_configure 'show advanced options', 1
    --go
    --reconfigure
    --go
    --exec sp_configure 'xp_cmdshell', 1  
    --go
    --reconfigure
    --go
    if OBJECT_ID('vbsScripts') is not null drop table vbsScripts
    go
    create table vbsScripts 
    (
        id bigint not null identity(1,1) constraint pk_vbsScripts primary key clustered 
        , name nvarchar(256) not null constraint uk_vbsScripts_name unique
        , script nvarchar(max) not null
        , timeoutSecs int null constraint df_vbsScripts_timeoutSecs default(86400)--leave as null if you don't want a timeout / defaults to 1 day / 24*60*60
        , batchMode bit not null constraint df_vbsScripts_batchMode default(1)
    )
    go
    /*
    Gets the temp directory from environment variables
    usage:
        declare @tempPath nvarchar(max)
        exec GetTempDirectory @tempPath out 
        select @tempPath 
    */
    if OBJECT_ID('GetTempDirectory') is not null drop proc GetTempDirectory
    go
    create proc GetTempDirectory(@path nvarchar(max) out)
    as
    begin
        set @path = ''
        declare @tempTable table(data nvarchar(max))
        insert @tempTable exec master..xp_cmdshell 'echo %temp%'
        select top 1 @path = data from @tempTable
        if SUBSTRING(@path,len(@path),1)  '\' set @path = @path + '\'
    end
    go
    /*
    Creates a unique filename (using guid to ensure uniqueness and datetime to make the name friendlier)
    usage:
        declare @tempPath nvarchar(max)
        exec GetTempDirectory @tempPath out 
        select @tempPath 
    */
    if OBJECT_ID('GetTempFilename') is not null drop proc GetTempFilename
    go
    create proc GetTempFilename(@fn nvarchar(max) out)
    as
    begin
        --exec GetTempDirectory @fn out --can just use environment variable - originally had issues testing as was looking at the wrong user's temp directory :/
        --set @fn = @fn + 'sqlTemp_' + replace(replace(replace(convert(nvarchar(24), getutcdate(),127),'-',''),':',''),'.','') + '_' + CAST(NEWID() as nvarchar(36)) + '.tmp'
        set @fn = '%temp%\' + 'sqlTemp_' + replace(replace(replace(convert(nvarchar(24), getutcdate(),127),'-',''),':',''),'.','') + '_' + CAST(NEWID() as nvarchar(36)) + '.tmp'
    end
    go
    if OBJECT_ID('dbo.fn_EscapeDosCharachters') is not null drop function dbo.fn_EscapeDosCharachters
    go
    create function dbo.fn_EscapeDosCharachters
    (
        @text nvarchar(max)
    )
    returns nvarchar(max)
    as
    begin
        --http://www.dostips.com/?t=Snippets.Escape
        set @text = REPLACE(@text,'^','^^')
        set @text = REPLACE(@text,'!','^!')
        set @text = REPLACE(@text,'&','^&')
        set @text = REPLACE(@text,'|','^|')
        set @text = REPLACE(@text,'%','%%')

	--based on experience
	set @text = REPLACE(@text,'>','^>')
	set @text = REPLACE(@text,'<','^<')
        return @text
    end
    go
    if OBJECT_ID('createTempTextFile') is not null drop proc createTempTextFile
    go
    create proc createTempTextFile
    (
        @fn nvarchar(max) out 
                          --the filename to output to (nb: environment variables don't currently work (e.g. you can't use %temp%\myFile.vbs)
                          --works fine with spaces in filename (so far at least)
                          --if user passes null a temporary filename will be auto allocated & returned in this variable
        , @content nvarchar(max)
    )
    as
    begin

        declare @charPos int
        , @cmd varchar(8000) --has to be varchar rather than nvarchar due to xp_cmdshell implementation

        if @fn is null or LEN(@fn)=0
        begin
            exec GetTempFilename @fn out
        end

        set @cmd = '@echo.>' + @fn --create a new file for our script output
        EXEC master..xp_cmdshell @cmd, no_output

        set @content = replace(@content,char(13) + char(10), char(10))--ensure uniform line endings (i.e. \r\n -> \n)
        set @content = replace(@content,char(13), char(10))--ensure uniform line endings (i.e. \r -> \n)
        set @content = @content + CHAR(10) --ensure last character of script is new line
        set @charPos = CHARINDEX(char(10),@content)
        while (@charPos > 0)
        begin
            --todo: consider what additional escaping is required to prevent injection issues
            set @cmd = '@echo.' + dbo.fn_EscapeDosCharachters(SUBSTRING(@content,1,@charPos-1)) + '>> ' + @fn
            EXEC master..xp_cmdshell @cmd, no_output
            set @content = SUBSTRING(@content,@charPos+1,len(@content))
            set @charPos = CHARINDEX(char(10),@content)
        end

    end 
    go
    if OBJECT_ID('deleteTempTextFile') is not null drop proc deleteTempTextFile
    go
    create proc deleteTempTextFile
    (
        @fn nvarchar(max)
    )
    as
    begin
        declare @cmd varchar(8000)
        if CHARINDEX(' ',@fn)>0 and CHARINDEX('"',@fn)>1 set @fn = QUOTENAME(@fn,'"')
        set @cmd = 'del ' + @fn
        EXEC master..xp_cmdshell @cmd, no_output
    end
    go
    if OBJECT_ID('sp_RunScript') is not null drop proc sp_RunScript
    go
    create proc sp_RunScript
    (
        @script nvarchar(max)
        , @arguments nvarchar(max)
        , @timeoutSecs int = null
        , @batchMode bit = 1
        , @tempfileUri nvarchar(max) out
    )
    as
    begin
    
        declare @cmd varchar(8000) --has to be varchar rather than nvarchar due to xp_cmdshell implementation
        
        exec createTempTextFile @tempfileUri out, @script
        
        if CHARINDEX(' ',@tempfileUri)>0 and CHARINDEX('"',@tempfileUri)>1 set @tempfileUri = QUOTENAME(@tempfileUri,'"')
        set @cmd = 'cscript ' + @tempfileUri + ' //E:vbscript //NOLOGO '
        
        --batch mode or interactive
        if @batchMode=1 
            set @cmd = @cmd + '//B '
        else
            set @cmd = @cmd + '//I '
        
        --should script timeout after x seconds?
        if @timeoutSecs is not null
            set @cmd = @cmd + '//T:' + CAST(@timeoutSecs as nvarchar(18)) + ' '
        
        set @cmd = @cmd + isnull(@arguments,'')
        --select @cmd --if debugging enable this line to see the script file / etc
        
        EXEC master..xp_cmdshell @cmd --if required we can capture output as has been done in GetTempDirectory
        
        exec deleteTempTextFile @tempfileUri --tidyup the temp script - disable this line for debugging
            
    end
    if OBJECT_ID('sp_RunScriptByID') is not null drop proc sp_RunScriptByID
    go
    create proc sp_RunScriptByID
    (
        @scriptId bigint
        , @arguments nvarchar(max)
    )
    as
    begin
    
        declare @timeoutSecs int
        , @batchMode bit
        , @script nvarchar(max)
        , @tempfileUri nvarchar(max)
        , @cmd varchar(8000) --has to be varchar rather than nvarchar due to xp_cmdshell implementation
            
        select @timeoutSecs=timeoutSecs 
        , @batchMode = batchMode
        , @script = script
        from vbsScripts 
        where id = @scriptId
        
        exec sp_RunScript  
			@script
			, @arguments 
			, @timeoutSecs 
			, @batchMode 
			, @tempfileUri out 
        
    end
    go
    if OBJECT_ID('sp_RunScriptByName') is not null drop proc sp_RunScriptByName
    go

    /*
    provides a friendly interface to sp_RunScriptByID
    */
    create proc sp_RunScriptByName
    (
        @scriptName nvarchar(256)
        , @arguments nvarchar(max)
    )
    as
    begin

        declare @id bigint

        select @id = id
        from vbsScripts 
        where name = @scriptName

        exec sp_RunScriptByID @id, @arguments

    end
    go

Example Usage:

    --demo

    --register a new script in the scripts table
    insert vbsScripts 
    select 'demo', '
    option explicit
    dim objFSO, i, path
    path = "c:\example1\"
    wscript.echo "hello" ''show what console output looks like (if interactive)
    for i = 0 to wscript.Arguments.Count-1 ''show that we can handle command line arguments
        wscript.echo wscript.arguments.item(i)
    next
    set objFSO = CreateObject("Scripting.FileSystemObject")    
    if not objFSO.FolderExists(path) then
		on error resume next
        objFSO.CreateFolder(path) ''create a folder to demonstrate that the vbs is running / affecting the outside environment
        if err.number = 0 then
			wscript.echo "Folder " & path & " successfully created."
		else
			wscript.echo "Folder " & path & " was not created.  " & cstr(Err.number) & ": " & Err.Description
			err.clear
		end if
        on error goto 0
    else
        wscript.echo "Folder " & path & " already exists."
    end if
    set objFSO = Nothing
    wscript.echo "Done"
    ', null, 0
    go

    --execute above script via friendly name
    sp_RunScriptByName 'demo','"this is a demo" "hopefully it will work" yes it does'

2010-06-29

My First Python Plugin

Filed under: Technology, WordPress — Tags: , , , , , , , , — Developer42 @ 22:51

I’ve read a lot about Python in the past, but have been so inundated with things to learn recently that it had always fallen to the bottom of the pile. However, recently I requested a feature for my favourite text editor, Programmers’ Notepad, and heard back from its developer, Simon, within moments of submitting my request. He’d provided a solution and instructions on how to implement it, which introduced me to a powerful set of features I’d previously been unaware of (namely that you could run apps which output to the command line straight from the tools menu, and have their output returned into the current document). After realising I’d been missing out on some of this potential, I spent a bit of time going over the pnotepad.org site, seeing what else I’d missed. This is where I come back to Python. PNotepad allows you to write custom scripts in Python, put them into pn’s \scripts directory, and run them from the scripts toolbar the next time you start up the editor. I figured any time spent learning python would easily be offset by the hours saved by having scripts take care of laborious tasks for me, so promptly downloaded and installed Python and PyPN (which enables Python scripts in PN), had a look at a script from the script repository, and a browse through the python manual, and then wrote my first script in a few minutes. For anyone wanting to see it, my script is a simple tool for replacing < > and & characters with their HTML codes, and inserting the PRE and CODE tags required to publish code to wordpress. I’ve uploaded it to the script share, here.

First Impressions
My first impressions of Python, after having coded for all of 20 mins or so, are that it’s a really simple language to learn, which anyone (including non developers) should be able to pick up pretty quickly. There are a few bits I’m not so keen on; select statements don’t exist, using instead piles of else-if (elif) statements, and not having curly brackets and semi-colons, but instead relying on indentation leaves me feeling a little out of my comfort zone, but all this is just a familiarity thing, which I’m sure I’ll soon get over. I’m hoping to write a few more scripts over the next few weeks (assuming I can find interesting and required things to write), and to learn a bit more about Python outside of PN, after which I’ll report back on my experiences for anyone else interested in heading in that direction.

Steps Taken to Run Python in PN
If you’d also like to get into Python for PN, here’s a step by step set of instructions on what to do (skipping a few steps to avoid typing “click next” too many times).
Download & install/extract the following, in the order listed below:

Once installed, go to the command line (windows key + r, cmd, enter), navigate to the programmer’s notepad directory (cd %programfiles%\programmer*), then type the following “pn –findexts”. It looks like nothing’s happened, but that’s good; it hasn’t errorred.
Next, load up programmer’s notepad, click view, windows, scripts (or press alt+F10), and you should see a list of available scripts.
Get an existing script from the script share site: http://scriptshare.rocketmonkeys.com/ and save the file to your …\Programmer’s Notepad\scripts\ directory.
Close and reopen PN. You should now see a new script (or several) listed in the scripts toolbar.
From here on, it’s pretty easy to figure out what to do to create your own scripts by looking at existing scripts and python code, and playing.
Good luck.

2010-04-10

2010 Scripting Games

Filed under: Microsoft, Technology — Tags: , , , , — Developer42 @ 15:40

I’ve just discovered a coding competition for people who write small but useful utility scripts. Since I my projects tend to get affected by my short attention span, but I enjoy writing useful & helpful stuff, this seems the ideal arena for my work. If you’re also interested in the games, please see the site, linked below:

2010 Scripting Games

Grab this badge here!

Blog at WordPress.com.

%d bloggers like this: