Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Haidong Ji » Using xp_cmdshell
Add to briefcase
53 posts, Page 5 of 6
«««23456»»

Using xp_cmdshell Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Krushna
Posted Saturday, March 28, 2009 12:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 18, 2013 5:53 AM
Points: 9, Visits: 33
RBarryYoung (3/26/2009)
"Not working" doesn't tell us very much. Is there an error message?


When the stored proc was executed, the output coming as null and the exe is not processing.
Post #685510
Krushna
Posted Saturday, March 28, 2009 12:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 18, 2013 5:53 AM
Points: 9, Visits: 33
Jeff Moden (3/26/2009)
krushnasahu (3/26/2009)
I have some more doubts.

Actually I need to run an exe file from stored proc.
So, I wrote the stored proc like:

create proc callExe
As
EXEC xp_cmdshell '\\192.10.10.245\E:\PrintToPDFConsole.exe'

In this 192.10.10.245 is another system apart from the sql server.
and the exe file path is E:\PrintToPDFConsole.exe

But the exe file is not working when I executed the stored proc callExe.

Note that the PrintToPDFConsole.exe don't have any interface.

Please help in this issue.

Thank you.
Krushna


Does that actually run if you try it from a real command window?


Yes, the exe is working fine when it run from command prompt.
But when the stored proc was executed, the output coming as null and the exe is not processing.
Post #685511
RBarryYoung
Posted Saturday, March 28, 2009 9:41 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374
OK, and what happens when you execute this yourself in a Query windows?:
xp_cmdshell '\\192.10.10.245\E:\PrintToPDFConsole.exe'

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
Post #685570
Krushna
Posted Monday, March 30, 2009 3:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 18, 2013 5:53 AM
Points: 9, Visits: 33
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.

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.

Please help in this. Does it need any modification or any other idea?

Also, I've tried with common exe file like 'notepad.exe' like:

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.

Is that mean exe file can't be executed in sql server 2000 using xp_cmdshell ?
Post #685936
RBarryYoung
Posted Monday, March 30, 2009 7:45 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
Post #686101
Krushna
Posted Wednesday, April 01, 2009 7:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 18, 2013 5:53 AM
Points: 9, Visits: 33
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.
Post #687909
RBarryYoung
Posted Wednesday, April 01, 2009 10:51 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
Post #688180
Krushna
Posted Wednesday, April 01, 2009 11:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 18, 2013 5:53 AM
Points: 9, Visits: 33
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.
Post #688535
RBarryYoung
Posted Thursday, April 02, 2009 7:26 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374
Krushna (4/1/2009)
RBarryYoung (4/1/2009)
Krushna (4/1/2009)
[quote]
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
Post #688860
Krushna
Posted Monday, April 13, 2009 7:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 18, 2013 5:53 AM
Points: 9, Visits: 33
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.
Post #695772
« Prev Topic | Next Topic »

Add to briefcase
53 posts, Page 5 of 6
«««23456»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.