September 27, 2007 at 11:56 am
I was wondering if anyone could offer some design advise for the following project request. One of our clients wants to be able to print 1 of 4 custom forms based on order criteria when they scan the packing slip for UPS shipping. The UPS machine will write a record of this scan event into a database, which I figured an insert trigger could be used to query the order to determine which form to print. My question is what would be the best way to print the form. They have 2 HP laserjets each with 2 trays, so I need to be able to control which printer and which tray the form will print on. They will then insert this form into the top of the package, seal and ship.
I currently do not have a clue as to the best way to approach this. They are using SQL server 2005 which has the report services, but I am not sure how I could call these reports from a trigger. Everything I have seen calls them from a URL and requires user input to print. They want the process to be totally transparent to the end user. Any help would be greatly appreciated.
September 27, 2007 at 12:17 pm
What is the volume of these scan events?
It may not really need to be event driven. You could create a report that would run on a schedule every 10 minutes and simply print any new records. This would be easier than having it really based on the table insert event.
However, Reporting Services does not natively support reporting directly to a printer, so you will have to program this anyway. So, if you did run this from a trigger event, you could create a CLR trigger that calls to the reporting services web service to generate the report and then to another web service to print the report (you would have to write or purchase this one). Calling the reporting services web service from the CLR is pretty easy (if you have some C# experience) and creating a web service to print the report is fairly easy as well.
September 27, 2007 at 12:20 pm
My experience has been that having triggers interact with objects outside the DBMS is a bad idea. More typically, I've seen this type of event-driven processing accomplished by having the trigger insert a 'request' row into a table. The request table is queried on a regular basis, every minute or every few seconds, by an Integration Services job which does the actual printing based on the information from the row in the request table.
September 28, 2007 at 5:59 am
The volume is pretty high. They ship about 2000 packages per day which would require these custom forms to be printed.
September 30, 2007 at 11:26 am
You might care to look into Notification services as well. This looks to be a good match for that in terms of generating timely "messages". It seems like it's built specifically to handle those kinds of "triggers" requiring interaction with the outside world (as opposed to classic triggers which tend to not do well at all with IO relating to none-SQ).
I'd generate them to file output, and then hook them into a free-standing .NET "file watcher" process to grab the files and actually send them along to the device. Or - build a custom delivery channel to the printer. That way - you don't have processing come to a grinding halt when your printer goes out of paper.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 30, 2007 at 1:08 pm
Consider service broker. The triggers just insert messages into the queue. Message delivery (may be the same db) triggers off a async process that goes off and does whatever necessary for the packages.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2007 at 8:59 pm
Whatever technology you use, you need some sort of application that can control the printer. I don't think SQL Server is a good place for this. Some small .NET app could read the data off the scanner, insert it, and print the report.
Triggers should not interact with anything outside the database.
October 1, 2007 at 8:22 am
Steve Jones - Editor (9/30/2007)
Whatever technology you use, you need some sort of application that can control the printer. I don't think SQL Server is a good place for this. Some small .NET app could read the data off the scanner, insert it, and print the report.Triggers should not interact with anything outside the database.
A couple of folks have mentioned that triggers should not interact with anything outside the database, but have not really explained why that is true. The main reason I do not use triggers to interact with things outside the db is because, in this case, if the attempt to print fails your transaction is rolled back. I am not sure that the users would appreciate having to re-scan orders because the printer is out of paper or jams:P
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 1, 2007 at 8:35 am
Sorry.
The big problem with triggers outside the database is that they're inside the transaciton that fired the trigger. so delays escalate locks and blocks. Transactions should be tight and sending email, printing, running a job to do something, all increase the transaction time.
Also, as Jack mentioned, if something fails, the insert rolls back.
October 4, 2007 at 1:51 pm
I guess I'll be more specific in that a trigger for this type of thing would really need to be an asynchronous trigger (which we can now build).
Many applications in the past would insert a record into a table and then there would be a service constantly looking for a new record in the queue. When a record was found, an action (print the document) would be taken.
Using a trigger for this type of thing was a bad idea in the past mostly because when you act on something outside of your dataase you really increase the risk of something outside your control messing up your initial transaction.
With the use of service broker, you can now make an asynchronous trigger that will allow you to do this kind of processing based on the event of a record being inserted rather than constantly querying a database to see if something is ready.
October 4, 2007 at 2:48 pm
Also - by having a separate transaction deal with the output, versus one directly tied in to the insert, you minimize data loss. You can reprint an invoice, as long as the invoice actually gets inserted into the table (versus what might happen with an INSERT rollback when the printer is turned off).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 4, 2007 at 4:02 pm
Never heard it called an asynchronous trigger, and don't think it should be. We have enough triggers now!
But that is what it does and I agree this is a great use of Service Broker.
October 5, 2007 at 5:10 am
I'm not sure where I ended up calling it an asynchronous trigger, but apparently I am not the only one. Here is a really simple sample of using service broker for a trigger:
http://www.dotnetfun.com/articles/sql/sql2005/SQL2005CreatingTSQLAsynchronousTriggers.aspx
I have spent a bit of time setting up a few queues to call CLR procedures that call web services making it possible to (within the web service) create an event handler based on something happening in the database. Using SOA, you can really make your database part of a large distributed application that just causes events that you can program in.
I'm a bit surprised more people aren't doing this. I remember when everyone was so thrilled when event driven programming replaced console applications (you put code into the button_click what?). It has always amazed me that you could not hook something to a database event without a lot of work or a lot of risk. Service Broker seemed to me an answer to that specific problem.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply