by DanMI’ve got VS2010 installed as an application inside XP mode which is fantastic except that I really rather wanted to use the instance of SQL 2008 installed on my Windows 7 box rather than the instance of SQLExpress that VS2010 can install with itself. And, should you need to do this yourself, this is how you do it…
In brief
- Install the Loopback Network Adapter on your Windows 7 machine
- Set the Loopback Adapter to have a static IP address which the VPC network adapter will use as its internet gateway
- Configure the XP Mode Virtual Machine to use the loopback adapter as its gateway.
- You should now be able to ping your host machine from your guest OS.
- Open SQL Server connections via TCP/IP on host machine [updated 24/12]
- Open incoming connections on port 1433/1434 for windows firewall
- You should now be able to telnet to port 1433 on the host OS from XP mode
More detailed instructions for each of these steps after the break.
More...
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
The actual support articles are
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.
- 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.
- 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.
- 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.
Why oh why can’t vendors can’t agree that when there is a standard they should implement it in a standard way? I suppose it’s their prerogative to add features over and above the standard but get the other thing right first. Case in point - data types for fields in SQL Tables. All the string-based types have different maximum lengths. In SQL Server, the char type has a max length of 8000. In MySQL it’s 255. Quite a shift. Then MySQL also implements the tinytext, mediumtext and longtext types as well which are all the same thing but with different maximum lengths. I like this but SQL server doesn’t support that idea. Boo.
Now take the datetime value. SQL Server believes that valid values should begin on Jan 1 1753. MySQL starts them on Jan 1 1000. Rather annoyingly, neither of these actually cater for the .NET date type whose valid values begin back on Jan 1 0000, and so presents a lot of quite invalid values if you want to store its value in a database, regardless of whichever one you choose.
Finally, let’s take the timestamp value. Now its SQL Server’s turn not to play ball with the standard. Even the help file says ’oh yeah, totally dude, we may change it to match the standard if, like, we have time before the next version is released.’ Given that MS have been working on Yukon since 2000 and probably wont finish until next year, who’s betting that in five years work, they will have ’forgotten’ to put this right. Given that MySQL has point releases every month or so, how come no-one has queried this? I can’t be the first person to want to use SQL Server and MySQL interchangeably, can I?