Data Type Conversions Using OLE Automation Stored Procedures
Because Microsoft® SQL Server™ uses Transact-SQL data types, and OLE Automation uses Microsoft Visual Basic® data types, the OLE Automation stored procedures must convert the data that passes between them.
The following table describes SQL Server to Visual Basic data type conversions.
| SQL Server data type | Visual Basic data type |
|---|---|
| char, varchar, text, nvarchar, ntext | String |
| 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.
These are the conversions from Visual Basic data types to SQL Server data types.
| Visual Basic data type | SQL Server data type |
|---|---|
| Long, Integer, Byte, Boolean, Object | int |
| Double, Single | float |
| Currency | money |
| Date | datetime |
| String with 4000 characters or less | varchar/nvarchar |
| String with more than 4000 characters | text/ntext |
| One-dimensional Byte() array with 8000 bytes or less | varbinary |
| One-dimensional Byte() array with more than 8000 bytes | image |
See Also
How to create an OLE Automation object (Transact-SQL)