Why should ANSI_NULLS and ANSI_WARNINGS be set to include related server requests in PHP?

Question

I have a related server statement using OpenQuery:

SELECT mycol1, mycol2, mycol3 FROM OPENQUERY(MYLINKEDSERVER, 'SELECT * FROM mysqldb.table') 

The above works without SSM. When I use the MySQL PHP frameworks to run this query in a web application (using the same SQL Server credentials), I need to insert the following 2 statements:

SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

I read the definitions for ANSI_NULLS and ANSI_WARNINGS, but I don’t quite understand why they need to be configured in order for the request to work in PHP.

Does anyone know the reason?

My linked server is an instance of MySQL.

Answer 1

Someone was digging. In short, this means that if you are executing a query with a linked server that is not a SQL Server field, then most likely you will need to enable these two functions to standardize the way that certain comparison operators handle different databases.

Presumably, in future versions of SQL Server, SET_NULLS will be set to the default by default without the option to change it.

Answer 2

In PHP:

• First, the SET command is issued: "SET ANSI_NULLS ON; SET ANSI_WARNINGS ON;"
• Then execute other commands, such as SELECT * FROM clients ....;

If you combine them all in one script:

  SET ANSI_NULLS ON; SET ANSI_WARNINGS ON; SELECT * FROM customers ... 

an error is issued. Works for me combining two MS SQL servers.

Answer 3

This is almost a duplicate of this question . Please note that in this case the error message was:

For heterogeneous queries, the ANSI_NULLS and ANSI_WARNINGS parameters for connection are required. This provides consistent query semantics. Enable these options and then reissue your request

And the documentation really says that:

SET ANSI_NULLS must be set to ON to perform distributed queries

It has the same comment for SET ANSI_WARNINGS. I suspect that, as Alvaro suggested, your PHP code is hiding the error, and there is a very good chance that it is mentioned above. This question on the DBA website goes much further.