Post reply

Error running xp_cmdshell

  • December 6, 2001 at 12:04 am

    #170399

    Hi all

    I've got a nagging problem trying to use xp_cmdshell. Environ is NT4 running SQL2k.

    We have a little VB program that generates text files ( in a very specific format ) from our database. This program used to be called from about a dozen different scheduled jobs with different parameters.

    I've successfully got the job list down to 3 distinct types of files. So now I have a stored procedure that executes the utility 3-4 times depending on the type of file to be generated.

    The problem is that I'm getting a error message about a prepared statement when I try to run the program via xp_cmdshell.

    Here is the portion of the script that builds and executes the command line,

    -- build the command string

    SELECT @vtCmd = '''C:\PROGRAM FILES\TMS\Dialer List Utility\DLU.EXE'

    SELECT @vtCmd = @vtCmd + ' /USER:' + @vtUser

    SELECT @vtCmd = @vtCmd + ' /PASS:' + @vtPass

    SELECT @vtCmd = @vtCmd + ' /SERVER:' + @vtServer

    SELECT @vtCmd = @vtCmd + ' /DB:' + @vtDbName

    SELECT @vtCmd = @vtCmd + ' /TEMPPATH:' + @vtTmpPath

    SELECT @vtCmd = @vtCmd + ' /CAMP:' + CONVERT(varchar(10), @iCmpgnID) + ','

    SELECT @vtCmd = @vtCmd + CONVERT(varchar(10), @iCmpgnCellID) + ''''

    -- call xp_cmdshell to run DLU program

    EXEC @iErr = master..xp_cmdshell @vtCmd, no_output

    This is the value of the @vtCmd variable prior to the EXEC statement

    'C:\PROGRAM FILES\TMS\Dialer List Utility\DLU.EXE /USER:username /PASS:password /SERVER:server /DB:TMSDVEL /TEMPPATH:C:\DLU_TEMP /CAMP:220,853'

    And here is the error message that is returned,

    Msg 0, Sev 0: Associated statement is not prepared [SQLSTATE HY007]

    Hope someone has an idea what the problem is.

    Thanks

    Phill

    --------------------
    Colt 45 - the original point and click interface

  • December 6, 2001 at 5:03 am

    #420822

    I ran this:

    declare @ierr int

    declare @vtcmd varchar(1000)

    set @vtcmd='C:\PROGRAM FILES\TMS\Dialer List Utility\DLU.EXE /USER:username /PASS:password'

    EXEC @iErr = master..xp_cmdshell @vtCmd

    print @ierr

    And got this:

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

    operable program or batch file.

    NULL

    Could it bet that the problem is in the exe you're running, not in this statement?

    Andy

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

  • December 6, 2001 at 7:08 am

    #420836

    You will need to put quotes in the path to allow the embedded space.

    '''C:\"PROGRAM FILES"\TMS\"Dialer List Utility"\DLU.EXE'

    Don't know if that is the problem but it won't help.


    Cursors never.
    DTS - only when needed and never to control.

  • December 6, 2001 at 7:21 am

    #420837

    You can actually wrap the whole path in double quotes and that will take care of the problem.

    ex: "C:\Program Files\Testing\Testing One\One.exe"

    Ultimately you should be able to take the value of the @vtCmd variable and paste it into the Windows run command and have it execute. If that fails then xp_cmdshell will not be able to run it. My first test is always to use the print to output the value of the variable and do just what I described above. Saves me tons of troubleshooting time.

    Hope this helps.

    David

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • December 6, 2001 at 10:34 am

    #420844

    I also use David's technique.

    Steve Jones

    steve@dkranch.net

  • December 6, 2001 at 4:49 pm

    #420850

    Ok, I've put quotes around the whole path, but I still get the same error.

    Unfortunately, due the usual political guff, I am not allowed to access the command prompt or desktop of the server. My only access is via SQL EM or SQL QA

    I've successfully run the command on my machine, using the exact same exe that exists on the server.

    So now I'm down to either,

    a) SQL has a problem running the command via xp_cmdshell

    or

    b) SQL is successfully running the command but there is a problem with it.

    Guess I really need to get onto the box or drop this little task and go back to having dozens of jobs.

    Thanks for all your input

    Phill

    --------------------
    Colt 45 - the original point and click interface

  • December 6, 2001 at 5:12 pm

    #420851

    Agree with Andy Warren.

    1) Add print statements in you vb code.

    Do you do "proper" error handling (displaying) in the VB app.

  • December 7, 2001 at 7:02 am

    #420868

    Phill,

    It should not be SQL having a problem running it from xp_cmdshell. Anything that you can run from a cmd prompt, it can run from through xp_cmdshell.

    A couple more questions;

    1. What happens when you try to run the cmd line from your PC with the UNC path to the executable on the server? If that flies, then you should be able to run the script from QA on your machine as a test case.

    2. What is running the job / script and does it have permissions to access / launch the executable?

    Hope this is helpful.

    David

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • December 7, 2001 at 10:59 am

    #420878

    I'd get some help on this one and get access to the server (even if its with an admin) to do some debugging. David has the right idea.

    Steve Jones

    steve@dkranch.net

  • December 8, 2001 at 8:26 pm

    #420904

    >> Unfortunately, due the usual political guff, I am not allowed to access the command prompt or desktop of the server. My only access is via SQL EM or SQL QA

    >> I've successfully run the command on my machine, using the exact same exe that exists on the server.

    Tell the political guff that you can't do anything without an admin to help you.

    Otherwise heads and brick walls come to mind.

    Make sure that you have done as much as you can in testing rights first though.

    >> I am not allowed to access the command prompt or desktop of the server.

    If you can run xp_cmdshell then you can (but don't tell the political guff - it may come in useful)

    Replace the VB app by a bat file which justs creates a file and see if that works. This will tell you if the call is doing anything at all. Also replace the call to the exe with a dir command - that will show if you can access the directory and xp_cmdshell.


    Cursors never.
    DTS - only when needed and never to control.

  • December 9, 2001 at 11:28 pm

    #420909

    All

    Thanks for your comments and suggestions.

    This task has been placed the too-hard basket. The "Server Support Technichians" are "not too sure a contract DBA should have access to a server on the production network" and moving the server to a testing/development network "would cause extra security risks and overtime requirements"

    I've been re-directed onto another project that has less "impact on the environment"

    Oh well, one can only try.

    Thanks again.

    Phill

    --------------------
    Colt 45 - the original point and click interface

  • February 25, 2003 at 4:13 pm

    #449754

    Well, a couple of years later and I have just run into the same problem...

    Here's my error:

    "'perl' is not recognized as an internal or external command, operable program or batch file."

    I checked the environment variable "path". It seems to have a path that points to perl.exe

    I ran the dos command directly in dos. It executes successfully.

    I tried writing a batch file that I could call that would in turn execute the perl script. Same "not recognized" error.

    I tried running simple commands like DIR and NET SEND. They worked fine.

    (Then out of frustration, I put NET SEND in a loop to bug a co-worker.)

    Now I'm searching for help...

    What funny stuff is going on behind the scenes?

    Is it possible that there is some security setting that doesn't allow xp_cmdshell to execute some scripts?

    Any clues?

    Thanks,

    -J

  • February 25, 2003 at 4:51 pm

    #449758

    A little more tinkering around and I found out some useful info:

    The following works from the command prompt but will not work from xp_cmdshell:

    perl e:\path\to\perlfile\importExcel.pl

    perl.exe e:\path\to\perlfile\importExcel.pl

    The following works from the command prompt AND from xp_cmdshell:

    e:\perl\bin\perl.exe e:\path\to\perlfile\importExcel.pl

    I guess that the moral is that the permissions that I used when running dos commands through xp_cmdshell, did not have access to the system environment variables. I'm a little skeptical about that though, because I'm nearly certain that the account that sql service is running on is a local sysadmin and the account that installed perl on the same machine was also a local sysadmin.

    So despite the problem being solved, there is still a question here:

    Does xp_cmdshell have access to environment variables OR am I proving myself to be a very incompetent Windows Admin?

  • February 27, 2003 at 8:17 am

    #449914

    Did you try to set proxy account for SQLServerAgent

    msh


    msh

  • February 27, 2003 at 8:27 am

    #449919

    I didn't set the proxy account, but I'm not sure that it would be necessary since the account that runs the procedure already has system administrator priviledges.

    -J

Viewing 15 posts - 1 through 15 (of 17 total)

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