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.
Best Answer
As I said in the question a lot of resources dealt with this issue but not when it pertained to a view. The solution I found was to
1- create a 2nd DB on my source server.
2- create a view on the 2nd db using the linked server to the remote DB
3- alter the view on the 1st DB to use the view on the 2nd DB
4- alter the 2nd DB to use ANSI WARNINGS ON at a DB level