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

Recordset Destination with Foreach Loop Container - finding indexes

I'm curious to know how other people are handling the Recordset Destination in to be processed by a Foreach Loop Container. It seems a little odd to me that you can only map the parameters by knowing the index of the columns of the Recordset, however, the order that you built the recordset destination doesnt stay the same. I've been debugging for a while to find out that after I saved my recordset destination the order of the fields changed. To some order without a clear logic. I'm going to guess it might be the lineage id.

The bigger problem was this was a really large record set with 60 or so columns. To try and debug the problem of finding the indexes, I had added a Multicast tranform and saved the output to an Excel Destination. Of course, the order I setup the Excel file was the order I got the fields. Why would this not be the case with the Recordset Destination?

I've never seen this behaviour. Its possible to define the order of the fields within the recordset destination. It is the order in which you select columns on the "Input Columns" tab.

-Jamie

|||

Strange. I know I saw it earlier tonight, however I just remote desktoped into my work machine to capture a quick video of it happening, and it worked right this time. I'll try it again in the morning, I'd be more than happy if it was working right (despite having to reorder my indexes again).

|||

There's lots to be said for being a name not a number.

I guess a suggestion at the product feedback centre is required.