Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hennie7863
Starting Member
12 Posts |
 Posted - 2003-11-04 : 05:29:47
|
| HiI'm executing SP_SpaceUsed in a stored procedure like this :Exec ('SP_SpaceUsed '+ @table) This works great but when i want to execute it for a table in an other database i'm running into troubles. Things i tried is this :Exec ('USE <DB> ; SP_SpaceUsed '+ @table) -->not working (incorrectsyntax)Exec ('USE <DB> ; Master.dbo.SP_SpaceUsed '+ @table) -->not working(incorrect syntax)Exec ('SP_SpaceUsed <DB>.dbo.'+ @table) --> not working (incorrectsyntax)Exec ('Master.dbo.SP_SpaceUsed <DB>.dbo.'+ @table) -->not working(incorrect syntax)Could someone give me a clue???Thanx,Hennie de Nooijer |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-11-04 : 06:08:01
|
| no can do.........if you inspect the code of sp_spaceuser in the master database....and work through the logic of it....using exec sp_spaceused otherdbname.dbo.othertablenameyou will come across the statement... raiserror(15250,-1,-1)which gives an error message of"Server: Msg 15250, Level 16, State 1, Line 1The database name component of the object qualifier must be the name of the current database."I think the way the sp is 'currently coded' it forces you to look at tables in your current database....BUT you could write your own version....to remove this limitation |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-11-04 : 07:07:19
|
| Actually you almost had it:Exec ('USE <DB> ; EXEC Master.dbo.SP_SpaceUsed '+ @table)The only time you can execute a stored procedure without using the EXECUTE command is if it is the very first command in the batch. It's better to always specify EXECUTE so that it becomes a habit and you never run into problems. |
|
|
|
hennie7863
Starting Member
12 Posts |
Posted - 2003-11-04 : 07:23:42
|
| He thanx, It worked. you're right...always use a EXEC with a stored procedure. Thanx again.. |
|
|
|
|
|
|
Executing SP_SpaceUsed for tables in a other datab