xp_cmdshell

  • I'm having a little trouble with xp_cmdshell...wonder if anyone can spot anything incredibly stupid I'm missing.

    This pdfcombine program just combines the first 2 files into one .pdf file. If I run the script below, it prints out 'GOOD'. along with the value of @CMD. But the file never gets created. However if I open a command prompt window using the same security context as the sqlserveragent account, copy the output from the PRINT @CMD, and run it, it works.

    Am I missing something obvious? I've tried various combinations of quotes, and have even tried a full UNC path. In all cases, running the command in the command line works, but stops working as soon as I'm in SQL server. But I can do something simple like SET @CMD = 'DIR D:\' and that works.

    DECLARE @CMD VARCHAR(512)

    DECLARE @RESULT INT

    SET @CMD =

    'D:\JPMorgan_IMPORT\pdfcombine.exe D:\431411406.PDF D:\431411408.PDF D:\COMBINED.PDF'

    PRINT @CMD

    EXECUTE @RESULT = master.dbo.xp_cmdshell @CMD

    IF @RESULT = 0

    PRINT 'GOOD'

    ELSE

    PRINT 'BAD'

    The Redneck DBA

  • What authentication method are you using to connect to SQL Server, Windows authentication or SQL Authentication?


    * Noel

  • noeld (12/12/2007)


    What authentication method are you using to connect to SQL Server, Windows authentication or SQL Authentication?

    Windows Authentication. The user is both a sysadmin, and a local admin on the server involved.

    The Redneck DBA

  • What service account is SQL Server running under? Make sure it has the necessary NTFS permissions to run the executable and write to the destination folder.



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • Scott Duncan (12/12/2007)


    What service account is SQL Server running under? Make sure it has the necessary NTFS permissions to run the executable and write to the destination folder.

    I don't think it's permissions. I logged onto the server as the user that the services are running under, and that's what I'm using to manually execute from the command prompt.

    It's got to be something just silly I'm missing...

    The Redneck DBA

  • What happens if you try running:

    master..xp_cmdshell 'D:\JPMorgan_IMPORT\pdfcombine.exe D:\431411406.PDF D:\431411408.PDF D:\COMBINED.PDF'

    Are any error messages returned? Or is it just the never helpful 'NULL'



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • Hi,

    The code is good

    Try just to execute in the command line

    D:\JPMorgan_IMPORT\pdfcombine.exe D:\431411406.PDF D:\431411408.PDF D:\COMBINED.PDF to see if it works

    Check the following link

    http://www.coolutils.com/forum/read.php?f=1&i=914&t=914

    Regards,

    Ahmed

  • Ahmed Bouzamondo (12/13/2007)


    Hi,

    The code is good

    Try just to execute in the command line

    D:\JPMorgan_IMPORT\pdfcombine.exe D:\431411406.PDF D:\431411408.PDF D:\COMBINED.PDF to see if it works

    Check the following link

    http://www.coolutils.com/forum/read.php?f=1&i=914&t=914

    Regards,

    Ahmed

    That's the strange thing. It works fine from the command line (when running with the same user the service accounts are running as).

    I've even tried putting a bunch of those commands in a batch file, and just running the batch file from xp_cmdshell. It doesn't work. But if I run the batch file from the command line it works again.

    Normally I would think it's a permission error, but since xp_cmdshell (at least according to books online) runs with the credentials of the sql server agent service account, and I can log on as that service account to the desktop and run it manually with sucess, I'm not sure it's security. I can do simple things like:

    ...

    xp_cmdshell 'dir d:\'

    And that works, so I also don't think it's any obvous configuration issue either.

    The Redneck DBA

  • It's finally solved.

    Our network guy who(m) is much more clever than me added an entry in the PATH variable and it magically started working. Apparently the pdfcombine program was having troulbe finding the license info.

    One interesting that that was discovered:

    Run the following: EXECUTE master.dbo.xp_cmdshell 'SET'

    and then run SET from the command prompt. (make sure you are logged in as the account that the agent is running as). For me it's returning different things in the 'PATH' variable.

    The Redneck DBA

  • Thanks Jason for sharing the info.

    Regards,

    Ahmed

  • Run the following: EXECUTE master.dbo.xp_cmdshell 'SET'

    and then run SET from the command prompt. (make sure you are logged in as the account that the agent is running as). For me it's returning different things in the 'PATH' variable.

    That's because SQL Server set's up it's own environment when it boots and each "command session" has it's own environment. You can simulate the same thing... open two CMD windows... make a bunch of changes in one... do a SET to verify the changes... do a SET in the other... you'll see nothing changed. Make changes in the second CMD window and verify that you can't see thos changes in the first. Open a 3rd CMD window and verify that none of the changes appear.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply