I’ve been trying my hand at extending Community Server. The SDK and Dave Stokes’ SDK installation guide are invaluable but I came upon an awkward glitch when trying to debug my first extension. Having attached the VS debugger to the web server process, and done anything in Community Server I’d get

An exception of type ‘System.Data.SqlClient.SqlException’ occurred in System.Data.dll but was not handled in user code

Additional information: EXECUTE permission denied on object ‘sp_sdidebug’, database ‘master’, owner ‘dbo’.

A quick bit of googling reveals that this isn’t a uncommon but it’s related to SQL debugging and I wasn’t attempting to do that. None of my VS projects had ‘Enable SQL Debugging’ checked. Fortunately the solution was quite straightforward : when attaching the aspnet_wp.exe process to VS, make sure you are only attach managed code.

• Click Tools > Attach to Process
• Click the Select button just above the list of available processes to attach to
• Select ‘Debug these code types’ and check only the boxes for Managed (and Native if necessary) code.
• Click OK
• Click Attach
• Debug as normal

Full credit to techenvelope for this.

For reference though, if you are trying to do some SQL debugging, the MS advice is to

• install Service Pack 4 for SQL2000 if you haven’t already or
• grant the ASPNET user the EXECUTE permission on sp_sdidebug)

The actual support articles are

• Troubleshooting tips for T-SQL Debugger in Visual Studio .NET
• FIX: SQL debugging does not work in Visual Studio .NET after you install Windows XP Service Pack 2
• PRB: A Database User Cannot Debug Stored Procedures That Another User Owns

I tried this last night and it chose to unpack the hotfixes and the like onto the same partition on the external hard disk. Once finished unpacking, it simply stopped saying it could not continue but without giving any reasons. Even the log was a bit useless - the corresponding entry read, ‘The patch installation could not proceed due to unexpected errors’. But the fix is simple. Copy the service pack to your internal hard drive, switch off the external disk and install it from C:. Works fine then.

Scenario : I have a .NET 1.1 console application that connects to a SQL 2000 database, queries some data and spews it out to a text file. This is successfully running on both Windows 2000 Server and Windows 2003 server. I decide to migrate the project to .NET 2.0, so open the project in VS2005, use the conversion wizard and rebuild. The newly built app works fine on Windows 2003 but not Windows 2000, where I get a connection timeout error. The .NET 1.1 version of the app still works fine on both boxes. I can also create a DSN to the database from both boxes that connects successfully. If I change the connection string in the .NET 2.0 app to use the IP address of the database server rather than its friendlier computer name, I still get a connection timeout error.

Diagnosis : The .NET 2.0 app is failing to make a connection to the database through either TCP or Named Pipes.

Comments : A lot of blog posts and indeed blogs have dedicate themselves to diagnosing connection issues between .NET 2.0 apps and SQL 2005 but the same principles apply here. The fact that the .NET 1.1 app and DSNs work fine indicate it's a client-side connection issue rather than one on the server-side. If you didn't know that though, you might want to check out the following issues.

1. Firewall : Client and database should be communicating on port 1433 and 1434, so make sure that these are open from the client side. If you haven't got a tool that can do this already, Microsoft make available a small port scanner called PortQry and, if you don't like command line apps, a GUI frontend called PortQryUI which automatically runs scans on common port groups for certain tasks. It's not brilliant and you could use nmap or something similar if you want but it does the job.    
2. Server Config : Unless you've deliberately switched them off, SQL 2000 will be listening via shared memory protocol (used only when you reference a database using (local) or (local)\instancename), named pipes and TCP. In SQL Enterprise Manager, right click the database you're trying to connect to and select properties. The General tab of the ensuing dialog will have a button named Network Configuration which you should click to brign you to the SQL Server Network Utility window where you can check what protocols are enabled and how they are enabled. Assuming both named pipes and TCP are up and running, hit properties for each and record the name of the pipe and the tcp port being used for comms to compare against your client config.    
3. Client Config : Your first port of call should be the connection string. Make sure it is correct. If it's not that, run cliconfg.exe from the command line. This application is the client-side equivalent of the Server Network utility. .NET 2.0 seems to need more clarity when it comes to connections than .NET 1.1 especially with pipes, so there's now a handy guide at http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q328306 to work through and see if you can debug the problem yourself. Remember to compare client settings with those you records from the server. You could also check your installation of MDAC for inconsistencies using the guide at http://support.microsoft.com/kb/307255/.

If you can't figure out the problem from here, you can also try the forums at SqlJunkies.com, the microsoft.public.sqlserver.connect newsgroup and the SqlProtocols team blog, all of which I used to trace down my own problem and are well worth a read as well.

My own solution was finally reached by building a clean Windows 2000 VPC, making sure I could run the same .NET 2.0 app in that and comparing client settings. Sure enough, the named pipe on the Win2k box was incorrect as can often be the case for access to a clustered database, and my TCP setting was also incorrect via an alias built to the database. I'm still not sure why the .NET 1.1 version of this app continued to work despite this, but there you go. Hopefully if you come across a similar problem, you'll at least have a head start on its diagnosis and solution.

Never judge a book by its cover. Kimberley Tripp has a post on her blog called A Simple Start - Table Creation Best Practices. I thought that it might not be a bad idea to see if her wise words tallied with those in BAND. Judge for yourself. There's table design and then there's table design it would appear. I don't think I'll need to cover this in the Whidbey update. Interesting to read though. Reminds me of building structs in VB6 and C++.