Global or system procedure? - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Global or system procedure?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-15 : 07:23:46

SQL_Newbie writes "Hi.

Please don't pay attention to the email address. We actually specialise in something completely unrelated to SQL servers.. Sorry if this is too simple, but I can't find the answer I'm looking for anywhere.

We have a database for each client that we monitor. In each database, there is a table that corresponds to a device that we monitor for a client. All such tables have exactly the same schema: same field names, field types etc..

I've been referring to your website and the on-line SQL server book and managed to write a stored procedure that accepts parameters, performs queries and emails me notifications when certain criteria are met. So far, I've been executing the stored proc using query analyser and I'm happy with the results. The stored procedure is currently stored in one of the databases.

Is there something within SQL Server (Enterprise Manager perhaps?) that will allow me to:
* execute a SINGLE job at regular intervals (I guess just a schedule job?)
* where the job basically runs one stored procedure, but feeding different parameters to correspond to each client (ie talks to multiple databases).

The way I'm executing my test procedure at the moment is via (what I'm hoping to turn into) some sort of 'master CALLING procedure' and all it has is:

CREATE PROCEDURE [dbo].[spCALL_TESTPROC] AS
EXEC spCALL_TESPROC param1, param2, param3
GO

This calling procedure is currently saved in the same place as spCALL_TESTPROC. I tried to fully-qualify the procedure (I can't remember the exact thing I tried, but I tried a few things) by using something like:

EXEC [dbname].[dbo].[spCALL_TESPROC] param1, param2, param3

but I don't think I'm doing this right. I can't make the procedure see anything outside of the db where I've saved the procedure. The actual procedure that does all the work actually requires me to put the table name in the query, so I'm fairly sure that I won't be able to get away from putting a 'modified' stored procedure in each database, but it would be IDEAL if I can have a single 'master calling procedure' that just goes into each database and executes a stored procedure of the same name and just feed different parameters..

Please let me know if this is not making any sense. This is the first time I've used SQL.

FYI:
* I was logged on as the administrator on the SQL server, which has FULL privileges to everything on that server. It is used as the startup service account for SQL
* we are using SQL Server Enterprise Edition - product version 8.00.194 (RTM)
* running on Windows 2000 Server SP3"

dsdeming

479 Posts

Posted - 2003-09-15 : 07:59:26

quote:
Is there something within SQL Server (Enterprise Manager perhaps?) that will allow me to:
* execute a SINGLE job at regular intervals (I guess just a schedule job?)
* where the job basically runs one stored procedure, but feeding different parameters to correspond to each client (ie talks to multiple databases).

You can execute a job at scheduled intervals using SQL Agent, and as long as the procedure either uses fully-qualified object names ( database.owner.object ), uses sp_msforeachdb ( undocumented system procedure ), or uses dynamic SQL, you should be able to do what you want to.

Dennis

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-15 : 14:15:25

You might want to consider putting th eparameters into a table and create an SP that uses this table to call your monitor SP with each one in turn. Then you only have to schedule a single SP call and adding a new database to the call is just by adding the entry to the table. You could also remove databases temporarily by having an enabled flag on the entry.

As dsdeming says the way is to quaify the table names with the database.
You will need to use dynamic sql for this as the database name is a literal.
To get values back you might consider using temp tables for resultsets or sp_executesql for single values.

select @sql = 'select * from ' + @dbmane + '..tblname'
insert #a exec @sql

select @sql = 'select @id = id from ' + @dbname + '..tblname where id = 1'
exec sp_executesql @sql, N'@id int out', @id out

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-15 : 14:21:18

Another option might be to call the SP as though it was in your database. This would mean naming the sp sp_monitor and putting it in the master database.
then
declare @sql varchar(1000)
select @sql = 'exec ' + @dbname + '..sp_executesql N''exec sp_monitor ' + @parm1 + '','' + ...'
exec (@sql)

This should call the SP but in the context of the database required.
Note that it's not usually a good idea to add user items to the master database.
You would also have t make sure that no old copies of the stored proc end up anywhere.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources