How to get Physical Drives associated with SQL Instance

I need to get a list of physical drives (ie: drive
letters) of all the drives associated with an instance of
SQL 2000 running on a cluster.
The ideal solution would be a stored procedure that given
the instance name you would get back the drives.
Can anyone make a useful suggestion? Is this possible with
a stored procedure?
Thanks,
kenKen,
You can use master..xp_fixeddrives to get the fixed drives name as well as
free space in MB.Since its a undocumented call, please refrain from using it
in production code.An alternative would be to use master..xp_cmdshell
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Ken McClain" <ken@.softbreeze.net> wrote in message
news:0b8e01c35062$4e3466e0$a501280a@.phx.gbl...
> I need to get a list of physical drives (ie: drive
> letters) of all the drives associated with an instance of
> SQL 2000 running on a cluster.
> The ideal solution would be a stored procedure that given
> the instance name you would get back the drives.
> Can anyone make a useful suggestion? Is this possible with
> a stored procedure?
> Thanks,
> ken|||In addition to Dinesh's suggestion which I can't recall if it works on a
cluster there is also the system function fn_servershareddrives() that will
give you the shared cluster drives, possibly combining them will give you
the answer, I haven't got a cluster to test on at the moment. See BOL for
details of this function
SELECT *
FROM ::fn_servershareddrives()
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ken McClain" <ken@.softbreeze.net> wrote in message
news:0b8e01c35062$4e3466e0$a501280a@.phx.gbl...
I need to get a list of physical drives (ie: drive
letters) of all the drives associated with an instance of
SQL 2000 running on a cluster.
The ideal solution would be a stored procedure that given
the instance name you would get back the drives.
Can anyone make a useful suggestion? Is this possible with
a stored procedure?
Thanks,
ken

How to get from JobID back to SubscriptionID

Hi!
I am hoping someone can explain how I can determine the subscription
associated with a currently running job (report). If a report is misbehaving,
we would like to determine the subscription that initiated the report
running. We can cancel the job to stop the report from running this time, but
we would like to be able to prevent it from running again.
I believe that this would mean tieing the jobid back to a subscriptionid,
but I have not been able to determine a way either programatically or via a
direct query of the RS tables to determine this.
Any help would be greatly appreciated.
Thanks in advance,
BobHello,
This is what I do:
--This will help you identify the OID of the report
SELECT ItemID, Path
FROM Catalog
--This will show all subscriptions for the report
SELECT *
FROM Subscriptions
WHERE Report_OID = '[ItemID from previous query]'
--This will tell you the job id
SELECT *
FROM ReportSchedule
WHERE SubscriptionID = '[SubscriptionID from the previous query]'
You can put all together, but I prefer to run them one by one because I have
a lot of reports and also several subscriptions per report.
Hope this helps.
Ricardo.
"bobhug" wrote:
> Hi!
> I am hoping someone can explain how I can determine the subscription
> associated with a currently running job (report). If a report is misbehaving,
> we would like to determine the subscription that initiated the report
> running. We can cancel the job to stop the report from running this time, but
> we would like to be able to prevent it from running again.
> I believe that this would mean tieing the jobid back to a subscriptionid,
> but I have not been able to determine a way either programatically or via a
> direct query of the RS tables to determine this.
> Any help would be greatly appreciated.
> Thanks in advance,
> Bob|||Ricardo,
Thanks for the response.
I do not see how this gets me to JobID however.
SELECT *
FROM ReportSchedule
WHERE SubscriptionID = '[SubscriptionID from the previous query]'
This would give me scheduleid and reportid, but I still do not know how to
get to jobid.
Perhaps I am just missing something.
Thanks,
Bob
"Ricardo Sampei" wrote:
> Hello,
> This is what I do:
> --This will help you identify the OID of the report
> SELECT ItemID, Path
> FROM Catalog
> --This will show all subscriptions for the report
> SELECT *
> FROM Subscriptions
> WHERE Report_OID = '[ItemID from previous query]'
> --This will tell you the job id
> SELECT *
> FROM ReportSchedule
> WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> You can put all together, but I prefer to run them one by one because I have
> a lot of reports and also several subscriptions per report.
> Hope this helps.
> Ricardo.
> "bobhug" wrote:
> > Hi!
> > I am hoping someone can explain how I can determine the subscription
> > associated with a currently running job (report). If a report is misbehaving,
> > we would like to determine the subscription that initiated the report
> > running. We can cancel the job to stop the report from running this time, but
> > we would like to be able to prevent it from running again.
> > I believe that this would mean tieing the jobid back to a subscriptionid,
> > but I have not been able to determine a way either programatically or via a
> > direct query of the RS tables to determine this.
> > Any help would be greatly appreciated.
> >
> > Thanks in advance,
> > Bob|||Hello Bob,
Probably you have already figured out thi one, but anyway, here is the
answer: What you get in ScheduleID from ReportSchedule is the job name in SQL
Server Agent.
Ricardo.
"bobhug" wrote:
> Ricardo,
> Thanks for the response.
> I do not see how this gets me to JobID however.
> SELECT *
> FROM ReportSchedule
> WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> This would give me scheduleid and reportid, but I still do not know how to
> get to jobid.
> Perhaps I am just missing something.
> Thanks,
> Bob
> "Ricardo Sampei" wrote:
> > Hello,
> >
> > This is what I do:
> > --This will help you identify the OID of the report
> > SELECT ItemID, Path
> > FROM Catalog
> >
> > --This will show all subscriptions for the report
> > SELECT *
> > FROM Subscriptions
> > WHERE Report_OID = '[ItemID from previous query]'
> >
> > --This will tell you the job id
> > SELECT *
> > FROM ReportSchedule
> > WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> >
> > You can put all together, but I prefer to run them one by one because I have
> > a lot of reports and also several subscriptions per report.
> >
> > Hope this helps.
> >
> > Ricardo.
> >
> > "bobhug" wrote:
> >
> > > Hi!
> > > I am hoping someone can explain how I can determine the subscription
> > > associated with a currently running job (report). If a report is misbehaving,
> > > we would like to determine the subscription that initiated the report
> > > running. We can cancel the job to stop the report from running this time, but
> > > we would like to be able to prevent it from running again.
> > > I believe that this would mean tieing the jobid back to a subscriptionid,
> > > but I have not been able to determine a way either programatically or via a
> > > direct query of the RS tables to determine this.
> > > Any help would be greatly appreciated.
> > >
> > > Thanks in advance,
> > > Bob|||Ricardo,
Hi!
Now I understand the confusion.
The JobID I was referring to is the one as found in the RunningJobs table
and the Job class: http://tinyurl.com/9zyb2 , which is different than what
you are talking about, the name of the SQL Server agent job.
Thanks for responding,
Bob
"Ricardo Sampei" wrote:
> Hello Bob,
> Probably you have already figured out thi one, but anyway, here is the
> answer: What you get in ScheduleID from ReportSchedule is the job name in SQL
> Server Agent.
> Ricardo.
> "bobhug" wrote:
> > Ricardo,
> >
> > Thanks for the response.
> > I do not see how this gets me to JobID however.
> > SELECT *
> > FROM ReportSchedule
> > WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> > This would give me scheduleid and reportid, but I still do not know how to
> > get to jobid.
> >
> > Perhaps I am just missing something.
> > Thanks,
> > Bob
> >
> > "Ricardo Sampei" wrote:
> >
> > > Hello,
> > >
> > > This is what I do:
> > > --This will help you identify the OID of the report
> > > SELECT ItemID, Path
> > > FROM Catalog
> > >
> > > --This will show all subscriptions for the report
> > > SELECT *
> > > FROM Subscriptions
> > > WHERE Report_OID = '[ItemID from previous query]'
> > >
> > > --This will tell you the job id
> > > SELECT *
> > > FROM ReportSchedule
> > > WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> > >
> > > You can put all together, but I prefer to run them one by one because I have
> > > a lot of reports and also several subscriptions per report.
> > >
> > > Hope this helps.
> > >
> > > Ricardo.
> > >
> > > "bobhug" wrote:
> > >
> > > > Hi!
> > > > I am hoping someone can explain how I can determine the subscription
> > > > associated with a currently running job (report). If a report is misbehaving,
> > > > we would like to determine the subscription that initiated the report
> > > > running. We can cancel the job to stop the report from running this time, but
> > > > we would like to be able to prevent it from running again.
> > > > I believe that this would mean tieing the jobid back to a subscriptionid,
> > > > but I have not been able to determine a way either programatically or via a
> > > > direct query of the RS tables to determine this.
> > > > Any help would be greatly appreciated.
> > > >
> > > > Thanks in advance,
> > > > Bob