Solved

Cannot get column information from ole DB provider for Linked server

Posted on 2014-01-24
8
4,301 Views
Last Modified: 2014-01-31
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 + '''' + ')'

EXEC(@finalQuery)

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,
        TABLE_NAME,
        COLUMN_NAME
FROM    INFORMATION_SCHEMA.COLUMNS
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.
0
Comment
Question by:LenTompkins
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 27

Accepted Solution

by:
Zberteoc earned 250 total points
ID: 39808146
Don't forget that you are connected to a MySql server so you there is no dbo schema there and you should not use [ ] to wrap objects. You should use:

SELECT PageID FROM clubUsage

Basically whatever query you put in the @sqlQuery variable is executed on the MySql server so it should follow the MySql syntax, which is slightly different than SQL server.
0
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 250 total points
ID: 39808231
Make it simple:

SELECT PageID FROM wcny_Prod...clubUsage

No need in openquery and in exec.
0
 

Author Comment

by:LenTompkins
ID: 39809443
I will try these ideas on Monday when I get back into the office.
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Author Closing Comment

by:LenTompkins
ID: 39811972
Once I took the database name out of the select, it worked.  Thanks very much.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39812207
Is no big deal but you should not give points to an answer that practically repeated an earlier one.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39812263
> answer that practically repeated an earlier one

It seems a little clarification is in order.

Assuming Zberteoc meant my comment, not only it did not "practically repeat" his, it has nothing in common with it.

When you use openquery, the 2nd parameter gets passed to the remote database server as is. There it gets interpreted by that server, in this case Mysql. This means that you have to write the query using the syntax of the remote server rather than SQL Server - in this case, the syntax of mysql, which is different from SQL Server.

When you use form select * from server.database.owner. table, which is what I suggested, you write the query in SQL Server syntax. The translation to the syntax of the remote database server is then performed by the ODBC driver, so you don't even have to be familiar with it.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39812350
However that was not the issue and your explanation only comes after the points awarded.. Beside that using the OPENROWSET makes it more efficient because the query is executed at the source, results can be filtered so that you only get back what you need, not to mention the advantage of index usage at the source.

Regardless, the question had some code that the asker used and needed and my answer was in regards to that. Even if your answer is correct it came later and did not give any extra information so at least the points should had been distributed differently.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39812438
>  your explanation only comes after the points awarded

which perhaps means that the question author recognized the value of it, and in fact did not even need the explanation. The clarification was for you, in case you did not realize it.

> using the OPENROWSET makes it more efficient because the query is executed at the source

The query, certainly, is executed at the source anyway. Indeed, having it translated into the native syntax saves 1-2 ms of processor time required for the ODBC driver to do the same.

> Even if your answer is correct it came later

The points usually depend on the perceived (by the question author) value of the comment, rather than the speed.

> and did not give any extra information
perhaps the author had little different view...

> so at least the points should had been distributed differently.
This is up to the author. I actually requested attention, maybe we will hear from the mod, if it's really a good idea to put a pressure on the author not to award points to other experts.
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question