dcsimg
Results 1 to 4 of 4

Thread: Passing variables to BATCH FILE

Hybrid View

  1. #1
    Robert Basham Guest

    Passing variables to BATCH FILE

    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


  2. #2
    Don Romano Guest

    Passing variables to BATCH FILE (reply)

    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

  3. #3
    Robert Basham Guest

    Passing variables to BATCH FILE (reply)

    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

  4. #4
    Join Date
    Jun 2010
    Posts
    1

    xp_cmdshell with variables

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •