ADODB, SQLSERVER, SERVERPROPERTY: different types using sqloledb or ODBC driver?

Question

I'm trying to switch one of my VBS scripts from using SQLOLEDB to ODBC driver. So long all works like expected - all but one thing:

When fetching SERVERPROPERTY("is_clustered") from an MSSQL instance the resulting value is different using each driver.

Here's the output of an example script (script follows below):

C:\> cscript test.vbs

Provider: sqloledb

is_clustered (name): is_clustered
is_clustered (type): 12
is_clustered (value): 0

Driver: (SQL Server)

is_clustered (name): is_clustered
is_clustered (type): 204
C:\test.vbs(33, 1) Microsoft VBScript runtime error: Type mismatch

Does anyone know what I'm doing wrong or what I'm missing in my code?

Oh, yes, the code... here's the example script itself:

Option Explicit

Dim RS, CONN1, CONN2

Set RS        = CreateObject("ADODB.Recordset")

Set CONN1      = CreateObject("ADODB.Connection")
CONN1.ConnectionTimeout = 2
CONN1.Provider = "sqloledb"
CONN1.Properties("Integrated Security").Value = "SSPI"
CONN1.Properties("Data Source").Value = "HOSTNAME\INST01"
CONN1.Open

WScript.echo "Provider: sqloledb" & vbLf
RS.Open "SELECT SERVERPROPERTY('IsClustered') AS is_clustered", CONN1
WScript.echo "is_clustered (name): " & RS.fields(0).Name
WScript.echo "is_clustered (type): " & RS.fields(0).Type
WScript.echo "is_clustered (value): " & RS("is_clustered") & vbLf
RS.Close

Set CONN2      = CreateObject("ADODB.Connection")
CONN2.ConnectionTimeout = 2
CONN2.ConnectionString = "driver={SQL Server};" & _
                         "server=HOSTNAME\INST01;" & _
                         "Trusted_Connection=yes"
CONN2.Open

WScript.echo "Driver: (SQL Server)" & vbLf

RS.Open "SELECT SERVERPROPERTY('IsClustered') AS is_clustered", CONN2
WScript.echo "is_clustered (name): " & RS.fields(0).Name
WScript.echo "is_clustered (type): " & RS.fields(0).Type
WScript.echo "is_clustered (value): " & RS("is_clustered")
RS.Close

Many Thanks in advance!

BR, Marcel

Answer 1

Driver	SQL Server type	OLEDB type	ADO type	Variant type
SQLOLEDB	sql_variant	DBTYPE_VARIANT (12)	adVariant (12)	VT_VARIANT (12)
ODBC	varbinary	DBTYPE_BYTES (204)	adVarBinary (204)	n/a

So it looks like the legacy SQLOLEDB is getting the value returned to it as type sql_variant, but when using the legacy ODBC driver it is coming back as varbinary.

This sort of thing isn't unheard of. SQL Server 2005 added varchar(max), varbinary(max), xml, and a few others. ADOdb doesn't understand those types, so you would have to be sure to add an option to your ConnectionString:

Server=hyperion; User ID=ruddiger;Password=hunter2;DataTypeCompatibility=80

This would cause SQL Server for example to return an xml column as ntext

• OLEDB type: DBTYPE_WSTR (203)
• ADO type: adLongVarWChar (203)

Because although they created a new OLEDB type for xml:

• OLEDB type: DBTYPE_XML (141)

ADO was never updated after Windows 2000 to know what DBTYPE of 141 is. So it would throw an error.

There might be something similar happening here with the legacy SQL Server ODBC driver, and there might be an option that needs to be enabled to cause SQL Server to return legacy-compatible data types.

You could also try using the modern, supported, ODBC driver.

Microsoft ODBC Driver 18 for SQL Server

which has a different name from the legacy:

SQL Server

But, i would also recommend not trying to use any ODBC driver. They have a number of won't-fix bugs:

1. Reading columns out of order returns incorrect values

   • reading column values in non-select order gives garbage values:
   • Reading columns out of order returns incorrect values (SQL Server ODBC driver)

   It's an optional performance optimization - that nobody needs or wants. ODBC drivers are perfectly free to support reading columns in any order; the Microsoft driver just refuses to because nobody wants to touch the code.

2. Errors are not raised; but are instead silently eaten

   • errors are not raised (i.e. silently eaten):
   • SQL Server ODBC Driver not raising errors

3. Calling a stored procedure synonym fails

   Except using ODBC Driver to call a synonym procedure fails with:

   The request for procedure '%s' failed because '%s' is a synonym object
   SQLState: 37000
   NativeError: 2809
   The cursor was not declared.
   SQLState: 37000
   NativeError: 16945
   

   • Cannot execute synonym stored procedure with SQL Server ODBC Driver; works with OLEDB

Do download the latest, supported, and functional, MSOLEDBSQL driver.

You will need to be sure to add the DataTypeCompatibiliy=80 in your ADO connection strings.

Also note this note from Microsoft:

The previous Microsoft OLE DB Provider for SQL Server (SQLOLEDB) and SQL Server Native Client OLE DB provider (SQLNCLI) remains deprecated and it is not recommended to use either for new development work.