sqlcmd - inputfile

25 Aug 2006 10:52 PM

payyans

Hi,
Is there a way to get the input-filename in a variable inside the script
file without using -v switch or environment variables?

I have a requirement to store the inputfilename in a database table when the
script is run successfully.

So, I am expecting to use the following command in my script
Insert into script_run_history(script_name, date_of_run)
values(@script_name,getdate())

I do not want to hard-code the script name in a variable at the time of
script-creation because it is a manual process and hence error-prone.
Your help is appreciated.
Thanks,
payyans

26 Aug 2006 9:36 PM

Roy Harvey

Not directly that I can tell - that would be a nice enhancement - but
I came up with something that may serve the purpose.

SQLCMD can read environment variables. Perhaps if you use the
convention of assigning the name of the script file to an environment
variable, then referencing that variable as the script name in the
line where SQLCMD is invoked, the script can then reference it
internally.

I set up a two line bat file, Test.bat:

set banana=abc.sql
SQLCMD -i %banana% -S Peregrine\TestBed

And a four line script file, abc.sql:

declare @script varchar(60)
set @script = '$(banana)'
PRINT @script
GO

This "printed" to the console the name of the script file, abc.sql. If
the output had been routed to a file it would appear in the file. It
could just as easily have been saved to a table, or whatever else you
might want to do with it.

Hope that helps.

Roy Harvey
Beacon Falls, CT

On Fri, 25 Aug 2006 15:52:03 -0700, payyans
<payy***@discussions.microsoft.com> wrote:

Show quote

>Hi,
>Is there a way to get the input-filename in a variable inside the script
>file without using -v switch or environment variables?
>
>I have a requirement to store the inputfilename in a database table when the
>script is run successfully.
>
>So, I am expecting to use the following command in my script
>Insert into script_run_history(script_name, date_of_run)
>values(@script_name,getdate())
>
>I do not want to hard-code the script name in a variable at the time of
>script-creation because it is a manual process and hence error-prone.
>Your help is appreciated.
>Thanks,
>payyans

26 Aug 2006 10:32 PM

payyans

Thanks for the reply. Actually, I was looking for a solution which can be
used directly to avoid a "retyping" of the filename.
-payyans

Show quote

"Roy Harvey" wrote:

> Not directly that I can tell - that would be a nice enhancement - but
> I came up with something that may serve the purpose.
>
> SQLCMD can read environment variables. Perhaps if you use the
> convention of assigning the name of the script file to an environment
> variable, then referencing that variable as the script name in the
> line where SQLCMD is invoked, the script can then reference it
> internally.
>
> I set up a two line bat file, Test.bat:
>
> set banana=abc.sql
> SQLCMD -i %banana% -S Peregrine\TestBed
>
> And a four line script file, abc.sql:
>
> declare @script varchar(60)
> set @script = '$(banana)'
> PRINT @script
> GO
>
> This "printed" to the console the name of the script file, abc.sql. If
> the output had been routed to a file it would appear in the file. It
> could just as easily have been saved to a table, or whatever else you
> might want to do with it.
>
> Hope that helps.
>
> Roy Harvey
> Beacon Falls, CT
>
> On Fri, 25 Aug 2006 15:52:03 -0700, payyans
> <payy***@discussions.microsoft.com> wrote:
>
> >Hi,
> >Is there a way to get the input-filename in a variable inside the script
> >file without using -v switch or environment variables?
> >
> >I have a requirement to store the inputfilename in a database table when the
> >script is run successfully.
> >
> >So, I am expecting to use the following command in my script
> >Insert into script_run_history(script_name, date_of_run)
> >values(@script_name,getdate())
> >
> >I do not want to hard-code the script name in a variable at the time of
> >script-creation because it is a manual process and hence error-prone.
> >Your help is appreciated.
> >Thanks,
> >payyans
>

27 Aug 2006 2:20 AM

Roy Harvey

On Sat, 26 Aug 2006 15:32:02 -0700, payyans
<payy***@discussions.microsoft.com> wrote:

>Thanks for the reply. Actually, I was looking for a solution which can be
>used directly to avoid a "retyping" of the filename.
>-payyans

I thought it accomplished that. The file name abc.sql was written in
one place, in the SET command of the bat file. It was then referenced
inside the SQL script using the environment variable to which it was
assigned.

Roy