query rollback policies in sql server 2008 r2

Question

I have a query that consists of some inserts and updates, something like this

   Insert Into #TempTable (...) Select top (1000) * from T where ....

   Update T2 Set c1=...,c2=... where w1

   Update T2 Set c1=...,c2=... where w2

   Update T2 Set c1=...,c2=... where w2

I run this query from a C# App. Each part of this query could be got time out because of heavy load on server, I wanna know that if one part of query got time out (for example second update), all prior queries (Insert and first update) would be rollback by Sql Server engine?

duplicate of http://stackoverflow.com/questions/10153648/correct-use-of-transactions-in-sql-server-2008 — Ram Singh, Nov 11 '13 at 07:30
@user1121366 You cannot do a rollback without using a transaction. — user247702, Feb 28 '14 at 00:31

score 0 · Answer 1 · answered Feb 12 '18 at 20:28

After opening the SqlConnection at your code, do SqlCommand.CommandTimeout = 0;, This will prevent timeout errors causing by .net C# side.

If you are creating SqlDataAdapter by using the sqlcommand you set the timeout to 0, make sure that: SqlDataAdapter.SelectCommand.CommandTimeout=0 after creation by debugging.

query rollback policies in sql server 2008 r2

1 Answers1