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

ID: Q201905

The information in this article applies to:

Microsoft SQL Server version 7.0
Microsoft OLE DB Provider for Jet, version 4.0

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"

REFERENCES

SQL 7.0 Books Online; search on: "sp_addlinkedserver"; topics: "OpenQuery"; "OpenRowset".

Additional query words: kbDSupport kbcode

Keywords : kbole kbDatabase kbOLEDB kbProvider kbSQLServ
Version : WINDOWS:4.0; winnt:7.0
Platform : WINDOWS winnt
Issue type : kbbug