Saturday, October 30, 2010

New release of ExportSqlCe SSMS add-in and command line utilities–Script to SQL Azure

The new release (version 3.5) of my scripting utilities for SQL Server Compact includes a couple of bug fixes and a single new feature: Script Schema and Data for SQL Azure.

image

This new feature allows you to migrate your SQL Server Compact solution to a SQL Azure database. The SQL Azure compatible script includes the following changes compared to the standard, SQL Server Compact and SQL Server compatible script:

- Primary keys are scripted before any INSERTs, as a clustered index is required on all SQL Azure tables.

- INSERTs are batched (1000 per GO), to improve performance when running the script against a SQL Azure database.

- ROWGUIDCOL property is not supported, so it is not included.

The SQL Azure script is also SQL Server Compatible, but not SQL Server Compact compatible.The matrix below illustrates your options:

Script option SQL Server and SQL Server Compact SQL Azure and SQL Server
Schema and Data

X

N/A

Schema and Data for SQL Azure

N/A

X

Schema and Data with BLOB files

X (Compact)
(requires SqlCeCmd)

N/A

Schema only

X

(X)

Happy scripting!

Wednesday, October 27, 2010

SQL Compact 4.0 now available as a .zip file

With the recent release of NuPack beta, SQL Server Compact 4.0 CTP is now available as a .zip file. a feature requested by many users: since SQL Server Compact is just a collection of DLL files, why do you need to install an MSI to get hold of these DLL files.

NuPack has the purpose of simplifying the process of incorporating third party libraries into a .NET application during development. An introduction to NuPack is available here.

You can download the NuPack add-in here.

Once you have downloaded the add-in, you can add the SQLCE package from the References context menu in Solution Explorer, using the new “Add Package Reference” dialog:

 image

image

Let’s have a closer look at the contents of the SQLCE NuPackage and the changes it makes to your project.

As you can see with “Show All Files” enabled, the installation has added a packages folder to the file system:

image

Let’s Open Folder in Windows Explorer and have a closer look:

image

The packages\SQLCE.4.0.8402.1 folder contains the .zip file that constitutes the SQLCE package, and folders with content from this .zip file:

Content contains a file with changes to your projects web.config or app.config file, which enables the SQLCE 4.0 DbProviderFactory, the lib folder contains the .NET DLL to be installed and referenced, the NativeBinaries folder contains two folders for the platform specific parts of the SQL Server Compact 4.0 engine. and finally, the tools folder contains scripts to add a post build job to your project, that copies the files in the NativeBinaries folder to the bin folder.

(This custom PowerShell solution for copying the native DLL files may be changed to a more general solution in the future: http://nupack.codeplex.com/workitem/109 )

So in summary, NuPack helps with many of the manual process involved in privately deploying SQL Server Compact 4.0.

Sunday, October 17, 2010

HOW TO: Detect SQL Server Compact version information

There are various aspects to getting the version information for SQL Server Compact. This blog post is an attempt to cover them all, let me know if something is missing.

Assembly Version

The System.Data.SqlServerCe.dll ADO.NET Provider version. (AssemblyVersion)

This is not part of the file information, but .NET specific, part of the assembly’s identity.

var version = typeof(System.Data.SqlServerCe.SqlCeConnection).Assembly.GetName().Version;

This will give you the assembly version of the System.Data.SqlServerCe.dll, not the build number.

For 3.1, this is 9.0.242.0 for the desktop and 3.0.3600.0 for devices (not exactly what you would expect)

For 3.5 RTM, this is 3.5.0.0

For 3.5 SP1 and SP2 this is 3.5.1.0

“ServerVersion”

The ServerVersion property of the SqlCeConnection object.

var ver = new System.Data.SqlServerCe.SqlCeConnection().ServerVersion;

For 3.5/4.0 this will give you the build version of the database engine, including any patches. For 3.5 SP2 this is "3.5.8080.0"

(Sadly for 3.1, this returns “9.0.242.0”)

File version of the managed DLLs

Getting the file system version of the managed ADO.NET provider will give you more precise version information (in particular for 3.0/3.1), and will not require guessing the location of any DLL files (as below for getting the version for unmanaged files).

string fullPath = System.Reflection.Assembly.GetAssembly(typeof(System.Data.SqlServerCe.SqlCeConnection)).Location;
var asmInfo = System.Diagnostics.FileVersionInfo.GetVersionInfo(fullPath);
string asmFileVersion = asmInfo.FileVersion;

For 3.1, this returns 3.0.5300.0 (for patches the final zero will be from 1 to 14)

File version of the unmanaged DLLs

As you may know, the SQL Server Compact engine consists both of managed DLL files (the System.Data.SqlServerCe.dll that has been referenced in all code above), and a number of C++ unmanaged DLL files.

Getting information about these files is more challenging, as we need to locate the unmanaged DLL files. This can be done like so for 3.5 and 4.0 (if installed by an administrator on the machine):

Microsoft.Win32.RegistryKey registryKey = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server Compact Edition\v3.5", false);
string pathName = (string)registryKey.GetValue("InstallDir");
var info = System.Diagnostics.FileVersionInfo.GetVersionInfo(System.IO.Path.Combine(pathName, "sqlceme35.dll"));
string fileVersion = info.FileVersion;

File format/version of database file

The sample in this blog entry gives you the file version, there are only difference in this per major version (2.0, 3.1/3.0, 3.5 and 4.0)

http://erikej.blogspot.com/2010/08/how-to-upgrade-version-3x-database-file.html

Product version information

Information from the product team about product release versions is available here: SQL Server Compact Release Versions and here: Description of the various build versions of SQL Server Compact Edition

In addition, I have lists of available hotfixes on this blog for version 3.5 SP1 and 3.5 SP2