We have 3 coldfusion servers. All of them have the same settings, as far as I can tell. The sql server connected has a linked oracle server, and when using OPENQUERY(), it sometimes throws this error on one of the servers. I think maybe it has to do with encoding, but nothing seems to be set incorrectly.
Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection.
This issue only occurs on 1 of the servers, and only sometimes. It has never thrown the error on the other two servers.
Here's what I've tried so far, without success. I also tried deleting and rebuilding the linked server connection to oracle.
Top of Page:
<cfprocessingdirective pageEncoding="UTF-8" />
Before queries:
SET ANSI_WARNINGS ON;
SET ANSI_NULLS ON;
While I'm not positive what resolved the issue (we tried several solutions in the same day), I believe it was one of two things:
We had previously deleted and added linked server instance with ansi_nulls on as part of the query, but it didn't work. However, the linked server was recently rebooted, so possibly it required relinking then rebooting.
We also found a SQL server agent job that was running with ANSI_NULLS off as part of the command. This part of the job was removed. I would find it quite odd if this was the issue, as I wouldn't expect one query shutting it off to affect another query that explicitly had it on, but maybe it will help someone else who runs into the same issue troubleshoot.