Adding Application Name to Invoke-SqlCmd2

In a previous post, I expressed some frustration over Invoke-SqlCmd not setting an Application Name for its ODBC connection, leaving us with the generic .NET SqlClient Library when looking at active sessions in sp_who2 and sp_whoisactive (and any other monitoring tool). Unfortunately, I can’t really do anything about Invoke-SqlCmd aside from posting a suggestion on Connect or the Client Tools Trello board, but Invoke-SqlCmd2 has the same issue and that’s on GitHub. So, here we go!

In its current form, if a SqlConnection object isn’t passed into Invoke-SqlCmd2, the cmdlet does the following:

1
2
3
4
5
6
7
8
9

if ($Credential) {
   $ConnectionString = "Server={0};Database={1};User ID={2};Password=\`"{3}\`";Trusted\_Connection=False;Connect Timeout={4};Encrypt={5}" -f $SQLInstance,$Database,$Credential.UserName,$Credential.GetNetworkCredential().Password,$ConnectionTimeout,$Encrypt;
} else { 
   $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2};Encrypt={3}" -f $SQLInstance,$Database,$ConnectionTimeout,$Encrypt;
}
$conn = New-Object System.Data.SqlClient.SQLConnection;
$conn.ConnectionString = $ConnectionString;
Write-Debug "ConnectionString $ConnectionString";
 }

I decided to change this around so that it no longer uses string formatting, but instead a SqlConnectionStringBuilder. I had a couple reasons for this:

• It will eliminate redundant code. There are several common elements in each of the ConnectionStrings above. If more complex logic is needed, there are potentially more copies of this ConnectionString kicking around.
• It’s prone to copy/paste and other editing errors. If there’s a change that affects both versions of the ConnectionString and the developer just copies the line from one branch of the if statement to the other, code will be lost or invalid values will be substituted because of positioning.

With this in mind, I factored the common elements out to build the base of the ConnectionString, then added the remaining elements conditionally based on the cmdlet inputs.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

$CSBuilder = New-Object -TypeName System.Data.SqlClient.SqlConnectionStringBuilder;
$CSBuilder["Server"] = $SQLInstance;
$CSBuilder["Database"] = $Database;
$CSBuilder["Connection Timeout"] = $ConnectionTimeout;
if ($Encrypt) {
   $CSBuilder["Encrypt"] = $true;
 }
 if ($Credential) {
   $CSBuilder["Trusted_Connection"] = $false;
   $CSBuilder["User ID"] = $Credential.UserName;
   $CSBuilder["Password"] = $Credential.GetNetworkCredential().Password
 } else {
   $CSBuilder["Integrated Security"] = $true;
 } 

Before going any further in adding support for inserting Application Name into the ConnectionString, I had to add a parameter to the cmdlet itself.

1

[Parameter( Position=11, Mandatory=$false )] [Alias( 'Application', 'AppName' )] [String] $ApplicationName 

With that complete, I can now add it into the SqlConnectionStringBuilder.

1
2
3
4
5
6
7
8

if ($ApplicationName) {
  $CSBuilder["Application Name"] = $ApplicationName
} else {
  $ScriptName = (Get-PSCallStack)[-1].Command.ToString();
  if ($ScriptName -ne "") {
    $CSBuilder["Application Name"] = $ScriptName;
  }
} 

Because ApplicationName is an optional parameter, I had to account for cases where it’s not specified by the caller. What the code above is doing is looking at the entire call stack and going back up to the very top to get the name of the script file that was run and ultimately called Invoke-SqlCmd2. Finally, I extract the ConnectionString from the SqlConnectionStringBuilder and assign it to the SqlConnection’s ConnectionString property.

1
2
3

$ConnectionString = $CSBuilder.ToString();
$conn.ConnectionString = $ConnectionString;
Write-Debug "ConnectionString $ConnectionString" 

My code changes complete, I reviewed my work and sent my first real pull request off to Warren (blog|twitter) as PR #7 for the module.