SSRS returning different results than the stored procedure

Question

I have a stored procedure that I can call from Management Studio and it returns 56 rows consistently. However, when I execute the query under from inside of ssrs 2008, I get back 61 rows. The stored procedure uses cursors and has some print statement inside of it.

Thanks,

In my case, yes (I'm having the same issue, but in reverse - not enough records when run from SSRS), but am supplying the same params in both places. — B. Clay Shannon-B. Crow Raven, Jun 28 '16 at 17:36

score 3 · Accepted Answer · answered Sep 20 '12 at 14:45

3

I had a similar problem when I had an a stored procedure return warnings messages. It caused problems returning inconsistent results just in my SSRS dataset/report. I set ansi-warnings to off and my problem went away. You may want to comment out your print statements and try that.

Thanks, Steve

answered Sep 20 '12 at 14:45

Steve Goedker

353
1
2
7

Where do you set asni warnings off? – B. Clay Shannon-B. Crow Raven Jun 28 '16 at 17:43
ansi, that is (not ansi, or antsy) – B. Clay Shannon-B. Crow Raven Jun 28 '16 at 17:50
You can do it in the command window "ALTER DATABASE AdventureWorks2012 SET ANSI_NULL_DEFAULT OFF; GO" or you could right click on database->properties -> options Ansi NULL Default. Set it to false. – mikemurf22 Jun 29 '16 at 13:00

score 0 · Answer 2 · answered Sep 20 '12 at 14:47

I would do two things.

Check that the SSRS report is not using cached data - it often does.
If that doesn't help... within your SP write to a table in the DB the params coming in and the data being output. That should point you in the correct direction.

Roy Latham · Answer 3 · 2018-08-11T23:01:23.913

In SSRS, I was trying to pass null as the value of a parameter to a stored procedure. It seemed that no matter what I tried SSRS was using 'null' which did not work with @Parameter is null inside the stored procedure.

I created a report parameter, called @Rs_null, with a default value of (null). I could then use that.

(Creating a report variable was no good since I could not pass that a stored procedure parameter.)

See also How to use stored proc with null parameters in SSRS?

SSRS returning different results than the stored procedure

3 Answers3

Linked