The new SNAC (SQL Native Client) OLE DB provider handles naming of parameters a tad differently than SQLOLEDB. Let's say that I have the parameterized query (using the ODBC-style question mark parameter markers):
SELECT * FROM authors WHERE au_id LIKE ?
And I call OLE DB's ICommandWithParameters::SetParameterInfo to set some parameter information. SetParameterInfo takes a parameter name as one of the parameters. In the case of the question mark parameter, what should I put for the name. Using the SNAC OLE DB provider and using a dummy name (say…"Dummy") produces the error:
DB_E_BADPARAMETERNAME – "Parameter name is unrecognized"
In SQLOLEDB the error is not produced. Difference between providers.
Looking at the OLE DB specification, although it's different behavior, it appears that the SNAC provider is "more correct". Here's the first part of the description of DB_E_BADPARAMETERNAME.
DB_E_BADPARAMETERNAME
In an element of rgParamBindInfo, the pwszName element specified an invalid parameter name. The provider does not check whether the name was correct for the specified parameter, just whether it was a valid parameter name.
SQL Server supports named parameters and positional parameters. Positional parameters have no name. Using a null pointer as the parameter name works, both in SNAC and SQLOLEDB. If you use named parameters in a SQL statement the parameters must start with an at-sign (@) like this:
SELECT * FROM authors WHERE au_id LIKE @auid
So valid values for SQL Server parameters are "null pointer" (no name) and names that start with at-sign. Names that do not start with at-sign SHOULD produce an error. Perhaps SQLOLEDB's forgiving behavior was to accomodate conversion from the OLE DB provider for ODBC (MSDASQL), where anything could be used as a parameter name. Nonetheless, it appears that SNAC provider's behavior is "more correct" they are checking that parameter names begin with at-sign.
Interestingly using either null pointer or @anyname as a parameter name works (ie SetParameterInfo does not produce an error) when you use SNAC. Even if its the wrong name. Note the spec says "The provider does not check whether the name was correct for the specified parameter, just whether it was a valid parameter name." Correct behavior here too. Although if you are using named parameter and use the *wrong* name, SetParameterInfo will work but ICommand::Execute will fail.
So, there are a few provider differences that I've found so far. If you're writing a new program or library, you should start with SNAC provider. To use the new features in SQL Server 2005 like MARS and Query Notifications, you MUST use SNAC (or SqlClient and ADO.NET of course). But be sure to test existing programs before making the switch.