Re: Best Practice - xp_cmdshell question
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 11/17/04
- Previous message: WJ: "Sql Security ?QL"
- In reply to: smaas_at_newsgroups.nospam: "Re: Best Practice - xp_cmdshell question"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Tue, 16 Nov 2004 23:37:55 -0500
You can run a job that basically never ends by placing it in a while loop
such as:
WHILE 1 = 1
BEGIN
** Do your stuff here
WAITFOR DELAY '00:00:05'
END
You can then print every 5 seconds (or whatever) if there is anything to
process. But stuff such as printing is usually best served in the
application and not directly from the database.
-- Andrew J. Kelly SQL MVP "smaas@newsgroups.nospam" <smaasnewsgroupsnospam@discussions.microsoft.com> wrote in message news:1D71CE15-0F71-4219-B162-E6566C396CA7@microsoft.com... >I thought this might be a good idea, but the tag reading software is > currently scheduled to run every *second* and so any scheduled job to > retrieve data would need to run just as frequently. From what I can see, > the > minimum interval for a scheduled job is 1 minute. If so, this would not > work, because users cannot be asked to wait for 1+ minutes before a tag > prints. We may just go ahead and stick with what we have as an acceptable > risk. The only saving grace is that the account is an application > account - > users do not know the password and thus it is already as if the execution > was > running under a service account of sorts... > > "Andrew J. Kelly" wrote: > >> 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? >> >> >> >> >> >> >> >> >>
- Previous message: WJ: "Sql Security ?QL"
- In reply to: smaas_at_newsgroups.nospam: "Re: Best Practice - xp_cmdshell question"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
