I recently got my first taste of the error "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection." which I hadn't come across. Nearly every resource I found on this site and elsewhere dealt with this occurring in a stored procedure.
My specific scenario is a Vendor supplied app and DB, I can change the DB but not the app. I created a view on the DB to another server (via a linked server) but when I attempted to use this in the app I got the aforementioned error. I used SQL Profiler to prove the app opens its connections with ANSI NULLs and WARNINGS OFF. I used SSMS to prove the link works with the default values there. But unlike an SP I can not set ANSI WARNINGS on a view.
I set ANSI WARNINGS on the DB as a test, the app now worked, but I couldn't afford a full regression test of the app so this solution does not work. I also tried many different ways of implementing the linked server including created an ODBC connection on the server and then using that. But it's like the app is explicitly setting ANSI WARNINGS to off.
Not sure if matters but the source server is SQL2000 and the remote server was SQL2008R2.