2

We have a long running job for report data generation and we are not worried about changes to the source data after the job has started. For this case the SNAPSHOT isolation mode seems to be a good fit. But we are looking at a possible way to use Snapshot isolation from a scheduled SQL job.

When connecting through a client using ADO.NET we are able to mention the transaction to use Snapshot Isolation (SqlTransaction sqlTran = connection.BeginTransaction(IsolationLevel.Snapshot)). But there seems to be no way of running a scheduled job with the same isolation.

Is there a way we can run an SQL job under snapshot isolation level?

3

If this is a job consisting of a Transact-SQL step, you can specify a SET TRANSACTION ISOLATION LEVEL statement at the beginning of your Transact-SQL script:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

…  /* the rest of your script */
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.