Using xp_cmdshell

  • RBarryYoung (3/30/2009)


    Krushna (3/30/2009)


    RBarryYoung (3/28/2009)


    OK, and what happens when you execute this yourself in a Query windows?:

    xp_cmdshell '\\192.10.10.245\E:\PrintToPDFConsole.exe'

    It was showing :

    The network path was not found.

    But after I've changed it to a shared folder 'Test' .

    Now the stored proc is :

    xp_cmdshell '\\192.10.10.245\Test\PrintToPDFConsole.exe'

    Now it is showing the output:

    Access is denied.

    Note that I'm executing on SQL Server 2000.

    This happens because xp_cmdshell cause the SQL Server to execute a DOS command for you on the server in a (probably) unprivileged account. You would need to figure out what account it is using and then give it all of the rights neccesary to get to the share. Note that for security reasons your SysAdmin may intentionally deny this account access to the network.

    Also, I've tried locally.

    I've kept the exe file in C:\.

    and changed the stored proc :

    ALTER proc callExe

    As

    EXEC master.dbo.xp_cmdshell 'C:\PrintToPDFConsole.exe'

    Now the output is :

    NULL

    Unhandled Exception: System.ComponentModel.Win32Exception: No application is associated with the specified file for this operation

    at PrintToPDF.Program.Main(String[] args)

    NULL

    and the exe file did not run again.

    This will not work because it is going to the Server's C: drive and not your client's. You would have to copy your exe to the Server's C: drive.

    ALTER proc callExe

    As

    EXEC master.dbo.xp_cmdshell 'notepad.exe'

    and executed the stored proc callExe, but the execution is going on and not showing any output. The execution time is more that 10 mins and going on...

    Then I forcefully closed the isqlw.exe from Task Manager.

    This probably opened a Notepad window on the server's console screen. A windows command executed from DOS like this will hang until some user exits the Windows app.

    Is that mean exe file can't be executed in sql server 2000 using xp_cmdshell ?

    No, it just means that you are doing it wrong. Remember that SQL Server is a Server. When you execute commands on SQL Server, they execute on the Server, not on your Client.

    Thank you for your reply.

    I have checked on server.

    I've kept the exe file in C:\.

    The stored proc is:

    ALTER proc callExe

    As

    EXEC master.dbo.xp_cmdshell 'C:\PrintToPDFConsole.exe'

    Now the output is :

    NULL

    and the exe file does not run.

    Let me more clear about the exe file.

    I've a exe file developed using Visual Studio .Net 2005. It is a console application and it does not contain GUI.

    This application will convert any file(like .txt, .doc etc) to .pdf file.

    It has following code:

    using System;

    using System.Collections.Generic;

    using System.Text;

    using System.Diagnostics;

    namespace PrintToPDF

    {

    class Program

    {

    static void Main(string[] args)

    {

    try

    {

    Process printProcess = new Process();

    printProcess.StartInfo.FileName = "C:\\k.txt";

    printProcess.StartInfo.Verb = "print";

    printProcess.StartInfo.WindowStyle = ProcessWindowStyle.Minimized;

    printProcess.StartInfo.CreateNoWindow = true;

    printProcess.Start();

    try

    {

    //printProcess.WaitForExit();

    }

    catch (InvalidOperationException ex)

    {

    throw ex;

    }

    printProcess.Dispose();

    }

    catch (Exception ex)

    {

    throw ex;

    }

    }

    }

    }

    Please note that I'm using Adobe Acrobat 6.0 Professional and PDF Printer is installed in my system and it is set to default printer. The default port is set to My Document\*.pdf .

    When I run the exe file, the 'k.txt' file getting converted to 'k.pdf' and save to My Document folder.

    Now please tell me what may be the possible errors.

    Is there any configuration problem in my sql server 2000?

    Please tell in detail that what is the configuration i need to change.

    Thank you.

  • Krushna (4/1/2009)


    ...

    I have checked on server.

    I've kept the exe file in C:\.

    That's good...

    Please note that I'm using Adobe Acrobat 6.0 Professional and PDF Printer is installed in my system and it is set to default printer. The default port is set to My Document\*.pdf

    Have you done this on the Server also? Because that is where it is running so it would need to have these things there also.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • RBarryYoung (4/1/2009)


    Krushna (4/1/2009)


    ...

    I have checked on server.

    I've kept the exe file in C:\.

    That's good...

    Please note that I'm using Adobe Acrobat 6.0 Professional and PDF Printer is installed in my system and it is set to default printer. The default port is set to My Document\*.pdf

    Have you done this on the Server also? Because that is where it is running so it would need to have these things there also.

    Yes, Now I'm working in server only.

    My system is having SQL Server 2000 (server) now.

    I've just checked

    exec xp_cmdshell 'C:\PrintToPDFConsole.exe'

    But I'm getting the output

    NULL

    and the exe does not run.

    Shall I need to configure something? Please tell in detail.

  • Krushna (4/1/2009)


    RBarryYoung (4/1/2009)


    Krushna (4/1/2009)


    Please note that I'm using Adobe Acrobat 6.0 Professional and PDF Printer is installed in my system and it is set to default printer. The default port is set to My Document\*.pdf

    Have you done this on the Server also? Because that is where it is running so it would need to have these things there also.

    Yes, Now I'm working in server only.

    My system is having SQL Server 2000 (server) now.

    I've just checked

    exec xp_cmdshell 'C:\PrintToPDFConsole.exe'

    But I'm getting the output

    NULL

    and the exe does not run.

    Shall I need to configure something? Please tell in detail.

    I cannot tell you anything in detail because the problem is with your PrintToPDFConsole program and it's environmental requirements, so I am just guessing here.

    When I have faced problems like this in the past, here are the steps that I have taken:

    1) Modify your PrintToPDFConsole.exe to always return to the console a detailed status description. If it failed, then it should return the error number, error message, line number, etc. AND if it succeeds (or thinks that it has), it should also say that: what it printed and to where. This is crucial to debugging any problems, because right now for instance you are getting nothing back and thus have no idea where the problem might be.

    2) Logon the the server's console (Workstation or Remote Desktop), open a DOS window and execute your PrintToPDFConsole command there. Debug any problems.

    3) Next, still from a console logon, use SSMS (Management Studio) to connect to SQL Server, and from a query window run your sp_ExecuteSql command. Debug and/or report any problems back to us.

    4) Now, still from the console, run your stored procedure and debug and/or report any problems back to us.

    5) Now open a query window from desktop and execute the sp_ExecuteSql command again. Debug and/or report any problems back to us.

    6) Finally run your stored procedure as you intend it to run. Debug and/or report any problems back to us.

    If or when you do report any problems back to us, be sure to indicate which of these steps you were one.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Actually the exe file is running but in the printer it is coming like

    Document Name --- Status --- Owner --- Pages --- Size

    --------------- --- ------- --- ------ --- ----- --- -----

    test ---------- ------ spooling---SYSTEM-- N/A--

    (The above one is like Document Name: test, Status : spooling ...)

    and the test.txt file is not printing.

    What may be the issue here ?

    Please reply.

  • Did you try the steps that I listed above?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • 1) Modify your PrintToPDFConsole.exe to always return to the console a detailed status description. If it failed, then it should return the error number, error message, line number, etc. AND if it succeeds (or thinks that it has), it should also say that: what it printed and to where. This is crucial to debugging any problems, because right now for instance you are getting nothing back and thus have no idea where the problem might be.

    Yes, Now the exe got changed. I've added the message 'Printing Done' after the process start. And it can't say where the printing has done. Because the setting is there in the PDF Printer, not in the code.

    2) Logon the the server's console (Workstation or Remote Desktop), open a DOS window and execute your PrintToPDFConsole command there. Debug any problems.

    Here there is no problem. The message comes 'Printing Done'. Also, the printing process finishes. When the process is going on, I can see the operations and in the status bar, Printer logo and adobe pdf logo also there.

    3) Next, still from a console logon, use SSMS (Management Studio) to connect to SQL Server, and from a query window run your sp_ExecuteSql command. Debug and/or report any problems back to us.

    This gives the following output

    Printing Done

    NULL

    But there is no printing process also no logo on the status bar.

    And in the adobe pdf printer, the status is 'spooling' and this status remains till it has not been canceled.

    and the owner is 'SYSTEM' here.

    4) Now, still from the console, run your stored procedure and debug and/or report any problems back to us.

    This gives result same as option 3.

    5) Now open a query window from desktop and execute the sp_ExecuteSql command again. Debug and/or report any problems back to us.

    This gives result same as option 3.

    6) Finally run your stored procedure as you intend it to run. Debug and/or report any problems back to us.

    This gives result same as option 3.

  • OK Krusna, thanks for the detailed reply, it was very helpful.

    Currently, my suspicion is that either the "PDF Printer" settings as user specific and have not been set for whatever user your SQL Server uses for sp_ExecuteSql, or else the default printer has not been set for that user.

    I think that you will need to check with the makers of your PDF Printer to see have you can set these from the command line or else how to configure them for another use in advance.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 8 posts - 46 through 52 (of 52 total)

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