9

When I'm trying to debug a SQL CLR via a SQL Test script in VS2010 (with MSSQL 2008) I get "Canceled by user" in the Debug output window as soon the deployment finish (which is successful.)

I have been up and down the Internet and tried all found solutions and still no go.

System:

Windows Server 2008 R2 Standard SP 1
64 bit Visual Studio 2010
Ultimate SQL Server 2008 R2 (Both running on the same computer)

Steps taken:

  • VS 2010 is run as administrator.
  • SQL Connection User has sysadmin rights on SQL 2008
  • Firewall is set up to allow inbound on the required ports for both VS 2010 and SQL 2008

Been through: http://msdn.microsoft.com/en-us/library/ms165051.aspx

And: http://msdn.microsoft.com/en-us/library/ee828498.aspx

Tried set set Target Framework to 2.0 (from 3.5)

Tried unchecked "Deploy code" in Project Properties under "Deploy" section.

Under Database in Project Properties is set to "Unsafe" and Assembly owner to "dbo".

Yaroslav
  • 6,178
  • 10
  • 44
  • 85
Quintium
  • 469
  • 5
  • 21
  • 1
    Hi, I had the same issue, but I was able to debug by attaching to the remote sqlservr process and running my script in SSMS. If you haven't tried that, it's only a couple extra steps, and works the same once attached. I like it better, actually, because the output is in the familiar SSMS format. – Kimberly Apr 12 '12 at 21:25
  • That is what I'm doing. But it is very annoying to have to stop debugging and re-attach to the process again and again each time you have to run another debugging session, where the debugging method should be doing that automatic for you. – Quintium Apr 17 '12 at 16:33
  • Are you able to debug regular SQL from SMS or VS? – abatishchev Jun 08 '12 at 17:46
  • The question was an issue on how to debug it in VS, and SQL CLR code is not accessible via SMS, so if you have a pointer on how to do it via SMS, then please let me know. Currently the only way to do it is hook into the actual SQL server process as mentioned in the other comment. But a real solution on correcting the issue in VS would be nice. – Quintium Jun 11 '12 at 16:04
  • 1
    Old question - and person did their homework - but one thing not mentioned for anyone who reads this - select Allow SQL/CLR debugging. right-click on the connection you want to debug and choose Allow SQL CLR Debugging – user1166147 Jun 17 '12 at 18:24

2 Answers2

1

Some of what's here may repeat what's on the above links - putting everything in one place to make a complete checklist for anyone with this issue.

Add the following ports/protocols to inbound exceptions on both machines (i.e. your desktop & the sql server). Alternatively turn off any firewalls on desktop & server to skip a few of these steps.

  • TCP: 135
  • UDP: 400,500

On your desktop also add devenv.exe to your firewall's process/program exceptions list.

On the server add firewall exceptions for the sqlsrvr.exe and msvsmon.exe processes.

On the server also add a firewall exception for File and Printer Sharing.

Is the VS remote debugging service installed on your server?

Is SQL/CLR debugging enabled on the database / server?

Are you running VS Pro or Team edition?

Does your account have admin rights on the server (OS)?

Does your account have admin rights on the database instance (SQL)?
Here's more info on why sysadmin's required on SQL Server in case there are arguments from DBAs / managers about restricting access further: http://blogs.msdn.com/b/sqlclr/archive/2006/07/07/659332.aspx

Are you connecting using windows authentication?

Have you tried editing sqlserver.config as per: http://www.sqlskills.com/BLOGS/BOBB/post/SQLCLR-debugging-and-VS-2010-revisited.aspx (please note the author's cautions; i.e. this is a hack which can cause issues with the SQL debugger's stability so only do this if you're the only person attaching to this server).

<configuration> 
   ...  
   <startup useLegacyV2RuntimeActivationPolicy="true"> 
       <supportedRuntime version="v2.0.50727" /> 
   </startup> 
</configuration>

If you're on a company network check that there are no proxies which may affect you (if your machine doesn't realise the server is on the same intranet sometimes you'll be going via a proxy). Try using the server's FQDN when connecting as having the full path explicitly stated sometimes helps (at least I've seen this get around issues with ISA Server).

If you have any virus software ensure that exceptions are setup for file extensions .mdf, .ndf, .ldf, .bak, .trn & .$$$.

Are you running the latest service packs for VS, SQL and client & server OSes?

If you still have issues after all that, have you tried running the SQL and VS installers in repair mode, or removing and reinstalling the software?

JohnLBevan
  • 18,988
  • 5
  • 75
  • 151
  • Thank you for your writeup. I have given up trying to figure it out. Since our development environment has SQL and VS running on the same server, I'm able to attach straight to the SQL server process instead, and that way be able to debug the SQL CLR. But since it is a shared environment and I do not have control on the installments, then I'm not going to try some of those steps at the moment. But I do hope it helps others, and if it does, then that person please leave a comment. – Quintium Oct 29 '12 at 15:10
  • No worries - had a feeling that may be the case as I'm 10 months behind in providing assistance :). – JohnLBevan Oct 29 '12 at 17:27
0

I had the same issue, although I was debugging fine for weeks before this started happening.

I got it working again by executing the following script on the db again:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

ALTER DATABASE [DBName] SET TRUSTWORTHY ON
Marcin Nabiałek
  • 98,126
  • 37
  • 219
  • 261
Nico
  • 1
  • 1