Hi,

VB 2005 Prof, SQL server 2005, Windows application

- I am using transaction scope and within this scope I am passing multiple
SQL statements to the command.text

On executing, all is well.

But noticed that in case there is SQL server time out, then the transaction
doesn't fail.
Say, if the system had processed 2 sql statements from the bunch of multiple
statements in the command, then the database gets updated with these 2 sql
statements.

Ofcourse, if the transaction scope does fail , then there is no update

So why should the transaction be not enforced in case of an SQL server time
out? If this is a normal behaviour, then how to avoid this pitfall?

thanks.

Hi,
From your description, I understand that:
You encountered the time out error of SQL Server in your VB.NET
application. The problem was that the transaction did not fail after the
timeout.
If I have misunderstood, please let me know.

I am afraid that the timeout should be caused by the settings in your SQL
connection string. As far as I know, the default timeout value in a
SqlConnection is 15 seconds. You may enlarge the timeout value by setting
the property "Connection Timeout = <value (s)>" in the connection string
and see what is going on. In fact, at the timeout point, the execution at
the server side is still running without any issue. The timeout exception
is not thrown by SQL Server, meanwhile SQL Server hasn't finished the
execution, so the transaction cannot rollback at that time.

I recommend that you try running the script file in Query Analyzer and see
how long it will cost, then you can set an appropriate timeout value for
your SqlConnection.

Charles Wang
Microsoft Online Community Support

================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Charles,

Thanks. You have understood it perfectly well.

I would definitely follow your advise on increasing the connection timeout
value.

Does this also mean that during the transaction process, if the network
connectivity is broken due to some reason or there is a serious application
error causing the application to shut down, the "transaction" will not
rollback??

thanks.



"Charles Wang[MSFT]" <changliw (AT) online (DOT) microsoft.com> wrote

Hi,
I am afraid not. If a connection is timeout and the transaction has been
running but not been committed, SQL Server will automatically rollback the
operations at the end. If your code snippet is like this:
SqlConnection cn = new SqlConnection("data
source=myserver;initial catalog=testdb;integrated security=SSPI;Connection
Timeout=15");
cn.Open();
SqlCommand cmd = new SqlCommand("select * into table1 from
table2 where id = 70", cn); //Assuming that it takes about 50seconds to be
accomplished
SqlTransaction trans = cn.BeginTransaction();
cmd.Transaction = trans;
try
{
cmd.ExecuteNonQuery();
trans.Commit();
textBox1.Text += "\r\nCommit Transaction.";
}
catch (Exception e1)
{
trans.Rollback();
textBox1.Text += "\r\nRollback Transaction:" + e1.Message;
}
finally
{
cn.Close();
textBox1.Text += "\r\nClosed.";
}
when the timeout comes out, trans.Rollback() will fail with an exception
but SQL Server will automatically rollback the transaction at the server
side since it cannot receive the commit command from the client. If an
application does not use trasaction, some operations may not be able to
automatically rollback when the connection is disconnected.

I am not clear what the situation that you are encountering. If this issue
persists, could you please mail me (changliw (AT) microsoft (DOT) com) a test database
backup file and your code snippet for further research?

If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.

Charles Wang
Microsoft Online Community Support

Hi Anisol,
I am just checking if you need furhter research on this issue. If it is
convenient to you, could you please let me know the issue status?
Please post back at your convenience. We will try to assist you further if
it has not been resolved.

Sincerely yours,
Charles Wang
Microsoft Online Community Support