Recordset.update with SQL Native Client doesn't work

We recently migrated from SQL Server 7 to SQL Server 2005. Now there's a curious thing with some legacy applications. I have pasted some code below. Don't judge me, because like I said, it's legacy.

You can see, that I have two connection strings. One is commented and accessing via SQL Native Client. The other one is doing this through the old SQL Server driver. Funnily enough, when I use the new Native Client driver, the exception "Run-time error '-2147467259 (80004005)' [Microsoft][SQL Native Client]Invalid attribute value" is thrown in the rsPorder2.Update line. With the old driver, this works just alright.

Is this a bug? Is there a way, to make the code run, because we don't want to search the whole application for other occurances, if not necessary.

Any insights would be greatly appreciated.

Best regards,
DD

Dim ilinx As New ADODB.Connection
Dim rsPorder As New ADODB.Recordset
Dim rsPorder2 As New ADODB.Recordset
Dim cmdLinx As New ADODB.Command
Dim strConn As String

Dim lvIli_number As String

'strConn = "DRIVER=SQL Native Client;Server=10.0.14.7;Description=Test2k;UID=sa;PWD=asdf;APP=Microsoft? Access;WSID=DEHHC023;DATABASE=Linx;Network=DBMSSOCN;"

strConn = "DRIVER=SQL Server;Server=10.0.14.7;Description=Test2k;UID=sa;PWD=asdf;APP=Microsoft? Access;WSID=DEHHC023;DATABASE=Linx;Network=DBMSSOCN;"
ilinx.Open strConn
Set cmdLinx.ActiveConnection = ilinx

lvIli_number = "12345"
cmdLinx.CommandText = "SELECT * FROM porder WHERE previous_ili_no = '" & lvIli_number & "' "
cmdLinx.CommandType = 1

rsPorder.Open cmdLinx, , 1, 1

If rsPorder.EOF Then

cmdLinx.CommandText = "SELECT * FROM porder WHERE ili_no = '" & lvIli_number & "' "
cmdLinx.CommandType = 1
rsPorder2.Open cmdLinx, , 1, 3

If rsPorder2.RecordCount > 0 Then

rsPorder2("Locked") = 0
rsPorder2.Update
End If

End If
rsPorder.Close

Hi,

did you trace the SQL Server with the profiler to see which command arrives at the server ? This would be very interesting to see if come commands arrive at the SQL Server level or not. The connection string you provided work for me with a .NET application an a ODBCConnection, so there has to be another problem with your connection.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Hi,

I just traced the server, but to be honest, I don't know, what to make out of it.
The last two steps are, which seem to represent the rsPorder2("Locked") = 0 bit.
declare @.p3 int
set @.p3=1
declare @.p4 int
set @.p4=1
exec sp_cursorfetch 180150003,256,@.p3 output,@.p4 output
select @.p3, @.p4

and

exec sp_cursor 180150003,32,1

I probably haven't been clear on this, but I can access the database with the Native Client just fine. Just when it comes to this particular update method, it's where I receive the exception described.

Best regards,
DD|||

Hi,

you should trace the information that is executed on the server while doing the Update method. Seems that the trace you posted here is something like a cursor move on the server. Otherwise I would open the post again for fellows which might already got in that problem.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Just change the default CursorLocation into ilinx.CursorLocation = adUseClient and it will run correctly.|||

Thanks, I was having the very same problem and changing the Cursor Location to adUseClient works

Recordset.update with SQL Native Client doesn't work

We recently migrated from SQL Server 7 to SQL Server 2005. Now there's a curious thing with some legacy applications. I have pasted some code below. Don't judge me, because like I said, it's legacy.

You can see, that I have two connection strings. One is commented and accessing via SQL Native Client. The other one is doing this through the old SQL Server driver. Funnily enough, when I use the new Native Client driver, the exception "Run-time error '-2147467259 (80004005)' [Microsoft][SQL Native Client]Invalid attribute value" is thrown in the rsPorder2.Update line. With the old driver, this works just alright.

Is this a bug? Is there a way, to make the code run, because we don't want to search the whole application for other occurances, if not necessary.

Any insights would be greatly appreciated.

Best regards,
DD

Dim ilinx As New ADODB.Connection
Dim rsPorder As New ADODB.Recordset
Dim rsPorder2 As New ADODB.Recordset
Dim cmdLinx As New ADODB.Command
Dim strConn As String

Dim lvIli_number As String

'strConn = "DRIVER=SQL Native Client;Server=10.0.14.7;Description=Test2k;UID=sa;PWD=asdf;APP=Microsoft? Access;WSID=DEHHC023;DATABASE=Linx;Network=DBMSSOCN;"

strConn = "DRIVER=SQL Server;Server=10.0.14.7;Description=Test2k;UID=sa;PWD=asdf;APP=Microsoft? Access;WSID=DEHHC023;DATABASE=Linx;Network=DBMSSOCN;"
ilinx.Open strConn
Set cmdLinx.ActiveConnection = ilinx

lvIli_number = "12345"
cmdLinx.CommandText = "SELECT * FROM porder WHERE previous_ili_no = '" & lvIli_number & "' "
cmdLinx.CommandType = 1

rsPorder.Open cmdLinx, , 1, 1

If rsPorder.EOF Then

cmdLinx.CommandText = "SELECT * FROM porder WHERE ili_no = '" & lvIli_number & "' "
cmdLinx.CommandType = 1
rsPorder2.Open cmdLinx, , 1, 3

If rsPorder2.RecordCount > 0 Then

rsPorder2("Locked") = 0
rsPorder2.Update
End If

End If
rsPorder.Close

Hi,

did you trace the SQL Server with the profiler to see which command arrives at the server ? This would be very interesting to see if come commands arrive at the SQL Server level or not. The connection string you provided work for me with a .NET application an a ODBCConnection, so there has to be another problem with your connection.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Hi,

I just traced the server, but to be honest, I don't know, what to make out of it.
The last two steps are, which seem to represent the rsPorder2("Locked") = 0 bit.
declare @.p3 int
set @.p3=1
declare @.p4 int
set @.p4=1
exec sp_cursorfetch 180150003,256,@.p3 output,@.p4 output
select @.p3, @.p4

and

exec sp_cursor 180150003,32,1

I probably haven't been clear on this, but I can access the database with the Native Client just fine. Just when it comes to this particular update method, it's where I receive the exception described.

Best regards,
DD|||

Hi,

you should trace the information that is executed on the server while doing the Update method. Seems that the trace you posted here is something like a cursor move on the server. Otherwise I would open the post again for fellows which might already got in that problem.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Just change the default CursorLocation into ilinx.CursorLocation = adUseClient and it will run correctly.|||

Thanks, I was having the very same problem and changing the Cursor Location to adUseClient works

Records don't lock-one user receives error

I created a MSAccess application with the forms in one db (front end) and the tables in another db (back end). The network admin migrated the back end to SQL Server. With a few minor changes (changing -1 to 1 in queries, etc.) the migration seemed to go pretty smoothly and everything works fine except....

The MSAccess backend used to lock records when someone was editing them. The status of the record was indicated in the record selector on each form, a right pointing arrow to indicate the record was free, a pencil with three dots indicating the record was locked by the current user, and a circle with a diagonal line to indicate the record was locked by another user. When 2 users attempted to simultaneously edit a record, the first user got the lock on the record. The second user received a message as soon as they attempted to edit the locked record to the effect that the record was locked.

Now (since the migration to SQL Server) the record selectors no longer give any indication as to the locking status of the records. When 2 users attempt to simultaneously edit a record, both are able to enter information in their forms, but when the second user attempts to save the record they receive the following message:

This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.

Please help me find the cause of this behavior. Is it something I need to change in the MSAccess front end or something that needs to be set in the SQL Server back end by the network admin?

I am moving this question to the SQL Server Database Engine forum.