[quoted text, click to view]

You can execute a stored procedure in another database with the
following syntax:

EXECUTE MyDatabase.dbo.MyProc

Or, if the procedure is owned by someone other than dbo:

EXECUTE MyDatabase.OtherOwner.MyProc


Best, Hugo
--

Can you be more specific? As some have said, creating them as system stored
procedures might be possible, and using the database name in the name of the
object works as well. Both have different values though.

--
----------------------------------------------------------------------------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

[quoted text, click to view]
within the same SQL server instance. Is there anyway I can reference the one
copy of the stored procedures, rather than copying them to each database?

Hi,

Yes, you can achive this by creating procedure in Master database with the
name starting with SP_. But creating procedures in Master database is not
suggested at all, as well as there will be slight performance issues if you
create procedures with 'SP_'

If you are ok with the above 2 constaints then you can create the procedure
with name 'SP_NAME' in Master database. eg:

use Master
go
create proc sp_disp_sysobjects
as
select id,name from sysobjects where type='u'

-- Execution

use master
go
exec sp_disp_sysobjects

(Displays the tables in master database)



use pubs
go
exec sp_disp_sysobjects

(This will display the objects in Pubs database.)

Thanks
Hari
MCDBA



[quoted text, click to view]
within the same SQL server instance. Is there anyway I can reference the one
copy of the stored procedures, rather than copying them to each database?

Hi Hugo,

This may not be useful for him, because your solution will use the tables
inside the Mydatabase only. Say if I run this "EXEC MyDatabase.dbo.MyProc"
from pubs database , the procedure will pick all the tables from MyDatabase
only , it will not take the data from Pubs database.

But the solution provided by me will read the tables from (any) the database
it is executing.

Thanks
Hari
MCDBA

[quoted text, click to view]
within the same SQL server instance. Is there anyway I can reference the one
copy of the stored procedures, rather than copying them to each database?
[quoted text, click to view]