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.

I’ve been trying to work around this problem for the last few months but the frustration has reached a boiling point.

VS 2005 is a great product — easily the best IDE of the Visual Studio line. Easy to use, customizable, well-integrated with the various tools I use, etc. However, since I’ve installed Vista Business, debugging directly out of VS 2005 has been a nightmare.

First I ran into the “Debugging failed because integrated Windows authentication is not enabled” error. Even when enabling Windows Authentication, I ran into the problem. So moving everything back to the Classic Pipeline worked, but I hated it — why have the new Integrated Pipeline available if you can’t use it?

Then, Microsoft released the hotfix KB 937523. Great? Not really. Now after F5 I get the error “IIS does not list an application that matches the launched URL“. I can debug to a root page, but not a subdirectory page. Irritating.

Another link to the IIS.net forums about this new issue.