Description:
I'm using SQL Server 2005 SP4 64bit on a Windows 2003 R2 x64 server.
MySQL Connector 5.2.5 Unicode is installed and an ODBC DSN is set up, and the test works OK. (The MySQL server is on a different machine)
The MySQL server version is 5.0.27
I have a "linked server" set up in SQL Server pointing to MySQL using the MSDASQL OLEDB provider for ODBC drivers.
Simple queries work fine using the SQL Server "linked server" syntax e.g.:
SELECT * FROM OPENQUERY(MYSQL, 'SELECT 1')
But it fails if the query string has a leading tab or line break:
SELECT * FROM OPENQUERY(MYSQL, '<tab>SELECT 1')
SELECT * FROM OPENQUERY(MYSQL, '
SELECT 1')
This works fine with non-MySQL linked servers, and it works fine with older MySQL drivers. (e.g. 5.1.12)
Using tabs or line breaks inside the query or at the end aren't a problem.
Using the "SQL Profiler" that comes with SQL Server, the error is:
OLE DB provider "MSDASQL" for linked server "MYSQL" returned an incorrect value for "DBPROP_CONCATNULLBEHAVIOR", which should be "either DBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULL".
There is an "OLEDB Call Event" just before this error with some parts that look relevant:
<hresult>265946</hresult>
<outputs>
<pcPropertySets>4</pcPropertySets>
<prgPropertySets>
...
<DBPROPSET>
<cProperties>17</cProperties>
<guidPropertySet>DBPROPSET_DATASOURCEINFO</guidPropertySet>
<rgProperties>
...
<DBPROP>
<dwPropertyID>DBPROP_CONCATNULLBEHAVIOR</dwPropertyID>
<dwOptions>0</dwOptions>
<dwStatus>0</dwStatus>
<colid>DB_NULLID</colid>
<vValue>
<VARIANT>
<vt>VT_I4</vt>
<lVal>0</lVal>
</VARIANT>
</vValue>
</DBPROP>
</rgProperties>
</DBPROPSET>
</prgPropertySets>
</outputs>
How to repeat:
run this from a SQL Server with a linked server called "MYSQL".
SELECT * FROM OPENQUERY(MYSQL, '<tab>SELECT 1')
SELECT * FROM OPENQUERY(MYSQL, '
SELECT 1')
Description: I'm using SQL Server 2005 SP4 64bit on a Windows 2003 R2 x64 server. MySQL Connector 5.2.5 Unicode is installed and an ODBC DSN is set up, and the test works OK. (The MySQL server is on a different machine) The MySQL server version is 5.0.27 I have a "linked server" set up in SQL Server pointing to MySQL using the MSDASQL OLEDB provider for ODBC drivers. Simple queries work fine using the SQL Server "linked server" syntax e.g.: SELECT * FROM OPENQUERY(MYSQL, 'SELECT 1') But it fails if the query string has a leading tab or line break: SELECT * FROM OPENQUERY(MYSQL, '<tab>SELECT 1') SELECT * FROM OPENQUERY(MYSQL, ' SELECT 1') This works fine with non-MySQL linked servers, and it works fine with older MySQL drivers. (e.g. 5.1.12) Using tabs or line breaks inside the query or at the end aren't a problem. Using the "SQL Profiler" that comes with SQL Server, the error is: OLE DB provider "MSDASQL" for linked server "MYSQL" returned an incorrect value for "DBPROP_CONCATNULLBEHAVIOR", which should be "either DBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULL". There is an "OLEDB Call Event" just before this error with some parts that look relevant: <hresult>265946</hresult> <outputs> <pcPropertySets>4</pcPropertySets> <prgPropertySets> ... <DBPROPSET> <cProperties>17</cProperties> <guidPropertySet>DBPROPSET_DATASOURCEINFO</guidPropertySet> <rgProperties> ... <DBPROP> <dwPropertyID>DBPROP_CONCATNULLBEHAVIOR</dwPropertyID> <dwOptions>0</dwOptions> <dwStatus>0</dwStatus> <colid>DB_NULLID</colid> <vValue> <VARIANT> <vt>VT_I4</vt> <lVal>0</lVal> </VARIANT> </vValue> </DBPROP> </rgProperties> </DBPROPSET> </prgPropertySets> </outputs> How to repeat: run this from a SQL Server with a linked server called "MYSQL". SELECT * FROM OPENQUERY(MYSQL, '<tab>SELECT 1') SELECT * FROM OPENQUERY(MYSQL, ' SELECT 1')