Hi all,
So I've got a stored procedure. It worked fine yesterday. I ran the procedure dozens of times yesterday and I ran the report the procedure is for dozens of times yesterday. New report and it was pretty much done. We've all been there, right? Then I realized I didn't have a zip mask on the postal code, so I went back and added that to the sp.
I go back to testing the report today thinking I'd just be tweaking formatting and now the same procedure run with the same parameters has no results. I tried copying the sql from the sp and running it in pieces to see where it goes wrong, expecting there to be some faulty join I missed somewhere, or something else I must've changed and not remember. But when I run ALL the sql from the stored procedure, including all variables with the same test values, I get the results I expected. Same results as yesterday (plus the zip mask). But running the procedure gets nothing.
Does anyone have any idea how that might happen?
Is this a procedure calling a standard procedure/report? In my previous life I had some weirdness like that and had to run the standard report once first, then my custom stuff would work.
Hi Amanda,
This is just a wild guess, but I had something like this happen to me. The stored procedure got created with different ANSI settings in live and behaved very differently than on test. Just to check, I generate the create procedure sql and look at which ANSI setting are enabled since these can cause different behavior.
John