Microsoft KB Archive/201905

= BUG: Distributed SQL Update, Jet Provider may not Return Errors =

Article ID: 201905

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft OLE DB Provider for Jet 4.0

-



This article was previously published under Q201905



SYMPTOMS
When using a SQL Server 7.0 distributed query, with the Jet 4.0 OLE DB Provider to update a row in a Microsoft Access .mdb file, the update may fail and you will see an error similar to the following:

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.

However, the Jet provider returns more details on the error when the same update is used outside a SQL Server distributed query.



RESOLUTION
When problems occur using a distributed query and no error information is available, simplify the query to see if there is a problem with a specific part of the the distributed query.



STATUS
Microsoft has confirmed this to be a bug in Microsoft SQL Server 7.0.



MORE INFORMATION
Microsoft SQL Server version 7.0 provides the ability to perform queries against OLE DB providers. This is done by using the OpenQuery or OpenRowset Transact-SQL functions or by using a query with four part names including a linked-server name.

You can perform a SQL Server distributed update through the Jet OLEDB provider. For example:

UPDATE Access...shippers SET CompanyName='Speedy Express' WHERE CompanyName='Speedy Expres'

'Access' is a linked server that points to the Microsoft Access sample NWind.mdb file. However, the Jet provider may not return details on the cause of the error when an Update query fails.

Steps to Reproduce Behavior
Set-up a linked server and run the following query in the SQL Query Analyzer:

EXEC sp_addlinkedserver

'Access', '',  'Microsoft.Jet.OLEDB.4.0', 'e:\VS98\VB98\nwind.mdb', NULL, NULL

go sp_addlinkedsrvlogin 'Access', 'FALSE', NULL, 'Admin', Null go update Access...orders set shipvia=10 where shipvia=1

The Update statement fails with the following error:

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.

If you run the same update statement directly through the Jet OLEDB provider in an ActiveX Data Objects ADO application, it will give the precise error message regarding why the update failed:

You cannot add or change a record because a related record is required in shippers table.

Following is a Visual Basic snippet of code that will produce error details:

Dim cn As ADODB.Connection, rs As ADODB.Recordset, I As Long Set cn = New ADODB.Connection Set rs = New ADODB.Recordset cn.Provider = "Microsoft.Jet.OLEDB.4.0" cn.Open "e:\vs98\vb98\nwind.mdb"

' The following gives an error "You cannot add or change a record ' because a related record is required in shippers table:

cn.Execute "Update orders set shipvia=10 where shipvia=1"

