none
dtexec - can run in a job (CmdExec) but not from xp_cmshell

    Question

  •  

    Hi all,

     

    I've working a while, not at full time, but seeking the solution...Here what I want to do and what I've done till now:

     

    I want to build a sql job, so I can run a package (witch loads 2 excel sheets into 2 tables) passing "dynamic" parameters, like convert(varchar,getdate(),112) in the format YYYYMMDD. From what I've found, I can do this with an Stored Procedure, which first set this variable, building a statement so it can be run by xp_cmdshell. For example:

     

    EXEC master.dbo.xp_cmdshell 'DTEXEC /SQ PACKAGE /SET "\Package.Variables[DAY].Value";20070101'

     

    And this statement runs with no problem in the SP until it reaches the step of loading the Excel Sheets into tables. Here it gives an error. It's about the JET driver. From what I've read in the forum and from the output error the problem seems to be that this statement executes the dtexec.exe 64bits, even the suggestion to change the property Run64BitRuntime set to False, it stills running from the dtexec.exe 64bits.

     

    So, I changed the statement to point to dtexec.exe 32bits:

     

    EXEC master.dbo.xp_cmdshell 'C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /SQ PACKAGE /SET "\Package.Variables[DAY].Value";20070101'

     

    But executing this statement it does not even run. The errors are:

    'C:\Program' is not recognized as an internal or external command

    operable program or batch file

     

    This was very wird to me, because this was very mentioned by the moderators.

     

    So, I copy this same statement and created a job with a step type of "Operating System (CmdExec)" and it runs great...With no problem with the extraction from excel source.

     

    Now my questions are:

    1. Why the step job CmdExec recognizes the path of dtexec.exe 32bits ('C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe ), but trying to run with xp_cmdshell it gives the error mencioned above. And if there is another way to set dtexec.exe 32bits besides this way?

     

    2. If I cannot run it throught xp_cmdshell, how could I pass a parameter like convert(varchar,getdate(),112) in the format YYYYMMDD instead of the static parameter 20070101.

     

    Thanks in advance.

    Marco Francisco (Portugal)

    Thursday, November 15, 2007 1:38 PM

Answers

  • Can you run the exact same syntax from a command prompt and have it run?

     

    Another approach would be to put the DTEXEC command in a batch file, store the batch file in a path with no spaces, and run the batch file from xp_cmdshell.

     

    Thursday, November 15, 2007 5:14 PM
    Moderator
  • See my post in this thread.  (Third post from the bottom)

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2150932&SiteID=1

    For xp_cmdshell, you'll want to use the DOS 8.3 notation for the directories as you can have only one set of quotes on the argument to xp_cmdshell.  (C:\PROGRA~1\...)
    Thursday, November 15, 2007 5:32 PM
    Moderator

All replies

  • I don't have a 64-bit SQL Server box handy, so I can't test this, but it looks like xp_cmdshell is having trouble with the spaces in the path, and the CmdExec job step is parsing the path correctly.

     

    I believe you can work around this by enclosing your file path in double quotes like so:

     

    Code Block
    EXEC master.dbo.xp_cmdshell '"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /SQ PACKAGE /SET "\Package.Variables[DAY].Value";20070101'

     

     

     

    Can you please test this and let us know if it solves your problem?

     

    Thursday, November 15, 2007 2:01 PM
    Moderator
  •  

    Sorry Matthew, I forgot the "" in the path of the previous post, but I have them in both situations, i. e., in the xp_cmdshell and CmdExec tests just like as your reply.

     

    Thanks.

    Thursday, November 15, 2007 5:07 PM
  • Can you run the exact same syntax from a command prompt and have it run?

     

    Another approach would be to put the DTEXEC command in a batch file, store the batch file in a path with no spaces, and run the batch file from xp_cmdshell.

     

    Thursday, November 15, 2007 5:14 PM
    Moderator
  • See my post in this thread.  (Third post from the bottom)

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2150932&SiteID=1

    For xp_cmdshell, you'll want to use the DOS 8.3 notation for the directories as you can have only one set of quotes on the argument to xp_cmdshell.  (C:\PROGRA~1\...)
    Thursday, November 15, 2007 5:32 PM
    Moderator
  •  MatthewRoche wrote:

    Can you run the exact same syntax from a command prompt and have it run?

     

    Another approach would be to put the DTEXEC command in a batch file, store the batch file in a path with no spaces, and run the batch file from xp_cmdshell.

     

     Phil Brammer wrote:
    See my post in this thread.  (Third post from the bottom)

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2150932&SiteID=1

    For xp_cmdshell, you'll want to use the DOS 8.3 notation for the directories as you can have only one set of quotes on the argument to xp_cmdshell.  (C:\PROGRA~1\...)

     

    Thanks, I like both of your suggestions.

     

    MatthewRoche, how can I execute a .bat file throught xp_cmdshell, is like executing a .exe file right? something like, for example EXEC xp_cmdshell 'C:\TEST.bat?

     

    Phil Brammer, I've read that post, but wasn't aware of the msg you were trying to pass. So, from what I understand, the xp_cmshell does't allow quotes in the file paths or program names referenced in command_string? this can only be achieved with DOS 8.3 notation?
    Thursday, November 15, 2007 6:09 PM
  •  marcoadf wrote:
    Phil Brammer, I've read that post, but wasn't aware of the msg you were trying to pass. So, from what I understand, the xp_cmshell does't allow quotes in the file paths or program names referenced in command_string? this can only be achieved with DOS 8.3 notation?


    If you're calling dtexec and some of its arguments require the use of quotes, then that command line string won't work with xp_cmdshell so long as you are using double quotes.  Try using single quotes to surround the entire command_string to xp_cmdshell.

    There can only be one set of double quotes to the argument of xp_cmdshell.

    Please read through the xp_cmdshell page at MSDN: http://msdn2.microsoft.com/en-us/library/ms175046.aspx

    Thursday, November 15, 2007 7:19 PM
    Moderator
  •  Phil Brammer wrote:

    If you're calling dtexec and some of its arguments require the use of quotes, then that command line string won't work with xp_cmdshell so long as you are using double quotes.  Try using single quotes to surround the entire command_string to xp_cmdshell.

    There can only be one set of double quotes to the argument of xp_cmdshell.

    Please read through the xp_cmdshell page at MSDN: http://msdn2.microsoft.com/en-us/library/ms175046.aspx

     

    Sorry Phil...When you first answered this post I read that page, now it's the 2nd and i've repeated again and again...Big Smile I'm having some trouble in translating/understand this one.

     

    From that page the text "command_string cannot contain more than one set of double quotation marks" and your post "There can only be one set of double quotes to the argument of xp_cmdshell.", this means and since executing SSIS package we have a lot of parameters, the all statement can only use one time the " " (double quotation)? Till here I think I understood. This means that if I have more than 1 parameter wich needs double quotations, then I must change the parameters so the statement has just one double quotation in the statement.

     

    Now, when you say "Try using single quotes to surround the entire command_string to xp_cmdshell" I think you mean using ' ' (single quotes) at the begining and at the end of the statement, right? But this is what I have posted before:

     

    EXEC master.dbo.xp_cmdshell '"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /SQ PACKAGE /SET "\Package.Variables[DAY].Value";20070101' 

    (this is just refering the 1st post, do not care about the path. This one is changed to DOS 8.3)

     

    Thank you again Phil.

    Thursday, November 15, 2007 10:19 PM