BULK COPY mit Delphi für SQL 2005

[Thomas Rabold]

Für Massendatenkopieren mit Delphi in eine MS SQL2005 Datenbank habe ich bislang BULK COPY über die Unit MSSQL und die Klasse TSQL gearbeitet.
Diese verwendet aber die DLL NTWDBLIB.DLL, welche im MS SQL2005 nicht mehr zur Verfügung steht. Dafür wird dort auf die Verwendung von BULK COPY über ODBC verwiesen.

Kann mir jemand über die Verwendung ein Beispielprogramm liefern ?? Oder eine passende Unit ?

Danke !

Thomas

[Andreas Kosch]

Hallo,
prinzipiell stehen verschiedene Wege zur Verfügung:
<br>
1. Der modernste Weg (.NET Framework 2.0): <b>SqlBulkCopy</b>-Klasse
<code>
DataSet aDS = <b>new</b> DataSet();
<font color="#003399"><i>// Beispieldaten einlesen</i></font>
aDS.ReadXml(@<font color="#9933CC">&quot;C:\Temp\BulkCopyData.xml&quo t;</font>);
dataGridView1.DataSource = aDS.Tables[0];
MessageBox.Show(aDS.Tables[0].Rows.Count.ToString());
<b>string</b> sCS = <font color="#9933CC">&quot;Server=(local);Integrated Security=True;Database=tempdb&quot;</font>;
SqlBulkCopy bcp = <b>new</b> SqlBulkCopy(sCS);
bcp.DestinationTableName = <font color="#9933CC">&quot;SQLBULKCOPYDEMO&quot;</font>;
bcp.WriteToServer(aDS.Tables[0]);
statusBar1.Text = <font color="#9933CC">&quot;BulkCopy-Aufruf ist beendet.&quot;</font>;
</code>
2. Der SQL-Weg:
<code>
<b>BULK INSERT</b>
OSTEST.dbo.Ziel
<b>FROM</b>
<font color="#9933CC">'C:\Temp\Quelle.bcp'</font>
<b>WITH</b>
(
DATAFILETYPE = <font color="#9933CC">'widenative'</font>,
BATCHSIZE = 10000,
TABLOCK
)
</code>
3. Der Kommandozeilen-Weg:
<code>
bcp "Zieldatenbank.dbo.Zieltabelle" in "Quelldatei.dat" -S ServerName -T -k -n -q
</code&gt

[Thomas Rabold]

CodePageProblem beim direkten BulkCopy

Hallo,

erstmal Danke für die Antwort. Allerdings haben wir keine der drei Varianten verwendet. Alle 3 varianten schaufeln die Daten erstmal in Files. Dies dauert bei echten Massendaten zu lange und ist ja auch eigentlich sinnleer, da der BulkCopy diese wenige Hundertstel später wieder liest.
Wir haben deshalb die NTWDBLIB.DLL mit ausgeliefert, wodurch der direkte BulkCopy auch beim SQL2005 funktioniert.
Nun haben wir aber eine Konstellation, in der die Umlaute nicht korrekt importiert werden. Eine Vorschau der Daten (ShowMessage) ist korrekt, im SQL-Server kommen die Umlaute aber falsch an.
Gibt es auch für den direkten BulkCopy eine Möglichkeit, die CodePage mitzugeben? Oder liegt die Ursache woanders?

Danke !

Thomas

[Bernhard Geyer]

Wenn du prepareds INSERT-Statements mit parametern verwendest und mehrer in einer SQL-Anweisung an die Datenbank abschickst bekommst du auch ziemlich gute Werte hin.

[Thomas Rabold]

INSERT Statements?
Wir schaufeln jede Nacht etwa 90 GigaByte Daten aus einem SAP-System in eine SQL-Datenbank. Ich kann mir nicht vorstellen, dass diese Daten alle in SQL-Statements umgewandelt und abgeschickt werden. Vom Transaktionslog mal ganz zu schweigen. In der gleichen Nacht werden die Daten zum Kunden repliziert...
Allerdings gibt es beim INSERT korrekte Umlaute ;-(

Mich interessiert welche Faktoren die Umsetzung von Sonderzeichen beim direkten BulkCopy beeinflussen. Die Änderung der Länderkennung des SQL-Benutzers hat genausowenig Einfluss gehabt wie die Umstellung des Windows-Users.
Bislang hatten wir nur englische Server mit englischem SQL2005. Das Problem tritt aktuell auf einem deutschen Windows-Server mit deutschem SQL2005 auf.

Könnte die SQL-Server-Version (dt./engl) einen Einfluss haben, oder die Version des Windows-Servers? Gibt es von ntwdblib.dll andersprachige Versionen?

Für sachdienliche Hinweise bin ich immer dankbar !

MfG, Thomas

[Bernhard Geyer]

INSERT Statements?
Wir schaufeln jede Nacht etwa 90 GigaByte Daten aus einem SAP-System in eine SQL-Datenbank. Ich kann mir nicht vorstellen, dass diese Daten alle in SQL-Statements umgewandelt und abgeschickt werden. Vom Transaktionslog mal ganz zu schweigen. In der gleichen Nacht werden die Daten zum Kunden repliziert...
Allerdings gibt es beim INSERT korrekte Umlaute ;-(

Evtl. den Prozess incrementell aufsetzen? Ich glaube nicht das ihr jeden Tag 90 GB neue Daten habt.
Wenn schon über INSERTS dann mußt du ähnlich wie beim BULK INSERT das Transaktionslog und die Transaktionen abschalten. Befehle dafür kenn ich nicht.

Mich interessiert welche Faktoren die Umsetzung von Sonderzeichen beim direkten BulkCopy beeinflussen.

Erzeug mal deine Datei als Unicode/UTF8-Datei (mit korrekten BOM) so das der Leseprozess die Codierung nicht raten muss.

[Bernhard Geyer]

Ach ja: 90 GB. Wieviele Datensätze wären das?

[Thomas Rabold]

90 GB sind derzeit ca. 110 Mio Datensätze in 170 verschiedenen Tabellen.
Die Änderungen fischen wir über ein technisches Deltaverfahren auf SAP-Seite heraus. Das sind aber immer noch wenigstens 14 bis 20 Mio Datensätze täglich. An Buchungstagen auch deutlich mehr. Irgendwann geht auch mal was schief, dann muss alles komplett neu gelesen werden - in einer Nacht!
Kurzum, ohne BulkCopy ist das nicht machbar. Für den reinen Import haben wir höchstens eine Stunde Zeit.
Bei anderen Anwendungen verwenden wir auch das ODBC-BulkCopy, also über Dateien. Das ist allerdings deutlich langsamer. Allein das Schreiben auf die Platte kostet da viel Zeit. Der Hauptspeicher ist halt 1000 mal schneller als die Platte. Deshalb möchte wir das Problem gern lösen - nicht umgehen indem wir das Verfahren wechseln und mit längerer Laufzeit bezahlen.

Die offenen Fragen bleiben wie bei meinen beiden Beiträge zuvor.

MfG, Thomas Rabold

[Bernhard Geyer]

Das sind aber immer noch wenigstens 14 bis 20 Mio Datensätze täglich.

Arbeitest du für eine Börse/Bank? Welche Firma hat schon 14-20 Mio. Änderungen am Datenbestand pro Tag.

Eine Hauptbremse der INSERTS gegenüber einem BULK-Copy ist der Netzwerk-Round-Trip-Delay der bei einem BULK-Copy nicht anfällt. Wenn du also die Inserts auf der gleichen Maschine wie die DB ablaufen lassen kannst wird es auch schneller. Aber 30000. INSERTS pro Sekunde sind nicht zu verachten. Hier muß ja der SQL-Server in einer Art Single-Connection ohne Transaktiossupport-Modus fallen. Denn Trigger und Referenzielle Integrität muß ja trotzdem beachtet werdn. Evtl. kann man diesen Modus auch für die INSERT-Connection bekommen.

[Thomas Rabold]

Sorry Bernhard, aber es geht mir hier nicht darum die Vorteile des BulkCopy-Verfahrens gegenüber einem INSERT darzustellen. Es geht mir auch nicht darum zu erklären, wie man das Transaktionslog abschaltet. Beides steht in der SQL-Hilfe und sicherlich in vielen Forenbeiträgen.

Mir geht es darum herauszufinden, wie man die CodePage beim direkten BulkCopy (ohne Dateien) beeinflussen kann.
Falls jemand hier helfen kann - bitte antworten.

[Thomas Rabold]

Das CodePage-Problem beim Importieren von Massendaten mit einem direkten BulkCopy (ohne Dateien zu schreiben) ist gelöst.

Beim SQL Server 2000 gibt es die NTWDBLIB.DLL, die die notwendigen BulkCopy Funktionen enthält.
Standardmäßig ist für SQL2000 ein Eintrag in der SQLServerClient-Konfiguration auf 'automatische Konvertierung von ANSI nach OEM' gesetzt. Das zugehörige Programm findet sich unter Start/Programme/MS SQL 2000/ClientKonfiguration.

Anders ist es beim SQL2005. Hier wird die DLL von Microsoft nicht mehr mit ausgeliefert - funktioniert aber immer noch. Das Konfigurationsprogramm ist nicht mehr unter Start/Programme zu finden, aber als cliconfg.exe unter c:\windows\system32.
Und das schlimmste: Der Eintrag steht bei SQL2005 standardmäßig nicht mehr auf 'automatische Konvertierung'. Erst nach Setzen des Hakens kommen auch die Umlaute und Sonderzeichen wie gewohnt im SQL-Server an.

Wir suchen nun nach einer Möglichkeit, den Eintrag AutoANSItoOEM automatisch setzen zu lassen (Registry oder SQL-Befehl oder ?).

Danke !