Paul

You're quite right - Books Online (2008 R2) says: "The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL Server automatically set ANSI_NULLS to ON when connecting. This setting can be configured in ODBC data sources, in ODBC connection attributes, or in OLE DB connection properties that are set in the application before connecting to an instance of SQL Server. The default for SET ANSI_NULLS is OFF." When I connect through SSMS, ANSI_NULLS is set to ON - this must be the OLE DB Provider setting this for me.

Interestingly, the same topic in Books Online says that in a future version of SQL Server, it will not be possible to set ANSI_NULLS to OFF.

John