Tuesday, November 27, 2007

SQL Compact 3.5 RTM available for download

The standalone components of SQL Compact 3.5 are now available for download. These components allows you to use and develop solutions based on SQL Compact 3.5 and ADO.NET Sync Services 1.0 without requiring Visual Studio 2008.

Microsoft SQL Server Compact 3.5 and Microsoft Synchronization Services for ADO.Net v1.0 for Windows Desktop

Microsoft SQL Server Compact 3.5 for Windows Mobile

Microsoft SQL Server Compact 3.5 Books Online and Samples

Update 30-11-2007:
Microsoft SQL Server Compact 3.5 Server Tools

The "SQL Compact Server Tools" download does not appear to be available yet.

No sign of the "Data Migration Wizard", or reference to same in Books OnLine.

No sign of the Windows CE 6.0 cab files either...

The Windows CE 6.0 cab files are in the wce500 folder! From 3.5 Books OnLine: "Note   The CAB files and DLL files under the folder %Program Files%\Microsoft SQL Server Compact Edition\v3.5\Devices\wce500 are also used to install SQL Server Compact 3.5 on Windows CE 6.0 and Windows Mobile 6.0 devices."

The file version of all the SQL Compact files in the web downloads are: 3.5.5386.0

The contents of %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5 after installation of these downloads:


Sunday, November 25, 2007

SQL Compact 3.5 query analyzer

The query analyzer and the other SQL Compact tools (also included in the Visual Studio 2008 Server Explorer) that are included with SQL Server Management Studio are now available for SQL Compact 3.5 with the latest SQL Server 2008 CTP. It does not appear that Intellisense is available for SQL Compact, but this is only a CTP, of course. 

The readme file for the SQL Server November CTP contains this information relating to Merge replication and SQL Compact 3.5:

"4.1.14 Replicating Columns That Contain the New Data Types to SQL Server Compact Is Not Supported

You cannot currently replicate to SQL Server Compact any columns that contain the new data types in SQL Server 2008. The new data types are as follows:

  • date
  • datetime2
  • datetimeoffset
  • FILESTREAM attribute
  • geography and geometry
  • hierarchyid
  • time

Replication will fail even if you use article schema options or the publication compatibility level to specify that these data types should be mapped to data types that are supported by SQL Server Compact. You can replicate tables that contain columns with the new data types by filtering out these columns. For more information about filtering, see SQL Server Books Online."

Let us hope that some of these new data types (in particular the date/time types) will be supported in an upcoming version of SQL Compact.

Monday, November 19, 2007

SQL Compact 3.5 has been released to web

Visual Studio 2008 has been released to web for all MSDN subscribers at http://msdn2.microsoft.com/en-us/subscriptions/default.aspx

VS 2008 includes SQL Compact 3.5 in various contexts, as “Local Database”, and as an ADO.NET Sync Services 1.0 “Local Data Cache”.

VS 2008 allows desktop development using SQL Compact with all editions of VS 2008, including the free Express edition.

For device development with SQL Compact 3.5, VS 2008 Standard or higher must be used.

For an overview of the new features of SQL Compact, see other entries in this blog, and this post from the SQL Compact team:





Monday, October 22, 2007

SQL Compact 3.5 features by platform

Since SQL Compact is now fully supported on the desktop, a divide in features between the original platform (Windows Mobile/Windows CE) and the ”new” platform (Win32), has now become apparent. It is therefore useful, if you are palling to use SQL Compact across platform, to know which features are available on each platform. I have attempted to collect this information in the attached document, and will continue to update this as the RTM approaches, and during subsequent releases and service packs.

Sunday, August 26, 2007

TOP clause now supported

So now in SQL Compact 3.5 you can make queries against Northwind.sdf with the following syntax:

SELECT Top(10) *

FROM [Order Details]
ORDER BY [Unit Price] * Quantity DESC

And you will get the 10 most valuable orders listed.

It's nice to see this omission finally added to the SQL Compact syntax.

Wednesday, August 15, 2007

Hidden gem: Rename table

This new feature is not mentioned anywhere, but listed in the docs:

sp_rename: Changes the name of a user table in the current database. Currently, sp_rename support in SQL Server Compact 3.5 is limited to tables.

sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name' [ , [ @objtype = ] 'object_type' ]
In the following example I will demonstrate it's use (using the VS 2008 beta 2 Server Explorer):

The current tablename is Test.

Run the sp_rename command, with the 2 parameters: Old name and new name

The query parser complains.

But runs the query none the less.

Do a refresh...

And voila... all from plain old T-SQL!

Tuesday, August 7, 2007

SQL Compact 3.5 - Manage relationships

Now foreign key relationships can be managed via the UI in Server Explorer, as two new pages have been added to the Table Properties dialog:

This is the "classic" dialog from SQL CE 3.1
(click to enlarge)

Notice the two new pages, a "Add Relations" page.

And a "Manage Relations" page.

This is a nice addition to the SQL Compact Management UI - and is a missing piece requested by many users.

Monday, August 6, 2007

SQL Compact 3.5 testing

I have begun testing SQL Compact 3.5 (as part of VS 2008 beta 2) and will start to post my findings in this blog.