I'm pulling in sales numbers from a database that is updated every 15 minutes and displayed on a monitor for our salespeople. The numbers are displayed in a graphical manner within Excel using the refresh option (in Connection Properties).
If it's that important, then stop having Excel do the refresh. Create two identical reporting tables. For purposes of discussion, we'll call them TableA and TableB. Have one "TableActive" view and a "TableWork" view (call them what ever you like). To start with, the TableActive view would point at TableA and the "TableWork" view would point at TableB. The spreadsheet would read only from the TableActive view.
Have a stored proc in a job that outputs to the updateable TableWork view, which is currently pointing at TableB. When it's done with the update (or whatever), alter the TableWork view to point at TableA and the TableActive view to point at the freshly updated TableB. Next run, do the exact same thing except reverse what the views are pointing at.
If a deadlock occurs, don't execute the code that repoints the views. Excel will never know the difference.
Jeff, I'm confused on how that will prevent Excel from alerting to a Deadlock message. I'm trying to think through the logic of your instructions but I might be missing something. Currently I already have a SP that updates a table within Excel. The table is updated every 15 minutes with raw data. I have formulas that pull data from that table along with a few other tables are eventually displayed in the graphs. The error occasionally occurs when the table is refreshed. How can I get around Excel doing the refresh?
It will prevent deadlock messaging because Excel will never see a deadlock. Excel will simply read from a view that points to a table that is guaranteed to be static.