locked
Using built in cmd_exec in sql server job agent with a variable? RRS feed

  • Question

  • I have a sql server job that I am using the sql server job agent's "cmd exec type" to run a bcp command on sql server 2008 R2. I can run it with no errors as long as I don't try to pass a variable into the bcp command line. Is there a way to pass a variable so I can dynamically name my text file with the current date?

    The error message I get is - (reason: The system cannot find the file specified).  The step failed.) 

    Example:

    It fails on this:

    Declare @CurrentDate varchar(30)

    SET @CurrentDate = ''
    Select @CurrentDate = REPLACE(REPLACE(REPLACE(CONVERT(varchar(30), GETDATE(), 120),'-',''),':',''),' ','_')

    bcp "Select * from [sometable] order by ROWID" queryout "C:\Users\outthere\Documents\MyFile\File_' + @CurrentDate  + '.txt" -T -c -t r\r\n

    It runs fine when hardcoded like this:

    bcp "Select * from [sometable] order by ROWID" queryout "C:\Users\outthere\Documents\MyFile\File_20131104.txt" -T -c -t r\r\n

    Thanks!


    Sue

    • Moved by ArthurZ Monday, November 4, 2013 9:45 PM
    Monday, November 4, 2013 9:28 PM

Answers

All replies