Add some form of “PrintStatus” column.
- When data is added, it is set to (say) 1, “Not Printed.
- When a client queries for data to print, only return data where PrintStatus = 1
- When a client picks up the data to print, set it to (say) 2, “Being Printed”
- When the client finishes printing, set the status to 3, “Printed”
- If for some reason the client fails to print the data, set the status back to 1 (and PrintedAt back to null)
This leaves the janitor issue, aka what happens if printing fails and that fact is not reported (because the janitor pulled the plug on the server. Don’t laugh, it happened here, though it was a tech repair guy who really should have known better.) If you worry about such things:
- Add some form of time logging, such as column “PrintedAt”, which is set to null whenever PrintStatus is set to 1, and set to getdate() when PrintStatus is set 2. Side benefit: now you know when (or perhaps "which printing") your data was printed!
- PrintStatus gets set to 3 when printing is done. Thus, if PrintStatus = 2, printing has (so far) taken getdate() – PrintedAt time to run.
- Have a regularly scheduled process (SQL Agent jobs are good for this) run periodically. It checks over the table, and if it finds any rows with PrintStatus = 2 where the PrintedAt column is greater than your tolerance (1 day? 2 hours?). Any such rows get “rolled back” by setting PrintStatus to 1 and PrintedAt to null.
This can seem like overkill, but you tell me how critical consistent data is to you.
Added:
Yes, concurrency and consistency is a problem. While you can mess around with BEGIN TRANSACTION ... COMMIT/ROLLBACK, I prefer leveraging the power of implicit transactions. Here's one way:
This sets up a testing table with some data:
-- Set up test data
CREATE TABLE Test
(
Data int not null identity(1,1)
,PrintStatus tinyint not null
,PrintedAt datetime null
)
INSERT Test (PrintStatus) values
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
SELECT *
from Test
GO
This bit contains an UPDATE statement that updates all rows to be printed, and uses the OUTPUT clause to store those items into a temporary table. The contents of that table are then returned to the printing routine.
-- First pass: Get everything to pring
DECLARE @ToBePrinted table
(Data int not null)
UPDATE Test
set
PrintStatus = 2
,PrintedAt = getdate()
output inserted.Data into @ToBePrinted (Data)
where PrintStatus = 1
SELECT Data PrintThese
from @ToBePrinted
SELECT * from Test
GO
Here, we add three more rows of data, then run the routine again. Old items are not selected, new ones are.
-- Second pass: Add three new items, they (and only they) get selected
INSERT Test (PrintStatus) values
(1),(1),(1)
SELECT *
from Test
DECLARE @ToBePrinted table
(Data int not null)
UPDATE Test
set
PrintStatus = 2
,PrintedAt = getdate()
output inserted.Data into @ToBePrinted (Data)
where PrintStatus = 1
SELECT Data PrintThese
from @ToBePrinted
SELECT * from Test
This works because of the ACID properties of SQL Server--specifically I)solation. Once the one statement beings to modify the contents of the table, no other statement can access the data being modified until the execution of the statement has been completed. (Unless you're fool enough to use NOLOCK. Do not use NOLOCK.)
