This is a followup to a question here
ctacke said there regarding SQL Server CE-related "issues" I was/am having:
"The problem, as indicated by the error text, is that the SDF file was created by a version of SQL Compact that doesn't match the version of SQL Compact that the application is referencing. I wouldn't focus on the reported version numbers, just the fact that it knows there's a mismatch.
If you don't know the version of the SDF, you can always look it up by reading a few bytes from the SDF file.
SQL Compact database files aren't 100% transportable. You definitely cannot take an SDF from a newer version and load it with an older set of runtimes.
If the database was created with 3.1, you can upgrade it to 3.5 by calling SqlCeEngine.Upgrade()."
I know now that the .SDF file in question is version 2.0
So should I use the "SqlCeEngine.Upgrade()" technique somewhere in my code?
Based on the C++ setup utility code, both devices (3090 and 3190, but I'm working testing the 3190 right now) should have SQLCE 2 installed:
cabModule installMC3090[] = {{_T("ceremote.sa1100.CAB"),_T("Microsoft Remote Display Control"),IDR_CEREMDISP,true},
{_T("netcf.all.wce4.armv4.cab"),_T("Microsoft .NET Compact Framework"),IDR_NETCF,false},
{_T("OpenNETCF.SDF.WCE4.ARMV4.CAB"),_T("OpenNETCF SDF v1.4"),(DWORD)0,true},
{_T("sql.wce4.armv4.CAB"),_T("Microsoft SQL Client"),IDR_SQLCLIENT,false},
{_T("sqlce.dev.wce4.armv4.CAB"),_T("Microsoft SQLCE 2.0 Dev"),IDR_SQLCEDEV,false},
{_T("sqlce.wce4.armv4.CAB"),_T("Microsoft SQLCE 2.0"),IDR_SQLCE,false},
{_T("symbol.all.arm.cab"),_T("Symbol Managed Class Libraries"),IDR_SYMBOL,false},
{_T("System_SR_enu.cab"),_T("Microsoft .NET CF 1.0 ENU-String Resource"),IDR_NETRESOURCES,false},
{_T("HHS_WCE4.ARMV4.CAB"),_T("SSCS HHS"),IDR_HHS,false},
{_T("END"),_T("END"),(DWORD)0,false}
};
//MC3190 includes .NET 3.5 in the default configuration
cabModule installMC3190[] = {{_T("ceremote.sa1100.CAB"),_T("Microsoft Remote Display Control"),IDR_CEREMDISP,true},
{_T("OpenNETCF.SDF.WCE4.ARMV4.CAB"),_T("OpenNETCF SDF v1.4"),(DWORD)0,true},
{_T("sql.wce4.armv4.CAB"),_T("Microsoft SQL Client"),IDR_SQLCLIENT,false},
{_T("sqlce.dev.wce4.armv4.CAB"),_T("Microsoft SQLCE 2.0 Dev"),IDR_SQLCEDEV,false},
{_T("sqlce.wce4.armv4.CAB"),_T("Microsoft SQLCE 2.0"),IDR_SQLCE,false},
{_T("symbol.all.arm.cab"),_T("Symbol Managed Class Libraries"),IDR_SYMBOL,false},
{_T("NETCFv35.Messages.EN.cab"),_T("Microsoft .NET CF 3.5 EN-String Resource"),IDR_NETRESOURCES,true},
{_T("HHS_WCE4.ARMV4.CAB"),_T("SSCS HHS"),IDR_HHS,false},
{_T("END"),_T("END"),(DWORD)0,false}
};
....though I can't find any "SQLCE" files anywhere on the device...
UPDATE
I added a call to Upgrade() after instantiating the SqlCeEngine:
engine = new SqlCeEngine(conStr);
engine.Upgrade(conStr); // <= this is new
...and that may have been a good thing, because now the err msg is a different one, to wit:
location: DBConnection.DBConnection
Exception: The password specified for the source database is incorrect [ Data Source = My DocumentsHHSDB.SDF ]
Inner Exception: [ blank ]
TypeFullName: System.Data.SqlServerCe.SqlCeException
StackTrace: ProcessResults, Repair...
Compare that with what it had been in Update 4 here (now it's a password problem; previously it seemed to be a Sql Ce version mismatch problem).
Okay, so the legacy code (which had worked previously) does not seem to add a password. How is a password normally "tacked on" (not that I even know at this point what the password should be)? Is there a way to get it NOT to expect a password?
Here is the DBConnection instantiation code, which may shed some light on this:
public class DBConnection
{
public string filename = HHSUtils.GetDataPath() + "HHSDB.SDF";
SqlCeConnection objCon = null;
SqlCeEngine engine;
private DBConnection()
{
try
{
// Connection String
//string conStr = "Data Source = " + filename; <== this is the legacy, but failing for some reason (see https://stackoverflow.com/questions/23438579/in-what-format-does-sqlceconnection-expect-its-connection-string-arg/23438672)
//string conStr = string.Concat("Data Source = ", Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "HHSDB.SDF"));
filename = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "HHSDB.SDF");
string conStr = string.Concat("Data Source = ", filename);
engine = new SqlCeEngine(conStr);
engine.Upgrade(conStr);
The formerly used GetDataPath(), which is called by the assignment to filename but is now moot because filename is overwritten, is simply:
public static string GetDataPath()
{
return @"My Documents";
}
UPDATE 2
Is this enough to solve my problem, simply to append "Persist Security Info=False;" to the connection string, a la:
Data Source=MyData.sdf;Persist Security Info=False;
? IOW, is appending that a way of saying, "forget about passwords, I'm not relying on them"?
I also notice that the example above (from here) has no spaces between "Source" and "=" and between "=" and the path to the SDF file. Is this unimportant (as I suspect - IOW, either way should work)?
Perhaps more importantly, though, my connection string begins with a whack ("Data Source = My Documents") whereas this one does not. Significant?
According to that same site, if I do need a password, I reckon it would be like so:
Data Source=MyData.sdf;Encrypt Database=True;Password=myPassword;
File Mode=shared read;Persist Security Info=False;
...or...???
UPDATE 3
Altho ctacke says the cab installations are not really relevant, in the interest of completeness/for posterity I am adding some notes I made on how the device's setups differ:
Those cab files marked with an asterisk differ from the other device's installation
3090 gets 8 cabs installed via the setup utility:
HHS_WCE4.ARMV4.CAB
*netcf.all.wce4.armv4.cab
OpenNETCF.SDF.WCE4.ARMV4.CAB
sql.wce4.armv4.CAB
sqlce.dev.wce4.armv4.CAB
sqlce.wce4.armv4.CAB
symbol.all.arm.cab
*System_SR_enu.cab
3190 gets 8 cabs installed via the setup utility (a comment in the setup utility says, "MC3190 includes .NET 3.5 in the default configuration"):
*ceremote.sa1100.CAB
HHS_WCE4.ARMV4.CAB
*NETCFv35.Messages.EN.cab
OpenNETCF.SDF.WCE4.ARMV4.CAB
sql.wce4.armv4.CAB
sqlce.dev.wce4.armv4.CAB
sqlce.wce4.armv4.CAB
symbol.all.arm.cab
So:
3090 has uniquely:
netcf.all.wce4.armv4.cab
System_SR_enu.cab
3190 has uniquely:
ceremote.sa1100.CAB
NETCFv35.Messages.EN.cab
Both get upgrades to:
HHS_WCE4.ARMV4.CAB
symbol.all.arm.cab
(why these "upgrade cabs" are not simply added in their respective install lists, I don't know - is there an "Upgrade" option?)
UPDATE 4
Inspired (provoked, prodded) by ctacke's latest comment, I looked closely at the SQL CE references I have in my app, and it looks like a putrid potion of mish-mashed mixing and matching:
System.Data is runtime 2.0.0.0 and Version the same (2.0.0.0)
System.Data is from C:Program Files (x86)Microsoft.NETSDKCompactFrameworkv2.0WindowsCESystem.Data.dll
System.Data.SqlClient is runtime version v2.0.50727 and Version 3.0.3600.0
System.Data.SqlClient is from C:Program Files (x86)Microsoft SQL Server Compact Editionv3.5DevicesClientSystem.Data.SqlClient.dll
System.Data.SqlServerCe is runtime version v2.0.50727 and Version 3.5.1.0
System.Data.SqlServerCe is from C:Program Files (x86)Microsoft SQL Server Compact Editionv3.5DevicesSystem.Data.SqlServerCe.dll
Maybe this witches brew has at least something to do with my problems.
UPDATE 5
So, I thought, surely this is some cross between Frankenstein and Dracula that I've got here. These related files must all exist in the same location. So I looked at all of them and found this:
C:Program Files (x86)Microsoft.NETSDKCompactFrameworkv2.0WindowsCE
System.Data.dll == 2/14/2007
System.Data.SqlClient.dll == [not there]
System.Data.SqlServerCe.dll == [not there]
C:Program Files (x86)Microsoft SQL Server Compact Editionv3.5DevicesClient
System.Data.dll == [not there]
System.Data.SqlClient.dll == 7/3/2007
System.Data.SqlServerCe.dll == [not there]
C:Program Files (x86)Microsoft SQL Server Compact Editionv3.5Devices
System.Data.dll == [not there]
System.Data.SqlClient.dll == [not there]
System.Data.SqlServerCe.dll == 2/12/2010
Say it ain't so! Is this really how things stand - I have to get this file from here, that one from there, etc.?
UPDATE 6
In reponse to this comment from ctacke:
"the version of SQL CE you are referencing must match that on the device down to the revision. I can't tell you what it should be, just that they must be the same, and they should match down to the minor version whatever created the database file"
I see no SqlServerCe.dll on the device at all; there is a folder named "ComputerClay's Device\Program FilesMicrosoft SQL Server Compact Editionv3.5" that has the following DLLs:
sqlceca35.dll
sqlcecompact35.dll
sqlceer35EN.dll
sqlceme35.dll
sqlceoledb35.dll
sqlceqp35.dll
sqlcese35.dll
(all created 2/12/2010)
There is also a folder on the devie, "ComputerClay's Device\WindowsSQLCE 2.0" that contains files dated 8/16/2002 and 8/28/2002:
ssce20.dll
ssceca.dll
ssceerror20en.dll
ssceerror20jp.dll
I assume the last two are for displaying error messages, and I assume the last one is for japanese, and I don't think we have any Japanese customers (and certainly there is no localized version of the app in Japanese), so that's kind of baffling.
So there is no SqlServerCe.dll on the handheld device at all, so no conflict between versions is possible - unless SqlServerCe.dll is there somewhere but invisible?
UPDATE 7
As a followup to Update 5 and for completeness' sake (the first two DLL files may be "uninteresting"):
I searched my entire hard drive for the three files:
System.Data.dll was found, besides where noted above, in:
C:Program Files (x86)Microsoft Visual Studio .NET 2003CompactFrameworkSDKv1.0.5000WindowsCE
Creation Date = 11/24/2002; Size = 403KB
C:Program Files (x86)Microsoft.NETSDKCompactFrameworkv2.0D
