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

MORE INFORMATION

Steps to Reproduce Behavior

1. 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.
2. 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.
3. Add a command button (Command1) and the following code to the default form.
   
   Note You must change User ID <User ID> and password <Strong 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=<User ID>;password=<Strong Password>;database=pubs"
     cn.Open "Provider=MSDASQL;Driver={SQL Server};server=myserver;uid=<User ID>;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.
   
   
4. Run the application and click the command button. The following list of data types will be returned:

   ID1            129      <-- adChar (incorrect)
   ID2            3        <-- adInteger
   ID3            2        <-- adSmallInt
   ID4            17       <-- adUnsignedTinyInt
   						

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

   ID1            20      <-- adBigInt
   ID2            3 
   ID3            2 
   ID4            17 
   						

6. Run the original code on a computer with MDAC 2.5 installed. The correct output will also be observed.