Stored Procedure returning different results based on how I compile it

This is driving me crazy. I have a stored procedure script that I can compile either from within SQL Server Management Studio or within Visual Studio 2005. When I compile from within SQL Management Studio, and then run the SP, i.e.

exec student_progress_report_get_by_student 500791

I get the right results (256 rows in this case). When I go into Visual Studio 2005 and select “Run On” to compile the stored procedure, the subsequent execution of that stored procedure (from either within SQLMS or via the web app interface) returns zero results!

I have spent the last hour building the proc side-by-side, both editors open, confirming it’s the right file, isolating anything out, such as a view, that might skew the results, but I can still reliably demonstrate the problem. I am guessing that since I’ve been awake since 4:30 my reptile brain is too tired to figure out the obvious, but this is one of those situations for which the phrase “going postal” seems apt.

UPDATE: this turned out to do with how the VS 2005 “compiler” deals with ANSI_NULLS. I’m not sure if there’s a default setting somewhere, and I’m too lazy to look, but when I explicitly set the ANSI_NULLS option in the script file using the statement:

set ansi_nulls on

… my problem became fixed. VS 2005 had ansi_nulls OFF by default.

Moral: always check your assumptions, and track everything down. Software doesn’t just stop working — there’s always a reason for everything.

Advertisements

0 Responses to “Stored Procedure returning different results based on how I compile it”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s





%d bloggers like this: