The MS SQL Server version in question is 8.
In the context of a database other than master, if I call stored procedures from the master database, for some of them I must append the master.. prefix (otherwise I get Could not find stored procedure 'procname' error), for some of them I don't.
For example, I can call
EXEC sp_addlogin 'user' 'pass';
and it works, but
EXEC xp_cmdshell 'command';
doesn't. I have to prepend master.. for it to work:
EXEC master..xp_cmdshell 'command';
I may be wrong here but I observed that one have to add master.. to only those stored procedures that start with xp_ (as opposed to sp_).
Why do I have to call some of them with master.. prepended while some of them can be called without?
Procedures in the master database whose name begin with sp_ can be called in any other user database without having to add the master.. prefix. Since procedures beginning with xp_ don't follow that rule, you still need to add the master.. prefix when calling them.