Post reply

SQL Agent - step type = T-SQL or Operating System for stored procedures in different databases

  • August 31, 2012 at 9:17 am

    #261319

    Hi Everyone,

    My manager has asked me to consolidate several SQL Agent jobs - they do the exact same thing in different databases. Each database is (supposedly) identical, but contains different data. All databases reside in the same instance of SQL Server.

    At the moment they are separate Agent jobs, each with a job step type of T-SQL, associated with a specific database. The current T-SQL is something like:

    EXEC dbo.Proc1

    My first thought was to combine them using separate steps, fully qualified by database name:

    EXEC DB1.dbo.Proc1

    EXEC DB2.dbo.Proc1

    etc...

    They could be executed from the master database context.

    But after thinking about it for a while, I thought that perhaps a safer approach would be to have separate Operating System (CmdExec) job steps that call SQLCMD, so that I can set the database context.

    I would appreciate any feedback from others that have tried to solve this type of problem.

    Thanks in advance --

    SQLNYC

  • August 31, 2012 at 9:55 am

    #1532500

    Is the list of databases static or are you going to need to modify the job step every time a database is added or dropped from your server? If it's static, the code you listed seems as good an approach as any. If the list is subject to change, you could iterate through sys.databases using some characteristic (naming convention?) to identify the desired databases and use dynamic SQL with a "use <<DatabaseName>>;" prefixed to your logic. If your database list will change and you are adament that dynamic SQL is taboo, you could generate the code that should be in the job step and modify the step using sp_update_jobstep.

  • August 31, 2012 at 11:13 am

    #1532548

    Hi Steve,

    Thanks very much for your reply - much appreciated.

    We only want to execute the stored procedure for specific databases.

    My concern was that there might be some issue with regard to being in one database (master or msdb, etc.) and executing the stored procedure in another database.

    Thanks again --

    Best,

    SQLNYC

  • September 2, 2012 at 10:18 pm

    #1532809

    Nothing wrong with having separate steps in one job, or have all calls in one step where the call is fully qualified. You can also use a USE statement if you like.

    Note that having them all in one job means they'll run sequentially. Having them in separate jobs allows you to schedule them to run concurrently which can be a great advantage.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply