Scheduled Stored Procedure, what Database ?

Question

Post reply

Scheduled Stored Procedure, what Database ?

homebrew01

SSC Guru

Points: 55548
More actions
January 22, 2013 at 7:21 am

#287923

I was just looking at a scheduled job that someone set up, that is basically just:
exec MyStoredProcedure
There is no database specified in the command, or in the database tab. I can't see anywhere in the scheduled job that SQL knows what database "MyStoredProcedure" is in. It's not in master. Does it look in the default database for the job's owner ?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

unless the procedure starts with "sp_", i believe it assumes the context of the msdb database, right?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

I found the stored procedure in a user database, but couldn't see how SQL knew where it was.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

In the job step dialog, right above where the command is, is a database dropdown.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

GilaMonster (1/22/2013)
In the job step dialog, right above where the command is, is a database dropdown.

Yes, but that is blank. I would think you need the dropdown populated, or specify database in the sql statement.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

homebrew01 (1/22/2013)
GilaMonster (1/22/2013)
In the job step dialog, right above where the command is, is a database dropdown.
Yes, but that is blank. I would think you need the dropdown populated, or specify database in the sql statement.

If it's not populated, it would be the default database for the login used.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Leo.Miller SSChampion Points: 13051 More actions · Answer 6

Is the job actually running and working? Is the job owner sa? If not is the default database for the job owner the one containing the stored proc?

The only way this could have happened was for the database to be given a new database ID, posibly by being dropped and restored. If you create a job from the GUI there is always a database of the steps, and if you create the job from script with a blank database name for the step it defaults to master.

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.