We help IT Professionals succeed at work.

Check out this week's podcast, "Dairy Farms to Databases: Community's Hand in Technology"Listen Now

x

Printing from server using xp_cmdshell

ewarner
ewarner asked
on
1,262 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
____________________________________________________
Comment
Watch Question

TMS

Commented:
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.

Author

Commented:
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 
____________________________________________________
TMS

Commented:
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.

Author

Commented:
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 
____________________________________________________
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.