October 8, 2012 at 2:38 pm
This is an interesting one. I found some vb.net code that would read from a connection a row at a time and fed it the update statement. I control-c'ed after reading one row, but none the less all rows were updated. (Now it could be that it only LOOKs like it reads from a connection a row at a time obviously...)
An important caveat here, I only did 10 rows, because I wanted SQL Server to have the chance to run to completion without filling up our tcp/network buffers or whatever to see just how far up the stack the acknowlegements went (yes, I'm sort of faking it with the terminology, feel free to lecture).
Now with such a small result set, it is absolutely possible that the sqlreader simply consumes all and then tells SQL thanks, then returns each row as it is requested, but none the less, the application in this case can bail yet the update statement was still executed, and the application has no control here. I'm going to tenatively go with pretty much "don't know". I had originally thought that the stack of communications would reach right on up to each line being programattically consumed but it doesn't work that way in this one particular case. It very well could be that other client programming interfaces and drivers act differently.
Interesting!
Module Module1
Sub Main()
' basic code from http://snipplr.com/view/13059/'
Dim objDR As SqlClient.SqlDataReader
Dim objCommand As SqlClient.SqlCommand
Dim ConnectionString As String = "Data Source=myserver\myinstance;Initial Catalog=mydb;integrated Security=SSPI;"
Dim objConnection As SqlClient.SqlConnection
Dim ssql As String
Dim doing As Integer
doing = 0
objConnection = New SqlClient.SqlConnection(ConnectionString)
ssql = "UPDATE dbo.test SET Retrieved = 'Y' OUTPUT inserted.*;"
If objConnection.State <> ConnectionState.Open Then
objConnection.Open()
End If
objCommand = New SqlClient.SqlCommand(ssql, objConnection)
objDR = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
objCommand = Nothing
If objDR.HasRows Then
While objDR.Read()
Console.WriteLine(objDR.Item("Retrieved"))
Console.WriteLine(doing)
' next line gives an opportunity to ctl-c, I ctl-c'ed after 1 row
Console.ReadLine()
doing = doing + 1
End While
End If
objDR.Close()
objDR = Nothing
End Sub
End Module
October 8, 2012 at 2:42 pm
Yes at that point all the data is on the client. Run a trace while running that code. It will not run a new query when you read the next row from the DataReader. It gets populated all at once.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 8, 2012 at 2:51 pm
I reran the little vb snippet with op3's original 1000000 row test, and while it started returning rows, THIS time when I control-c'ed, no rows updated. I do realise that it ran the query once, but clearly with this particular snippet of code, there is nothing in this situation that will predict behavior on a commit. If I were using this style of connectivity, I'd try to keep the transaction independent of the client reader.
I know one time I went to an ATM to withdraw 100 dollars, and everything worked except for the physical dispensing of my money. This reminds me of that 😀
edit: I'm now conclusively going with "don't know".
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply