Converting Profiles to Stored Procedures

New to SQL Server. I am considering converting my Profiles that I run to
Stored Procedures. I am listening for long running querys and some other
things. If I use just the Profiler, I can look at the all the databases in
the server at the same time. However, if I convert the profile to a stored
procedure, the scope of that profile is ONLY the database the stored
procedure is in. It appears I would need to place the stored procedure into
each database on that server!
Can I run a stored procdure at the server level?Not sure what you mean by profiles.
There is a way to create stored procedures once "at the server level," have
them interact with user objects, and have the proc's code resolved in the
local database; but it's undocumented and unsupported with all that implies.
You create a special procedure (sp_, in the master database), and mark it as
a system object with the undocumented sp_MS_MarkSystemObject procedure.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C99ADF63-1E76-402B-AA0A-46AF69211769@.microsoft.com...
> New to SQL Server. I am considering converting my Profiles that I run to
> Stored Procedures. I am listening for long running querys and some other
> things. If I use just the Profiler, I can look at the all the databases
> in
> the server at the same time. However, if I convert the profile to a
> stored
> procedure, the scope of that profile is ONLY the database the stored
> procedure is in. It appears I would need to place the stored procedure
> into
> each database on that server!
> Can I run a stored procdure at the server level?|||Oh, sorry for being unclear. The Tool Profiler is what I mean. Instead of
running my traces in the Profiler application, I am converting them to store
d
procedures. That way I do not need to open Profiler at all and can place th
e
scan into a Job controled By the SQL Server Agent.
"Itzik Ben-Gan" wrote:

> Not sure what you mean by profiles.
> There is a way to create stored procedures once "at the server level," hav
e
> them interact with user objects, and have the proc's code resolved in the
> local database; but it's undocumented and unsupported with all that implie
s.
> You create a special procedure (sp_, in the master database), and mark it
as
> a system object with the undocumented sp_MS_MarkSystemObject procedure.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:C99ADF63-1E76-402B-AA0A-46AF69211769@.microsoft.com...
>
>|||In that case, you only need to create them as the fully supported special
procedures as the trace related procedures themselves are special
procedures, and they interact with system objects.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C576AF29-CA75-45B4-A3CF-BB730C72FEC2@.microsoft.com...
> Oh, sorry for being unclear. The Tool Profiler is what I mean. Instead
> of
> running my traces in the Profiler application, I am converting them to
> stored
> procedures. That way I do not need to open Profiler at all and can place
> the
> scan into a Job controled By the SQL Server Agent.
> "Itzik Ben-Gan" wrote:
>|||do you have a web address I can do some reading on that?
"Itzik Ben-Gan" wrote:

> In that case, you only need to create them as the fully supported special
> procedures as the trace related procedures themselves are special
> procedures, and they interact with system objects.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:C576AF29-CA75-45B4-A3CF-BB730C72FEC2@.microsoft.com...
>
>|||Sure. Go to www.sqlmag.com, and look for the articles about sql trace, and
tracing with stored procedures. Kalen, Brian and I wrote many articles on
the subject.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:F7B4ED88-8E81-4501-8008-5477B6D6EEF9@.microsoft.com...
> do you have a web address I can do some reading on that?
> "Itzik Ben-Gan" wrote:
>|||One more question Itzik; because it appears that I DO NOT need to place a
trace stored procedure into each of the databases I wish to record activity
on, I could place all the stored procedures I wish to run into a "dumby"
database.
The only purpose of this database is to hold stored procedures (my trace
profiles). This way the production databases are not cluttered with my
stored procedures used to monitor things with. this would be a great time
saver!
"Rich" wrote:
> do you have a web address I can do some reading on that?
> "Itzik Ben-Gan" wrote:
>