Solved

Printing from server using xp_cmdshell

Posted on 1997-11-25
5
968 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 100 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now