Anonymous Block/Stored Proc with Queries with Linked Server error

Document created by cdnadmin on Jan 25, 2014
Version 1Show Document
  • View in full screen mode
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?

Attachments

    Outcomes