|
I'm connecting to an SQL Server database, using BeginTransaction, and I've discovered that Rollback isn't working! The transactions are not rolled back.
Reading through MSDN, I found this:
To ensure that the .NET Framework Data Provider for SQL Server transaction management model performs correctly, avoid using other transaction management models, such as the one provided by SQL Server.
Now, what does that mean? How do I disable SQL Server's transaction management model? Or is this not the problem.
Thanks!
Marc
My website
Latest Articles:
Object Comparer
String Helpers
|
|
|
|
|
The MSDN text means "dont send BEGIN TRAN commands to the server yourself.".
Have you assigned the transaction back to the command, before using it?
There's a sample on Rollback.
|
|
|
|
|
Morten Rasmussen wrote:
The MSDN text means "dont send BEGIN TRAN commands to the server yourself.".
OK. Not doing that.
Morten Rasmussen wrote:
Have you assigned the transaction back to the command, before using it?
Yes. I'm looking at the Rollback example in MSDN, and I'm not doing things different (so says me now, but I'm sure there's something goofy going on).
I did notice, to my horror, that I was using the same SqlConnection over and over. Gads, and this is for a server application!
Marc
My website
Latest Articles:
Object Comparer
String Helpers
|
|
|
|
|
1. issue "set implicit_transactions on" at any point in your code (sql statements) as this turns on autocommit and defeats the possibility of rollback
2. Use transactions inside stored procedures or SQL statements, since commits inside the sproc/SQL will break rollback of the containing (external .NET) transactions.
Basically you have three choices:
1. do all transactions with the API.
2. Use autocommit and forget transactions (bad idea most of the time).
3. do transactions in stored procedures/SQL and not the API.
any mixing of these will be broken in strange ways.
this quote from books online is also pertinent:
If a severe error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. If the client's network connection to SQL Server is broken, any outstanding transactions for the connection are rolled back when the network notifies SQL Server of the break. If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and SQL Server rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back.
If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in SQL Server is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
[Edit] modified to clarify that Transactions inside SQL statements should not be mixed either
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in SQL Server is to roll back only the statement that generated the error.
Hmmm. Even if a BeginTransaction (via ADO.NET) was called before the batch commands were issued?
Marc
My website
Latest Articles:
Object Comparer
String Helpers
|
|
|
|
|
Yes.
but, if you issue a "SET XACT_ABORT ON" the behavior is achanged sot the entire transaction is rolled back (by the rollback statement).
See SET XACT_ABORT in the BOL for an example
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
BTW, that is EXACTLY the problem! Except its not a constraint violation, but a "Cannot insert the value NULL into column..." error.
[edit]erm, ok, that is a constraint violation. What am I thinking![/edit]
Thank you very much!
Now, erm...how does one set this programatically in ADO.NET?
Marc
My website
Latest Articles:
Object Comparer
String Helpers
|
|
|
|
|
sqlCommand.ExecuteNonQuery() whith "SET XACT_ABORT ON" as the SQL text should work. Not sure if the setting persists beyond the life of the connection, but I wouldn't plan on it.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Rob Graham wrote:
sqlCommand.ExecuteNonQuery() whith "SET XACT_ABORT ON" as the SQL text should work.
Tried that. It doesn't seem to. Works fine in Query Analyzer, just not running it from code. Hmmm...
Marc
My website
Latest Articles:
Object Comparer
String Helpers
|
|
|
|
|
Possibly you need "Enlist = true" in your connection string. Connection pooling may be messing with you...
Also, you would need to "SET XACT_ABORT ON" before beginning the transaction.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Hi Rob,
OK, it's probably working. I just put debug statements around the open/close/commit/rollback code, and it's committing the transaction when it shouldn't. So now it's my bug!
Thank you very much for your help. I'd never have found this otherwise!
Marc
My website
Latest Articles:
Object Comparer
String Helpers
|
|
|
|
|
Here is an example of transactional error handling that might help you out.
You can use this in a stored procedure, which is the only way you should update a database anyway.
<br />
Declare @intErrorCode int<br />
select @intErrorCode = @@Error<br />
<br />
begin transaction<br />
<br />
If @intErrorCode = 0<br />
begin<br />
-- insert SQL Statement<br />
set @intErrorCode = @@Error<br />
end<br />
<br />
If @intErrorCode = 0<br />
begin<br />
-- insert another SQL Statement<br />
set @intErrorCode = @@Error<br />
end<br />
<br />
<br />
IF @Error = 0<br />
commit transaction<br />
else<br />
rollback transaction<br />
return @Error
"People who never make mistakes, never do anything."
My blog
http://toddsnotsoamazinglife.blogspot.com/
|
|
|
|
|
Thanks for the code snippet.
ToddHileHoffer wrote:
You can use this in a stored procedure, which is the only way you should update a database anyway.
Why?
BTW, in our application, the SQL is being generated dynamically from the schema information and the dirty fields. So SP's are pretty much out of the question.
Marc
My website
Latest Articles:
Object Comparer
String Helpers
|
|
|
|
|
There are three reasons only to use stored procs.
#1 Security. You can grant users the ability to update records without having permissions to the table. It prevents users from connecting to your database from say access to update the tables.
#2 Prevents SQL Injection attacks.
#3 Performance is improved because the queries' execution plans are cached on the server.
If you have to write out your sql commands because you are dynamically generating sql statements then so be it, but that sounds hairy to me.
"People who never make mistakes, never do anything."
My blog
http://toddsnotsoamazinglife.blogspot.com/
|
|
|
|
|
ToddHileHoffer wrote:
#1 Security. You can grant users the ability to update records without having permissions to the table. It prevents users from connecting to your database from say access to update the tables.
In our system, the user doesn't have direct access to the database. All activity between the client and the server (not the DB server, but the application server) is secured, and only the application server on the remote machine has access to the DB.
ToddHileHoffer wrote:
#2 Prevents SQL Injection attacks.
My understanding is that SQL injection attacks can be prevented by using parameters.
ToddHileHoffer wrote:
#3 Performance is improved because the queries' execution plans are cached on the server.
My understanding is that SQL Server caches execution plans, regardless of whether they're from SP's or not.
ToddHileHoffer wrote:
If you have to write out your sql commands because you are dynamically generating sql statements then so be it, but that sounds hairy to me.
Not at all. It's quite robust. 
Marc
My website
Latest Articles:
Object Comparer
String Helpers
|
|
|
|
|
I am having a hard time using a correlated subquery. I have a table
id - int (identity)
ForwardDate - datetime
EffectiveDate - datetime
Price - float
I am trying to return the record for each ForwardDate where the EffectiveDate is equal to the max(EffectiveDate) for that particular ForwardDate. (There are many different ForwardDate's, each with a different max(EffectiveDate)) I tried many variations of this:
SELECT ForwardDate, EffectiveDate, Value
FROM dbo.ArchiveForward f1
WHERE (EffectiveDate = (SELECT MAX(f2.EffectiveDate)
FROM dbo.ArchiveForward f2
WHERE f1.ForwardDate = f2.ForwardDate))
ORDER BY ForwardDate
but all it does is return the values for those records where EffectiveDate is equal to the max(EffectiveDate) for the entire table.
What am I missing?
Thx
Mark
|
|
|
|
|
This should do the trick
SELECT f1.ForwardDate, f1.EffectiveDate, f1.Value
from dbo.ArchiveForward f1 join
(select ForwardDate, max(EffectiveDate) as EffectiveDate
from dbo.ArchiveForward group by ForwardDate) A
on (f1.ForwardDate = A.ForwardDate and f1.EffectiveDate = a.EffectiveDate)
"People who never make mistakes, never do anything."
My blog
http://toddsnotsoamazinglife.blogspot.com/
|
|
|
|
|
Hi, I hope someone can come up with an explanation to this.
In order to populate my DB with ramdom records I execute this code:
(Parameters are generated using Randomize and a loop calls this function)
Private Function Insert_FakeTRans(ByRef TransNum As Long, ByRef Descrip As Byte, ByRef DDate As Date, ByRef Amount As Integer) As Boolean
Dim Cmd1 As New ADODB.Command() 'To Insert in Table1
Dim Cmd2 As New ADODB.Command()'To Insert in Table2
Try
Cmd1.Prepared = True
Cmd1.ActiveConnection = Con
Cmd1.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
Cmd1.Parameters.Append(Cmd1.CreateParameter("TransNum", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, , TransNum))
Cmd1.Parameters.Append(Cmd1.CreateParameter("Descri", ADODB.DataTypeEnum.adTinyInt, ADODB.ParameterDirectionEnum.adParamInput, , Descrip))
Cmd1.Parameters.Append(Cmd1.CreateParameter("DDate", ADODB.DataTypeEnum.adDate, ADODB.ParameterDirectionEnum.adParamInput, , DDate))
Cmd1.Parameters.Append(Cmd1.CreateParameter("Monto", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, , Amount))
Cmd1.CommandText = "[Insert FakeTrans]"
Cmd2.Prepared = True
Cmd2.ActiveConnection = Con
Cmd2.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
Cmd2.Parameters.Append(Cmd2.CreateParameter("TransNum", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, , TransNum))
Cmd2.Parameters.Append(Cmd2.CreateParameter("CDate", ADODB.DataTypeEnum.adDate, ADODB.ParameterDirectionEnum.adParamInput, , DDate))
Cmd2.CommandText = "[Insert FakeTrans2]"
Cmd1.Execute(, , ADODB.ExecuteOptionEnum.adExecuteNoRecords) 'Err
Cmd2.Execute(, , ADODB.ExecuteOptionEnum.adExecuteNoRecords) 'Err
Return True
Catch
Return False
End Try
End Function
This code runs nicely 340 times, but at attempt 341 gives me a not so nice
"Object not set to an instance of an objet" error message.  (When it gets to the .Execute instruction)
I am VERY new to programming and honestly have no idea why I get this error. PLEASE, HELP, HELP. 
|
|
|
|
|
Hi
Suppose we have table T as :
ID | Rank
________|________
3 2
3 1
6 7
8 3
6 9
12 8
3 9
3 1
ID is the Id of an article. Each user that see the Article can give it a rank ; (1 =< rank <= 9)
For a given ID , I want all the ranks of it as a ordered table. For example :
For ID = 3 I want to have
Rank | Count
______|_________
1 2
2 1
3 0
4 0
5 0
6 0
7 0
8 0
9 1
I have wrote a stored procedure as:
SP_GetRank<br />
<br />
@ID int<br />
.<br />
.<br />
.<br />
<br />
select ID , Rank into #temp1 from T where ID = @ID<br />
select Rank , count(Rank) as [count] from #temp1 group by rank<br />
<br />
.<br />
.<br />
.
This procedure works peoperly when for each ID , each number of 1 to 9 at least one time presents in table T.
How i change my sp?
Thanks a lot!
|
|
|
|
|
First, create a Ranks table with the possible ranks (1 to 9), just one column called Rank.
Now try this query:
SELECT Ranks.Rank, CASE WHEN [count] IS NULL THEN 0 ELSE [count] END AS [count]
FROM Ranks
RIGHT OUTER JOIN (
SELECT Rank, COUNT(*) AS [count]
FROM T
WHERE ID = @ID
GROUP BY Rank) AS sub ON sub.Rank = Rank.Rank
* DISCLAIMER: I get my left and right outer joins mixed up. So, you may need to change the RIGHT OUTER JOIN to a LEFT OUTER JOIN.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Using the above Ranks table, couldn't you just do a left join?
SELECT Ranks.Rank, Count(T.Rank)
FROM Ranks
LEFT JOIN T
ON (Ranks.Rank = T.Rank AND T.ID = 3)
GROUP BY Ranks.Rank
The left join forces all of Ranks to be displayed and Count() can than properly process the null entries as Zero.
|
|
|
|
|
|
Hi,
I'm new to ADO.net,but have couple of years of experience in classic ADO !
Could you please explain me, what is the best method (way) to insert a record in a multiuser environement.
Stored Procedure option seems to be good, but since i'm using MySql,i can't.
Advance Thanks,
aB
|
|
|
|
|
If you're stuck with MySQL, you need to just pass SQL. If you don't have transactions, you're in trouble.
MySQL sucks.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Hi Everyone,
1.I am working on DataGrid. Actually I have relationship between two table(Customers and orders)
displaying on DataGrid. I want to update a record of orders table when I click or select on datagrid,
all the records of order table goes to Textbox control then I or user can update the table easily.
2. How to store the primary key of order table. ( I am using ArrayList )
Thanks
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
