FIX: SQL Server CE eMbedded Visual Basic CompactDatabase Unable to Fix Corrupted Database (306452)

MORE INFORMATION

Private Sub Command1_Click()
On Error Resume Next
Dim DBEngine As Object
Dim oFS As Object
Dim srcConn As String
Dim destConn As String
Dim txtlog as String

Err = False
srcConn = "Provider=Microsoft.SQLServer.OLEDB.CE.1.0;Data Source=\sourceDB.sdf"
destConn = "Provider=Microsoft.SQLServer.OLEDB.CE.1.0;Data Source=\destinDB.sdf"
Set oFS = CreateObject("Filectl.FileSystem")

' Make sure there isn't already a file with the name of the compacted database.
If oFS.Dir("destinDB.sdf") <> "" Then
    oFS.Kill "destinDB.sdf"
    txtlog.Text = "drop destinDB.sdf complete" & vbCrLf
End If

Set DBEngine = CreateObject("SSCE.Engine.1.0")

' This statement creates a compact version of the database
DBEngine.CompactDatabase srcConn, destConn
If Err Then
    txtlog = txtlog & "Error: " & Err.Number & " " & Err.Description
Else
    txtlog = txtlog & "compactDB complete" & vbCrLf
    oFS.Kill "\sourceDB.sdf"
    txtlog = txtlog & "drop \sourceDB.sdf complete" & vbCrLf
    oFS.MoveFile "destinDB.sdf", "sourceDB.sdf"
End If

msgbox txtlog

Set DBEngine = Nothing
Set oFS = Nothing
End Sub
				

1. Create a .cpp file and open it in eVC++.
2. Edit the path to the source and the destination database on the device and specify a password (if you require a password).
3. Use the Build menu option to create a default project workspace.
4. Specify the correct settings for the CPU your device is using. As an example, use the project settings for the eVC++ OLEDBSeek sample installed in the C:\Program Files\Microsoft SQL Server CE\Samples\eVC folder.
5. You should see the following libraries in the Object Library module:
   • Commctrl.lib
   • Coredll.lib
   • Ole32.lib
   • Oleaut32.lib
   • Uuid.lib
   • Oledb.lib
   
   
6. Set the path to the appropriate Include and Library files for the device's CPU type. To add the files, from the Tools menu, click Options, and then click Directories.
7. Build the project, and then run the EXE on the device. Here is the C++ OLE DB code:

   #define OLEDBVER 0x210
   #define DBINITCONSTANTS
   #define INITGUID
   
   #include <oledb.h>
   #include "ssceoledb.h"
   
   //Path to source and destination database
   const WCHAR wszDbSrc[] = L"sourceDB.sdf";
   const WCHAR wszDbDst[] = L"destinDB.sd2";
   const WCHAR wszDbPwd[] = L"";  //specify password if the database has a password
   
   #ifdef REAL_WIN32
   int WINAPI WinMain(HINSTANCE hInstance, HINSTANCE hPrevInstance, LPSTR lpCmdLine, int nCmdShow)
   #else
   int WINAPI WinMain(HINSTANCE hInstance, HINSTANCE hPrevInstance, LPWSTR pwszCmdLine, int nShowCmd)
   #endif //REAL_WIN32
   {
   	HRESULT				hr = NOERROR;					// Error code reporting
   	DBPROPSET			dbpropset[2];					// Property Set used to initialize provider
   	DBPROP				dbprop[2];						// property array used in property set to initialize provider
   
   	// Provider Interfaces
   	IDBProperties *		pIDBProperties = NULL;
   	IDBCreateSession *	pIDBCreateSession = NULL;
   	ISSCECompact		*pISSCECompact	= NULL;
   	IDBSchemaRowset *	pIDBSchemaRowset = NULL;
   
   	VariantInit(&dbprop[0].vValue);
   
   	// Initialize environment
   #ifdef REAL_WIN32
   	if (FAILED(hr = CoInitialize(NULL)))
   #else
   		if (FAILED(hr = CoInitializeEx(NULL, COINIT_MULTITHREADED)))
   #endif //REAL_WIN32
   		{
   		//wcerr << L"COM Initialization Failure." << endl;
   		goto Exit;
   		}
   
   	// Create an instance of the OLE DB Provider for SSCE 1.0
   	hr = CoCreateInstance(CLSID_SQLSERVERCE_1_0, 0, CLSCTX_INPROC_SERVER, IID_IDBProperties, (void**) &pIDBProperties);
   	if(FAILED(hr))
   		{
   		//wcerr << L"Failed to instantiate OLE DB Provider for SSCE10" << endl;
   		goto Exit;
   		}
   
   	// Initialize Property with name of database
   	dbprop[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
   	dbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
   	dbprop[0].vValue.vt = VT_BSTR;
   	dbprop[0].vValue.bstrVal = SysAllocString(wszDbSrc);
   	if(NULL == dbprop[0].vValue.bstrVal)
   		{
   		hr = E_OUTOFMEMORY;
   		goto Exit;
   		}
   	// +++++++++++++ Initialize Database Password Property
   	dbprop[1].dwPropertyID = DBPROP_SSCE_DBPASSWORD;
   	dbprop[1].dwOptions = DBPROPOPTIONS_REQUIRED;
   	dbprop[1].vValue.vt = VT_BSTR;
   	dbprop[1].vValue.bstrVal = SysAllocString(wszDbPwd);
   
   	// Initialize property set
   	dbpropset[0].guidPropertySet = DBPROPSET_DBINIT;
   	dbpropset[0].rgProperties = dbprop;
   	dbpropset[0].cProperties = 1;
   ;
   	dbpropset[1].guidPropertySet = DBPROPSET_SSCE_DBINIT;
   	dbpropset[1].rgProperties = &dbprop[1];
   	dbpropset[1].cProperties = 1;
   
   	// Set properties into the provider's DSO object
   	hr = pIDBProperties->SetProperties(2, dbpropset);
   	if(FAILED(hr))
   		{
   		//wcerr << L"Failed to set properties." << endl;
   		goto Exit;
   		}
   
   	// Get IDBCreateSession interface
   	hr = pIDBProperties->QueryInterface(IID_ISSCECompact, (void **) &pISSCECompact);
   	if(FAILED(hr))
   		goto Exit;
   
   	DeleteFile(wszDbDst);
   
   	SysFreeString(dbprop[0].vValue.bstrVal);
   	dbprop[0].vValue.bstrVal = SysAllocString(wszDbDst);
   	if(NULL == dbprop[0].vValue.bstrVal)
   		{
   		hr = E_OUTOFMEMORY;
   		goto Exit;
   		}
   
   	hr = pISSCECompact->Compact(1, dbpropset);
   	if(FAILED(hr))
   		{
   		goto Exit;
   		}
   
   Exit:
   
   	if(FAILED(hr))
   	{
   		WCHAR wszTemp[20];
   		//char wszTemp[20];
   		MessageBoxW(NULL, _itow(hr, wszTemp, 16), L"FAILED", MB_OK | MB_SETFOREGROUND);
   		//MessageBoxA(NULL, _itoa(hr, wszTemp, 16), "FAILED", MB_OK | MB_SETFOREGROUND);
   	}
   
   	VariantClear(&dbprop[0].vValue);
   
   	// Release interfaces
   	if(NULL != pISSCECompact)
   		{
   		pISSCECompact->Release();
   		}
   	if(NULL != pIDBSchemaRowset)
   		{
   		pIDBSchemaRowset->Release();
   		}
   	if(NULL != pIDBCreateSession)
   		{
   		pIDBCreateSession->Release();
   		}
   	if(NULL != pIDBProperties)
   		{
   		pIDBProperties->Release();
   		}
   	
   	// Uninitialize the environment
   	CoUninitialize();
   	
   	return 0;
   }