Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

migrated from stackoverflow.com May 13 '13 at 15:01

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

2 Answers

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"')
        .master.sys.sp_configure;
    

    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! –  user196124 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. –  user196124 May 15 '13 at 7:02
add comment

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{
    param(
        [string]$Server,
        [string]$Database,
        [string]$UserName,
        [string]$Password,
        [string]$ApplicationName
        )

    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
{
    param(
        [string]$Server,
        [string]$Database,
        [string]$UserName,
        [string]$Password,
        [string]$Query,
        [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
    $Connection.Open()

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

    [void]$SqlDataAdapter.Fill($SqlDataSet)
    $Connection.Close()

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

Your Answer

 
discard

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.