SETTING ANSI NULLS and WARNINGS to ON

Version 1
    This document was generated from CDN thread

    Created by: Ruzel Jamilon on 10-09-2013 11:47:28 AM
    Having the error below (complete error message at the end of the entire post) when trying to run a report.


    Error information:
    com.cisco.ccbu.cuic.businesslogic.datasource.CuicDbException: DbException: CuicDataSourceServiceManagerImpl.getDataSet() { Nested SQLException; SQLState: S0001 Vendor code: 7405 Message: 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.

    I had this erros a while ago when trying to create Report Definition. I found a solution on another post that suggests resseting the datasource by editing the datasource, save, edit to point to the correct DB and then I created the report defintion with SET ANSI values to ON and it let me create the report defintion without errors. 

    But when I was testing the Report, I encountered the same error again.

    Any suggestions on how to resolve this?


    Error information:
    com.cisco.ccbu.cuic.businesslogic.datasource.CuicDbException: DbException: CuicDataSourceServiceManagerImpl.getDataSet() { Nested SQLException; SQLState: S0001 Vendor code: 7405 Message: 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. } at com.cisco.ccbu.cuic.businesslogic.datasource.CuicDataSourceServiceManagerImpl.getDataSetBuilder(CuicDataSourceServiceManagerImpl.java:954) at com.cisco.ccbu.cuic.businesslogic.engine.CuicDataProcessingCenterWorkerRunnable.runReport(CuicDataProcessingCenterWorkerRunnable.java:361) at com.cisco.ccbu.cuic.businesslogic.engine.CuicDataProcessingCenterWorkerRunnable.run(CuicDataProcessingCenterWorkerRunnable.java:160) at com.cisco.ccbu.infra.threads.InstrumentedRunnable.run(InstrumentedRunnable.java:88) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:619) at com.cisco.ccbu.infra.threads.ThreadPoolThread.run(ThreadPoolThread.java:164) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 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. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:390) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:283) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at com.cisco.ccbu.cuic.businesslogic.datasource.CuicDataSourceServiceManagerImpl.executeAndLoadDataset(CuicDataSourceServiceManagerImpl.java:1063) at com.cisco.ccbu.cuic.businesslogic.datasource.CuicDataSourceServiceManagerImpl.getDataSetBuilder(CuicDataSourceServiceManagerImpl.java:940) ... 7 more

    Subject: RE: SETTING ANSI NULLS and WARNINGS to ON
    Replied by: Edwin Andrews on 10-09-2013 12:42:51 PM
    Encountered this only this morning!!! ?

    Sounds like a report is using data from one or more SQL Server "linked servers" .  The linked server connections must have the
    SET ANSI_NULLS ON
    and
    SET ANSI_WARNINGS ON
    ...options to be set for the connection. On the linked server itself. Not within the report, job, or stored procedure.

    Making this change may require removing and recreating the linked server.

    If the report is using data from only one server, this shouldn't occur. If it is only using AWDB and HDS it also should not occur.

    SQL Copy paste might help.

    Subject: RE: SETTING ANSI NULLS and WARNINGS to ON
    Replied by: Ruzel Jamilon on 10-09-2013 02:27:54 PM
    Hi Edwin - were you asking me if I encountered this only this morning? OR were you telling me you also encountered the same this morning?

    Yes, the query is accessing data from linked servers (fixed the login issue on the linked server that I had before and now got this).

    The weird thing is, this worked fine (on report definition atleast after I reset the datasource and run the query with ANSI options to ON, and then got the error when trying to run a report, and now I cant get to build a new report definition with the same query.

    I will look on setting the ANSI options to ON on the linked server. Not sure how to do that, but will update in here once I get something done.

    Thanks!