Anonymous Block/Stored Proc with Queries with Linked Server error

Version 1
    This document was generated from CDN thread

    Created by: Ruzel Jamilon on 11-09-2013 10:34:11 AM
    Yesterday I posted an issue I am having with a DB Query with a linked server and getting the : "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection."

    I have tried a suggestion from a post to reset datasource - and it worked AT FIRST for me. It let me create my report definition. And then when I tried running the report, I get the ANSI errors, and then I get the error again everytime I tried creating a new definition.

    I also tried the same with anonymous block and stored procedures, and still having the same error.

    I have the following at the start of my query:
    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON

    I checked on my DB and Linked DB that the 2 options are set to ON byusing this script that I found online:

    DECLARE @options INT
    SELECT @options = @@OPTIONS

    PRINT @options
    IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK'
    IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS'
    IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT'
    IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS'
    IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING'
    IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS'
    IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT'
    IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'
    IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER'
    IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT'
    IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON'
    IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF'
    IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL'
    IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT'
    IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

    I even tried resetting my datasource connection to the linked server, running query in report defintion to set ANSI options to ON but I still get the same error when I create my report definition.

    The table I am trying to access on the linked server are the wrapup codes.

    The query and stored procedure run on SQL Studio without errors - which is why I was expecting Stored Proc to work.

    Any tip on how to take this forward? Thanks!

    Subject: RE: Anonymous Block/Stored Proc with Queries with Linked Server error
    Replied by: Ruzel Jamilon on 12-09-2013 10:52:39 AM
    So I finally got it working using Anonymous Block yesterday - created the report definition, and actually created the report succesfully. But getting the ANSI erros today again.

    Cisco CUIC peeps, any tips?