Document your SQL Server database with free tools: SQL Server Compact Toolbox and DB>Doc

In this blog post I will highlight an overlooked feature in my SQL Server Compact Toolbox (Visual Studio add-in or standalone), the ability to create interactive relationship diagrams of SQL Server databases. And in combination with the free, open source DB>Doc for SQL Server, you have a full suite of documentation tools.

In order to create a diagram showing tables, columns and relationships for your SQL Server database, install the Toolbox.

Right click the root node of the Toolbox window, and select “Create SQL Server Database Graph (DGML)”:

Select the database that you want to diagram (the tool needs SELECT permission on the system views in the database)

You can also select to diagram only a subset of the tables in the database:

You will be prompted for a location to save the DGML file, enter this, and click OK, and Visual Studio will show the generated diagram in the DGML viewer, that comes with Visual Studio 2012 Pro or higher:

The viewer is quite advanced, from the Layout menu you can choose between various layouts of the dependency graph, and other advanced features to analyse the graph, for example. From the viewer you can Share as Image or XPS.
UPDATE: With the DGML Power Tools installed, you can also share a .SVG, which you can view in any web browser.

Click on a table, and select the down arrow button to expand the table into columns, and refer to the Legend also displayed (via the Legend menu item in the viewer):

More information on working with the graphs is available on MSDN:

Browse and Rearrange Dependency Graphs

Edit and Customize Dependency Graphs

To create HTML/XML based documentation of your database columns, you can use the open source DB>Doc tool. The main idea [of this tool] is to serve as a complementary tool to database diagrams, focusing on columns in tables and their meaning (common database diagrams focus to entity relationships).

I also use this tool for the documentation feature in the Toolbox for SQL Server Compact database files, which produces output similar to this:

Happy documenting! And feel free to provide feedback at https://sqlcetoolbox.codeplex.com/WorkItem/Create

The trouble with Any CPU–Prefer 32 bit–BadImageFormatException

In my previous 2 blog posts here and here, I have briefly mentioned the new default Target Platform for new .NET 4.5/4.5.1 projects in Visual Studio 2012/2013. To be perfectly honest, it was not exactly clear why this was an issue with the SQL Server Compact ADO.NET provider, so I decided to dig a little deeper.

To read more about the new default Platform target option introduced in .NET 4.5, see the MSDN documentation here, and the blog post here.

Why is this Target platform an issue for the SQL Server Compact provider?

Let’s find out how the provider detects if it is running on a x86 or x64 (AMD64) system. In .NET 4.0, two new properties were introduced, System.Environment.Is64BitProcess and System.Environment.Is64BitOperatingSystem (for a nice overview of their implementation see this blog post. But the provider code is old, and uses a different method: It looks at the PROCESSOR_ARCHITECTURE environment variable, and assumes that it reflects the value of the current process bitness (possible values are x86 and AMD64 on a Intel x64 system). In fact, it first looks in the .exe folder for a valid version of sqlceme40.dll, and if not found, then in a subfolder named after the processor architecture (ie x86 or AMD64).

In order to test how an app responds, I then made a small .NET 4.5 console app to test the values of the new Environment properties and the PROCESSOR_ARCHITECTURE value. I added the SQL Server Compact NuGet package (Microsoft.SqlServer.Compact) to test how the engine responds using Private Deployment under the four different Target Platform options. And I uninstalled the SQL Server Compact 4.0 runtime from Add/Remove Programs, so it is no longer in the GAC.

string path = @"c:\temp\test.sdf";
Console.WriteLine("Is64BitOS: " + System.Environment.Is64BitOperatingSystem);
Console.WriteLine("Is64BitProc: " + System.Environment.Is64BitProcess);
Console.WriteLine("PROCESSOR_ARCHITECTURE: " + System.Environment.GetEnvironmentVariable("PROCESSOR_ARCHITECTURE"));
Console.ReadKey();
if (System.IO.File.Exists(path))
    System.IO.File.Delete(path);
using (SqlCeEngine engine = new SqlCeEngine(string.Format("Data Source={0}", path)))
{
    engine.CreateDatabase();
}

1: Output with Any CPU, Prefer 32 bit:

Is64BitOS: True
Is64BitProc: False
PROCESSOR_ARCHITECTURE: AMD64

And getting error: Unable to load the native components of SQL Server Compact corresponding to the ADO.NET provider of version 8876.

As you can see even if it is a 32 bit process, the PROCESSOR_ARCHITECTURE variable has the “wrong” value, causing the SQL Server Compact ADO.NET provider System.Data.SqlServerCe.dll to try to load the dll files from the AMD64 folder in a 32 bit process.

2: Output with Any CPU, Prefer 32 bit unchecked:

Is64BitOS: True
Is64BitProc: True
PROCESSOR_ARCHITECTURE: AMD64

- and no errors

3: Output with x64:

Is64BitOS: True
Is64BitProc: True
PROCESSOR_ARCHITECTURE: AMD64

- and no errors

4: Output with x86:

Is64BitOS: True
Is64BitProc: False
PROCESSOR_ARCHITECTURE: x86

- and no errors

Hopefully this blog post will prevent some form getting bitten by this “issue”, which also affects the SQLite ADO.NET provider.