Please forgive me if this issue has already been discussed and answered. My searches haven't produced any meaninful answers, so I figured I'd try posting this problem.
Using VS 2010 (Ultimate) and .Net 3.5, we've deployed CLR stored procedures to our SQL Server (2008). (Dev system is on Win 7, 64 bit, and the SQL Server we're testing with is on the same box as VS.) The deployment of the DLL seems to go fine, and the stored procedures appear to run correctly. Furthermore, there seems to be no problem stepping through the code when debugging in VS (using 'direct debugging ... via the Test.sql file).
The problem now is that we're trying to track down a bug (possibly in one or more of the stored procedures) that can't be reproduced simply by making direct calls to the SP from VS. Our need is to attach VS to the Sql Server process, and then actually run the client app to perhaps see exactly what's happening on the SP side of things. Unfortunately, we can't seem to successfully attach to SQL Server from VS. When we 'Attach to Process', and select SqlServer, VS "appears" to attach correctly and our project goes into the debug mode. However, when we run our client app, as soon as it gets to a point where it makes a call into any one of the SP's, it hangs. Although we have a breakpoint (in VS) on the entry point of all of the SP's, none of them are ever hit. Basically, nothing happens until we 'Stop Debugging' in VS, at which point our client app resumes functioning normally. During this time, we see no apparent errors or warnings.
I'm guessing that we've omitted one or more critical steps in setting up for this kind of test environment, either on the VS side or the Sql Server side, but if so, we have no clue what those missing steps are.
Any thoughts would be appreciated.
Have you tried the hints mentioned here: http://www.sqlskills.com/BLOGS/BOBB/post/SQLCLR-debugging-and-VS-2010-revisited.aspx and here: http://blogs.msdn.com/b/robertbruckner/archive/2010/06/20/debugging-custom-extensions-with-visual-studio-2010.aspx
Thanks so much for the reply.
Well, this is an odd one. I had in fact tried those hints (and in fact had spent quite a bit of time doing so. I had also tried a different hint in which changing the processor infinity setting in SQl Server Management Studio might help on machines with quad core processors (which I have). Regardless of those changes, the client app was still hanging on the first SP call to SQL Server.
When I read your reply, I decided to give it another try. The new config file was still in place from the previous attempts, so I tried it once more and the client app still hung. It then occurred to me that I hadn't tried the processor infinity change AND the new config file both at the same time. So, I once again modified my processor infinity settings, left the config file in place ... and then rebotted the entire PC.
Well, I can't say that any of that was the reason for success, but when the PC came back up and I attached my CLR project to SQL Server, and then ran the client app, the breakpoints hit perfectly!
I've only tried it once, so I don't know if this success is going to last ... but at least for now, it seems we're good to go for our debuggging effort.
Thanks again for your reply!