Re: EXEC Command problem

From: Andrea Montanari (andrea.sqlDMO_at_virgilio.it)
Date: 12/29/04 

Date: Wed, 29 Dec 2004 16:22:53 +0100

hi,
"BobCo" <BobCo@discussions.microsoft.com> ha scritto nel messaggio
news:3B1E774A-0376-4CE3-B7D3-6BF054155724@microsoft.com
> Tried to do a search on EXEC and got a lot of hits, but none that I
> saw addressed my problem.
>
> I'm trying to move a DB, but when I type,
> EXEC sp_detach_db 'database'
> I get the unrecognized command error.
> I did a search for EXEC, but couldn't find it.
>
> Any suggestions/assistance?
>
> Thanks.

EXEC is just execute the stored procedure (the system stored procedure, in
this case), and is required when the call to stored procedure is not the
very first line of the current Transact-SQL batch
so the problem is not EXEC ...
actually you usually should get
Server: Msg 229, Level 14, State 5, Procedure sp_detach_db, Line 50
EXECUTE permission denied on object 'sp_detach_db', database 'master', owner
'dbo'.

if your current user has not been granted membership to sysadmins server
fixed role, as only relative members are allowed to execute that system
stored procedure... further details at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_da-di_83fm.asp 

-- 
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm        http://italy.mvps.org
DbaMgr2k ver 0.9.1  -  DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

Relevant Pages

  • Re: regex to find an stored proc name
    ... exec this a, b, c ... execute ... The one I gave you will do this, and will put the Stored Procedure names ... Hard work is a medication for which ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: EXECUTE tsqlstring with parameters
    ... I've tried using exec by itself, exec sp_executesql with an embedded exec ... string and sp_executesql with parameter substitution. ... until I need to execute a stored procedure that uses an OUTPUT parameter. ...
    (microsoft.public.sqlserver)
  • Re: Please Help
    ... I think if you use USE in a stored procedure, ... execute the entire statement dynamically with an EXEC - e.g. ... The database where the ...
    (microsoft.public.sqlserver.security)
  • Re: noise words, @@ERROR, and stop and resume indexing
    ... EXEC sp_fulltext_catalog 'adsfull', 'stop' ... > 1) check noise words inside stored procedure ... > can be solved by changing the language specific file with noise words ... ...
    (microsoft.public.sqlserver.fulltext)
  • source text file as input parameter from a stored procedure
    ... I am using an active X script in combination with a global ... transform task that loads data from a .txt file to a table. ... I can also tell that the stored procedure is correctly passing in the ... EXEC sp_OAGeterrorinfo @object, @hr ...
    (microsoft.public.sqlserver.dts)
Loading