Data Type Conversions Using OLE Automation Stored Procedures

Accessing and Changing Relational Data

Accessing and Changing Relational Data

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)

OLE Automation Sample Script

How to debug a custom OLE Automation server (Transact-SQL)

System Stored Procedures