For some reason I wasn't getting the SQL Server Mobile Data Provider listed in Visual Studio as a valid database to connect to. I was baffled because I was still able to connect to a SQL Sever Mobile database through SQL Server Management Studio, and the data provider files were installed into the GAC. After a few hours of trying to figure out what the problem was I finally stumbled upon this TechNet forum post. Apparently there is a problem with the installation sequence of SQL Server 2005 when Visual Studio is already installed. I'll memorialize the post here for future reference:
Please verify whether SQL Mobile Registration is visible to Visual Studio:
1) In machine.config from .NET Fx directory (i.e. %WINDOWSDIR%\Microsoft.NET\Framework\v2.0.xxxxx\config\machine.config).
a) Data Providers section: SQL Mobile is listed? No, then reinstall SQL Mobile MSI from
%VSINSTALLABLE%\wcu\SQL Server\
It looks like an issue with the installation sequence or not selected “SQL Mobile” in Visual Studio.
SQL Mobile gets installed in VS 2005 only when the user selects “Languages -> C# / C++ -> Smart Devices -> Programmability”.
VS 2005 and SQL 2005 can co-exist on the same machine that is side-by-side installation. In that case, once must be very careful in the installation steps and procedures.
For example,
User has installed VS2005 first, and to install SQL 2005 he is supposed to run “Build Uninstall Wizard” of SQL 2005 to remove the SQL Express first. While using this Build Un-install wizard, the user should only uninstall SQL Express and not .NET Framework. If the user un-installs the .NET framework also then SQL Mobile registration to .NET Fx Data Provider is also gone.
To fix the problem in case user has uninstalled .NET Fx by mistake and re-installed .NET Fx: Run SQL Mobile Dev Tools MSI from %VSINSTALLABLE%\wcu\SQL Server\
As far as I can tell the install file primarily changes the machine.config file by adding the following line:
<system.data>
<DbProviderFactories>
...
<add name="SQL Server CE Data Provider"
invariant="Microsoft.SqlServerCe.Client"
description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition"
type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
</DbProviderFactories>
</system.data>
though it may well have changed some registry settings.
Wow am I glad that is finally fixed.