Indexerstellung mit SQL CE nicht möglich?

[Dirk Michaelsen]

Guten Morgen,

ich habe eine C#-Applikation geschrieben, die eine SQL Server Compact
Edition Datenbank für eine MDE-Anwendung erstellt. Das Füllen der
Datenbank funktioniert auch soweit sehr gut. Lediglich die Erstellung
der Indizes will einfach nicht funktionieren.

Zur Erstellung der Indizes verwende ich folgende Methode:

--- snip ---
private SqlCeConnection conn;
private SqlCeTransaction trans;

public void GenerateIndices()
{
Console.WriteLine("Erstelle Indizes");
try
{
trans = conn.BeginTransaction();

string[] indexDefinitions =
{
"CREATE INDEX ArtstmId ON Artikel (artstm_id)",
"CREATE INDEX ArtikelNr ON Artikel (artikel_nr)",
"CREATE INDEX MestmId ON Mengeneinheiten (mestm_id)",
"CREATE INDEX ArtstmId ON Mengeneinheiten
(artstm_artstm_id)",
"CREATE INDEX MestmId ON Ean (mestm_mestm_id)",
"CREATE INDEX Ean ON Ean (ean)",
"CREATE INDEX MestmId ON Konditionen (mestm_mestm_id)"
};

foreach (string index in indexDefinitions)
{
Console.WriteLine("sende SQL-Command: " + index);
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = index;
cmd.ExecuteNonQuery(); // dies ist Zeile 140
}
trans.Commit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
--- snip ---

Bereits beim ersten Aufruf von cmd.ExecuteNonQuery() springt die
Anwendung in den Catch-Block mit der relativ nichtssagenden Meldung
"[Artikel]" und dem folgenden Exception-Stacktrace:

$exception {" [ Artikel ]"} System.Exception
{System.Data.SqlServerCe.SqlCeException}
bei System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
bei System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr&
pCursor, Boolean& isBaseTableCursor)
bei
System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior
behavior, String method, ResultSetOptions options)
bei System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
bei MDEDatenImport.DataConnection.GenerateIndices() in C:\Dokumente
und Einstellungen\dimich\Eigene Dateien\Visual Studio
2005\Projects\MDEDatenImport\MDEDatenImport\DataConnection.cs:Zeile
140.

Was mache ich falsch?

Gruß
Dirk

 

[Zachovich]

Ist es möglich so wirst du einfach müssen "code snip" auf englisch
verdolmetschen, so du eine behilflige antwort becommen willst auf diese
newsgroup.

Entschuldigung bitte.

 

[Dirk Michaelsen]

I' very sorry. My fault was I made a crosspost to a German language
newsgroup. ;-)

The problem is this: i wrote a C# application with Visual Studio 2005
Professional that creates a SQL Server Compact Edition database. The
database is copied to a smart device (Windows Mobile 5) after its
creation. Creating and filling of the database works like a charm but
unfortunately I'm unable to crate indexes on the tables.

I'm using the following code to create the indexes:

--- snip ---
private SqlCeConnection conn;
private SqlCeTransaction trans;

public void GenerateIndices()
{
Console.WriteLine("Creating indexes");
try
{
trans = conn.BeginTransaction();

string[] indexDefinitions =
{
"CREATE INDEX ArtstmId ON Artikel (artstm_id)",
"CREATE INDEX ArtikelNr ON Artikel (artikel_nr)",
"CREATE INDEX MestmId ON Mengeneinheiten (mestm_id)",
"CREATE INDEX ArtstmId ON Mengeneinheiten
(artstm_artstm_id)",
"CREATE INDEX MestmId ON Ean (mestm_mestm_id)",
"CREATE INDEX Ean ON Ean (ean)",
"CREATE INDEX MestmId ON Konditionen (mestm_mestm_id)"
};

foreach (string index in indexDefinitions)
{
Console.WriteLine("sending SQL-Command: " + index);
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = index;
cmd.ExecuteNonQuery(); // --> this is line 140
}
trans.Commit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
--- snip ---

Already the first call of cmd.ExecuteNonQuery() leads to an exception
with the non-verbose message "[Artikel]" and this stacktrace:

---
$exception {" [ Artikel ]"} System.Exception
{System.Data.SqlServerCe.SqlCeException}
bei System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
bei
System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr&
pCursor, Boolean& isBaseTableCursor)
bei
System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior
behavior, String method, ResultSetOptions options)
bei System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
bei MDEDatenImport.DataConnection.GenerateIndices() in
C:\Dokumente
und Einstellungen\dimich\Eigene Dateien\Visual Studio
2005\Projects\MDEDatenImport\MDEDatenImport\DataConnection.cs:Zeile
140.
---

I don't know what I'm doing wrong. Consulting the MSDN documentation
this should work with SQL CE.

Can it be true that index creation is not possible with SQL CE
although MSDN claims it should be?

cu
Dirk

 

[Christopher Fairbairn]

Hi,

Already the first call of cmd.ExecuteNonQuery() leads to an exception
with the non-verbose message "[Artikel]" and this stacktrace:

The SqlCeException thrown should have a NativeError property. This error
code is important and will help clarify what is going on. If you search for
the NativeError code in a table such as the "Engine Errors" table available
at http://technet.microsoft.com/en-us/library/aa256772(SQL.80).aspx you
should hopefully get a better idea of what the problem is.

Hope this helps,
Christopher Fairbairn

 

[Dirk Michaelsen]

Hi Christopher,

thanks for your reply but unfortunately the NativeError property is 0.

There are two other properties that look like error codes:

InnerException -> Static members -> Non-public-members ->
_COMPlusExceptionCode = -532459699

Non-public-members -> _HResult = -2146233087

Does that help any way?

Dirk Michaelsen

 

[Dirk Michaelsen]

It's me again

I solved the problem.

The _HResult code led me to the problem that the DTC service was not
started on my computer. After starting the service I got another
exception with _HResult code -2147217856 wich means "the specified
table was in use".

I then tried to disconnect from the database after filling it with
data and reconnected before I started indexing. That worked.

Dirk Michaelsen