SQL Server & ASP .NET Blog

Interessantes und Wissenswertes

Standard Verzeichnisse im SQL Server

Der SQL Server besitzt in seiner Konfiguration Standard-Verzeichnisse für die Daten-Dateien, Log-Dateien und auch für Backups. Werden diese in einem Skript benötigt, können diese folgendermaßen ausgelesen werden:

-- SQL Data Root
DECLARE @SQLDataRoot nvarchar(512) 
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\Setup'
,N'SQLDataRoot',@SQLDataRoot OUTPUT

SELECT
@SQLDataRoot SQLDataRoot -- Standard-Datenverzeichnis DECLARE @DefaultData nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer'
,N'DefaultData',@DefaultData OUTPUT
select
@DefaultData DefaultData -- Standard-Logfile-Verzeichnis DECLARE @DefaultLog nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog'
,@DefaultLog OUTPUT
select
@DefaultLog DefaultLog -- Standard Backup-Verzeichnis DECLARE @DefaultBackup nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory'
,@DefaultBackup OUTPUT
select
@DefaultBackup DefaultBackup

Sollte eines der Verzeichnisse nicht explizit gesetzt sein (sondern in der Standardeinstellung, wie bei Installation belassen), dann wird folgende Meldung zurückgegeben:

RegQueryValueEx() returned error 2, 'Das System kann die angegebene Datei nicht finden.'
Msg 22001, Level 1, State 1

Die Standardwerte für nicht explizit festgelegte Verzeichnisse sind:

  • DefaultData => SQLDataRoot + “\DATA”
  • DefaultLog => SQLDataRoot + “\LOG”
  • DefaultBackup => SQLDataRoot + “\Backup”

Nicht nur auslesen, auch Setzen kann man diese Werte per Skript (außer das SQLDataRoot):

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'C:\data' GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'C:\Log' GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', REG_SZ, N'C:\Backup'

SQL CLR Integration – Test

Eine kurze Erweiterung zum letzten Artikel: siehe [hier]

Möchte ich die erstellte Funktion testen und debuggen, dann kann ich das auch direkt aus dem Visual Studio heraus. Dazu gibt es im erstellten Projekt eine bereits vorhandene Test.sql, die dazu dienen soll eigene Funktionen etc. auszuführen, zu testen und zu debuggen.

image

Mit Rechtsklick auf die Test.sql und “Set as default debug script” wird dieses Skript immer beim Debuggen ausgeführt.

image

Nun kann ich im Skript meine Funktion ausführen und ein paar Tests durchführen. Beispiel:

IF dbo.fDNSCheck('MyDNSName') = 1
PRINT 'OK'
IF dbo.fDNSCheck('NotMyDNSName') = 0
PRINT 'OK'  

Dazu muss auf dem SQL Server allerdings der Remote Debugger installiert sein (msvsmon.exe). Weitere Informationen dazu gibt es [hier]Außerdem müssen einige Berechtigungen als Voraussetzung vorhanden sein (siehe ebenso der Link). Sind alle Voraussetzungen vorhanden, dann kann per F5 das Skript gestartet werden, es können Haltepunkte im .NET Code gesetzt werden etc.

SQL CLR Integration - .NET Code im SQL Server

Der SQL Server bietet die Möglichkeit CLR-Code direkt auszuführen und somit erweiterte Funktionen zu erfüllen. Ein kleines Beispiel: Ich möchte prüfen, ob mein SQL Server auf einen bestimmten DNS-Namen im Netzwerk hört. Dazu ist mir mit Bordmitteln keine Möglichkeit bekannt.

Zunächst einmal muss die CLR-Integration aktiviert werden.

sp_configure 'clr_enable', 1
RECONFIGURE

Ergebnis ist:

Configuration option 'clr enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.

Jetzt können wir loslegen. Ich erstelle im Visual Studio ein neues Datenbankprojekt und nenne es DNSChecker:

image

Im darauf folgenden Dialog muss eine Verbindung zu einer Datenbank hergestellt werden (die Datenbank, in der die Assembly letztendlich liegen soll und aus der sie ausgeführt wird).

image

Nachdem die Verbindung eingetragen ist, habe ich über einen Rechtsklick auf das Projekt und “Add” die Möglichkeit verschiedene Objekte zu erstellen:

image

Die einzelnen Möglichkeiten sind:

  • User-Defined Function – Eine Funktion mit Eingabeparametern und einem Ausgabeparameter
  • Stored Procedure – Ähnlich einer T-SQL Prozedur
  • Aggregate – Eine Aggregatfunktion, wie SUM(), AVG() etc.
  • Trigger – Ein CLR Trigger
  • User-Defined Type – Ein eigener “Datentyp”

Für das Beispiel beschränke ich mich auf das Erstellen einer Funktion, die mir nach Eingabe eines DNS-Namens zurückgibt, ob mein Server auf diesen Namen hört, oder nicht. Also => User-Defined Function

Nach Erstellen der Funktion erhalte ich einen bereits funktionsfähigen Rumpf mit einer Art “Hello World”.

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString fDNSCheck()
{
// Put your code here
return new SqlString("Hello");
}
};

Würde ich die Funktion nun aufrufen würde ich ganz einfach “Hello” als Ergebnis erhalten. Ich möchte allerdings “wahr” oder “falsch” als Bit-Wert zurückgeben und benötige als Eingabeparameter noch einen string (den DNS-Namen)

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean fDNSCheck(string sDnsName)
{
// Put your code here
return new SqlBoolean(false);
}
};

 

 

Nun fehlt noch die eigentliche Überprüfung, ob der Server auf den Namen hört.

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean fDNSCheck(string sDnsName)
{
bool result = false;
try
{
//Get all ips for DNS-Name
IPHostEntry ip = Dns.GetHostEntry(sDnsName);
IPAddress[] IpA = ip.AddressList;
//check if a local ip for this name exists
foreach (IPAddress ipDNS in IpA)
{
foreach (IPAddress ipLocal in Dns.GetHostEntry(Dns.GetHostName()).AddressList)
{
if (ipDNS.ToString() == ipLocal.ToString())
{
result = true;
}
}
}
}
catch (Exception ex)
{
result = false;
}
return new SqlBoolean(result);
}

Dazu iteriere ich über alle für den DNS-Namen gefundenen IP-Adressen und vergleiche mit allen auf dem Server gefundenen IP-Adressen. Wird eine Übereinstimmung gefunden wird “true” zurück gegeben. Nun ist die Funktion fertig und könnte arbeiten. Da die Funktion allerdings die Berechtigungsstufe “unsafe” benötigt (näheres siehe unter http://technet.microsoft.com/de-de/library/ms131071.aspx) muss die Datenbank in der diese laufen soll die Eigenschaft “TRUSTWORTHY ON” haben. Also…

ALTER DATABASE CLRTestDB SET TRUSTWORTHY ON

…und für die Assembly muss eingestellt werden, dass der Zugriff in der Stufe “unsafe” erfolgen soll (in den Projekteigenschaften).

image

Nun kann ich die Assembly per “Deploy” auf dem Server installieren. Führe ich die Funktion nun aus und mein Server hört z.B. auf den Namen “MyDNSName”, dann gibt die Funktion “1” für “wahr” zurück.

image

Dieses kleine Beispiel hat gezeigt, wie einfach es ist den SQL Server mit eigenen .NET Funktionen zu erweitern.

ASP .NET - Bild aus Datenbank abrufen und anzeigen

In einem anderem Beitrag [hier]habe ich bereits beschrieben, wie einfach es ist, ein Bild auf einer Webseite hochzuladen und mit wenigen Zeilen C#-Code in einer SQL Server Datenbank zu speichern. Interessant ist natürlich auch der Rückweg: Also wie kommt das Bild wieder aus der Datenbank heraus und kann im Browser angezeigt werden?

Auch das geht wieder in einfachen Schritten.

Schritt 1 – Eine ASPX-Seite zur Ausgabe eines Bildes

Um ein Bild aus der Datenbank zu lesen und im Browser anzuzeigen, erstelle ich eine neue Seite “GetPicture.aspx”. Diese Seite hat keinen eigentlichen Inhalt, sondern nur den Zweck ein Bild anhand des Namens (oder eines beliebigen anderen Parameters, z. B. einer ID) aus der Datenbank zu holen und an den Browser zu senden.

protected void Page_Load(object sender, EventArgs e)
{
//Variable für die Bilddaten
Byte[] myFile;
//der Dateiname aus dem QueryString
string myFileName = Request.QueryString["FileName"];
if (myFileName != null)
{
//Verbindung zum Server
SqlConnection con = 
new SqlConnection("Data Source=192.168.1.13;Initial Catalog=BildDB;User ID=xxx;Password=xxx");
//Insert Statement mit den entsprechenden Parametern
SqlCommand cmd = new SqlCommand("SELECT picture FROM pictures WHERE name = @name", con);
//Parameter für den Bildnamen
SqlParameter paramName = new SqlParameter("@name", System.Data.SqlDbType.VarChar);
paramName.Value = myFileName;
cmd.Parameters.Add(paramName);
//Daten lesen
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read() ) //im Beispiel interessiert nur der erste Satz
{
myFile = (Byte[])reader["picture"];
Response.ContentType = "image/jpeg"; //ContentType setzen
Response.BinaryWrite(myFile);        //Bild ausgeben
}
con.Close();
}      

Ruft man nun diese Seite mit einem in der Datenbank vorhandenen Bildnamen auf, dann wird das Bild direkt im Browser angezeigt.

image

Schritt 2 – Bilder in einem Gridview anzeigen

Um nun mehrere Bilder aus der Datenbank anzuzeigen kann bspw. ein GridView verwendet werden. Auf einer zusätzlichen Seite “Anzeigen.aspx” habe ich eine neue SQLDatasource und ein GridView erstellt.

        <!-- Die Datenquelle -->
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:BildDBConnectionString %>" 
SelectCommand="SELECT picture, name, addedat FROM [pictures]">
</asp:SqlDataSource>
<!-- Anzeige im Gridview -->
<asp:GridView ID="GridView1" runat="server" 
DataSourceID="SqlDataSource1" 
AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="name" HeaderText="name" SortExpression="name" />
<asp:BoundField DataField="addedat" HeaderText="addedat" 
SortExpression="addedat" />
<asp:ImageField DataImageUrlField="name" 
DataImageUrlFormatString="~/GetPicture.aspx?Filename={0}">
</asp:ImageField>
</Columns>
</asp:GridView>

Das “Besondere” an diesem GridView ist nun, dass es ein ImageField hat, welches direkt die in Schritt 1 erstellte ASPX-Seite aufruft und den aus der Datenbank gelieferten Dateinamen übergibt.

Das Endergebnis sieht dann so aus:

image 

Das Projekt (Visual Studio 2010) hängt zum Anschauen direkt hier am Beitrag. Fragen sind natürlich immer gern gesehen ;)

Bildverwaltung.zip (32,13 kb)

Server Trigger – DROP DATABASE verhindern

Nur ein kurzer Tipp: Möchte man verhindern, dass Datenbanken auf einem Server gelöscht werden, kann man dies durch einen Trigger auf dem Server tun.

CREATE TRIGGER TR_LoeschenVerhindern 
ON ALL SERVER 
FOR DROP_DATABASE 
AS 
PRINT 'Um eine Datenbank zu löschen muss erst der Trigger TR_LoeschenVerhindern deaktiviert werden.'
ROLLBACK;

Wird nun versucht die Datenbank zu löschen, gibt es folgende Meldung:

Um eine Datenbank zu löschen muss erst der Trigger TR_LoeschenVerhindern deaktiviert werden.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted…
…so kann niemand mehr sagen, die Datenbank ist "ausversehen” abhanden gekommen ;-)

OUTPUT – Gelöschte oder eingefügte Sätze ausgeben

Um zu prüfen, bzw. anzuzeigen welche Sätze nach einem DELETE gelöscht wurden, kann die OUTPUT Klausel verwendet werden. Hier ein kleines Beispiel.

Wir haben eine Tabelle mit einer Identitätsspalte und einem Wert und fügen dort ein paar Werte ein:

CREATE TABLE toDelete
(
id int primary key identity(1,1),
value int
)
insert into todelete values (1)  
insert into todelete values (2)
insert into todelete values (3)
insert into todelete values (4)

Will ich nun alle Datensätze löschen, deren Wert > 2 ist und diese auch noch ausgeben (um diese beispielsweise einem Benutzer anzuzeigen), dann verwende ich die OUTPUT-Klausel:

delete toDelete
OUTPUT DELETED.value
WHERE value > 2

Ergebnis ist folgendes:

output

Das gleiche funktioniert auch mit der Tabelle INSERTED, wenn zum Beispiel neue Werte der Identitätsspalte angezeigt werden sollen.

INSERT INTO toDelete 
OUTPUT INSERTED.*
VALUES (5)

Ergebnis hier ist:

image

Damit kann man sich ein weiteres SELECT auf die Tabelle sparen.

UNIQUE - Mehrere NULL

Um sicherzustellen, dass eine Spalte einer Tabelle nur eindeutige Werte enthält, kann diese Spalte um einen UNIQUE constraint ergänzt werden. Zum Beispiel:

CREATE TABLE uniq
(
id int primary key identity(1,1),
val int unique
)

Bei dieser Tabelle verhindert der SQL-Server, dass gleiche Werte in die Spalte “val” eingefügt werden. Versucht man also folgendes:

INSERT INTO uniq VALUES (1)
INSERT INTO uniq VALUES (2)
INSERT INTO uniq VALUES (1)

…erhält man diese Fehlermeldung:

Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'UQ__uniq__21B6055D'. Cannot insert duplicate key in object 'dbo.uniq'.

So weit, so gut. Was passiert nun aber, wenn man mehrere NULL-Werte einfügen möchte?

INSERT INTO uniq VALUES (NULL)
INSERT INTO uniq VALUES (NULL)

…es erscheint die gleiche Fehlermeldung.

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint 'UQ__uniq__21B6055D'. Cannot insert duplicate key in object 'dbo.uniq'.
The statement has been terminated.

Aber wie kann das sein? NULL ist doch ein unbestimmter Wert (eigentlich nichtmal ein Wert), nicht vergleichbar und kann somit verglichen mit sich selbst nie gleich sein ;-) Nunja, ist er im SQL Server aber doch. Ist es also notwendig eine Spalte eindeutig zu haben geht das im SQL Server nicht…oder doch? Mehr...

SQL - Leerzeichen in der Mitte eines String entfernen...INNERTRIM()?

Habe mich gerade gefragt, wie ich die Leerzeichen innerhalb eines VARCHAR entfernen kann. Also aus "Blah Blah" wird "BlahBlah". Schon hundert-tausend-mal gemacht, trotzdem habe ich plötzlich nach einer Art "INNERTRIM" gesucht :-D.

Natürlich geht das ganz einfach mit REPLACE(feld, ' ', '')

Falls jemand über die Suche nach INNERTRIM hierher gelangt ist, dann hätte ich gern einen Kommentar :-)

SSIS – Startobjekt in einem Projekt

Nur ein kleiner Tipp. Beim Debugging von SSIS-Paketen kann innerhalb eines Projektes ein Paket als Startobjekt gewählt werden (durch Rechtsklick und “Set as StartUp Object”). Will man nun ein einzelnes Paket debuggen startet immer dieses Paket. Soll das wieder geändert werden genügt ein Klick in die Projekteigenschaften im Bereich “Debugging”.

startprojekt

Dort kann das Startup Object wieder abgewählt werden, so dass das jeweils aktive Paket gestartet wird.

In welcher Datenbank bin ich – DB_NAME()

Teilweise kommt es vor, dass man gleiche Views für verschiedene Datenbanken benötigt, die nur ein wenig voneinander abweichen. Beispielsweise soll in Datenbank 1 eine Nummer immer 4-stellig, in Datenbank 2 immer mit führender 0, also 5-stellig angezeigt werden.

Möchte man solche Views gemeinsam warten, also nur einmal ändern und dann verteilen kann DB_NAME() helfen. DB_NAME() gibt immer den Namen der aktuellen Datenbank zurück.

CREATE TABLE myTable
(
number int
)
INSERT INTO myTable VALUES (2542)
INSERT INTO myTable VALUES (8845)
INSERT INTO myTable VALUES (9895)
INSERT INTO myTable VALUES (3213)

Um nun eine View zu erstellen, die für beide Datenbanken passt geht folgendes:

CREATE VIEW [dbo].[myView] AS
SELECT 
CASE 
WHEN DB_NAME() = 'Datenbank1' 
-- vierstellig
THEN RIGHT('0000' + convert(varchar(4),number),4) 
WHEN DB_NAME() = 'Datenbank2'
-- fünfstellig 
THEN RIGHT('00000' + convert(varchar(4),number),5)
ELSE CAST(number as VARCHAR)
END AS number
FROM myTable