Menu
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.
516
ANSI_NULLS should be set to ON for executing distributed queries. ANSI_NULLS must also be ON when you are creating or changing indexes on computed columns or indexed views. If SET ANSI_NULLS is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.
ANSI_NULLS define the comparison rule for NULL values in SQL Server. When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. Example.
Heterogeneous just means different. So a heterogeneous query is in one database and is querying a DIFFERENT database.
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
194
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
