Passing variables to BATCH FILE

[Robert Basham]

Similar to a previous thread, but not exactly. I have a batch file that takes
two date parameters. I have a SQL script that gets the values I want and assigns
them to variables, but I don`t know how to pass these to the batch.

I`m thinking of something like:

declare @myvar1 datetime, @myvar2 datetime

select @myvar1 = <some date value>
select @myvar2 = <some date value>

exec xp_cmdshell "c:mssqlscriptsMYBATCH.BAT" @myvar1 @myvar2

This just errors out when it reaches the variables in the last line. If I move
the variables inside the quotes, it passes them as literal text strings, not as
the assigned datetime values.

Any ideas? Is this possible?

Thanks,
Robert

[Don Romano]

You might try creating a string variable and building your xp_cmdshell line ahead of time.

This isn`t really tested out but maybe this will lead in a positive direction. I don`t know DOS extremely well any more :-) so I`m not exactly sure how datetime fields as parameters need to be delimited to handle the spaces, etc...

Don

i.e.

/* new */
declare @cmdstring varchar(255)

declare @myvar1 datetime, @myvar2 datetime

select @myvar1 = <some date value>
select @myvar2 = <some date value>

/* new */
select @cmdstring = `"` + "c:mssqlscriptsmybatch.bat " +
"`" + convert(varchar(24),@myvar1) + "` `" +
convert(varchar(24),@myvar2) + "`" + `"`

exec xp_cmdshell @cmdstring

[Robert Basham]

Don:

You`re absolutely right, this is a workable approach. Just couldn`t make
that last little leap. Thanks for the help!

Robert

On 8/5/98 8:24:39 AM, Don Romano wrote:
> You might try creating a string variable and building your xp_cmdshell line
> ahead of time.This isn`t really tested out but maybe this will lead in
> a positive direction. I don`t know DOS extremely well any more :-) so I`m
> not exactly sure how datetime fields as parameters need to be delimited to
> handle the spaces, etc...

Don

[tequila00shots]

DECLARE @dateTimeAMPM varchar(15)
declare @diskString varchar(80)
declare @dbname varchar(50)
declare @dbnameFile varchar(65)
declare @rename1 varchar(80)

SELECT @dateTimeAMPM =
convert(varchar,YEAR(GETDATE())) + '_' +
convert(varchar,MONTH(GETDATE())) + '_' +
convert(varchar,DAY(GETDATE()))
set @diskString = 'Step_235_Info' + '_' + rtrim(ltrim(@dateTimeAMPM)) + '.csv'
set @rename1 = '"ren \\pacdcsql03\L$\Step_235\Step_235_Info.csv ' + @diskString + '"'
--EXEC xp_cmdshell "ren \\pacdcsql03\L$\Step_235\Step_235_Info.csv Step_235_Info111.csv"
EXEC xp_cmdshell @rename1