Article ID: 306452
Article Last Modified on 2/12/2007
APPLIES TO
- Microsoft SQL Server 2000 Windows CE Edition 2.0
- Microsoft Encarta Interactive World Atlas 2001
- Microsoft SQL Server 2000 Windows CE Edition 1.1
This article was previously published under Q306452
SYMPTOMS
A corrupted database may produce the following error message in the Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE) Query Analyzer if you attempt to open a connection:
The error code is SSCE_M_DATABASECORRUPTED (Non-database file or database file corrupted).
A Microsoft embedded Visual Basic (eVB) application that uses the CompactDatabase method is not able to compact a corrupted SQL Server CE database. An attempt to call the CompactDatabase method may return the following error message:
CAUSE
The engine ActiveX code for CompactDatabase tries to initialize the source database before compacting the source database. If the initialize fails, the CompactDatabase method aborts. Hence, you cannot use the CompactDatabase method on a corrupted database because the Initialize method on a corrupted database always fails.
RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000 Windows CE Edition. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
316498 INF: How to Obtain the Latest SQL Server 2000 Windows CE Edition Service Pack
WORKAROUND
To work around this problem use the Microsoft embedded Visual C ++ (eVC++) to implement the OLE DB Compact method.
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft SQL Server 2000 Windows CE Edition Service Pack 1.
MORE INFORMATION
The following eVB code compacts a SQL Server CE database. However, the code may not fix a corrupted database:
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
OLE DB Application in eVC++ Using Compact Method
The following eVC++ OLE DB code compacts a SQL Server CE database. This code can also fix a corrupted database. To build a compact tool for a specific device, use these steps:
- Create a .cpp file and open it in eVC++.
- Edit the path to the source and the destination database on the device and specify a password (if you require a password).
- Use the Build menu option to create a default project workspace.
- 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.
- You should see the following libraries in the Object Library module:
- Commctrl.lib
- Coredll.lib
- Ole32.lib
- Oleaut32.lib
- Uuid.lib
- Oledb.lib
- 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.
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; }
Keywords: kbbug kbfix kbsqlserv2000ceesp1fix KB306452