Microsoft KB Archive/245355

= FIX: SQL Server 2000 BIGINT Data Type Is Mapped by MSDASQL to DBTYPE_STR Instead of DBTYPE_I8 in Schema =

Article ID: 245355

Article Last Modified on 12/3/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.1 Service Pack 1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.01
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ODBC Driver for Microsoft SQL Server 3.7

-



This article was previously published under Q245355



SYMPTOMS
When you open a Schema Recordset, such as adSchemaColumns or adSchemaProviderTypes, against Microsoft SQL Server 2000, the BIGINT data type is mapped to adChar (129) instead of adBigInt (20).



RESOLUTION
Use the SQL Server OLE DB Provider.



STATUS
This bug was corrected in Microsoft Data Access Components 2.5.



Steps to Reproduce Behavior
 On a computer with MDAC 2.0 or 2.1, use Microsoft Visual Basic 5.0 or 6.0 to create a new Standard EXE project. From the Project menu, select References and add a reference to either the Microsoft ActiveX Data Objects 2.0 Library. or the Microsoft ActiveX Data Objects 2.1 Library.  Add a command button (Command1) and the following code to the default form.

Note You must change User ID  and password  to the correct values. Make sure that User ID has the appropriate permissions to perform this operation on the database. Option Explicit

Private Sub Command1_Click Dim cn As ADODB.Connection, rs As ADODB.Recordset Set cn = New ADODB.Connection ' cn.Open "Provider=SQLOLEDB;Data Source=myserver;user id=;password=;database=pubs" cn.Open "Provider=MSDASQL;Driver={SQL Server};server=myserver;uid=;pwd=<Strong Password>;database=pubs" cn.Execute "CREATE TABLE bigint_test (ID1 BIGINT, ID2 INT, ID3 SMALLINT, ID4 TINYINT)" Set rs = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, "bigint_test")) Do While Not rs.EOF Debug.Print rs!COLUMN_NAME, rs!DATA_TYPE rs.MoveNext Loop rs.Close cn.Execute "DROP TABLE bigint_test" cn.Close End Sub Change the connect string to point to a Microsoft SQL Server 2000 database.

</li> Run the application and click the command button. The following list of data types will be returned:

<pre class="fixed_text">ID1           129      <-- adChar (incorrect) ID2           3        <-- adInteger ID3           2        <-- adSmallInt ID4           17       <-- adUnsignedTinyInt

</li> Comment the cn.Open line and uncomment the alternate code that uses the SQLOLEDB provider. This time, the correct data is output:

<pre class="fixed_text">ID1           20      <-- adBigInt ID2           3 ID3           2 ID4           17

</li> Run the original code on a computer with MDAC 2.5 installed. The correct output will also be observed.</li></ol>

Keywords: kbbug kbfix kbmdac250fix kbmdacnosweep KB245355

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.