BUG: Data Loss When You Use the Save Method with adPersistADTG and adUseServer

View products that this article applies to.

This article was previously published under Q317913

SYMPTOMS

When you save a large recordset to the Advanced Data Tablegram (ADTG) format by using the Microsoft SQL Server OLE DB Provider (SQLOLEDB) and setting the CursorLocation property to adUseServer, some data may be lost when the recordset is reopened from the file.

NOTE: This behavior does not occur with Microsoft Data Access Components (MDAC) version 2.5.

Back to the top

CAUSE

When you use the Save method to persist the recordset to the file, certain properties are saved with it, including the maximum number of rows. In MDAC 2.6 and MDAC 2.7, this value (DBPROP_MAXROWS) is incorrectly updated.

Back to the top

RESOLUTION

To work around this issue, use any of the following methods:

Set the CursorLocation property to adUseClient instead of adUseServer.
Use the adPersistXML value instead of adPersistADTG.
Use MSDASQL and the SQL Server ODBC Driver instead of the SQL Server OLEDB Provider.

Back to the top

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

Back to the top

MORE INFORMATION

Steps to Reproduce the Problem

Create a Standard EXE project in Microsoft Visual Basic.

Add the following code to the Form_Load event:

Private Sub Form_Load()
Const sFileLocation As String = "C:\MyRecordset.adtg"
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
    Set con = New ADODB.Connection
    con.CursorLocation = adUseServer
    con.Provider = "SQLOLEDB"
    con.ConnectionString = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI;"
    con.Open
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM Orders, Shippers", con, adOpenStatic, adLockReadOnly, adCmdText
    Debug.Print "RecordCount before Save: " & rs.RecordCount
    If Dir(sFileLocation, vbNormal) <> "" Then Kill sFileLocation
    rs.Save sFileLocation, adPersistADTG
    rs.Close
    rs.Open sFileLocation, con, adOpenStatic, adLockReadOnly, adCmdFile
    Debug.Print "RecordCount after Save: " & rs.RecordCount
    rs.Close
    con.Close
    Set rs = Nothing
    Set con = Nothing
End Sub

Change the ConnectionString setting to connect to the Northwind database on your SQL Server computer.
Run the application. The Immediate window displays the following:
RecordCount before Save: 2490
RecordCount after Save: 1297

Back to the top

REFERENCES

The following MSDN Web site

Save Method
http://msdn.microsoft.com/en-us/library/ms808658.aspx (http://msdn.microsoft.com/en-us/library/ms808658.aspx)

states the following:

For best results, set the CursorLocation property to adUseClient with Save. If your provider does not support all of the functionality necessary to save Recordset objects, the Cursor Service will provide that functionality.

For more information about rows and row handles, browse to the following MSDN Web site:

MSDN Library topic "Rows and Row Handles"
http://msdn.microsoft.com/en-us/library/ms719753(VS.85).aspx (http://msdn.microsoft.com/en-us/library/ms719753(VS.85).aspx)

For more information about the DBPROP_MAXROWS value, browse to the following MSDN Web site:

MSDN Library topic "DBPROP_MAXROWS"
http://msdn.microsoft.com/en-us/library/ms723104(VS.85).aspx (http://msdn.microsoft.com/en-us/library/ms723104(VS.85).aspx)

Back to the top