SQL Server Linked Server Queries w/ ANSI_WARNINGS OFF

I am trying to use the linked server feature in SQL Server (2014) to run a cross server query (calculation) from a stored procedure.

The stored procedure was originally designed to run a local query (calculation) using dynamic T-SQL (via sp_executeSQL). The expression is generated by our application and can be any arithmetic expression.

A simple example (A / B) query is implemented like:

(dynamic sql)
SELECT f1.Value / f2.Value
FROM dbo.DimDate d
INNER JOIN
dbo.vAverageData f1 ON f1.ParentID=x and f1.TimeStamp = d.TimeStamp
INNER JOIN
dbo.vAverageData f2 ON f2.ParentID=y f2.TimeStamp = d.TimeStamp
WHERE d.TimeStamp BETWEEN @StartDateTime AND @EndDateTime

Now, it gets interesting in that none of the calculation expressions check for or handle conditions such as divide by zero. Instead, the original developer decided to issue two statements before any of the dynamic T-SQL is executed:

SET ANSI_WARNINGS OFF
SET ARITHABORT OFF

This has worked well for years until one day, someone asked us if we could perform cross server queries. Naturally, the first option that popped into my head was to implement (1) a linked server connection between the two servers and (2) modify the dynamic T-SQL generation code (in our application) to prefix each object with the linked server name and database name.

The example above would be transformed into something like:

(dynamic sql)
SELECT f1.Value / f2.Value
FROM dbo.DimDate d
INNER JOIN
dbo.vAverageData f1 ON f1.ParentID=x and f1.TimeStamp = d.TimeStamp
INNER JOIN
*<LinkedServer>.<RemoteDatabase>*.dbo.vAverageData f2 ON f2.ParentID=y f2.TimeStamp = d.TimeStamp
WHERE d.TimeStamp BETWEEN @StartDateTime AND @EndDateTime

The first time, I tried to run one of these cross server queries, I received the infamous:

"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."

Obviously, the easiest thing would be to remove the SET calls mentioned above. However, in our case, they do serve a purpose and prevent the query/transaction from aborting in case of divide by zero, overflows, etc.

At this point (without a major rewrite), assume we have to have those SET calls in place...

Is there anything I can do to make this work without removing the SET calls? Are there any settings on the remote server/database that can be set? What about on the linked server object?

I haven't tried it, but there are database properties for ANSI Warnings Enabled, etc. Would this fix it if it's set consistently on both databases? Is that a bad/dangerous practice?

Both servers are the exact same version of SQL Server (2014) and both databases contain our code, i.e. we can change anything we want.

Whatever the type of linked server it is, there is no way around it per BOL found here.

A connection must have the ANSI_NULLS and ANSI_WARNINGS options set ON before the connection can execute distributed queries. For more information, see SET ANSI_DEFAULTS (Transact-SQL)

One way to go about it is to modify the query by adding statements like below. By making a local copy of the remote table, you can minimize the use of the options you don't want, and minimize the changes elsewhere.

SET ANSI_WARNINGS ON;

SELECT Columns_Used_For_Query INTO #Temporary_Table 
FROM Remote_Table
WHERE Search_Condition;

SET ANSI_WARNINGS OFF;

--Do other stuff 

This might not the best solution in terms of performance, especially when a proper index is crucial. But that's yours to test.

On the side note, if you still get the same error message, then that most likely means the stored procedure was created with ANSI_NULLS OFF, in which case you'd need to turn the option on then ALTER. Stored Procedure remembers ANSI_NULLS and QUOTED_IDENTIFIER setting at the time of creation and alteration.