Post reply

SQL job is running successful without the database name in the step

  • July 6, 2012 at 3:56 am

    #260807

    There are 10 databases on a server and one of them has the 'Proc' Stored Proc(SP) . None of the other database have simliar/identical SP.Screenshot of the job is attached

    SET XACT_ABORT ON

    exec Proc 1,1,90,'T'

    GO

    Even though database name not specified in the job step It is ruuning successful, I'm bit puzzled?

    Thanks

  • July 6, 2012 at 4:03 am

    #1509559

    May be the login default database is the same in which the proc is defined.

    Is the name start with sp_ something like ssytem procs?

    GulliMeel

    Finding top n Worst Performing queries[/url]
    Improve the performance of Merge Join(special case)
    How to Post Performance Problem -Gail Shaw[/url]

  • July 6, 2012 at 4:08 am

    #1509564

    yes, its a user defined proc defined in User database but name starts with sp_proc.

    Login's deafult database is 'Master' not user defined database where SP is

  • July 6, 2012 at 5:19 am

    #1509606

    do not use sp_ as prefix for your procedures. SQL server considers these as special procedures

    and search for these in all databases and then executes it.That is why it is runing fine.

    Check below

    http://msdn.microsoft.com/en-us/library/ms190669(v=sql.105)

    GulliMeel

    Finding top n Worst Performing queries[/url]
    Improve the performance of Merge Join(special case)
    How to Post Performance Problem -Gail Shaw[/url]

  • July 6, 2012 at 5:22 am

    #1509609

    Thanks! it's a third party database.

    Thanks again for all your help with this.

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

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