obtaining physical file and path using T-SQL

SQL 2000.
Is there a way to get a databases physical file names for data and log
using t-sql?
exec sp_helpdb '<database name>' returns two resultsets, the latter
would be good if I can get at the filename in the second resultset. If
anyone knows how?...
sysdatabases seems to hold the physical name for the data only. So
where is the logfile physical name stored?
I need to get the physical names into t-sql variables for later use.
Any help much appreciate.
thanks
PaulDid you look at sp_helpfile?
"Paul Fell" <penzina@.iinet.net.au> wrote in message
news:bea9531b.0308271721.7a2ea54f@.posting.google.com...
> SQL 2000.
> Is there a way to get a databases physical file names for data and log
> using t-sql?
> exec sp_helpdb '<database name>' returns two resultsets, the latter
> would be good if I can get at the filename in the second resultset. If
> anyone knows how?...
> sysdatabases seems to hold the physical name for the data only. So
> where is the logfile physical name stored?
> I need to get the physical names into t-sql variables for later use.
> Any help much appreciate.
> thanks
> Paul|||> I found all the info I needed by looking at sysdatabases and sysaltfiles
Note that the behavior (and existence!) of these tables might change in a
future release, which is why I tend to recommend using system stored
procedures (and information_schema views, where possible) rather than direct
system table access, especially if you're putting code into production.|||Agreed. But if you look at my orginal posting, you'll see I did try
sp_helpfile.
This system SP returns two recordsets. How do I get at the second
records set in my SP? I need to be able to access this data and I
don't know of a way of doing it dynamically.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message news:<#FImIRWbDHA.2580@.TK2MSFTNGP09.phx.gbl>...
> > I found all the info I needed by looking at sysdatabases and sysaltfiles
> Note that the behavior (and existence!) of these tables might change in a
> future release, which is why I tend to recommend using system stored
> procedures (and information_schema views, where possible) rather than direct
> system table access, especially if you're putting code into production.|||> This system SP returns two recordsets. How do I get at the second
> records set in my SP?
No can do. INSERT ... EXEC only picks up the first resultset. Either pick it up in a client program,
or use the source code for the proc to write your own proc.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Paul Fell" <penzina@.iinet.net.au> wrote in message
news:bea9531b.0308281845.730c942b@.posting.google.com...
> Agreed. But if you look at my orginal posting, you'll see I did try
> sp_helpfile.
> This system SP returns two recordsets. How do I get at the second
> records set in my SP? I need to be able to access this data and I
> don't know of a way of doing it dynamically.
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:<#FImIRWbDHA.2580@.TK2MSFTNGP09.phx.gbl>...
> > > I found all the info I needed by looking at sysdatabases and sysaltfiles
> >
> > Note that the behavior (and existence!) of these tables might change in a
> > future release, which is why I tend to recommend using system stored
> > procedures (and information_schema views, where possible) rather than direct
> > system table access, especially if you're putting code into production.

Obtaining PATH to master DB

I need to obtain the path to the master DB in a Wise install script. I know that for SQL2K it is in the registry key:
HKLM\Software\Microsoft\MSSQLServer\Setup

It seems that a SQL2005 installation does not have the same default
directory.

Is there a way to query this from the server itself so I don't have to read the registry?With the usual caveat that it is not a 'best practice' to query the system tables, you can find the answer by executing:

SELECT filename
FROM sysfiles

while connected to the master database. You will get two records. One is the data file, one is the log file. You can narrow the query by adding:

WHERE name = 'master'

Regards,

hmscott

Edit to add: I did verify this against a SQL 2005 instance we are running in development, but it will also work in SQL2K.

obtain the name and path of a report

how to save the name and the path of a report on my database when the user click save report

Does this cover what you need?

http://msdn2.microsoft.com/en-us/ms155836.aspx

|||

I need something that doesn't depend on a stored procedure or manual procedure.
I need to execute something when the user saves the report; something that returns the path for this report.
I need to generate in my website all paths for reports recently saved.

thanks again and I′ll be waiting for your answer

Regards

|||

What is "recently saved?" Is it time-bound? Why not use the SOAP API ListItems() and get the created date for every report in the catalog, and then filter for the ones which were recently created?

There is no supported way to inject your own code into the CreateReport() API which is used to publish reports.

|||

Let me try to be a little more specific. What I want is to be able to "catch" the event where the user saves or closes the report, so that I can create the related class, and it has to be right when it happens, it can′t be later. It would also be useful if the event could identify the name and path of the report I just created. I am sorry if I am not able to be a little more clear regarding this subject, but I hope you understand what I want, and if there is no way to do it just let me know either way.

|||

There is no built-in way of doing this via reporting services.

|||

and with reporting services extensions?

or is there another possible way to do this?

|||

Though you can't do this generically, you can provide a custom user interface for your users to access reports. In this way, you can determine when users click on reports or when they navigate away from them. The RS SOAP API allows you to expose all of the same functionality as you see in SQL Server Management Studio or Report Manager.

RS Extensions do not help you in this respect.

Not entirely sure what you want this kind of behavior for...

-Lukasz