Temporary Windows file

Sometimes it can be usefull to create a temporary Windows file, that is more general available than a personal temporary user file.
If you use the environmental variablesTEMP“ or „TMP“ you get your personal folder for temporary files, e.g. „C:\Users\Niels\AppData\Local\Temp“. Both variables gives the same folder name.

To get the path of the more general folder of temporary files „C:\Windows\Temp“ you can use the environmental variables „SystemRoot“ or „windir“ and add the name of the Temp-folder in PowerShell like

To create a unique and identifiable temporary file I usually add a timestamp to the filename like
.$(("{0:s}Z" -f $([System.DateTime]::UtcNow)).Replace(':', '_')).
The Z is to indicate that the timestamp is UTC. That is timezone Z also called Zulu time.
The Replace method of the formatted String-object is used to get rid of the colons in the standard format. A colon is not acceptep by Windows in a file name.

The name of a temporary file can be created in a single PowerShell statement
$TempFile = "$Env:SystemRoot\Temp\_temp.$(("{0:s}Z" -f $([System.DateTime]::UtcNow)).Replace(':', '_')).ps1"

Writing to the temporary file can be done in PowerShell with the cmdlet Out-File or PowerShell redirection operators.
The first line can be written without any terms, but the following lines must be added to preserve the existing contents of the temporary file.
The first line, that also creates the temporary file, can be written in PowerShell like
'1st line' | Out-File -FilePath $TempFile
A second line can be added using the cmdlet Out-File like
'2nd line' | Out-File -FilePath $TempFile -Append
A third line can be added with the PowerShell appending redirection operator like
'3rd line' >> $TempFile

With the three examples above a temporary file is created
The contents of the temporary file is
1st line
2nd line
3rd line

This simple technique can be used to create other temporary Windows files like text files or data (csv) files.


xp_instance_regwrite syntax

The stored procedure "master.sys.xp_instance_regwrite" is not documented in SQL Server Books Online (BOL), but is usefull. In general the syntax is
EXECUTE [master].[sys].[xp_instance_regwrite]
  @rootkey = N'<name of root key>'
 ,@key = N'<key name>'
 ,@value_name = N'<key name value>'
 ,@type = N'<key type>'
 ,@value = N'<key value>';

For example the number of SQL Server Errorlog files can be updated by the statement
EXECUTE [master].[sys].[xp_instance_regwrite]
  @rootkey = N'HKEY_LOCAL_MACHINE'
 ,@key = N'Software\Microsoft\MSSQLServer\MSSQLServer'
 ,@value_name = N'NumErrorLogs'
 ,@type = N'REG_DWORD'
 ,@value = 42;

Use with caution!

The parameter @type takes a value without N'' around (@type = <key type>), but I like the Unicode framing. This way I try to avoid difficulties when building the values outside the call.

The type of the value for the parameter @value depends on the registry value type.
If the registry value type is a integer like REG_DWORD, the parameter value must be a integer.
 ,@value = 42;
If the registry value type is a string like REG_SZ, the parameter value must be a string.
 ,@value = N'John';

PowerShell usage

If the usage of this (undocumented) procedure should be included in a PowerShell script, it could be with ADO.NET in a function like this
function Write-MsSqlDbInstanceRegistry
  Param (
    # help description
    [Parameter(Mandatory=$true, Position=0)]

    # Registry key path
    [Parameter(Mandatory=$true, Position=1)]

    # Registry key name
    [Parameter(Mandatory=$true, Position=2)]

    # Registry key type
    [Parameter(Mandatory=$true, Position=3)]

    # Registry key value
    [Parameter(Mandatory=$true, Position=4)]

  Begin {
    "{0:s}Z Write-MsSqlDbInstanceRegistry( '$RegKey', '$RegName', '$RegType', '$RegValue')" -f $([System.DateTime]::UtcNow) | Write-Verbose

  Process {
    $MsSqlDbCmd = New-Object System.Data.SqlClient.SqlCommand
    $MsSqlDbCmd.Connection = $MsSqlDbCnn
    $MsSqlDbCmd.CommandText = '[master].[sys].[xp_instance_regwrite]'
    $MsSqlDbCmd.CommandType = [System.Data.CommandType]::StoredProcedure

    $MsSqlDbCmd.Parameters.Add("@rootkey", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@rootkey'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@rootkey'].Value = 'HKEY_LOCAL_MACHINE'

    $MsSqlDbCmd.Parameters.Add("@key", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@key'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@key'].Value = $RegKey

    $MsSqlDbCmd.Parameters.Add("@value_name", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@value_name'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@value_name'].Value = $RegName

    $MsSqlDbCmd.Parameters.Add("@type", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
    $MsSqlDbCmd.Parameters['@type'].Direction = [System.Data.ParameterDirection]::Input
    $MsSqlDbCmd.Parameters['@type'].Value = $RegType

    switch -CaseSensitive ($RegType) {
      'REG_DWORD' {
        $MsSqlDbCmd.Parameters.Add("@value", [System.Data.SqlDbType]::Int) | Out-Null
        $MsSqlDbCmd.Parameters['@value'].Value = [Int]$RegValue
      'REG_SZ' {
        $MsSqlDbCmd.Parameters.Add("@value", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
        $MsSqlDbCmd.Parameters['@value'].Value = $RegValue
      default {
        "{0:s}Z Unknown Registry Value Type '$RegKeyType'." -f $([System.DateTime]::UtcNow) | Write-Error
    $MsSqlDbCmd.Parameters['@value'].Direction = [System.Data.ParameterDirection]::Input

    [Int]$RowCount = $MsSqlDbCmd.ExecuteNonQuery()

  End {}

The PowerShell function can be used like this
$Cnn = New-Object System.Data.SqlClient.SqlConnection
$Cnn.ConnectionString = 'Data Source=(local);Integrated Security=SSPI;Application Name=MsSqlDbInstanceRegistry'

Write-MsSqlDbInstanceRegistry `
  -MsSqlDbCnn $Cnn `
  -RegKey 'Software\Microsoft\MSSQLServer\MSSQLServer' `
  -RegName 'NumErrorLogs' `
  -RegType 'REG_DWORD' `
  -RegValue '42' `


2010-09-15  First entry.
2013-09-09  PowerShell added.
2013-11-19  Registry Value Types handled correct.


Backup failed - nonrecoverable I/O

On a SQL 2008 SP2 (10.0.4000) I take database backup with a Maintenance Plan. This morning I had a failed database backup:
Executing the query "BACKUP DATABASE [thedatabase] TO  DISK = N'T:\\Backup\\th..." failed with the following error: "A nonrecoverable I/O error occurred on file "T:\\Backup\\thedatabase_backup_2011_01_18_221400_5137140.bak:" 112(failed to retrieve text for this error. Reason: 15105).
No nice :-(

When I looked in the log file generated by the Maintenance Plan, the error message was:
Task start: 2011-01-18T22:14:00.
Task end: 2011-01-18T22:14:01.
Failed:(-1073548784) Executing the query "BACKUP DATABASE [thedatabase] TO  DISK = N'T:\\Backup\\th..." failed with the following error: "A nonrecoverable I/O error occurred on file "T:\\Backup\\thedatabase_backup_2011_01_18_221400_5137140.bak:" 112(failed to retrieve text for this error. Reason: 15105).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

When I looked in the Windows System Log, there was no relevant entries.
But looking at the drive and the free space I saw that there was not space enough for the next backup file.
After a cleanup and manual execution of the Maintenance Plan everything was green.

The amount of free space can be looked up with PowerShell like this:
Get-WmiObject -Query "SELECT FreeSpace, Size FROM Win32_LogicalDisk WHERE DeviceID = 'T:'" -ComputerName SANDY.sqladmin.lan |
Format-Table @{Name='Freespace (GB)';Expression={"{0:N1}" -f($_.FreeSpace/1gb)}}, @{Name='Size (GB)';Expression={"{0:N1}" -f($_.Size/1gb)}} -AutoSize

The value of the parameter ComputerName should be changed to the actual databaseserver, also value in the WQL WHERE clause should be changed to the drive indicated in the error message.

The follow up is to order some additional storage.

I find the error message somewhat misleading. It looks like the ResultSet part is from the connectivity and not from the root error.


2011-01-19 : This is postede for the first time.
2013-08-22 : The PowerShell script is added.



With SQL Server 2012 SQLPS was changes from a mini-PowerShell to a PowerShell module. This makes is much simpler to import the functionality in a regular PowerShell script.
The examples below are written on a computer running Windows 8 and SQL Server 2012.
SQLPS is imported much simpler than we used to import SQL Server Management Objects SMO assemblies.
Import-Module 'SQLPS'
The parameter „DisableNameChecking“ can be added to avoid a message about unapproved verbs.
A list of exported Cmdlets in the module can be shown
(Get-Module 'SQLPS' -ListAvailable).ExportedCmdlets.Values.Name


Server Object

To create a SMO Server object on a local default SQL Server database instance can be like
$SmoServer = New-Object Microsoft.SqlServer.Management.Smo.Server '(local)'
SMO only connects to the when you ask for something specific. For a start I would like to know the basics about the installation.

Parent                      : [TITANIUM]
Version                     : 11.0.3128
EngineEdition               : EnterpriseOrDeveloper
ResourceVersion             : 11.0.3000
BuildClrVersion             : 4.0.30319
BuildClrVersionString       : v4.0.30319
BuildNumber                 : 3128
Collation                   : Latin1_General_100_CI_AS_KS_WS_SC
The full set of Server property values can be accessed
$SmoServer | Format-List *

AuditLevel                  : Failure
BackupDirectory             : D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup
BrowserServiceAccount       : NT AUTHORITY\LOCALSERVICE
BrowserStartMode            : Disabled
BuildClrVersionString       : v4.0.30319
BuildNumber                 : 3128

Database Object

A SMO Database object can be created on a database with a known name
$SmoDatabase = $SmoServer.Databases['msdb']
This object can give access to some information
$SmoDatabase | Format-Table DataSpaceUsage,IndexSpaceUsage -AutoSize

DataSpaceUsage IndexSpaceUsage
-------------- ---------------
12080            3136

Table Object

Also the Database object can be used to create a SMO Table object. This can be direct on the table name alone
Or the Table object can be create more specific on the schema name with the table name
$SmoTable= ($SmoDatabase.Tables |
Where-Object -FilterScript { $PSItem.Schema -eq 'dbo' -and $PSItem.Name -eq 'sysjobhistory' })

Again the Table object gives access to some basic information
$SmoTable | Format-Table Schema,Name,DataSpaceUsed,IndexSpaceUsed,RowCount -AutoSize

Schema Name          DataSpaceUsed IndexSpaceUsed RowCount
------ ----          ------------- -------------- --------
dbo    sysjobhistory            32             32       84

Stored Procedure Object

The Database object can also be used to create a SMO Stored Procedure object. Like the Table object it can be created in two ways, but I will be (professional) lazy and only show the creation on the name alone
$SmoStoredProcedure = $SmoDatabase.StoredProcedures['sp_get_job_alerts']
A stored procedure usually has one or more parameters that can be described with
$SmoStoredProcedure.Parameters | Format-Table Name,DataType,DefaultValue,IsOutputParameter -AutoSize

Name      DataType         DefaultValue IsOutputParameter
----      --------         ------------ -----------------
@job_id   uniqueidentifier                          False
@job_name sysname                                   False

Server Job Object

With the installation of the SQL Server database service is also installed SQL Server Agent that is the SQL Server scheduler used for maintenance, monitoring and other automated tasks.
A SMO Job Server object can be created from a Server object property and gives a quick access to some basic SQL Server Agent information

AgentDomainGroup          : NT SERVICE\SQLSERVERAGENT
AgentLogLevel             : Errors, Warnings
AgentMailType             : SqlAgentMail
AgentShutdownWaitTime     : 15
DatabaseMailProfile       : 
ErrorLogFile              : D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT
More interesting is the information om a job, where a SMO Server Job object can be created on the Server object
$SmoJob = $SmoServer.JobServer.Jobs['syspolicy_purge_history']
This gives access to some basic information on the job and its execution
$SmoJob | Format-Table CurrentRunStatus,LastRunDate,LastRunOutCome,NextRunDate -AutoSize

CurrentRunStatus LastRunDate         LastRunOutcome NextRunDate        
---------------- -----------         -------------- -----------        
            Idle 05-04-2013 02:00:00      Succeeded 21-05-2013 02:00:00


There are much more on SMO in MSDN Library, you can start with
Microsoft.SqlServer.Management.Smo Namespace
SMO is much larger which is indicated at
SQL Server Management Objects Reference“, take a look at the index to the left.


SQL Alias

Some applications can not use a connectionstring on a given TCP port number or a named database instance. This can be solved by a SQL Alias. Usually this is created using SQL Server Configuration Manager, but a installation of SQL Server Client Connectivity is actually not required.
A SQL Alias can be created as a Connectivity alias using the tool"cliconfg.exe", that is a part of Windows Data Access Components (WDAC, formerly MDAC).


The "cliconfg.exe" comes in two builds
  • 64-bit: %windir%\System32\cliconfg.exe
  • 32-bit: %windir%\SysWOW64\cliconfg.exe
when you are on a 64-bit (x64) Windows. On a 64-bit Windows the 64-bit "cliconfg.exe" is default.
Usually you have to be local administrator to add a new alias.
Any SQL Alias if it is 32-bit or 64-bit created by "cliconfg.exe" can be crated and edited in SQL Server Configuration Manager if the SQL Client Connectivity is installed.


The registration of a SQL Alias is placed in the registry:
  • 64-bit: \\HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo\
  • 32-bit: \\HKLM\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo\
Like any other entry in the registry they can be managed by the native API or by Windows Management Instrumentation (WMI) using a COM tool like Windows Scripting Host (WSH) by JScript or using a .NET tool like PowerShell.
The PowerShell cmdlet New-ItemProperty can be used like this:
[String]$RegPath = 'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo'
[String]$RegName = 'SPSQL'
[String]$RegData = 'DBMSSOCN,DBSERVER.bacon.lan,54321'

New-ItemProperty -Path $RegPath -Name $RegName -PropertyType String -Value $RegData

DBMSSOCN is the TCP/IP network libary.

Check SQL Alias

Like any other connection a SQL alias can be checked by a Universal Data Link (UDL) file.
  1. Create a empty text file and rename it “something.udl” to the filetype UDL.
  2. Edit the properties of the UDL definition. This can be done by doubleclick on the file in Windows Explorer.
  3. Pick a provider, e.g. “Microsoft OLE DB Provider for SQL Server”
  4. Enter the name of the SQL Alias as servername and security information.
  5. Click “Test”.
WARNING! The content of a UDL file is clear text. If you enter username and password for a SQL Login, this will be readable.


MSDN Blogs > SQL Protocols: “Connection Alias”.
Molson Online: “Windows 7 64 bit and SQL Server cliconfg”.
MSDN Blogs > SQL Protocols: “Configure and manage SQL Server 2005 from your application using WMI”.
MSDN Blogs > SQL Protocols: “Configure Aliases Programmatically Using WMI”.


2011-04-23 Original blog entry.
2013-04-13 PowerShell cmdlet New-ItemProperty added.


SQLPS Clear-Host error

When a ps1-scriptfile contains a Clear-Host statement, which I often use when testing a script in PowerShell ISE, the script fails on SQLPS with an error like this:
A job step received an error at line 3 in a PowerShell script. The corresponding line is '$space.ForegroundColor = $host.ui.rawui.ForegroundColor'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception setting "ForegroundColor": "Cannot convert null to type "System.ConsoleColor" due to invalid enumeration values. Specify one of the following enumeration values and try again. The possible enumeration values are "Black, DarkBlue, DarkGreen, DarkCyan, DarkRed, DarkMagenta, DarkYellow, Gray, DarkGray, Blue, Green, Cyan, Red, Magenta, Yellow, White"."

A quick work-around is to comment out the Clear-Host statement as a preparation to execute it as a PowerShell step in a SQL Server Agent job.

This is on a Windows Server 2008 R2 with SQL Server 2008 R2. The Windows PowerShell is 3.0 while SQLPS is 2.0.


Call command-line in PowerShell

Often I have to build a command-line statement dynamic and then execute the statement.
The PowerShell call operator (&) can be cumbersome to use, but following some simple rules or using a general template can do the trick. This operator is also called the invoke operator.


When using the call operator you have to remember it executes the string to the first space, and because of this it can not take a command-line string with parameters like „ping www.sqladmin.info -n 5“.
A solution to this is given by Devlin Bentley in the blog entry „PowerShell Call Operator (&): Using an array of parameters to solve all your quoting problems“.

In general the trick is to call the command with the operator and put parameters in an additional array.
[String]$cmd = 'ping'
$cmd += '.exe'
[String[]]$param = @('www.sqladmin.info','-n','5')
& $cmd $param

A output of this could be
Pinging www.sqladmin.info [] with 32 bytes of data:
Reply from bytes=32 time=16ms TTL=120
Reply from bytes=32 time=24ms TTL=120
Reply from bytes=32 time=23ms TTL=120
Reply from bytes=32 time=20ms TTL=120
Reply from bytes=32 time=19ms TTL=120

Ping statistics for
Packets: Sent = 5, Received = 5, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 16ms, Maximum = 24ms, Average = 20ms

By putting the command in a string by itself, it can be build in script with path and so on. I have tried to illustrate this by adding the file extension in a seperate line of the script above.

The parameter values can be defined element by element into a parameter array.
[String]$cmd = 'ping'
$cmd += '.exe'
[String[]]$param = @()
$param += 'www.sqladmin.info'
$param += '-n'
$param += '5'
& $cmd $param

Catch output

To catch the output line by line it can be taken from the output stream.
[String]$cmd = 'ping'
$cmd += '.exe'
[String[]]$param = @('www.sqladmin.info','-n','5')
& $cmd $param |
ForEach-Object { "{0:s}Z $($_)" -f $([System.DateTime]::UtcNow) }

A output of this could be
2013-03-26T13:10:39Z Pinging www.sqladmin.info [] med 32 byte data:
2013-03-26T13:10:39Z Reply from byte=32 time=3ms TTL=118
2013-03-26T13:10:40Z Reply from byte=32 time=2ms TTL=118
2013-03-26T13:10:41Z Reply from byte=32 time=3ms TTL=118
2013-03-26T13:10:42Z Reply from byte=32 time=2ms TTL=118
2013-03-26T13:10:43Z Reply from byte=32 time=2ms TTL=118
2013-03-26T13:10:43Z Ping statistics for
2013-03-26T13:10:43Z Packets: Sent = 5, Recieved = 5, Lost = 0 (0% loss),
2013-03-26T13:10:43Z Approximate round trip times in milli-seconds:
2013-03-26T13:10:43Z Minimum = 2ms, Maximum = 3ms, Average = 2ms

Be aware of large amounts of output. I have experienced that several thousand lines of output will make the script execution unstable in a unpredictable way.


A simple exception handling with Try-Catch-Finally blocks could be like
[String]$cmd = 'ping'
$cmd += '.exe'
[String[]]$param = @('www.sqladmin.info','-n','5')
try {
  & $cmd $param |
  ForEach-Object { "{0:s}Z $($_)" -f $([System.DateTime]::UtcNow) }
catch {
  "{0:s}Z ERROR: $($_.Exception.Message)" -f $([System.DateTime]::UtcNow)

If the command in the variable $cmd is change to „noping.exe“, the output will like
2013-03-29T14:42:31Z ERROR: The term 'noping.exe' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.


In the beginning PowerShell only had the Trap command, but it had some limitations. Passing on a error from a Trap can be done with the Throw.
It is generally recommended to use Try-Catch-Finally that we got with PowerShell v2.
Jeffrey Snover shared a very short but precise comparison in „Traps vs Try/Catch“.


The automatic variable $LastExitCode contains the exit code of the last program that was executed.
This is nice in a simple setup, but sometimes there are several errors from one program. In such case I would like to know all errors and their details.
The automatic variable $Error is an array of all errors (System.Management.Automation.ErrorRecord) in the current PowerShell session. The latest error is the first element in the array ($Error[0]).
This is a simple demonstration how $Error can be used:

try {
  throw [System.DivideByZeroException]
catch {
finally {
  ":: Final"

if ($Error) {
  "{0:s} Error in script execution. See log for details." -f $([System.DateTime]::Now)
  if ($Error[0].Exception -match 'System.DivideByZeroException') {
    "-- Divide-By-Zero Exception"

"$($Error.Count) Error(-s)."

The result will be something like this:
Category : OperationStopped
Activity :
Reason : RuntimeException
TargetName : System.DivideByZeroException
TargetType : RuntimeType

:: Final
2014-01-27T20:45:29 Error in script execution. See log for details.
-- Divide-By-Zero Exception
1 Error(-s).

When building/developing a script $Error is a great tool to look into an error.
Details on the exception itself can be very usefull. This you can get by piping the Exception to the CmdLet Get-Member. The professional lazy scripting guy can use the alias:
$error[0].Exception | gm

Injection attack

Several blog posts and forum answers uses the cmdlet Invoke-Expression, but please notice that this could make the script open to injection attacs. This is described by the Windows PowerShell Team in the blog post „Invoke-Expression considered harmful“.

Running Executables

This subject is already described in a TechNet Wiki article: „PowerShell: Running Executables“.
The article covers several other methods to call a command-line in PowerShell, but I will for now stay with the call operator.


2014-01-27 : Exception handling added note on Trap. Error section added.
2013-03-29 : Exception handling example added.
2013-03-26 : The parts on piping the output and defining the parameter values element by element are added.