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.

For my backup script I was using an SMO Server object which could be constructed with a SQLConnectionInfo object which happened to allow me to set the application name, here is an example of that.

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null

$sqlapp = New-Object microsoft.sqlserver.management.common.sqlconnectioninfo DBSERVER
$sqlapp.ApplicationName = “Powershell Magic”

$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!

Advertisements

About jrich

I am the Solutions Architect for Apex Learning in Seattle WA. I've been working with computers since I was 13. Started programming when I was 14. Had my first IT job as tech support at an ISP at the age of 15 and became a network admin at the age of 17. Since then I've worked at a variety of small to mid size companies supporting, maintaining and developing all aspects of IT. Mostly working with Windows based networks but have recently been working with Solaris system as well. I created this blog mostly as a place for me to take my own notes, but also share things that I had a hard time finding the info for.

Posted on August 2, 2011, in SQL Server, WMF (Powershell/WinRM) and tagged , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: