Datenbanken und mehr

13. Dezember 2007

Upload-Only Artikel in Merge Replikation

Filed under: Replikation — Urs Gehrig @ 21:02

Musstes du in einer Merge Replikation auch schon Datensätze nur vom Subscriber zum Publisher schicken; mit Betonung auf nur. Wofür ich das brauche? Zum Beispiel in einer Mobilen Applikation, mit welcher auf dem Mobile Device Stundenrapporte vor Ort beim Kunde erfasst werden. Sobald der Mitarbeiter wieder im Office ist, müssen diese Rapporte auf den Server, damit seine Leistungen auch fakturiert werden können. Ein runterholen von alten Rapporten auf das Device macht aber absolut keinen Sinn, verbraucht auf dem Device nur unnötig kostbaren Speicherplatz und kann erst noch ein Datenschutzproblem beinhalten. Kurzum; Upload-Only Artikel machen absolut Sinn!

Leider kennt SQL Server 2005 kein Upload-Only Property für Artikel; ganz im Gegensatz zu Download-Only. Dies ist aber kein Beinbruch – mit einem kleinen Trick schaffen wir auch dies. Das ganze kann mit einem einfachen static row filter realisiert werden:

WHERE 1 = 2

Wie funktioniert dies? Ganz einfach: Beim Runterladen vom Publisher zum Subscriber wird das Filter angewandt; eins kann aber nie gleich zwei sein und somit kommt keine einzige Row zum Subscriber runter. Anders sieht es beim Hochladen aus. Dort werden nämlich immer alle Änderungen zum Publisher gesandt; die Filter werden nicht berücksichtigt. Somit kommen die erfassten Rapporte zum Server. Beim nächsten Runterladen werden die ‚alten‘ Rapporte auf dem Device gelöscht, da ja keine Row unser Filterkriterium erfüllen kann. Et voliá – das war’s, einfach und effizient.

Advertisements

11. Dezember 2007

Neu in Katmaii – GROUPING SETS

Filed under: Katmai, T-SQL — Urs Gehrig @ 08:54

Hast du schon mal probiert, mit SQL Server 2005 in ein und demselben Query mehrere GROUP BY Kriterien miteinander zu verbinden? Mit CUBE und ROLLUP kommst du bestimmt schon weit, mit der in Katmaii neuen GROUPING SETS Klausel sind dir nun aber praktisch keine Grenzen mehr gesetzt. Warum die ganzen Kriterien in bloss einem SQL Statement und nicht mit mehren, welche du mit UNION zusammen fügst? Naja, geht bestimmt, aber wir wollen ja ein effizientes Query. Nun aber zu GROUPING SETS. Fangen wir mit einem Beispiel an:

USE AdventureWorks;
GO

SELECT CustomerID
    
, SalesPersonID
    
, YEAR(OrderDate) AS OrderYear
    
, MONTH(OrderDate) AS OrderMonth
    
, SUM(SubTotal) AS Total
FROM Sales.SalesOrderHeader
WHERE CustomerID < 10
GROUP BY
GROUPING SETS
(
 
(CustomerID, SalesPersonID),
 
(CustomerID),
 
(SalesPersonID)
),
GROUPING SETS
(
 
(YEAR(OrderDate), MONTH(OrderDate)),
 (YEAR(OrderDate))
)
ORDER BY CustomerID, SalesPersonID, OrderYear, OrderMonth

Was macht dieses Query? Nichts anderes als insgesamt sechs verschiedene GROUP BY’s. Nämlich

  1. GROUP BY CustomerID, SalesPersonID, YEAR(OrderDate), MONTH(OrderDate)
  2. GROUP BY CustomerID, SalesPersonID, YEAR(OrderDate)
  3. GROUP BY CustomerID, YEAR(OrderDate), MONTH(OrderDate)
  4. GROUP BY CustomerID, YEAR(OrderDate)
  5. GROUP BY SalesPersonID, YEAR(OrderDate), MONTH(OrderDate)
  6. GROUP BY SalesPersonID, YEAR(OrderDate)

Kommt in einer GROUB BY Klausel ein einzelnes Feld nicht vor, erhält das Resultat an der entsprechenden Stelle den Wert NULL. Das Ergebnis aus dem obigen Query sieht in etwa wie folgt aus; damit denke ich, dass du die Funktionsweise von GROUPING SETS nun verstehst, auch ohne dass ich hierzu noch mehr Worte verliere:

CustomerID SalesPersonID OrderYear OrderMonth Total
NULL 268 2002 7 3569.4338
NULL 275 2003 1 97037.566
NULL 275 2003 4 94133.8413
NULL 275 2003 7 73368.0931
NULL 275 2003 10 73455.3404
NULL 275 2004 NULL 176878.6938
NULL 275 2004 3 15431.5196
NULL 275 2004 6 31559.7518
NULL 276 2002 10 2449.6033
NULL 276 2003 1 1769.1174
NULL 276 2003 9 59.9326
NULL 276 2004 NULL 2696.2418
NULL 276 2004 6 2137.231
NULL 277 2001 9 18555.8149
NULL 277 2002 NULL 390424.4465
NULL 277 2002 6 11796.1804
NULL 277 2002 9 71112.1316
NULL 277 2002 12 51198.5404
NULL 279 2002 11 290.1614
NULL 279 2003 2 4903.775
NULL 279 2003 8 10309.0561
NULL 279 2004 NULL 14645.1583
NULL 279 2004 5 8441.6436
NULL 280 2001 8 13216.0537
NULL 280 2002 NULL 65489.709
NULL 280 2002 5 31423.5209
NULL 281 2002 9 20720.6175
NULL 281 2003 NULL 54485.9727
NULL 281 2003 6 9134.6608
NULL 281 2003 12 18431.5666
NULL 281 2004 3 7950.2303
NULL 283 2002 NULL 14166.2185
NULL 283 2002 11 4949.8589
NULL 283 2003 2 1574.1247
NULL 283 2003 8 3534.1671
NULL 283 2004 NULL 4490.7426
NULL 283 2004 5 822.009
1 NULL 2001 8 13216.0537
1 NULL 2002 NULL 65489.709
1 NULL 2002 5 31423.5209
1 280 2001 8 13216.0537
1 280 2002 NULL 65489.709
1 280 2002 5 31423.5209
2 NULL 2002 8 9216.3596
2 NULL 2003 NULL 10966.5406
2 NULL 2003 5 1751.5987
2 NULL 2003 11 4106.6501
2 NULL 2004 2 3668.7336
2 283 2002 NULL 14166.2185
2 283 2002 11 4949.8589
2 283 2003 2 1574.1247
2 283 2003 8 3534.1671
2 283 2004 NULL 4490.7426
2 283 2004 5 822.009
3 NULL 2001 9 18555.8149
3 NULL 2002 NULL 152392.4906
3 NULL 2002 6 11796.1804
3 NULL 2002 12 51198.5404
3 NULL 2003 3 27899.9579
3 NULL 2003 9 83436.1754
3 NULL 2004 NULL 46991.2714
3 NULL 2004 6 31559.7518
3 275 2003 3 27899.9579
3 275 2003 9 83436.1754
3 275 2004 NULL 46991.2714
3 275 2004 6 31559.7518
3 277 2001 9 18555.8149
3 277 2002 NULL 152392.4906
3 277 2002 6 11796.1804
3 277 2002 12 51198.5404
4 NULL 2002 7 118394.1258
4 NULL 2003 NULL 337994.8408
4 NULL 2003 4 94133.8413
4 NULL 2003 10 73455.3404
4 NULL 2004 1 67414.7924
4 275 2003 NULL 337994.8408
4 275 2003 4 94133.8413
4 275 2003 10 73455.3404
4 275 2004 1 67414.7924
4 277 2002 NULL 238031.9559
4 277 2002 10 119637.8301
5 NULL 2002 9 20720.6175
5 NULL 2003 NULL 54485.9727
5 NULL 2003 6 9134.6608
5 NULL 2003 12 18431.5666
5 NULL 2004 3 7950.2303
5 281 2002 NULL 30199.7195
5 281 2002 12 9479.102
5 281 2003 3 8456.292
5 281 2003 9 18463.4533
5 281 2004 NULL 18679.7381
5 281 2004 6 10729.5078
6 NULL 2003 9 59.9326
6 NULL 2004 NULL 2696.2418
6 NULL 2004 6 2137.231
6 276 2003 9 59.9326
6 276 2004 NULL 2696.2418
6 276 2004 6 2137.231
7 NULL 2002 7 3569.4338
7 NULL 2003 NULL 3365.4121
7 NULL 2003 4 1596.2947
7 268 2002 7 3569.4338
7 276 2002 10 2449.6033
7 276 2003 1 1769.1174
8 NULL 2003 NULL 17592.0783
8 NULL 2003 11 8888.6076
8 NULL 2004 2 5571.0288
8 279 2003 NULL 17592.0783
8 279 2003 11 8888.6076
8 279 2004 2 5571.0288
9 NULL 2002 NULL 290.1614
9 NULL 2003 NULL 10318.1878
9 NULL 2003 5 586.989
9 NULL 2003 11 3221.8384
9 NULL 2004 2 632.4859
9 279 2002 NULL 290.1614
9 279 2003 NULL 10318.1878
9 279 2003 5 586.989
9 279 2003 11 3221.8384
9 279 2004 2 632.4859

23. November 2007

Data Change Capture

Filed under: Katmai — Urs Gehrig @ 21:24

Data Change Capture ist ein cooles neues Feature von SQL Server 2008; Änderungen an Datensätzen (INSERT, UPDATE und DELETE) können automatisch in einer Logtabelle protokolliert werden. Beim Einrichten von Data Change Capture spezifizierst du, von welchen Tabellen, welche Spalten beobachtet, respektive protokolliert werden sollen. Ab dann schreibt der SQL Server jede Mutation in eine Logtabelle; die Daten hierfür holt er dabei direkt aus dem Transaktions Logfile. Mit Hilfe speziellen UDF’s kannst du auf diese Daten elegant zugegriffen werden. Dabei legt der Server für jede zu beobachtende Tabelle einen eigenen Satz von UDF’s an.

Die Logtabellen können zum Beispiel für das Aktualisieren eines Datawarehouse nützlich zu sein. Anstelle immer die ganzen riesigen Tabellen nach Mutationen zu durchstöbern, genügt es so sich auf diese Logtabellen zu konzentrieren. SSIS kennt hierfür sogar einen eigenen Task.

Bei grossen Datenbeständen mit häufigen Mutationen können diese Logtabellen sehr schnell riesig werden. SQL Server schmeisst daher automatisch alte Datensätze fort; per Default behält er jeden Datensatz drei Tage lang; aber auch dies kannst du nach Belieben abändern. Für dieses Aufräumen legt der SQL Server eigens einen SQL Server Agent Job an.

So, genug geschrieben; erst mache ich mal mit einem Beispiel weiter:

USE AdventureWorks;
GO
 
— determine whether a database is enabled
SELECT name, is_cdc_enabled
FROM sys.databases;

— enable the database for change data capture
EXECUTE sys.sp_cdc_enable_db_change_data_capture;

— determine whether a source table has been enabled for change data capture
SELECT name, is_tracked_by_cdc
FROM sys.tables;

— create a capture instance for individual source tables
— (maximum 2 instances per table)
EXECUTE sys.sp_cdc_enable_table_change_data_capture
    @source_schema = ‚Person‘,
    @source_name = ‚Contact‘,
    @role_name = ‚cdc_admin‘,
    @supports_net_changes = 1,
    @captured_column_list = N‚ContactID, LastName, EmailAddress‘;

— make some changes
UPDATE Person.Contact
SET LastName = N‚Gehrig‘
FROM Person.Contact
WHERE ContactID = 1;

UPDATE
Person.Contact
SET LastName = N‚Meier‘
FROM Person.Contact
WHERE ContactID = 1;

UPDATE
Person.Contact
SET LastName = N‚Achong‘
FROM Person.Contact
WHERE ContactID = 1;

— querying change data
DECLARE @from_lsn binary(10), @to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_get_min_lsn(‚person_contact‘);
SET @to_lsn = sys.fn_cdc_get_max_lsn();
SELECT *
FROM cdc.fn_cdc_get_all_changes_person_contact(@from_lsn, @to_lsn, ‚all‘);
SELECT *
FROM cdc.fn_cdc_get_net_changes_person_contact(@from_lsn, @to_lsn, ‚all‘);

— disable the database for change data capture
EXECUTE sys.sp_cdc_disable_db_change_data_capture

Die beiden letzten SELECT Statements rufen mit Hilfe der speziellen UDF’s die mutierten Daten ab. Das erste Statement (all changes) liefert:

__$start_lsn           __$seqval              __$operation __$update_mask ContactID LastName EmailAddress
0x0000002D000016E80016 0x0000002D000016E80014 4            0x02           1         Gehrig   gustavo0@adventure-works.com
0x0000002D000016F80004 0x0000002D000016F80002 4            0x02           1         Meier    gustavo0@adventure-works.com
0x0000002D000017080004 0x0000002D000017080002 4            0x02           1         Achong   gustavo0@adventure-works.com

Für jede Mutation gibt es also genau einen Eintrag. Die ersten beiden Spalten beschreiben den zeitlichen Ablauf der Updates und kommen aus dem Transaktionslog raus. Eine $operation von 4 beschreibt einen Update Befehl und die $update_mask 0x2 gibt an, dass nur das zweite Feld, welches beobachtet wird (also LastName) mutiert wurde. Am Schluss werden dann noch die aktuellen, d.h. nach dem Commit der Transaktion gültigen, Werte aller Felder aufgeführt. Cool oder? Wenn du in deiner Tabelle auch noch Spalten für Benutzer, PC-Name etc. einfügst, dann hast du ein vollständiges Security-Log.

Das zweite Statement (net changes) liefert:

__$start_lsn           __$operation __$update_mask ContactID LastName EmailAddress
0x0000002D000017080004 4            NULL           1         Achong   gustavo0@adventure-works.com

Jetzt bekommst du nur noch eine Zeile; nämlich den Zusammenzug aller Änderungen pro Datensatz. Die $operation ist wiederum ein Update Befehl, die $update_mask aber NULL. Warum? Ganz einfach. Der LastName vom letzten Update (Achong) ist nämlich der Wert, der zu Begin schon drin stand. Somit hat der Datensatz unter dem Strich keine Änderung erfahren; daher der Wert NULL.

So, das war’s für heute. Viel Spass beim ausprobieren.

24. September 2007

Dynamisches T-SQL

Filed under: T-SQL — Urs Gehrig @ 08:37

Nenn es wie du willst; dynamisches T-SQL, Codegenerierung oder einfach ein cleveres Script. Fact ist, dass du bei der Erstellung deiner T-SQL Scripts nicht immer schon den ganzen Code kennst, den du zur Laufzeit ausführen musst. Mit dem richtigen Gewusst-Wie hast du aber bereits gewonnen. Beispiel gefällig?

Für ein partnerspezifisches Zusatzmodul zu einem Standard ERP System musste ich ein SQL Setup-Script erstellen. Dieses Script hat die Aufgabe langwierige Konfigurationseinstellungen, welche der ERP Administrator in der Regel per Hand via GUI vornimmt, automatisch einzuspielen. Das Script muss für ein und dieselbe DB beliebig oft ausgeführt werden können. Daher hat das Script zu Begin eine Clean-Up Section, wo allenfalls bereits existierende Konfigurationen aus der DB gelöscht werden. Eine Teilaufgabe in dieser Clean-Up Section besteht aus dem Löschen einzelner Felder aus einer Tabelle. Und genau hier benötige ich mein dynamisches T-SQL Script: Die Namen der zu löschenden Felder sind nicht konstant, sondern müssen aus einzelnen Werten in einer weiteren Tabelle hergeleitet werden (Vielleicht etwas ungewöhnlich. Da es sich hier aber um ein Standard ERP System handelt, welches dieses Verhalten vorgibt, habe ich darauf keinen Einfluss und muss wohl oder übel einfach damit leben). Wer mich schon etwas länger kennt, weiss dass ich nichts von Cursor basierenden Lösungen halte; bei mir haben nur Set basierende Scripts eine Chance. So auch hier. Zuerst die Lösung, dann die Kommentierung:

— drop columns form table tdAttributes
DECLARE @stmt nvarchar(1000)

SET @stmt = (SELECT N‚ALTER TABLE dbo.tdAttributeValues DROP COLUMN Attr_‘ + AttributeName + N‚; ‚ as [text()]
             FROM dbo.tdAttributes
            
WHERE AttributeGroup = ‚bbvPersECA‘
             FOR XML Path());

EXEC sp_executesql @stmt

Die zu löschenden Spalten sind also in der Tabelle dbo.tdAttributeValues und die Namen der Spalten lassen sich aus dbo.tdAttributes.AttributeName herleiten (fixes Präfix Attr_). Die Idee liegt nahe, alle notwendigen ALTER TABLE Befehle zuerst in einem String (@stmt) zusammen zu fassen und anschliessend diesen String mittels sp_executesql auszuführen. Leider kann ich einem String nicht mehrere Ergebniszeilen eines SELECT-Kommandos zuweisen. Ich muss also alle Zeilen (gleichbedeutend mit alle ALTER TABLE Kommandos) in einer einzigen Zeile zusammenfassen. Genau das macht FOR XML. as [text()] und PATH() sorgen lediglich für die korrekte Formatierung. That’s it! Viel Spass beim adaptieren meiner Lösung für deine Problemstellungen.

17. September 2007

Typofehler in text Feldern

Filed under: T-SQL — Urs Gehrig @ 15:56

Heute wurde ich um einen Tipp gebeten: Wie korrigiere ich einen Tippfehler in meinen Daten?

Eigentlich mit REPLACE() ganz einfach, wenn da nicht noch die Randbedingungen wären J. Einerseits läuft die DB noch immer auf einem SQL Server 2000 (wie so viele andere ja auch) und anderseits sind die Daten in einem Feld vom Typ text abgelegt. Und da haben wir auch schon den Schlamassel; REPLACE() lässt sich nicht auf Felder vom Typ text anwenden L. Glück gehabt: Es gibt eine Einschränkung in den Daten, welche die ganze Sache wieder vereinfacht: Das falsch geschriebene Wort kommt immer in den ersten 8000 Zeichen vor.

Die korrekte Fragestellung lautet also: Wie korrigiere ich einen Tippfehler in den ersten 8000 Zeichen meiner Daten vom Typ text? Und das ist ganz klar ein Fall für UPDATETEXT:

BEGIN TRAN

DECLARE @wrongWord varchar(50)
DECLARE @correctWord varchar(50)
SELECT  @wrongWord = ‚generalinvoicerequest_410.xsd‘, @correctWord = ‚generalInvoiceRequest.xsd‘ –- use camel-case

DECLARE @ptrval binary(16)
DECLARE @offset int
DECLARE @length int
DECLARE myCursor CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT TEXTPTR(message_data), CHARINDEX(@wrongWord, SUBSTRING(message_data,1,8000)) 1, LEN(@wrongWord)
   
FROM IO_XMLOutput
    WHERE Identifier IN (SELECT f.hnnummer
                         FROM fall f

                              JOIN auftrag a ON a.id = f.auftrag_id AND a.nr IN (‚2007.00001‘, ‚2007.00004‘, ‚2007.00006‘)
                              
JOIN leistungserbringer le ON le.id = f.leistungserbringer_id AND le.nr = 999007
                              JOIN kostentraeger_patient kp ON kp.id = f.Kostentraeger_Patient_Id
                             
JOIN kostentraeger kt ON kt.id = kp.Kostentraeger_Id
                        
WHERE f.patienttiers = 2
                             
AND kt.kassencode IN (11110, 11103))
OPEN myCURSOR
FETCH NEXT FROM myCursor INTO @ptrval, @offset, @length
WHILE @@FETCH_STATUS = 0
BEGIN
   
UPDATETEXT IO_XMLOutput.message_data
              
@ptrval — textpointer
              
@offset — insert offset
              
@length — delete length
              
@correctWord — new value
    FETCH NEXT FROM myCursor INTO @ptrval, @offset, @length
END
CLOSE myCursor
DEALLOCATE myCursor

COMMIT

Ganz schön kompliziert, oder? Sicher ein Grund mehr auf SQL Server 2005 zu migrieren. Denn neu gibt es ja den Datentyp varchar(max); und da funktioniert auch REPLACE() wieder. Mehr darüber findest du in meinem Blogbeitrag Wie lade ich Dokumente in die DB?

Older Posts »

Bloggen auf WordPress.com.