exec master.dbo.xp_ versus exec master..xp_
in [Programming SQL]
|
Prev: SSIS Package Comparison Utility (Freeware)
Next: SQL 2005 - SQL Native Client doesn't work, MDAC does
From: Neil W on 23 Oct 2009 11:15 Is there any difference in executing a stored procedure this way: exec master.dbo.xp_cmdshell versus this way? exec master..xp_cmdshell They both seem to work, but are there implications I need to know about? Thanks.
From: Neil W on 23 Oct 2009 11:40 Of course I meant "extended stored procedure", not "stored procedure".
From: Aaron Bertrand on 23 Oct 2009 13:14 I prefer to use the .dbo. prefix in the middle. If they ever change the behavior (e.g. move xp's to the sys schema), you are going to have to change your code in either case. What benefit do you see from the .. syntax, except that you don't have to type three characters? I prefer being explicit in any case - making this a consistent habit and convention in your system will help you avoid pitfalls in cases where the schema prefix *does* matter. I recently wrote a bog post about this: http://is.gd/4xSjt On 10/23/09 11:15 AM, in article utq5aO$UKHA.1232(a)TK2MSFTNGP05.phx.gbl, "Neil W" <neilw(a)netlib.com> wrote: > Is there any difference in executing a stored procedure this way: > exec master.dbo.xp_cmdshell > versus this way? > exec master..xp_cmdshell > > They both seem to work, but are there implications I need to know about? > > Thanks. > > > >
From: TheSQLGuru on 23 Oct 2009 14:26
You should ALWAYS use the owner (2000-) or schema (2005+) to reference ANY object in sql server. 1) it avoids internal lookups and 2) you can actually get the WRONG data if accessing a table depending on duplicate names and your default schema. -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Neil W" <neilw(a)netlib.com> wrote in message news:utq5aO$UKHA.1232(a)TK2MSFTNGP05.phx.gbl... > Is there any difference in executing a stored procedure this way: > exec master.dbo.xp_cmdshell > versus this way? > exec master..xp_cmdshell > > They both seem to work, but are there implications I need to know about? > > Thanks. > > > > |