SQL Server & ASP .NET Blog

Interessantes und Wissenswertes

T-SQL Zeilen in Spalten ausgeben (PIVOT)

Um Zeilen in T-SQL in Zeilen ausgeben zu können gibt es den PIVOT Operator. Ein kleines Beispiel:

Im Forum von http://www.sqlservercentral.com wurde gefragt, wie eine Tabelle, die folgendermaßen aussieht umgeformt werden kann:

DatabaseName Date Qty
Db1 20090101 1000
Db2 20090101 500
Db1 20090201 2000
Db2 20090201 600

Diese Tabelle zeigt den von mehreren Datenbanken verbrauchten Speicherplatz im Zeitverlauf an. Um jetzt das Ergebnis so umzuformen, das folgendes erscheint…

DatabaseName 20090101 20090201
Db1 1000 2000
Db2 500 600

…kann folgendes SQL Statement verwendet werden:

declare @dates varchar(1000) = ''
select @dates = @dates + ',' + '[' + convert(varchar,date,112) +']' 
from 
(
select distinct date from dbs
) a
DECLARE @sql nvarchar(1000)
set @sql = 
'select * from dbs 
PIVOT
(
MAX(qty)
FOR date in (' + substring(@dates,2,LEN(@dates)) + ')
) p
'
exec sp_executesql @sql

Der Hauptteil ist die in dynamischen SQL geschriebene Abfrage mit PIVOT. Hier wird für jedes Datum, für jede DB die MAX(qty) bestimmt. Für jedes Datum gibt es nur eine Qty also wird diese zurückgegeben. Da nicht bekannt ist, wie viele Daten (Datums) in der Tabelle enthalten sind und mit PIVOT keine Parameter erlaubt sind (zumindest habe ich darüber nichts gefunden) muss der obere Teil alle Daten selektieren und als String für PIVOT zurückgeben zurückgeben.

Weitere Informationen zu PIVOT gibt es bspw. hieroder kurz gehalten hier

Zum ausprobieren gibt es hier noch die CREATE Skripte etc. Mehr...

SSMS Toolpack

Ich möchte kurz das SSMS Toolpack vorstellen, welches unter http://www.ssmstoolspack.com/Main.aspx zum Download verfügbar ist. Das Toolpack funktioniert mit dem Management Studio für SQL 2005, SQL 2008 und auch SQL Express und bietet einige nützliche Features für die Arbeit mit dem Management Studio.

Was kann das Toolpack?

Die Funktionen werden auf der Seite http://www.ssmstoolspack.com/Features.aspx ausführlich beschrieben. Am nützlichsten finde ich das “Window Connection Coloring”, die “Query Execution History” und die Suche für die Ergebnismenge. Mit dem Connection Coloring wird jede Verbindung zu einem bestimmten Server in einer definierten Farbe angezeigt. So passiert es nicht so leicht, dass man ein SQL Statement ausversehen auf dem falschen Server absetzt.

concoloring

Die Query Execution History speichert alle an den Server gesendeten SQL-Statements. So kann man immer wieder zurück, falls man mal ein Fenster ungewollt geschlossen hat oder wenn man mal nachschauen möchte was man eigentlich den ganzen Tag gemacht hat ;-)

exechistory

Mit der Suche für die Ergebnismenge kann man leicht im Grid nach Text suchen (was sonst nicht geht). Spart man sich teilweise das zusätzliche ORDER BY oder Scroll-Orgien, wenn man mal nur etwas nachschauen möchte.

suche

Diese drei Features waren nur Beispiele…im Toolpack gibt es noch weit mehr interessante Funktionen. Es lohnt sich auf jeden Fall das Toolpack mal anzuschauen.

SQL Server Database Copy Tool – Beta

Habe soeben eine neue Version des SQL Server Database Copy Tool auf Codeplex veröffentlicht: http://dbcopytool.codeplex.com

Habe es jetzt schon öfter eingesetzt um Datenbanken erfolgreich (:-) ) von einem auf den anderen Server zu kopieren und umgehe damit die Methode per Copy Database Task aus dem SSMS. Wer trotzdem wissen möchte, wie das geht findet hier eine Beschreibung: [Copy Database Beschreibung]

Achja, das Tool hat nun auch ein “hübsches” Logo :-)

logo

SQL Server Messages empfangen (ExecuteNonQuery)

Im SQL Server kann per Skript eine Nachricht ausgegeben werden. Ganz einfach per:

PRINT 'HALLO'

Um jetzt diese Nachricht in einer SQLConnection abzufangen gibt es nun eine einfache Möglichkeit. Zunächst wird eine Methode benötigt, die die Nachricht empfängt und weiterverarbeitet. Hier am Beispiel _con_InfoMessage(…).

private void _con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
Console.WriteLine(e.Message);                
}

Nun muss noch der EventHandler für das Ereignis festgelegt werden:

_con.InfoMessage += new SqlInfoMessageEventHandler(_con_InfoMessage);

Mit dieser Einstellung wird nach einem ExecuteNonQuery() die Nachricht, die vom SQL Server empfangen wird in der Methode _con_InfoMessage verarbeitet (und hier in diesem Fall einfach in die Konsole geschrieben).

Für das SQL Server Database Copy Tool wollte ich anzeigen, wie weit der Server bereits mit dem BACKUP bzw. dem RESTORE ist. Mit der obigen Einstellung wird wie gesagt das Event nur einmal am Ende von ExecuteNonQuery() ausgeführt. Für eine Fortschrittsanzeige soll es allerdings jedes Mal auftreten, wenn eine Nachricht empfangen wird. Dafür gibt es die Eigenschaft (für SQLConnection):

_con.FireInfoMessageEventOnUserErrors = true;

Diese Eigenschaft legt fest, dass das Ereignis jedes Mal ausgeführt wird. Wie am Namen “OnUserErrors” zu erkennen gilt dies nicht nur für Nachrichten, die mit PRINT erzeugt werden. Vielmehr werden Fehler in der Ausführung bis zu einem Schweregrad von 16 an die Methode weiter gegeben (normalerweise würde eine Exception bei ExecuteNonQuery() entstehen. Um nun an die richtigen Exceptions (also Fehler) ran zu kommen habe ich mir noch Folgendes gebastelt:

private void _con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    if (e.Errors.Count > 0)
{
if (e.Errors[0].Class > 0)
{
//save error for use
_internalError += e.Message + " ";
}
}
Console.WriteLine(e.Message);             
}

Tritt also ein richtiger Fehler auf (Schweregrad > 0 [PRINT = Schweregrad 0]) wird dieser in _internalError gespeichert und ich kann diesen später nach ExecuteNonQuery() behandeln.

Prüfen ob ein Job gelaufen ist

Um zu prüfen, ob ein bestimmter Job innerhalb eines gewissen Zeitraums gelaufen ist kann folgendes Skript verwendet werden.

-- check if a sql server agent job ran successfully
use msdb
declare @rundate datetime;
declare @run_time varchar(6);
select @rundate = run_date, @run_time = run_time
from
(    
-- select the last succesful run of your job        
select top 1 cast(run_date as varchar(8)) as run_date, run_time, run_status from 
sysjobhistory jh
inner join sysjobs j on jh.job_id = j.job_id
where step_id = 0
and j.name = 'Testjob' -- your job name
order by run_date desc, run_time desc
) a
-- add leading zero if needed
set @run_time = RIGHT(CAST(CAST(@run_time as int) + 1000000 as varchar(7)),6)
-- calculate rundatetime
set @rundate = DATEADD(second, cast(right(@run_time,2) as int), @rundate)
set @rundate = DATEADD(minute, cast(substring(@run_time, 3,2) as int), @rundate)
set @rundate = DATEADD(hour, cast(left(@run_time,2) as int), @rundate)
-- if last successful run was more than 25 minutes ago do something
if DATEDIFF(minute, @rundate, GETDATE()) > 25
BEGIN
-- do something (write mail, netsend etc.)    
-- or start your job :-)
exec sp_start_job 'Testjob'
END

SQL Server Database Copy Tool – Start per Kommandozeile

Heute habe ich dem SQL Server Database Copy Tool noch eine, meiner Meinung nach, wichtige Funktion hinzugefügt. Jetzt kann eine SQL Server Datenbank auch per Kommandozeile kopiert werden. Nützlich wenn man Datenbanken öfter kopieren möchte (bspw. eine “frische” Version auf den Testserver) oder mehrere Datenbanken per Batch kopiert.

So sieht das dann aus:

db_copy_tool_cmd

Gestartet wird das Tool über die Kommandozeile per: dbcopytool FROM_SERVER TO_SERVER FROM_DB TO_DB. (startet man das Tool ohne Parameter wird es wie "normal", also mit Form gestartet)

Hierbei ist wichtig, dass die angegebenen Server mit genau dem Namen in der Konfiguration enthalten sind, da das Tool die Verzeichnisse für Backup und Restore daraus erhält.

Clone Detective – duplizierten Code finden

Ich habe soeben auf Codeplex ein sehr nützliches AddIn für das Visual Studio gefunden. Clone Detective for Visual Studio findet automatisch innerhalb einer Solution doppelten Code.

clone 

Damit ist es nun ein Leichtes diesen doppelten Code zu finden und so umzubauen, dass jede Zeile auch wirklich nur einmal im Projekt vorkommt…keine doppelten Änderungen mehr, kürzerer Code…echt eine tolle Sache!

[Link zum Projekt auf Codeplex]

P.S. schön, dass ich keinen im Database Copy Tool hatte…ich musste erst Code klonen, um überhaupt den Screenshot machen zu können ;-)

SQL Server Database Copy Tool – neue Version auf CodePlex

Ich habe soeben eine neue Version des Database Copy Tool for SQL Server auf [Codeplex] hochgeladen. Was hat sich geändert:

  1. Ich habe es mit dem SQL Server 2005 und dem SQL Server 2008 getestet
  2. Kopieren von Dateien wird nun nicht mehr mit der Methode Microsoft.VisualBasic.FileIO.FileSystem.CopyFile durchgeführt. Stattdessen verwende ich einen Aufruf der CopyFileEx aus der Kernel32.dll. Praktisch: Damit kann ich den Fortschritt des Kopierens in meiner eigenen Form anzeigen, statt den Standard-Windows-Kopierdialog zu verwenden
  3. Ein wenig im Code aufgeräumt (aber nur ein Wenig :-))
  4. Ein neuer Dialog um einen Server in der Konfiguration hinzuzufügen (statt der Standard-InputBox)

Hier nochmal ein paar Screenshots

02_settings

03_addserver

Wie ich schon zwei Mal geschrieben hab – Was fehlt noch?

  • Anzeige des Fortschritts des BACKUP und des RESTORE – muss also irgendwie an die Meldungen, die sonst im Management Studio zu sehen sind rankommen
  • Kommentare im Code…;-)

Weiteres dazu in meinen vorherigen Posts [hier]und [hier]