[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Printing from server using xp_cmdshell

Posted on 1997-11-25
5
Medium Priority
?
1,066 Views
Last Modified: 2008-03-17
Greetings.

I have written a small VB application which prints a packing list for a specified key value.  The syntax for the application is:

   Packing List.exe nnnn

where nnnn is the ID of the packing list to be generated.

My intent is for client machines to be able to automatically print packing lists when products are shipped.  I first created a command file called PACKLIST.CMD with the following contents:

   c:
   cd \winnt
   "Packing List" %1
   exit

Finally, I have placed the following code into the Update trigger of the associated table:

   execute master..xp_cmdshell "packlist nnnn"

When I execute the above command in ISQL/w, the following is returned:
 
   output
   ----------
   (null)
   C:\WINNT\system32>c:
   (null)
   C:\WINNT\system32>cd \winnt
   (null)
   C:\WINNT>"Packing List" 3001
   (null)
   C:\WINNT>exit

   (8 row(s) affected)

This seems to indicate that the command is running successfully.  However, no packing lists are printing, regardless of whether I execute the command from a client or from the server.

My question is this: Is it possible to run a program located on a SQL server from a client machine that sends output to a printer?  When I try something simpler, such as "execute master..cmdshell 'dir *.exe'" everything seems to work properly, but running other executables doesn't work.  In books Online there is an example that runs NET SEND and then pauses and later pauses the SQL server, so I thought that I should be able to execute *any* executable on the server using xp_cmdshell.

If xp_cmdshell won't do what I want it to, then how can I accomplish this task, if at all?

Thank you in advance for your assistance.  Please send any responses to ewarner@eai.com.

Cheers,
Eric

--
____________________________________________________
Eric Warner
Database Programmer
Engineering Animation Inc.
____________________________________________________
V · 515.296.7056              F · 515.296.7025
M · ewarner@eai.com           W · http://www.eai.com
____________________________________________________
0
Comment
Question by:ewarner
  • 2
  • 2
5 Comments
 

Expert Comment

by:TMS
ID: 1089310
What if you execute your packing list executable directly (from the command line ?
(I guess this is working fine)
Well, might be an 'account' problem. When you execute xp_cmdshell, the command you're executing is run under the SQL SERVER account, not the currently logged on user (if any). that means, if you're printing to a network printer, and using a device name (like LPT1), the device is certainly not available from this SQL server account. Try to use the UNC name of the printer. What what you can do is write a small app in VB the iterates the available printers and save them in a file.
What is a good habit too is to write an error handler in VB and log it in a file, so you might get an idea of why the program is failing. Just a 'on error goto XXX' statement and some code writing the contents of the error object to a file.
Hope this helps.
0
 

Author Comment

by:ewarner
ID: 1089311
Thanks for the comment.  My comments are below.

First, the application works correctly when run from the command line on the server.

Second, I am not using any device names to denote which printer to send output.  The VB application simply gathers pertinent records and sends them to a bound Crystal Report, which prints the packing slip to the default printer.  Since the application will always run from this server, I have pre-selected the default network printer as the one to which packing slips are printed.

Third, I have implemented some fairly sophisticated error handling in the VB application and am logging these errors.  Furthermore, no errors occur when the process that I outlined is attempted.

Thank you very much for your continued assistance.

Cheers,
Eric

--
                          ____________________________________________________
Eric Warner
Database Programmer
Engineering Animation Inc.
                          ____________________________________________________
V · 515.296.7056              F · 515.296.7025
M · ewarner@eai.com           W · http://www.eai.com 
____________________________________________________
0
 

Expert Comment

by:TMS
ID: 1089312
Seems like you knew my suggestions before I write them :)

Well, with your comments in mind, I'd look in the account direction... Do you know the account name that was used for the installation of SQL server ? Does your program run fine under this account ?
As you're talking about crystal report, (note I have very little experience with this product) I think reports need a 'run time' component, and maybe that is failing (just a thought), but in that case, you'd tell me your error handler should be noticed... I know, it's just a thought.

Hope this helps anyway.
0
 

Author Comment

by:ewarner
ID: 1089313
Thanks again for your help.  I ran a few more tests and checked some things out, and here's what I found:

First of all, I am logged into the NT server as Administratior, which is the same account under which SQL Server was installed.  Furthermore, I am logged into SQL Server as sa.

Just to make sure that things would work, I set the "xp_cmdshell - Use SQLExecutiveCmdExec Account for Non SA's" option.

When I run the executable from a command prompt on the server, no matter who is logged on to the server, it runs correctly.

Just as a debug, I added a msgbox to the Form_Load routine in the application to make sure that it was executing.  As it happens, when I run the "exec master..xp_cmdshell 'packlist 3001'" command in ISQL/w, the application never even loads, even though the SQL results show it executing normally (see my original comments).

Strange, huh?  For some reason, the application isn't running at all.

Cheers,
Eric
____________________________________________________
Eric Warner
Database Programmer
Engineering Animation Inc.
____________________________________________________
V · 515.296.7056              F · 515.296.7025
M · ewarner@eai.com           W · http://www.eai.com 
____________________________________________________
0
 
LVL 1

Accepted Solution

by:
mikkon earned 200 total points
ID: 1089314
As you probably know, the applications run with xp_cmdshell may not have any kind of user-interface. Does your VB application create a window? Even if it doesn't, some of VB's internal routines may make it look like an interactive application to the SQL Server, and cause it to refuse running.

Can you create console applications with VB? I have successfully run my own programs with xp_cmdshell, and those programs were written in plain C as Win32 console applications. DOS applications can be run as well, of course.

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question