Query is fast in management studio but hangs from Console App

I'm going to include my little narrative of how I troubleshot this issue. But first let me just get the answer out there. You need to include this at the start of your stored procedure:

set arithabort on

My troubleshooting story is also pretty concise I guess. I used SQL Server Profiler to see what my app was doing differently from management studio. The actual call was identical, but any .NET MSSQL connection calls a bunch of set commands before it does anything. The entire list I found is below:

set quoted_identifier on

set arithabort off

set numeric_roundabort off

set ansi_warnings on

set ansi_padding on

set ansi_nulls on

set concat_null_yields_null on

set cursor_close_on_commit off

set implicit_transactions off

set language us_english

set dateformat mdy

set datefirst 7

set transaction isolation level read committed

By starting with a fresh query editor window and adding in these set commands one at a time I found the culprit pretty quickly. Then I just set the property the way I want it inside the proc to override the damage the connection from .NET causes.

StackOverflow was also really helpful in two posts. One focusing on the SqlDataAdapter and another that was more generally about SqlCommand. Just go help vote up the answer that this post is consistent with. Unless you find otherwise, then let me know.

What would be nice is being able to control which set commands are issued from my app. I've found nothing on how to do this. If you find something please share!