Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is it possible to execute a stored procedure from a SQL agent job while specifying a custom Application Name, similar to specifying the Application Name in a connection string?

The reason I want to do this is is to a avoid a trigger on the database that specifically distinguished on the Application Name used by the connection string.

I know you can execute an integration package with a different connection string, but I hope this is possible for T-sql jobs as well.

share|improve this question

migrated from May 13 '13 at 15:01

This question came from our site for professional and enthusiast programmers.

up vote 4 down vote accepted

I don't think there's a built-in way to do this, but here are a few alternatives:

  1. Use OPENDATASOURCE like this:

    EXEC OPENDATASOURCE('SQLNCLI', 'Data Source=MyServer;Integrated Security=SSPI;Initial Catalog=master;Application Name="test app"')

    Note that using OPENDATASOURCE requires you to turn on the Ad hoc Distributed Queries sp_configure setting (as noted in the Remarks section of the documentation) even though you won't be writing code that accesses another server (SQL Server doesn't know that).

  2. Create a wrapper SSIS package that specifies the application name in a Connection Manager, and then uses an Execute SQL task to run the stored procedure.

  3. Create a wrapper command-line app that takes a connection string and a command as parameters; execute it using an Operating System (cmdexec) job step.

share|improve this answer
Option one will work I think. The reason is that I want to be able to set up the SP and the Job using a .net Gui, so I want to avoid using packages or other files. Thanks! – coussej May 14 '13 at 7:16
@user196124: You're welcome. There are many ways to do what you want (I think even something like a SQL CLR procedure, or PowerShell as mentioned in the other answer); it comes down to which types of objects you want to set up. (Having said that, I do like the PowerShell approach.) – Jon Seigel May 14 '13 at 12:31
It works :-) I can now set up a job and schedule it just by executing some sql code from my .net application, without having to worry about files. – coussej May 15 '13 at 7:02

You could write a PowerShell script to achieve this and have that called from the SQL Agent which allows PowerShell as one of the command types:

Here's some code to help you get started:

Function Get-MyConnectionString{

    If (!($ApplicationName)) { $ApplicationName = "PowerShell (Unknown)" }

    $SqlConnectionBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
    $SqlConnectionBuilder["Data Source"] = $Server;
    $SqlConnectionBuilder["Application Name"] = $ApplicationName;

    If (!(($UserName -eq $null) -or ($UserName -eq ""))) {
        $SqlConnectionBuilder["User ID"] = $UserName
        $SqlConnectionBuilder["Password"] = $Password
    Else {
        $SqlConnectionBuilder["Integrated Security"] = $true

    If (!(($Database -eq $null) -or ($Database -eq ""))){
        $SqlConnectionBuilder["Initial Catalog"] = $Database

    #SWrite-Debug ("Built connection string: {0}" -f $SqlConnectionBuilder.ConnectionString)
    Write-Output ($SqlConnectionBuilder.ConnectionString)

Function Invoke-MySqlCmd
        [string]$ApplicationName="My Application"

    # Build the connection string
    $ConnectionString = (Get-MyConnectionString -Server $Server -Database $Database -Username $UserName -Password $Password -ApplicationName $ApplicationName)
    #Write-Debug("Using connection string: {0}" -f $ConnectionString);

    $Connection = New-Object System.Data.SqlClient.SqlConnection
    $Connection.ConnectionString = $ConnectionString

    $SqlCommand = New-Object System.Data.SqlClient.SqlCommand($Query, $Connection);
    $SqlDataSet = New-Object system.Data.DataSet
    $SqlDataAdapter = New-Object system.Data.SqlClient.SqlDataAdapter($SqlCommand)


    Write-Output ($SqlDataSet.Tables)
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.