Get script filename within SQL script

Hi,
SQL Server 2000. How do I get the filename of the script currently being
executed from within the .SQL script itself?
Is there a special variable containing the filename of the .SQL script, or
another method of obtaining it?
thanks
Hi
I am not quite sure what you are wanting, but the filename is really only
known by the application and AFAIK will not get passed to the server. If you
are running the command from a command line or batch file you could use
command prompt variables to substitute into a script something that is sent
to the server.
If you expand and post some example of what you are trying to do it may help!
John
"JJ Williams" wrote:

> Hi,
> SQL Server 2000. How do I get the filename of the script currently being
> executed from within the .SQL script itself?
> Is there a special variable containing the filename of the .SQL script, or
> another method of obtaining it?
> thanks
>
>
|||Hi, thanks for your reply. Here's a simple example. test.sql contains:
print 'Script: test.sql'
select @.@.version
Execute it using osql:

> osql -U sa -i test.sql
Instead of hardcoding the script filename within the script, I want to get
the script filename programmatically within the script itself, e.g. via SQL
statement/command or otherwise. I've got dozens of .sql scripts and want a
general method to output the filename of the script (it doesn't matter if
the filename printed includes the whole folder path or not) and would rather
not hardcode the print statement in each script.
Hope that clarifies what I want to do.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...[vbcol=seagreen]
> Hi
> I am not quite sure what you are wanting, but the filename is really only
> known by the application and AFAIK will not get passed to the server. If
> you
> are running the command from a command line or batch file you could use
> command prompt variables to substitute into a script something that is
> sent
> to the server.
> If you expand and post some example of what you are trying to do it may
> help!
> John
> "JJ Williams" wrote:
|||The origin of a batch is beyond the knowledge of the sql server engine that
executes it (which is where tsql code is interpreted and converted into
executable statements). This functionality would have to be something
implemented within the client application (osql in your example). The
execution of sql scripts isn't that difficult a task - you could write your
own application to do this - or perhaps wrap an application around osql -
you didn't indicate how you wanted to use this information.
|||Hi
If you had two files script.sql and sript1.sql you could do something like:
script.sql
PRINT 'Script Script1.sql'
:r Script1.sql
script1.sql
SELECT @.@.VERSION
Then run script.sql
osql -E -S (local) -d master -n -i script.sql -o script.out
If you want to do this for multiple files you could create the file and then
run it something like:
del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i' >>
script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n -i
script.sqf > script.out
If you have a large number of scripts it may be useful to limit how many are
run at any one given time.
John
"JJ Williams" wrote:

> Hi, thanks for your reply. Here's a simple example. test.sql contains:
> print 'Script: test.sql'
> select @.@.version
>
> Execute it using osql:
>
> Instead of hardcoding the script filename within the script, I want to get
> the script filename programmatically within the script itself, e.g. via SQL
> statement/command or otherwise. I've got dozens of .sql scripts and want a
> general method to output the filename of the script (it doesn't matter if
> the filename printed includes the whole folder path or not) and would rather
> not hardcode the print statement in each script.
> Hope that clarifies what I want to do.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...
>
>
|||Hi
If you don't want to use the command prompt you may want to look at DMO to
do this.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> If you had two files script.sql and sript1.sql you could do something like:
> script.sql
> PRINT 'Script Script1.sql'
> :r Script1.sql
> script1.sql
> SELECT @.@.VERSION
> Then run script.sql
> osql -E -S (local) -d master -n -i script.sql -o script.out
> If you want to do this for multiple files you could create the file and then
> run it something like:
> del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i' >>
> script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n -i
> script.sqf > script.out
> If you have a large number of scripts it may be useful to limit how many are
> run at any one given time.
> John
>
> "JJ Williams" wrote:
|||"Scott Morris" <bogus@.bogus.com> wrote in message
news:OrWEkAc$GHA.4808@.TK2MSFTNGP03.phx.gbl...
> The origin of a batch is beyond the knowledge of the sql server engine
> that executes it (which is where tsql code is interpreted and converted
> into executable statements). This functionality would have to be
> something implemented within the client application (osql in your
> example). The execution of sql scripts isn't that difficult a task - you
> could write your own application to do this - or perhaps wrap an
> application around osql - you didn't indicate how you wanted to use this
> information.

> you didn't indicate how you wanted to use this information.
I just want to log the filename along with the script contents and results
to an output file as the script executes. I have multiple scripts running
in sequence from separate osql command lines, all outputting to the same
file and I want to be able to see within the file which script did which
bit.
I think I'll stick with the simple hardcoded method.
thanks,
|||OK thanks again for your reply.
I'll stick with hardcoding the filename in a PRINT statement within each
script.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:2A3737FA-C8B3-44DD-94EA-D0AFA16E02AC@.microsoft.com...[vbcol=seagreen]
> Hi
> If you don't want to use the command prompt you may want to look at DMO to
> do this.
> John
> "John Bell" wrote: