asked on

How to set Isolation level for sql server queries within Excel?

Hi,

I've got an excel spreadsheet which has some 'external data' - which comes from an SQL Server database - and is set to refresh automatically. This has been working fine for a long time...

Then today - I start getting errors in the spreadsheet saying its been selected as a deadlock victim. I tracked down that the deadlock was between some info the spreadsheets query was reading - and writting that info by another process. God knows why this just started happening?

Anyway - I'm thinking that maybe somehow the spreadsheet has started using a more restrictive isolation level today? How is that stuff defined for a query within excel? Any other reasons why my excel query might suddenly start causing deadlocks?

- reddal

reddal

ASKER

Here is a graph of the deadlock from SQL Profiler.

The process that is selected as the victim is just doing a select from a table - and the other table is doing a delete from the table of one row using the primary key.

Thanks for any pointers you might have as to how to approach this. Is it an isolation level issue on the client select or something different?

- reddal
deadlock.jpg

nmcdermaid

Are you just reading data into the spreadsheet? Perhaps you could set up a special user with read only rights and use that for your Excel login.

reddal

ASKER

Problem went away on its own in the end - weird. I still don't know how to set isolation levels in Excel though...

ASKER CERTIFIED SOLUTION

nmcdermaid

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial