Using CASE for quieries via a Linked Server (MySQL)

Question

Using CASE for quieries via a Linked Server (MySQL)

Kazmerelda

SSCarpal Tunnel

Points: 4667
More actions
July 12, 2012 at 3:54 am

#392727

Hi there,
I am hoping that you can help me with a problem I am having using CASE in a query via a linked server.
I have set up a linked server to a MySQL database and so far most queries are working. However I am having some issues with using CASE, there are some items that I need to change the value for in the report (to make it more meaningful) and this seems to throw up errors.
So far I have this query:
select *
from openquery (MYSQLVS,' SELECT date
, seconds
, type
, sitechecksId
, CASE siteId WHEN 91 THEN 'UK' END siteId
FROM monitorsearchresponseTime
WHERE siteId IN (91,92,93,94,95,96,97,98)')
It throws up the error:
OLE DB provider "MSDASQL" for linked server "MYSQLVS" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.1.39-community-log]Table 'v.monitorsearchresponseTime' doesn't exist".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "MYSQLVS".
Can anyone assist with how the CASE statement works in cases (pardon the pun) like this?
Many thanks in advance....

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

rhythmk SSCertifiable Points: 7271 More actions · Answer 1

karen.blake (7/12/2012)
Hi there,
I am hoping that you can help me with a problem I am having using CASE in a query via a linked server.
I have set up a linked server to a MySQL database and so far most queries are working. However I am having some issues with using CASE, there are some items that I need to change the value for in the report (to make it more meaningful) and this seems to throw up errors.
So far I have this query:
select *
from openquery (MYSQLVS,' SELECT date
, seconds
, type
, sitechecksId
, CASE siteId WHEN 91 THEN 'UK' END siteId
FROM monitorsearchresponseTime
WHERE siteId IN (91,92,93,94,95,96,97,98)')
It throws up the error:
OLE DB provider "MSDASQL" for linked server "MYSQLVS" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.1.39-community-log]Table 'v.monitorsearchresponseTime' doesn't exist".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "MYSQLVS".
Can anyone assist with how the CASE statement works in cases (pardon the pun) like this?
Many thanks in advance....

Few things are here.

1. This forum is specific to SQL Server.

2. From the error it seems the problem is not with CASE statement.

3. Don't know about MySql but in SQL you need to specify 'ELSE' clause in CASE statement.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

The constant 'UK' in the command string will need extra quotes:

CASE siteId WHEN 91 THEN '''UK''' END

There are two flavours of CASE statement, simple and searched. This is a searched case. Try simple case:

CASE WHEN siteId = 91 THEN '''UK''' END

The column alias may also give you problems (I'm not familiar with MySQL syntax), try at the other end of the statement:

SiteID = CASE WHEN siteId = 91 THEN '''UK''' END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Kazmerelda SSCarpal Tunnel Points: 4667 More actions · Answer 3

Thank you both for your replies :).

rhythmk ironically I did post first on a MySQL forum but got told it might be more SQL Server as it is a linked server. I saw a post on here about linked servers to MySQL so thought I would take a chance. I will try else, if only it was as easy as SQL Server :).

Thanks ChrisM@Work I will give those a go....

kiril.lazarov.77 Hall of Fame Points: 3742 More actions · Answer 4

rhythmk (7/12/2012)
karen.blake (7/12/2012)
Hi there,
I am hoping that you can help me with a problem I am having using CASE in a query via a linked server.
I have set up a linked server to a MySQL database and so far most queries are working. However I am having some issues with using CASE, there are some items that I need to change the value for in the report (to make it more meaningful) and this seems to throw up errors.
So far I have this query:
select *
from openquery (MYSQLVS,' SELECT date
, seconds
, type
, sitechecksId
, CASE siteId WHEN 91 THEN 'UK' END siteId
FROM monitorsearchresponseTime
WHERE siteId IN (91,92,93,94,95,96,97,98)')
It throws up the error:
OLE DB provider "MSDASQL" for linked server "MYSQLVS" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.1.39-community-log]Table 'v.monitorsearchresponseTime' doesn't exist".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "MYSQLVS".
Can anyone assist with how the CASE statement works in cases (pardon the pun) like this?
Many thanks in advance....
Few things are here.
1. This forum is specific to SQL Server.
2. From the error it seems the problem is not with CASE statement.
3. Don't know about MySql but in SQL you need to specify 'ELSE' clause in CASE statement.

No, you don't.

Using CASE for quieries via a Linked Server (MySQL)

Cookies on SQLServerCentral