6

I have a linked server statement using OpenQuery:

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

The above works with no qualms in SSMS. When I use PHP MySQL wrappers to run this query on a web application (using same SQL Server credentials), I have to insert the following 2 statements:

SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

I've read through the definitions for ANSI_NULLS and ANSI_WARNINGS, but don't quite understand why they have to be set to on for the query to function in PHP.

Does anyone know the reason?

My linked server is a MySQL instance.

Lloyd Banks
  • 35,740
  • 58
  • 156
  • 248
  • 1
    If you don't add those two lines, what happens? – Abe Miessler May 29 '13 at 15:33
  • @AbeMiessler Page doesn't load, but no errors are thrown – Lloyd Banks May 29 '13 at 15:48
  • 2
    No errors? Really? Is it possible you are just eating the exception some where? – Abe Miessler May 29 '13 at 15:55
  • Sorry if I'm stating something obvious but a blank page often means that your script is throwing an error but you haven't configured PHP to display error messages. You need to make sure you can see error messages before you go further; it's impossible to code without them. Here's a [brief explanation](http://stackoverflow.com/a/5680885/13508). – Álvaro González May 29 '13 at 16:16

3 Answers3

5

Did some digging. In short, it means that if you're running a query against a Linked Server that isn't a SQL Server box, then more than likely you'll need to turn these two features on to standardize the way certain comparison operators are handled across different databases.

Supposedly, future versions of SQL Server will have SET_NULLS set to ON by default with no option to change it

Lloyd Banks
  • 35,740
  • 58
  • 156
  • 248
  • 1
    From [documentation](https://msdn.microsoft.com/en-us/library/ms188048.aspx), *In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.* this applies to SQL Server 2008 to 2015, and no longer require in SQL Server 2016. – Raptor Mar 21 '17 at 06:09
1

In PHP:

  1. First isssue the SET command „SET ANSI_NULLS ON; SET ANSI_WARNINGS ON;“
  2. Then execute other commands like SELECT * FROM customers ....;

If you combine them all in one script: 

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

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

Ahmad
  • 12,336
  • 6
  • 48
  • 88
Jesenko Sokoljak
  • 19
  • 4
0

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

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

And the documentation does in fact say that:

SET ANSI_NULLS should be set to ON for executing distributed queries

It has the same comment for SET ANSI_WARNINGS. I suspect that as Álvaro suggested, your PHP code is hiding an error and there's a very good chance that it's the one mentioned above. This question on the DBA site goes into much more detail.

Community
  • 1
  • 1
Pondlife
  • 15,992
  • 6
  • 37
  • 51