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.
These are the conversions from SQL Server to Visual Basic data types.
| SQL Server data type | Visual Basic data type |
|---|---|
| char, varchar, text | 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 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 |
| How to create an OLE Automation object (Transact-SQL) | OLE Automation Sample Script |
| How to debug a custom OLE Automation server (Transact-SQL) | System Stored Procedures |