I am trying to select data using SQLServer 2008 from MYSQL on another server. I created a linked server and the test connection is successful, but when I run the following statement, I get an error.
DECLARE @variable VARCHAR(20)
DECLARE @sqlQuery VARCHAR(8000)
DECLARE @finalQuery VARCHAR(8000)
SET @sqlQuery = 'SELECT PageID FROM wcny_Prod.dbo.[clubUsage] '
SET @finalQuery = 'SELECT * FROM OPENQUERY(wcny_Prod,' + '''' + @sqlQuery + '''' + ')'
OLE DB provider "MSDASQL" for linked server "wcny_Prod" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.5.28-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.[clubUsage]' at line 1".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "wcny_Prod".
I have also run the query where I didn't add the database name and schema and received the same results.
I also ran the following select to obtain schema information and it works, but no table information is returned.
EXEC ('SELECT TABLE_SCHEMA,
WHERE TABLE_NAME = ''[clubUsage]''
AND TABLE_SCHEMA = ''SchemaName'' ;') AT [wcny_Prod] ;
What kind of additional permissions does the user name need that was used to create the ODBC connection? I gather this is a connection problem. Any input would be greatly appreciated.