Command dialect is not supported by SQLNCLI11 provider SQL Server 2012

Question

We are using classic ASP and SQL Server 2012 standard edition.

Our connection string is as below

provider=SQLNCLI11;Data Source=xxx; MultiSubnetFailover=True; Initial Catalog=xxx; User ID = xxx; Password =xxxxxx; DataTypeCompatibility=80; Integrated Security=SSPI; MARS Connection=true;

We were using SQLOLEDB provider and it was working fine in our application. When we tried to change provider to SQL Native client (SQLNCLI 11) it was throwing error

Command dialect is not supported by SQLNCLI11 provider SQL Server 2012

on below line

set ocmd = server.CreateObject("ADODB.Command")  'created command object
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"  ' assigned dialect
oCmd.Execute , , 1024     'Error line

Answer 1

Had to look this one up not seen it for a while.

The Dialect property of the ADODB.Command object has this description

^{From ADO API Reference - Dialect Property}
Indicates the dialect of the CommandText or CommandStream properties. The dialect defines the syntax and general rules that the provider uses to parse the string or stream.

So the Dialect is specific to a provider, now that you have changed the provider the Command Dialect is not longer supported for that specific GUID reference, that isn't to say that SQLNCLI11 won't support others.

The specific Dialect

{5D531CB2-E6Ed-11D2-B252-00C04F681B71}

Provides support for Applying an XSL Transformation with the SQLXMLOLEDB provider.

I doubt there is an equivalent setting in the SQL Server Native Client because SQL Server 2012 supports the FOR XML clause for returning XML data.