When trying to use SQL Server Compact Edition to replace SQL Server modifying just the connect string my application is unable to access the .sdf database file successfully.
I would like to have additional information in the following areas:
- what is the process for changing from SQL Server to SQL Server Compact Edition
- this process needs to be for an older C++ application using ADO
- do I need to change from ADO to instantiating a COM object (please tell me it ain't so)
- what should the Provider= value be for SQL Server Compact Edition 2005
Things I have done:
- I have installed SQL Server Compact Edition 4.0 though I am not sure it was necessary
- I have tried various connect strings
- I am currently using Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0 which throws an exception
I have read enough Microsoft web pages that are unhelpful so please do not provide a link to any of the short cryptic articles that appear to assume a modern source code base. I have spent enough time perusing connection strings at connectionstring.com so please do not provide any links to there.
I have an old C++ and C application that has a DLL which accesses an SQL Server database using a connect string which works with several versions of SQL Server (the old MSDE as well as the new SQL Server Express 2012).
ConnectionStringNoDB = L"Provider=MSDASQL;DRIVER={SQL Server};SERVER=lpc:(local);DATABASE=;UID=; Password=;";
I am now looking to replace SQL Server with SQL Server Compact Edition. Thus far what I have been able to determine is that I need to modify my connect string to use a different provider such as this one.
ConnectionStringNoDB = L"Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=MyDatabase#1.sdf;";
However when I attempt to do an open using the above connect string, the open fails throwing an exception with an hResult of E_FAIL and the error text from the exception object thrown is "Unspecified error". Using the VS 2005 debugger, I catch an exception being thrown with an error dialog that says "First-chance exception Microsoft C++ exception: _com_error at memory location 0x00128f10".
When I use the VS 2005 tool from the Tools menu "Connect to database..." menu item I can set data source to "Microsoft SQL Server 2005 Compact Edition" with a data provider of ".NET Framework Data Provider for SQL Server Compact Edition".
The actual source for the open is as follows
HRESULT hr = NULL;
CString errorMessage;
_bstr_t userId = "", passWord = "";
try
{
if (m_pConnection->State != adStateOpen)
{
m_pConnection->ConnectionString = ActiveConnection;
m_pConnection->Open(ActiveConnection, userId, passWord, -1);
}
hr = m_pRecordSet->Open((VARIANT)Source, _variant_t((IDispatch *)m_pConnection),CursorType,LockType,Options);
}
catch( _com_error &e)
{
_bstr_t bstrSource(e.Description());
TCHAR *description;
description = bstrSource;
errorMessage.Format(_T("ERROR OpenRec %s %s"), e.ErrorMessage(), description);
hr = e.Error();
}
where the m_pConnection and the m_pRecordSet objects are:
_RecordsetPtr m_pRecordSet; // recordset object
_ConnectionPtr m_pConnection;
When I run my application in the VS 2005 debugger I can see the following lines in the Output window which indicate to me that the SQL Server Compact Edition 4.0 is being accessed and loaded.
'Framework.exe': Loaded 'C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\sqlceer40EN.dll', No symbols loaded.
'Framework.exe': Loaded 'C:\WINDOWS\system32\comsvcs.dll', No symbols loaded.
'Framework.exe': Loaded 'C:\WINDOWS\system32\colbact.dll', No symbols loaded.
'Framework.exe': Loaded 'C:\WINDOWS\system32\mtxclu.dll', No symbols loaded.
'Framework.exe': Loaded 'C:\WINDOWS\system32\wsock32.dll', No symbols loaded.
'Framework.exe': Loaded 'C:\WINDOWS\system32\clusapi.dll', No symbols loaded.
'Framework.exe': Loaded 'C:\WINDOWS\system32\resutils.dll', No symbols loaded.
'Framework.exe': Loaded 'C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\sqlceqp40.dll', No symbols loaded.
'Framework.exe': Loaded 'C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\sqlcese40.dll', No symbols loaded.
First-chance exception at 0x7c812fd3 in Framework.exe: Microsoft C++ exception: _com_error at memory location 0x00128f10..
c:\users\rchamber\genpos_workspaces\rel_221\nhpos\plutotal\cnadoxp.h(426): ERROR OpenRec Unspecified error (null)
EDIT Aug-28-2013
One other thing I wondered was if the provider was actually being found. When I used Add or Remove Programs to remove SQL Compact Edition 4.0 and then modified the connect string to "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0" the open fails with an error message of "ERROR OpenRec Unknown error 0x800A0E7A Provider cannot be found. It may not be properly installed." as expected. This indicates to me that the connection string is valid in that it is finding the provider however there is some other error.
I am wondering if there may be a dependency on .NET however checking Add or Remove Programs I do have Microsoft .NET Framework 4 Client installed.
When I have changed the connect string to use SQL Compact Edition 3.5 I get the same error message of an Unspecified error. Checking Add or Remove Programs, I do have Microsoft .NET Framework 3.5 SP1 installed as well as Microsoft SQL Server Compact 3.5 SP1 English installed.
