Microsoft SQL Linked MySQL Server - Can't query from PHP

Question

I've setup a linked MySQL server on our Microsoft SQL Server. I can run this query successfully in MSSQL Management Studio...

SELECT * FROM OPENQUERY(MYSQL, 'SELECT * FROM countries')

When I run this from a website in PHP though, I get this error...

Message: Failed to query SQL statement. Reason: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

Any ideas what I can do to get this to work?

score 2 · Accepted Answer · 2011-12-10 00:10:22Z

2

the workaround is to shift the “SET ANSI_WARNINGS OFF” statement just below the linked server call. And to be on the safer side apply the “SET ANSI_WARNINGS ON” statement at the end of the sp/query.

update

SET ANSI_WARNINGS ON

SET ANSI_NULLS ON

SELECT * FROM OPENQUERY(MYSQL, 'SELECT * FROM countries')

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/1d501b57-fc58-4fbe-9bec-6c38ad158a62

edited Dec 10, 2011 at 0:10

answered Dec 9, 2011 at 23:07

user240141

Sign up to request clarification or add additional context in comments.

4 Comments

Chad Over a year ago

Could you provide an example?

Chad Over a year ago

I'm not creating or using any stored procedure though. I'm just trying to run a simple select query using OPENQUERY().

Chad Over a year ago

I tried your updated answer and unfortunately it did not work. Any other ideas?

Chad Over a year ago

Ok I got it to work with the following PHP code. I'm using the Kohana LEAP ORM here, but the main point to getting this to work is to run these as separate queries in the same connection.

$sql = "SELECT * FROM OPENQUERY(MYSQL, 'SELECT * FROM states') part";     $connection = DB_Connection_Pool::instance()->get_connection(new DB_DataSource('production_tm'));     $connection->execute('SET ANSI_WARNINGS ON');     $connection->execute('SET ANSI_NULLS ON');     $res = $connection->query($sql);

Collectives™ on Stack Overflow

Microsoft SQL Linked MySQL Server - Can't query from PHP

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related