Re: Best Practice - xp_cmdshell question

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 11/15/04 

Date: Mon, 15 Nov 2004 13:37:08 -0500

Why not have the trigger insert the data into a table and then have a
scheduled job routinely poll the table for items to be printed. You can then
use vbscript or xp_cmdshell in the job to do the printing. 

-- 
Andrew J. Kelly  SQL MVP
"smaas@newsgroups.nospam" <smaasnewsgroupsnospam@discussions.microsoft.com> 
wrote in message news:1BDEB22E-0923-4825-AF22-62037C376AEB@microsoft.com...
> Mike,
>
> Thanks for answering.  I read the overview of Notification Services, and 
> I'm
> not seeing how it could help us resolve this particular issue, but perhaps 
> I
> need to clarify a bit.
>
> The table the trigger runs on is a "tag print" table.  Whenever users
> receive product, they use an application that writes to this table, 
> "tagging"
> the product with a tag code and also storing relevant information such as
> item #, etc.  From there, these tags need to be printed, so the trigger 
> pulls
> together the information from the inserted record plus formatting for the
> barcode program we use, and it then uses xp_cmdshell to run a VB 
> executable
> that creates a file with a title that looks to be a combination of date 
> and
> time and the contents of the file are those assembled in the trigger.
> Looking at the files created, I'm not sure why we really need the VB 
> program
> if we can have SQL Server write this file itself in some way that would 
> not
> need to use xp_cmdshell.  Do you have any suggestions?
>
> "Mike Epprecht (SQL MVP)" wrote:
>
>> Hi
>>
>> The only way is using xp_cmdshell, but having a trigger (and its 
>> associated
>> locks) wait on a batch file is really suicide. If the batch fails, the
>> trigger rolls back the data.
>>
>> Notification Services might be a possible way to get this out of your
>> trigger. http://www.microsoft.com/sql/ns/default.asp
>>
>> Slap the 3rd party for their terrible understanding of what SQL Server's
>> function is.
>>
>> Regards
>> --------------------------------
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>>
>> IM: mike@epprecht.net
>>
>> MVP Program: http://www.microsoft.com/mvp
>>
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "smaas@newsgroups.nospam" 
>> <smaasnewsgroupsnospam@discussions.microsoft.com>
>> wrote in message 
>> news:467505F1-3DC5-4D0B-ACE4-B0C80EDD8F7B@microsoft.com...
>> > I would like to follow the Microsoft suggested best practice of 
>> > removing
>> > execute permissions on xp_cmdshell to non-sysadmin users.  Currently, 
>> > we
>> are
>> > allowing an account to run xp_cmdshell due to a trigger written by a 
>> > 3rd
>> > party that executes a batch file.  Are there any alternative ways of
>> > executing the batch file from the trigger that do not require 
>> > xp_cmdshell?
>>
>>
>>

Relevant Pages

  • Re: Update trigger
    ... nvltrigger for each column for example. ... SQL> drop trigger emp_upd_trig ... SQL> create table emp ( ... Commit complete. ...
    (comp.databases.oracle.server)
  • Re: Update trigger
    ... nvltrigger for each column for example. ... record update even if we are only effectively changing one column ... SQL> create or replace trigger emp_upd_trig ... SQL> update emp ...
    (comp.databases.oracle.server)
  • Re: One more trigger question
    ... They also have a history table which shows all the opt outs and opt ... So I figured a trigger on the ... SQL> create table inactive( ... a customer opts out of some email product. ...
    (comp.databases.oracle.misc)
  • Re: on delete set null
    ... > Nun versuche ich das ganze mit einem Trigger zu simulieren (auf SProcs ... SQL Server verlangt ... > CREATE TABLE voranstellen kann, in denen die Tabellen erzeugt werden, ...
    (microsoft.public.de.sqlserver)
  • Re: One more trigger question
    ... They also have a history table which shows all the opt outs and opt ...  So I figured a trigger on the ... SQL> create table inactive( ...
    (comp.databases.oracle.misc)