Martin's Blog

Wieder mal ein ganz normaler Wahnsinn, der einen Stunden gekostet hat…

Wenn wir uns die nachfolgenden SQL Statements ansehen, dann würde ich davon ausgehen, dass die ersten 6 eine Ausgabe erzeugen.

SELECT 'Test 1' WHERE 1 IN (NULL, 1, 2, 3, 4)
SELECT 'Test 2' WHERE 1 IN (1, 2, 3, 4)
SELECT 'Test 3' WHERE 1 NOT IN (NULL, 2, 3, 4) -- !!!
SELECT 'Test 4' WHERE 1 NOT IN (2, 3, 4)
SELECT 'Test 5' WHERE NOT (1 IN (NULL, 2, 3, 4)) -- !!!
SELECT 'Test 6' WHERE NOT (1 IN (2, 3, 4))
-- Empty as expected
SELECT 'Test 5' WHERE 1 IN (NULL, 2, 3, 4)
SELECT 'Test 6' WHERE 1 IN (2, 3, 4)

Aber Pustekuchen
Die Zeilen 3 und 5 erzeugen keine Ausgabe Dadurch, dass in dem Ausdruck in der Klammer NULL enthalten ist funktioniert der NOT IN Test nicht mehr korrekt.

Ich bin darauf gestoßen, weil ich ein Subquery durchgeführthabe und mit NOT IN prüfen wollte, dass ein bestimmter Wert eben nicht in diesem Subquery enthalten ist. Einziges Problem war, dass in einigen Fällen dieser Subquery eben auch NULL als Ergebnis geliefert hat. 
Und genau in diesem Fall funktionierte die Abfrage nicht korrekt (s.o.). Das sah vereinfacht in etwas so aus:

SELECT [ID] FROM [Table1]
    WHERE [SomeData] NOT IN
        (SELECT [OtherIDWithNULL] FROM [Table2])

Ich habe den Subquery dann entsprechende um ein WHERE … IS NOT NULL erweitert und siehe da es ging:

SELECT [ID] FROM [Table1]
    WHERE [SomeData] NOT IN
        (SELECT [OtherIDWithNULL] FROM [Table2]
            WHERE [OtherIDWithNULL] IS NOT NULL)

Rein gefühlt ist das für mich ein Bug, aber es ist keiner
Hier kommt eine Einstellung zum tragen, die sich ANSI_NULL schimpft. (siehe SET ANSI_NULL { ON | OFF })

ANSI_NULL ist im Allgemeinen ON, und das bedeutet, dass ein Vergleich eines Wertes mit NULL immer undefiniert ist. Das ein IN-Statement aber nichts anderes ist als ein Vergleich der einzelnen Werte in der Klammer mit dem Zielwert, führt dies hier zu einem irritierenden Ergebnis. NULL=NULL und 1=NULL ist eben undefiniert und nicht False wenn ANSI_NULL ON ist! Also können die Zeilen 3+5 kein korrektes Ergebnis liefern.
Man beachte: Dies hat keinen Einfluß solange man IN verwendet und nicht NOT IN!

Meine rein persönliche Meinung:
Ich habe selten solch einen Unsinn in einem ANSI-Standard gesehen. Einfach Unlogisch
Für mich ist NULL=NULL und NULL<>AnyThingElse eben True… Just my 2 cents…

Jetzt könnte man meinen, dass es also auch eine Lösung wäre SET_ANSI_NULL OFF zu verwenden (BTW: Eine Einstellung die auch als Attribut direkt auf der Datenbank selbst gesetzt werden kann). Aber der folgende Hinweis in der Doku sollte einen zum Umdenken bewegen:

Wichtig:
In einer späteren Version von SQL Server wird ANSI_NULLS immer auf ON festgelegt, und jede Anwendung, die für die Option explizit OFF festlegt, löst einen Fehler aus. Verwenden Sie dieses Feature beim Entwickeln neuer Anwendungen nicht, und planen Sie eine Änderung von Anwendungen, in denen es zurzeit verwendet wird.

Da dies also evtl. Einfluss auf andere Codebereiche haben könnte und zukünftig nicht mehr unterstützt wird habe ich auf diese Nutzung verzichtet und lieber das Subquery abgeändert.

PS: Bitte jetzt nicht darauf hinweisen, dass es auch JOIN gibt. Aufgrund der Komplexität der Abfrage und weil diese auch noch nach bestimmten Bedingungen gebaut wurde, war ein IN mit einem Subquery, der einfachere Weg.

Wie bekommt man eigentlich einfach eine Liste aller verfügbaren SQL-Server im Netz?

In der MSDN findet sich schnell ein Artikel How to enumerate available instances of SQL Server by using the SQLDMO components. Allerdings ist dieser Artikel wenig nützlich, denn SQL-DMO findet man nur noch selten auf einem Rechner.

Dabei ist es doch relativ einfach, denn OLE-DB sieht hierfür Enumeratoren vor. Aber auch die sind nicht sonderlich gut dokumentiert. In der SQL-2000 Server Doku findet sich noch ein Eintrag für den SQLOLEDB Enumerator. Für den neuen nativen OLE-DB Clienst für den SQL-Server finde ich nichts mehr dazu.

ATL stellt direkt Klassen zur Verfügung, die die Nutzung von Enumeratoren zu einem Kinderspiel machen.

Anbei ein Codeschnippsel der alle bekannten MS-SQL Server enumeriert. Ich beginne dabei mit dem neuesten Client (2008) und gehe die Schleife weiter bis zum ältesten Server Client (2000).
Wird ein Enumerator gefunden, und dieser lieferte Ergebnisse, dann wird die Schleife abgebrochen. Denn alle Enumeratoren liefern im Allgemeinen das gleiche Ergebnis.

Code:

 
//////////////////////////////////////////////////////////////////////////
// Main function to enumerate all servers with the appropriate
// known enumerators.
 
typedef std::set  TSET_CString;
 
void EnumSQLServer(TSET_CString &setSQLServer)
{
  // We may need the local server name. 
  // We replace the token (local) with the current computer name.
  CString strCompLocal;
  DWORD dwLen = MAX_COMPUTERNAME_LENGTH+1;
  ::GetComputerName(CStrBuf(strCompLocal,dwLen),&dwLen);
 
  // Loop over all enumerators we know
  static const PCWSTR aEnumerator[] =
  {
    L"SQLNCLI10 Enumerator",    // SQL 2008
    L"SQLNCLI Enumerator",      // SQL 2005
    L"SQLOLEDB Enumerator"      // SQL 2000
  };
 
  // Try all enumerators
  for (int i=0; i < _countof(aEnumerator); ++i)
  {
    // Check if we have an enumerator
    bool bFoundAny = false;
    HRESULT hr;
    CLSID clsid;
    hr = CLSIDFromProgID(aEnumerator[i],&clsid);
    if (SUCCEEDED(hr))
    {
      // Open enumerator and loop over all entries
      CEnumerator enumrator;
      hr = enumrator.Open(&clsid);
      if (SUCCEEDED(hr))
      {
        while ((hr=enumrator.MoveNext())==S_OK)
        {
          CString strServerName(enumrator.m_szName);
 
          // Skip empty server names 
          // (older enumerators return sometimes an empty name)
          if (strServerName.IsEmpty())
            continue;
 
          // Some enumerators return (local) for a local main
          // SQL server instance
          if (strServerName.CompareNoCase(_T("(local)"))==0)
          {
            ATLTRACE(__FUNCTION__ " found local computer\n");
            strServerName = strCompLocal;
          }
 
          // get uppercase server name
          strServerName.MakeUpper();
 
          // Insert in list and avoid duplicates with this, if
          // developer decides not to break the loop after the first
          // enumerator.
          if (setSQLServer.insert(strServerName).second)
            ATLTRACE(__FUNCTION__ " found server %s\n",
                  CT2A(strServerName.GetString()));
          bFoundAny = true;
        }
      }
 
      // After we have found data in one enumerator. There is no need
      // to do this again.
      // But a developer might decide to do this for every enumerator
      if (bFoundAny)
        break;
    }
  }
}

Ein lauffähiges Projekt kann man hier herunterladen: EnumSQLServer.zip.

Ich nutze viel das MS-SQL Server Management Studio für MS-SQL 2005. Besonders um eben auch SQL-Statements zu testen und zu entwerfen.

Häufig lasse ich mir dazu einfach eine aktuelle Tabelle anzeigen. Mit Strg+3 kann man sich nun das SQL-Statement anzeigen lassen und es ändern. Also z.B. eine WHERE Bedingung hinzufügen.
Jeder der den Enterprise Manager gewohnt ist haut nun auf die F5-Taste, es passiert natürlich nichts . OK times are changing, wir benutzen also brav Strg+R um das neue Statement auszuführen.

OK nun klicken wir auf den Datenbanknamen und wählen hier im Kontextmenü Neue Abfrage aus. Ein bisschen SQL Statement schreiben und wir drücken Strg+R . Was denn nun? Wir bemühen also das entsprechende Menü und finden das hier der entsprechende Hotkey F5 ist.

Wer nun wieder mit MS-SQL Server 2008 arbeitet wird nun mit Strg+R kein Glück haben. Jetzt ist alles wieder gut und hier funktioniert nur noch F5. Jetzt ist dafür der Abfrage-Designer ist nun ein separater Dialog geworden, der das entsprechende Statement in das Abragefenster überträgt.
(Nachtrag 2009-05-06) Wenn man aus im Management Studio 2008 den Punkt Zeilen bearbeiten auswählt, hat man das selbe miese Verhalten wie im Management Stusio 2005. Man kommt nur nicht mehr so offensichtlich in diesen Bearbeitungsmodus und landet eher auf einer Abfrage, in der man dann aber das Ergebnis nicht bearbeiten kann.

Es gibt Momente in denen ich mich beherrschen muss nicht in die Tischplatte zu beißen…

Datumsangaben in SQL Queries sind immer wieder eine Freude. Im Gegensatz zu allen anderen Datentypen hat man es hier auch noch mit einer sprachspezifischen Einstellung (US English, Deutsch etc.) zu tun.
Also was macht der Entwickler, er schreibt SQL Code wie diesen, der die Funktion CONVERT oder CAST verwendet:

CREATE VIEW dbo.International_Dates AS
SELECT PurchaseOrderID, TotalDue
FROM AdventureWorks.Purchasing.PurchaseOrderHeader
WHERE OrderDate < CONVERT(DATETIME,'2002.05.01',102);

Man kann eben nicht sicher sein wie der Datumswert interpretiert wird. Das folgende Statement würde also nur mit einer englischen Locale funktionieren:

CREATE VIEW dbo.USA_Dates AS
SELECT PurchaseOrderID, TotalDue
FROM AdventureWorks.Purchasing.PurchaseOrderHeader
WHERE OrderDate < 'May 1, 2002';

Dabei gibt es ein Datmsformat im MS-SQL Server, dass immer gleich interpretiert wird, das so genannte Unseparated String Format . Netterweise geht das sogar für DateTime Werte wenn man diesen im Format yyyyMMdd hh:mm:ss.ttt angibt.
Dies entspricht den CAST/CONVERT Formaten 112 und 114!

Man kann den obigen Query also komplett locale-unabhängig so schreiben.

CREATE VIEW dbo.Uniform_Date AS
SELECT PurchaseOrderID, TotalDue
FROM AdventureWorks.Purchasing.PurchaseOrderHeader
WHERE OrderDate < '20020501';

Man kann sich CAST und CONVERT also oft genug sparen wenn man Queries aufbaut, da Datum-Strings im Format yyyyMMdd hh:mm:ss.ttt, eben sprach/localeunabhängig sind.
Ich habe eine spezielle Funktion die genau dieses MS-SQL-Format benutzt um ein COleDateTime in das entsprechende Stringformat für einen SQL Query zu bringen, wenn ich mal keine Parameter in einem Query benutzen will oder kann.

Nachtrag: es ist ohne Probleme möglich die Uhrzeit auch abgekürzt zu übergeben, wie z.B. auch in der Form: yyyyMMdd hh:mm, oder yyyyMMdd hh:mm:ss

Auf einer Datenbank in einem MS-SQL 2005 Server (32bit) verursachte ein simpler DETETE FROM tbl… in einem meiner Programme die folgende Fehlemeldung:

Der Abfrageprozessor hatte während der Abfrageoptimierung zu wenig Stapelspeicherplatz. Vereinfachen Sie die Abfrage.

Das Ganze war etwas mysteriös, und auch in den Newsgroups und im Netz gab es keine vernünftige Antwort. Also habe ich kurzerhand eine Supportanfrage an Microsoft gestellt.

Hier in Kurzform die Problematik:

• Wir haben eine sehr komplexe Datenstruktur, die der Anwender mit der Hilfe eines Tabellen-Generators selbst manipulieren und definieren kann. Die Folge in einem Fall:
  Es gab zu einer Tabelle über 400 Fremdschlüssel.
• Besagte Fremdschlüssel sollten garantieren, dass aus dieser Tabelle, die für Datensatz Klassifikationen verwendet wurde, keine verwendeten Datensätze gelöscht werden können. 
• An dem Statement selbst gab es nichts zu optimieren, das war wirklich nur ein einfaches Statement im Stile von DELETE FROM tbl WHERE Id=4711

In Kurzform die Antwort von Microsoft:

• Das Problem ist nachvollziehbar.
• Das Problem ist hausgemacht und liegt an der Anzahl der Fremdschlüssel.
• Einen Fix bzw. eine Änderung oder Parameter mit denen man das Verhalten ändern, oder erweitern kann, gibt es nicht und ist nicht geplant.
• Der Speicherblock, der hier zu klein wird, ist bei einem MS SQL-2005 Server in der 32bit Version 512KB groß, auf einer 64bit Installation ist besagter Speicherblock viermal so groß, d.h. 2MB!
  Der Fehler tritt auf einem 64bit Server und der gleichen DB-Struktur nicht auf, dafür aber später , wenn noch mehr Schlüssel verwendet werden.

Einen kleinen bissigen Kommentar kann ich mir hierzu aber nicht verkneifen, denn ganz blind bin ich in dieses Problem nicht hinein gelaufen

Hier der entsprechende Link zu Spezifikationen der maximalen Kapazität für SQL Server 2005, und dort lesen wir folgendes:

Verweise auf Fremdschlüsseltabellen pro Tabelle 253

- und in der-

Fußnote 4:
Auch wenn eine Tabelle eine unbeschränkte Anzahl von FOREIGN KEY-Beschränkungen enthalten kann, beträgt das empfohlene Maximum 253. In Abhängigkeit von der Hardwarekonfiguration, die SQL Server hostet, kann das Angeben weiterer Fremdschlüssel den Abfrageoptimierer bei der Verarbeitung stark beanspruchen.

Diese Information deckt sich mit der englischen Dokumentation.

Meine Interpretation dazu ist die folgende:
Es kann den Abfrageoptimierer stark beanspruchen wenn man so einen Konstrukt baut, allerdings war und ist mir das egal. Von mir aus kann die Ausführung Minuten dauern. Der Fall des Löschens in dieser Tabelle ist die seltene Ausnahme. Das Problem ist hier, dass die Ausführung gar nicht möglich ist. Hier steht nicht, dass ich nicht mehr als 253 Schlüssel verwenden darf! Wenn dem so ist, müsste dieser Satz klar umformuliert werden, oder es müsste bereits beim Anlegen eines weiteren Fremdschlüsselverweises eine Fehlermeldung kommen.
De facto steht hier: “Auch wenn eine Tabelle eine unbeschränkte Anzahl…”
Die Operation sollte fehlerfrei durchgeführt werden können, was aber nicht so ist.

Besagte Supportanfrage wurde geschlossen

Bleibt noch Abschließendes hinzuzufügen:

• Auch der MS-SQL Server 2008 zeigt das gleiche Verhalten.
• Man erhält den selben Fehler auch im MS-SQL 2000, dort heißt er dann
  Interner Fehler des Abfrageprozessors: Im Abfrageprozessor wurde bei der Ausführung ein unerwarteter Fehler festgestellt.

Wer Reports aus dem TFS in Visual Studio benutzt kann oft genug nicht die Date/Time Picker verwenden, wenn er die deutschen  regionalen Einstellungen verwendet.

Wenn man einen dieser Date/Time Picker benutzt wird das Datum im englischen Format in das Eingabefeld übernommen, was dann anschließend mit der Meldung

Der Wert, der für den ParamEndDate-Berichtsparameter angegeben ist, ist für dessen Typ ungültig. (rsReportParameterTypeMismatch)

quittiert wird.

Das ganze ist kein Problem des TFS sondern des MS-SQL 2005 Reportservers.
Es gibt für den MS-SQL 2005 SP2 einen nicht offiziellen Hotfix, der dies behebt:
http://support.microsoft.com/kb/949095/en-us

Leider muss man um ihn zu erhalten den Microsoft Support kontaktieren. Einfach dort den Hotfix anfordern. Mir ist nicht klar warum man diesen Hotfix nicht direkt herunterladen kann.

Weitere Infos auch hier:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=271928

Bei einem Kunden muss ich aktuell einem Problem auf den Grund gehen, das irgendwie mit Locks in den tiefsten Tiefen des SQL-Server 2005 zu tun hat. Aus diesem Grund habe ich Einblick in den originalen – doch etwas größeren – Datenbestand erhalten. Besagter Kunde ist in seiner Branche ziemlich populär. Es werden in dieser Datenbank 750.000 Kunden verwaltet. Alles aktive Kunden eines bestimmten Produkts einer kleinen geographischen Zone in Deutschland. Qualitativ wirklich aktuelles Material, denn alle diese Datensätze sind wirklich aktive Kunden, d.h. bekommen mindestens einmal im Jahr eine Rechnung.

Um sich nun mit unserer Anwendung als Benutzer Administrator anzumelden benötigte ich noch ein Kennwort. Das hatte mir keiner mitgeteilt (bis jetzt). Aber was soll’s, probieren wir doch mal

1. Versuch: Der Firmenname
2. Versuch: Der Produktname
3. Versuch: admin… Bingo

Ich will nicht davon reden, wieviele Aktivität betrieben wird,  die Datenbanken nach außen abzuschirmen. Auch will ich nicht über die interne Panik klagen, die uns gegenüber immer geschoben wird, dass alles hoch sicher und geheim zu behandeln ist.

Jeder Angestellte, mit etwas Spielwitz kann in etwa 20 Sekunden als Admin an alle Daten…

Nur als Anmerkung: Dieses Sicherheits-Problem ist natürlich mittlerweile behoben…

Es gibt noch ein paar nette Tabellen und Abfragen die einem Informationen über die physikalische Größe eine SQL-Datenbank liefern. Denn sowohl die Daten-Datei als auch die Protokolldatei können über nicht allokierten Speicherbereich verfügen. Wenn man also wissen will, wie viel Platz wirklich noch verfügbar ist, muss auch dieser noch verfügbare Platz mit einbezogen werden.

Es sind also noch zu ergänzen:

1. Sehr einfach ist die dbo.sysfiles zu verwenden. Auch über diese Tabelle erhält man schnell die Größe der Protokoll- und Datendatei in 8kb Blöcken.
2. sp_spaceused liefert einem die komplette Größe der Datendatei und die Größe des nicht verwendeten Bereiches. sp_spaceused liefert übrigends zwei Resultsets wenn man es ohne Objektnamen aufruft! Nicht wundern.
3. Und last but not least DBCC SQLPERF(LOGSPACE), dass die selbe Information für die Protokolldatei liefert. D.h. Größe der Protokolldatei in MB und den prozentual benutzten Speicherbereich. 

PS: Wie man so etwas herausbekommt.
Nun man kann die MSDN lesen, aber man kann es sich auch einfacher machen. ;-)
Ich habe einfach den SQL Profiler angeworfen, dann den SQL Server Enterprise Manager gestartet. Im Enterprise Manager habe ich die Taskpad Ansicht gewählt und einfach mal F5 gedrückt. Der Profiler hat mir dann gezeigt was der Enterprise Mangager so abfragt um die relevanten Daten zu ermitteln.

Kann man den MS-SQL Server abfragen ob es noch genug Plattenplatz gibt?

Manche Operationen kosten sehr viel Platz, besonders wenn man Datenbanken umstrukturiert. Da kann es leicht sein, dass gleich 100% mehr Platz benötigt wird. Zum einen weil die Protokolldatei evtl. immens wächst und zum zweiten weil einfach die Tabellen stark anwachsen weil zum Beispiel neue Indexe hinzukommen oder Spalten auf Unicode umgestellt werden.

Kann man also irgendwie den freien, zur Verfügung stehenden Platz ermitteln und entsprechende Berechnungen anstellen?

Ja! Es geht.

1. Ausgangspunkt ist hier zum einen die dokumentierte System Tabelle sysdatabases, die den Namen der Datenbank, und den Namen physikalischen mdf-Datei liefert.
2. Nun benötigen wie noch die aktuelle Größe, die wir ohne Probleme mit der Storedprocedure sp_databases ermitteln können, die Namen, die Größe in KB und eine NULL Spalte zu dieser Datenbank liefert.
3. Den dritten und letzten Baustein für die Aufgabe liefert die nicht dokumentierte Funktion master..xp_fixeddrives, die in der ersten Spalte den Laufwerkbuchstaben liefert und in der zweiten den freien Speicherplatz in MB.

Wie man jetzt errechnet ob eine Größenänderung um 75% noch abgedeckt wird, bleibt dem Leser überlassen. Nett nicht!

BTW: Diese Funktionen laufen verifiziert auf MS-SQL 2000 und auch auf MS-SQL 2005 Servern inkl. der Express-Edition.

Link Useful undocumented extended stored procedures

Ich wollte mir das Microsoft SQL Server Management Studio Express auf Vista installieren. Aber während der Installation erhielt ich die folgende Fehlermeldung:

Bei der Installation dieses Pakets ist ein unerwarteter Fehler aufgetreten. Es liegt eventuell ein das Paket betreffendes Problem vor. Der Fehlercode ist 29506.

Und nun? Man sollte doch annehmen, dass eine MSI-Datei sich installieren lässt und auch im Admin-Modus installiert wird. Pustekuchen.

Es geht doch, mit einem Trick:

Man starte eine Console im Admin-Modus: Rechtsklick auf die Verknüpfung und Als Administrator ausführen wählen. Dann von dort die entsprechende SQLServer2005_SSMSEE.msi aufrufen. Dann läuft das Setup ohne Fehler durch.

Langsam habe ich das Gefühl, dass dieses Verfahren zur Installation unter Vista grundsätzlich zu empfehlen ist. Leider kann man MSI-Pakete nicht direkt über ein Kontextmenü im Admin-Modus installieren.
Das wäre was für Vista SP1

Nachtrag 22.08.2009:
Das Problem betrifft auch die Installation unter Windows Server 2008 als auch Windows 7, sofern UAC eingeschaltet ist. Das MSI Paket muss in jedem Fall als Admin (elevated) gestartet werden.