SETTING ANSI NULLS and WARNINGS to ON

[cdnadmin]

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!

[anna.esposito]

Hi, i have the same problems of Ruzel about ANSI_NULLS and ANSI_WARNINGS when trying to create a Report definition or trying to run a report.
I tried to set the ANSI options to ON on the linked server definition in Microsoft SQL Server Management Studio but i don't know how to do that.
Any suggestions on where i set that options?

Thanks!

[jasondurie@moonjet.com]

I  (Actually, someone I work with showed me)  found a solution to this.  If you are seeing this error when first creating the report definition, the trick is to create a "fake" simple query that works to generate your parameters and fields.  Then Save the definition.  Then go back in and paste in your original query.  This worked for me using a linked server that did NOT have ANSI_WARNINGS or ANSI_NULLS defined at the server level.

Example:  FAKE QUERY

Query Type: Anonymous block

Database:  UCCE_Historical

Query:

SET ANSI_WARNINGS ON;

SET ANSI_NULLS ON;

SELECT 2019 AS Year, 2 AS Month, 8 AS Week, 49 AS DOY, 2 AS DOW, '02/18/2019' AS DATE, 7140 AS IVRCalls, 10 AS SCRCalls, 10 AS SCRRecordings, 0.12345 AS PerRecorded;

Then Create Parameters (should be none)

Then Create Fields (should succeed at creating the parameters for the fields above.  Note: you should generate a fake query that creates the field types you want, for example, for DECIMAL 10 AS Calls, for a TEXT '10' AS Calls, for a DATETIME getdate() AS EndDate, etc)

Save Report Definition

Then go back in and paste the original query you were trying to create.. my example is below

SET ANSI_WARNINGS ON;

SET ANSI_NULLS ON;

-- ORIGINAL FAKE QUERY COMMENTED OUT   SELECT 2019 AS Year, 2 AS Month, 8 AS Week, 49 AS DOY, 2 AS DOW, '02/18/2019' AS DATE, 7140 AS IVRCalls, 10 AS SCRCalls, 10 AS SCRRecordings, 0.12345 AS PerRecorded;

-- BEGIN ACTUAL QUERY

declare @StartDate DateTime, @EndDate DateTime

set @StartDate=:start_date

set @EndDate=:end_date

;WITH CCA_IVRDATA(Year, Month, Week, DOY, DOW, DATE, IVRCalls) AS

(SELECT Year= Datepart(yy, DateTime),

                   Month= Datepart(mm, DateTime),

                   Week=Datepart(ww, DateTime),

                   DOY=Datepart(dy, DateTime),

                   DOW=Datepart(dw, DateTime),

                   DATE = CONVERT(char(10),DateTime, 101),

IVRCalls=COUNT(*)

FROM Route_Call_Detail

WHERE DateTime BETWEEN @StartDate AND @EndDate

GROUP BY Datepart(yy, DateTime),

                   Datepart(mm, DateTime),

                   Datepart(ww, DateTime),

                   Datepart(dy, DateTime),

                   Datepart(dw, DateTime),

                   CONVERT(char(10),DateTime, 101)),

etc... the rest of your query...