The Problem occurs when an aggregate function(max,sum,avg..) exists on null values. Trying to get rid of the null values may be the solution but in certain cases..you may need the extra data relying on the null values.
So whats the solution?
Append set ANSI_WARNINGS OFF on the beginning of the sql statement.
When using the same in SSIS packages the Packages may fail.The Output Window may indicate that there is already a OLEDB destination exported which in this case is the error message.
An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x00040EDA Description: “Warning: Null value is eliminated by an aggregate or other SET operation.”
So use set ANSI_WARNINGS OFF
Great ! Thanks. I was using MAX on a Date field and it had a few null values.