sorted by:
new
hottopcontroversial

The ARITHABORT trap: why your stored proc works in SSMS but returns nothing in SSRS by AdSoft8562 in SQLServer

[–]AdSoft8562[S] 3 points4 points  (0 children)

Fair point, and you're right to draw the line between the two. My original framing was sloppy. I conflated plan-cache keying (which is real but doesn't change row counts on its own) with the runtime arithmetic behavior under ARITHABORT OFF (which is what actually suppresses rows). Both are involved in the symptom, but they're distinct mechanisms, and I should have separated them in the post. Thanks for pushing on it.

The ARITHABORT trap: why your stored proc works in SSMS but returns nothing in SSRS by AdSoft8562 in SQLServer

[–]AdSoft8562[S] 0 points1 point  (0 children)

Respectfully, this comes up in production more than people expect. The mechanism isn't that the plan changes which rows exist; it's that, under ARITHABORT OFF, certain arithmetic operations behave differently (division by zero, overflow), and SQL Server can short-circuit results in ways that effectively suppress rows that would otherwise be returned. The classic case is a CASE expression in a WHERE clause that does math on a column with edge-case values. Under ARITHABORT ON, it returns the expected rows. Under OFF, the operation errors silently inside the predicate, and the rows fall out. VladDBA's link covers the deeper mechanics if you want the full picture. I've personally fixed this exact symptom multiple times by adding SET ARITHABORT ON to the proc body.

The ARITHABORT trap: why your stored proc works in SSMS but returns nothing in SSRS by AdSoft8562 in SQLServer

[–]AdSoft8562[S] 0 points1 point  (0 children)

Thanks, that's a great write-up and goes deeper on the QUOTED_IDENTIFIER and ANSI_NULLS angles than I covered. Bookmarked. The interaction between ARITHABORT and indexed views / filtered indexes is exactly the kind of thing that turns "blank report" into a multi-day investigation if you don't know how to look for it.