The commenter is correct. To enable persisting Data Connections in Server Explorer under Windows Vista comment out the line shown in bold from \Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine[.config]:The issue with SQL Connection strings is much larger than what you elude to. It also breaks almost all visual database design tools in Orcas, SQL Server Management Studio and Visual Studio 2005. This includes the table designer, database diagrammer and the view designer.
I have good news though. There is a fix. If you open up the machine.config file you will notice multiple entries for the SQL CE stuff in the system.data section. Comment these out (this will break SQL CE, which is fine for most people) and your SQL Server editing will return. I believe if you comment out 2 of the 3 leaving the version 9 dll intact the SQL CE stuff will remain working.
<system.data> <DbProviderFactories> <add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> <add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> <add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> <add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> <!-- <add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Everywhere Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /> --> <add name="SQL Server Compact Edition Data Provider" invariant="System.Data.SqlServerCe" description=".NET Framework Data Provider for Microsoft SQL Server Compact Edition" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /> <add name="SQL Server Compact Edition Client Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server Compact Edition Client" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /> </DbProviderFactories> </system.data>The SQL Server CE Data Provider element (for v3.1 disguised as v9.0.242.0) appears to conflict with the SQL Server Compact Edition (SSCE) Data Provider v3.5.0.0, which is the correct version for Orcas March 2007 CTP, when running under Windows Vista. Version 3.5 supports the Microsoft Synchronization Services (OCS) CTP, has new data types, and other improvements. Note: The machine.config file is present only in the .NET Framework 2.0's CONFIG folder. The other framework folders (1, 1.1, 3.0, and 3.5) don't have a CONFIG folder.
Here's a screen capture of the line to edit in your machine.config file (click for full size image):
And just to prove that the fixes work, here's a screen capture of Server Explorer showing two remote SQL Server 2005 Express instances and a local SQL Server 2005 Compact Edition Test.sdf file created when the connection was made (click for full size image):
Caution: Installing Orcas March 2007 CTP without installing SQL Server 2005 Express (SSX) requires that you change the connection strings for many sample applications, such as those for LINQ to SQL, from local user instances of Northwind.mdf to remote instances of the Northwind sample database. My Updated Orcas March 2007 CTP LINQ C# Samples post provides an example for the Sample Queries project's LinqToSqlSamples class.
Note: Here's a link to an ADO.NET Orcas post from a tester with the problem solved here. Two more confirmations here and here.
Update 3/19/2007: Use Orcas's Server Explorer, not SSMS, to manipulate SSCE data files. Server Explorer's Show Table Data context menu choice lets you edit SSCE table data.
Laxmi Narsimha Rao ORUGANTI from Microsoft India posted the following in the Microsoft Synchronization Services for ADO.NET forum regarding SSCE versioning issues:
With SQL Server 2005 (RTM, SP1, SP2), the version of SQL SERVER COMPACT EDITION integrated with it is 3.0/3.1 (Note: DLL versions would still remain as 3.0.xxxx.0). SQL Server CE 3.5 which is part of ADO.NET Sync Services CTP is not integrated with database management tool SSMS. We would encourage you to use ORCAS to manipulate the Sync Services databases created on client side. SQL Server CE 3.5 is fully integrated with ORCAS. Use Database Explorer (or Server Explorer). We are also working towards to integrate with Orcas Express editions but nothing is concrete or materialized yet so no promises.
Excellent. I just pointed to this post on the forum thread where a few people are having this problem.
ReplyDeleteThanks a lot. I had spend so much time trying to find a way to fix this.
ReplyDeleteAt last it is working now.
Great work
Thanks, finally solve my problem
ReplyDelete