Identify your Powershell Script to SQL
I recent wrote a backup script that ran asynchronously and have had some weird problems with it rarely just never stopping. I’m no SQL expert so its hard for me to diagnose on that end. I’ve popped in to SQL Analyzer to try and see what DB its hung up on and just cant figure out what processes are from my job since there are just so many. I noticed there is an Application Name listed and there were some custom names so I got looking in to this and found I can name my connections to help identify which SQL processes are from my script.
|[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null
$sqlapp = New-Object microsoft.sqlserver.management.common.sqlconnectioninfo DBSERVER
$sql = New-Object microsoft.sqlserver.management.smo.server $sqlapp
That’s all there is to it. If you still use the old style connection objects with a connection string you can specify the application name in the connection string.
Data Source=DBSERVER;Initial Catalog=TheDB;Application Name=Powershell Magic;
This certainly helps with debugging Powershell SQL scripts!