Unexpected results from ISNULL in SQL Compact 3.5

In SQL Server, the ISNULL function takes two arguments – the first is a potentially NULL value, and the second is another potentially NULL value. If the first value is NULL, the function returns the second value instead. It’s always been this way – for example:

  SELECT V1, V2,
         ISNULL(V1, V2) as [Result],
         Descr
  FROM (SELECT 10 as V1, 20 as V2, 'Returns first' as Descr UNION
        SELECT 10 as V1, NULL as V2, 'Returns first' as Descr UNION
        SELECT NULL as V1, 20 as V2, 'Returns second' as Descr UNION
        SELECT NULL as V1, NULL as V2, 'Both NULL - return NULL' as Descr) t

In SQL Server, you get the following results:

V1      V2      Result  Descr
10      20      10      Returns first
10      NULL    10      Returns first
NULL    20      20      Returns second
NULL    NULL    NULL    Both NULL - return NULL

However, SQL Compact implements ISNULL differently, in a way that’s not compatible at all – instead, the ISNULL function returns a 0 or 1 based on whether the first parameter is NULL! It doesn’t even process the second parameter, but instead of failing with an “Invalid Syntax” error, it happily accepts it, executes, and returns unexpected results. In SQL Compact, you get the following result set:

V1      V2      Result  Descr
10      20      0       Returns first
10      NULL    0       Returns first
NULL    20      1       Returns second
NULL    NULL    1       Both NULL - return NULL

Notice the difference? The ISNULL column is returning the value based only on the first parameter (a “0” if it’s not NULL, and a “1” if it is), and if you’re not expecting it, it silently mis-calculates any formulas containing that function. If you’re relying on the function in any statements that add values together or make decisions based on the results, you’re in for some very expected behavior (in my case, I noticed totals that were wildly incorrect).

As an alternative, you should be using the COALESCE – it accepts multiple parameters and it walks down the list until it finds one that’s not null, returning a NULL value if all parameters are NULL. This function can be used in place of ISNULL in SQL Server code with exactly the same behavior (i.e. no changes), and as a bonus, it acts the same in SQL Compact, so your formulas will work properly there as well. The example above becomes:

  SELECT V1, V2,
         COALESCE(V1, V2) as [Result],
         Descr
  FROM (SELECT 10 as V1, 20 as V2, 'Returns first' as Descr UNION
        SELECT 10 as V1, NULL as V2, 'Returns first' as Descr UNION
        SELECT NULL as V1, 20 as V2, 'Returns second' as Descr UNION
        SELECT NULL as V1, NULL as V2, 'Both NULL - return NULL' as Descr) t

And on both SQL Server and SQL Compact, the result set is the same:

V1      V2      Result  Descr
10      20      10      Returns first
10      NULL    10      Returns first
NULL    20      20      Returns second
NULL    NULL    NULL    Both NULL - return NULL

+1 for consistency!

MORAL of the story:

When executing statements on different platforms, ensure the functions you use behave in the same manner on both – and even when considering multiple platforms from the same vendor, named almost identically, don’t assume things behave unless you’ve tested them 🙂

“Initializing Reconciler has failed” when setting up SQL Compact replication

When initializing replication to a .NET Compact Framework client on a mobile device, I was receiving an error message when I attempted to start the synchronize:

Initializing the SQL Server Reconciler has failed. Try again.

I had confirmed that SQL Compact web replication was set up correctly, and checking the URL came back as expected. Searching for the error online comes back with a dozen recommendations, but when I traced the replication sync attempt, I saw the following statement executed:

exec sp_helpdistpublisher N’SQLSERVERNAME’

Followed immediately by the error message:

The remote server “SQLSERVERNAME” does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers.

Sure enough, executing that command in SSMS, logged in as my replication user, gave me the same error message. At some point, I’d changed the user I was using to set up the subscription, and that user didn’t have rights to view the publication list on my SQL Server. The fix was pretty easy:

  1. In SQL Management Studio, right-click the publication
  2. Select “Properties” and then open the “Publication Access List” tab
  3. Add the user you’re connecting your subscriber with to this list

Here’s a shot of the screen where I had to make this change, in case there’s any confusion:

Publication Security Settings

Matching up SQL CE DLL version numbers

The Abstract:

While developing a Windows Mobile device application on two different computers, I thought I’d installed SQL Server Compact Edition 3.5 SP2 on both of them, but ran into some version difference issues. In researching it, I couldn’t find good information about the different versions of the System.Data.SqlCompactCe DLL, so thought some future developers might enjoy what I found out, all in one place.

The Problem:

After checking the project into source control on one computer and fetching it on the other, I’d see a broken reference to System.Data.SqlServerCe:

Missing reference to System.Data.SqlServerCe
Missing reference to System.Data.SqlServerCe

And I received the following message (and about 100 errors – one for every reference to the namespace in my code) when I attempted to compile:

Warning 10
Could not resolve this reference. Could not locate the assembly “System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken=3be235df1c8d2ad3, processorArchitecture=MSIL”. Check to make sure the assembly exists on disk. If this reference is required by your code, you may get compilation errors.

If I dropped the reference, re-added it on my second computer (BTW – only one version was listed in my .NET reference list on both machines) and checked it back in, then I’d get the same situation on my first computer with the following error message:

Warning 10
Could not resolve this reference. Could not locate the assembly “System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=3be235df1c8d2ad3, processorArchitecture=MSIL”. Check to make sure the assembly exists on disk. If this reference is required by your code, you may get compilation errors.

The Research:

I had a DLL version mismatch – a general problem when you’ve got multiple computers you’re developing something on, since you have to ensure the same versions and packages are installed on each. However, searching for details on the different versions of this DLL in the wild wasn’t fruitful – did I have SQL Compact 3.5 RTM installed (3.5.0.0)? If I’d installed v3.5 SP2, why was it shown as 3.5.1.0 (shouldn’t it be 3.5.2.0?) on one machine, but as 3.5.0.0 the other? To top that off, both versions (v3.5.0.0 and v3.5.1.0) were in my GAC as MSIL – what?

It turns out that when you add a reference to this DLL in a Compact Framework project, Visual Studio is using the following version (depending on whether you’re on an x86 or x64 machine):

x86: C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Devices\System.Data.SqlServerCe.dll
x64: C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5\Devices\System.Data.SqlServerCe.dll

There, I found my issue – that file version was a different version on each machine (3.5.5692.0 on one and 3.5.8080.0 on the other). To find out why, I had to dig into the registry, where I found all kinds of version information about the SQL Server Compact Edition components I’d installed, the DLL version, and the service pack levels:

x86 or x64 components
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server Compact Edition\v3.5\ENU

or if you want to see x86 components installed on your x64 machine:
HKLM\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server Compact Edition\v3.5\ENU

There, you’ll find a combination of “Version” and “ServicePackLevel” entries for every SQL CE component you have installed, and it will shed some light. Here are the version numbers I found (for the various components):

SQL Compact 3.5 RTM
ServicePackLevel: 0
Version: 3.5.5386.0

SQL Compact 3.5 SP1
ServicePackLevel: 1
Version: 3.5.5692.0

SQL Compact 3.5 SP2
ServicePackLevel: 2
Version: 3.5.8080.0

So how did I end up with the SP1 version installed on one machine, but not on the other? Since I’d installed SQL Compact SP2 on both machines, I was baffled. As it turns out, Visual Studio 2008 RTM deploys SQL Compact 3.5 RTM (3.5.5386.0), and applying Visual Studio 2008 SP1 patches that to the SP1 version (3.5.5692.0) – notice the VSToolsVersion/VSToolsServicePackLevel values in the registry location above, which reflect your current service pack level of Visual Studio 2008.

The Solution:

While I’d installed VS 2008 SP1 on both machines, I’d installed the actual SQL Compact 3.5 SP2 for Windows Mobile (EDIT 2016-10-31: This link has been updated to the current location for this download) package on only one machine and not the other – this left the DLL on one machine patched to SP1 (courtesy of Visual Studio) and the other machine with a fully-patched SP2 DLL (courtesy of the actual SP2 Windows Mobile installer).

I confirmed this by running the SP2 for Windows Mobile installer, and it patched my DLL right up to the full SP2 version – problem resolved, and the project now opens up on both workstations with no need for any DLL reference swapping.

The Moral:

Honestly, a bit embarrassing and a pretty amateur move where some vigilance could have saved me quite a bit of aggrivation.

ENSURE YOU’RE DEPLOYING ALL THE SAME PACKAGES ON ALL DEVELOPMENT MACHINES! JUST BECAUSE SOMETHING ALREADY EXISTS ON ONE MACHINE, DON’T ASSUME IT’S THE SAME VERSION!