Because SQL Server uses Transact-SQL datatypes, and OLE Automation uses Visual Basic data types, the OLE Automation stored procedures must convert the data that passes between them.
This table shows the conversion from SQL Server to Visual Basic data types.
| SQL Server datatype | Visual Basic data type |
|---|
| char, varchar, text, decimal, numeric |
String |
| bit | Boolean |
| binary, varbinary, image | One-dimensional Byte() array |
| int | Long |
| smallint | Integer |
| tinyint | Byte |
| float | Double |
| real | Single |
| money, smallmoney | Currency |
| datetime, smalldatetime | Date |
| anything set to NULL | Variant set to Null |
All single SQL Server values are converted to a single Visual Basic value with the exception of binary, varbinary, and image values. These values are converted to a one-dimensional Byte() array in Visual Basic. This array has a range of Byte(0 To length-1) where length is the number of bytes in the SQL Server binary, varbinary, or image values.
This table shows the conversions from Visual Basic data types to SQL Server datatypes.
| Visual Basic data type | SQL Server datatype |
|---|
| Long, Integer, Byte, Boolean, Object | int |
| Double, Single | float |
| Currency | money |
| Date | datetime |
| String with 255 characters or less | varchar |
| String with more than 255 characters | text |
| One-dimensional Byte() array with 255 bytes or less | varbinary |
| One-dimensional Byte() array with more than 255 bytes | image |