Linked Server issue due to syntax with MySQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Linked Server issue due to syntax with MySQL

Author

Topic

sakarus7501
Starting Member

1 Post

Posted - 2004-07-23 : 00:36:53

Simple problem.

SELECT column1, column2 FROM
OPENQUERY (mysqldb,'SELECT column1, column2 FROM table1 where id = 1')

Works great!

Problem

I need the where to be from a varchar field ie:

SELECT column1, column2 FROM
OPENQUERY (mysqldb,'SELECT column1, column2 FROM table1 where columun = sample_record')

IE in mysql syntax the sample record would be enclosed by 'sample record'

Any ideas?

Thanks,
Mark Workman

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 00:53:39

Can you explain a bit more about how the WHERE is a VARCHAR? (I'm probably being thick, but I don't Get It from your example)

Kristen

Sitka
Aged Yak Warrior

571 Posts

Posted - 2004-07-23 : 10:59:47

EDIT try this first

SELECT column1, column2 FROM
OPENQUERY (mysqldb,'SELECT column1, column2 FROM table1 where columun = ''sample_record''')

EDIT 2.... but ya never know exactly, just keep trying.

I think this is just simple escaping OPENQUERY CAN mess you up though

If in doubt work backwards by separating the <'> s from the actual string.

PRINT ('SELECT column1, column2 FROM table1 where columun = '+''''+'jones'+'''')

If it still fails try some other combos.

I'l try and find some OPENQUERY strings that are working in code here. NOt on MYSQL though.

It is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-07-23 : 11:24:44

try putting the where clause after the openquery statement

something like:

SELECT column1, column2
FROM OPENQUERY (blah blah blah)
WHERE columun = 'Sample Record'

-ec

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 12:17:52

That's gonna pull a lot of data locally and then do the compare, isn't it?

Kristen

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-07-23 : 14:43:45

Try sitka's approach with the double single quotes. That worked in my test.

OPENQUERY (mysqldb,'SELECT column1, column2 FROM table1 where columun = ''sample_record''')

-ec

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-07-23 : 14:46:23

quote:
Originally posted by Kristen

That's gonna pull a lot of data locally and then do the compare, isn't it?

yes, that is a potential problem with this approach. Sitka's solution works also, with the WHERE clause in the openquery.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources