Problem in cyrstal report

Question

When I try to view the Report this Error message appears,

---

Crystal Report Viewer

Failed to open a rowset. Details: ADO Error Code: 0x Source: Microsoft OLE DB Provider for SQL Server Description: Warning: Null value is eliminated by an aggregate or other SET operation. SQL State: 01003

Native Error:

OK

Note:I connect Direct to sotred procedure.

Answer 1

Check how to inside procedure used aggregate functions.

You can turn of this message by using

SET ANSI_WARNINGS ON;

However I do not recommend do this.

Example how to get warning "Null value is eliminated by an aggregate or other SET operation"

  CREATE TABLE T1 (
     a INT, 
     b INT NULL, 
     c VARCHAR(20)
  );
  GO

  SET NOCOUNT ON

  INSERT INTO T1 
  VALUES (1, NULL, '');
  INSERT INTO T1 
  VALUES (1, 0, '');
  INSERT INTO T1 
  VALUES (2, 1, '');
  INSERT INTO T1 
  VALUES (2, 2, '');

  SET NOCOUNT OFF;
  GO

  PRINT '**** Setting ANSI_WARNINGS ON';
  GO

  SET ANSI_WARNINGS ON;
  GO

  PRINT 'Testing NULL in aggregate';
  GO
  SELECT a, SUM(b) 
  FROM T1 
  GROUP BY a;
  GO