SQL View – Manchmal darf’s nicht null sein !

In einer SQL View soll eine Spalte als (int, NULL) ausgegeben werden, die in der zugrundeliegenden Tabelle als Spaltentyp (int, Not NULL) definiert ist.

Hintergrund dieser Forderung ist, das die View als Datasource für ein recht aufwendig zu formatierendes DataGridView dient und bei der Formatierung dieser Spalte nicht als (not null) definiert sein darf.

Mit einem einfachen Cast direkt in der View ist diese Anforderung sehr einfach umzusetzen. (siehe nachfolgenden Code Abschnitt)

SELECT CAST(dbo.MyTable.ID AS int) AS MyID, .....

FROM MyTable

SQL Transaktionsprotokoll verkleinern (sichern) – SQL 2005 – SQL 2008

Bereits vor 6 Jahren (wie die Zeit vergeht) hatte ich in einem Artikel „SQL 2000 Transaktionsprotokoll verkleinern“ beschrieben wie man ein zu groß gewordenes Transaktionsprotokoll verkleinern kann.

Da die neuere Version SQL 2008 nicht mehr über die Option WITH TRUNCATE_ONLY verfügt, funktioniert die beschrieben Methode nicht mehr.

Nachfolgend möchte ich daher eine alternative Method vorstellen, welche den gleichen  gewünschten Effekt hat.

Das verkleinern des Transaktionsprotokolls.

Damit die Verkleinerung des Transaktionprotokolls nicht zu problemen bei einer eventuellen Rücksicherung führt, würde ich vor dem verkleinern unbedingt eine Sicherung der Datenbank und des Transaktionsprotokolls durchführen. Denn nur die Sicherung des Transaktionsprotokolls ermöglicht auch das spätere verkleinern.

Hier also nun der Transakt SQL Code zur Speicherung der DB und des Logs mit anschließendem Verkleiner der Transaktionsprotokoll Datei.

USE pubs
BACKUP DATABASE pubs TO DISK='C:\SqlDataBackup\pubs.bak' WITH INIT
BACKUP LOG pubs_log TO DISK='C:\SqlDataBackup\pubs_log.bak' WITH INIT
DBCC SHRINKFILE(pubs_Log, 10)
GO

Am besten kann man das in einer Batch Datei (Kommando Stapeldatei) einbauen und z. B. regelmäßig als Tast auf dem Rechner laufen lassen.
Wer das regelmäßig ausführen möchte kann das auch in eine Batch Datei packen und von der Kommandozeile ausführen. Was übrigens auch dann als Task ausgeführt werden kann.

@ECHO OFF
set SQL_UTIL_HOME="%ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\osql"
rem Tragen Sie in der nachfolgenden Zeile den Namen des SQL Server ein z.B. (localhost)
set SQL_SERVERNAME=localhost
rem Tragen Sie in der nachfolgenden Zeile den Namen der zu sichernden Datenbank ein
set SQL_DBNAME=pubs
set SQL_LOGNAME=pubs_log
rem Tragen sie den Namen inkl. Pfad der Backup Datei ein
set SQL_BACKUP_FILE='C:\SqlBackup\pubs.BAK'
set SQL_LOG_FILE='C:\SqlBackup\pubs_log.BAK'
@ECHO ON
@ECHO Datenbank sichern
%SQL_UTIL_HOME% -S%SQL_SERVERNAME% -E -n -Q "BACKUP DATABASE %SQL_DBNAME% TO DISK = %SQL_BACKUP_FILE% WITH INIT;"
@ECHO Protokolldatei sichern
%SQL_UTIL_HOME% -S%SQL_SERVERNAME% -E -n -Q "BACKUP LOG %SQL_DBNAME% TO DISK = %SQL_LOG_FILE% WITH INIT;"
@ECHO Protokolldatei verkleinern
%SQL_UTIL_HOME% -S%SQL_SERVERNAME% -E -n -d%SQL_DBNAME%  -Q "DBCC SHRINKFILE(%SQL_LOGNAME%, 10)"

Wenn man nun noch ein Backup Programm wie Cobian Backup verwendet um die erzeugten Datenbank Backup Dateien auf einen FTP Server zu sichern, hat man eine kostenlose Lösung zur Sicherung der SQL Datenbank.

Ich denke, ich werde bald man in einem neuen Beitrag beschreiben, wie man Dedizierte WEB Server, die man bei einem Hoster stehen hat, mit Hilfe von Batch Dateien und Cobian Backup WEB Seiten inkl. Datenbanken ganz einfach und kostenlos sichern kann.

MS SQL – Views ermitteln in welchen ein bestimmtes Feld verwendet wird

In einem recht umfangreichen Projekt (C# Windows Forms Anwendung) welches als Datenbank eine MS SQL Datenbank verwendet, musste ich Erweiterungen der Datenbank (zusätzliche Felder) vornehmen.

Da ich diese neuen Felder auch in allen vorhandenen Abfragen ergänzen musste, in welchen ein anders bereits vorhandenes Feld (wir nennen es einfach mal “EinfahrtLKW”) der gleichen Tabelle ausgegeben wird, habe ich nach einer Lösung gesucht um mir alle Abfragen (Views) anzeigen zu lassen, in dem das Feld EinfahrtLKW Verwendung findet.

Um dies zu realisieren habe ich folgende Systemsicht verwendet:

INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

Meine Abfrage sieht dann so aus:

select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE where COLUMN_NAME = 'EinfahrtLKW'

Und das Ergebnis sieht dann so aus:

 image

Mit dieser Information habe ich nun die Name der Views, die ich anpassen muss.

SQL – Größe aller Tabellen einer Datenbank

Beim stöbern in meinen SQL Skripten bin ich gerade wieder auf das hier nachfolgend aufgeführte Skript zur Ausgabe der “Größe aller Tabellen einer Datenbank” gestoßen.

Da ich sicherlich nicht der einzige bin, der diese Informationen einer Datenbank hin und wieder benötigt, veröffentliche ich hier das Skript mit der Hoffnung dass es jemand gebrauchen kann.

SET NOCOUNT ON
CREATE TABLE #TableSpace 
(
	Rows int, 
	DataSpaceUsed int, 
	IndexSpaceUsed int
)
DECLARE @TableSpace table
(
	TableName varchar(255),
	Rows int, 
	DataSpaceUsed int, 
	IndexSpaceUsed int
)
DECLARE @Rows int, @DataSpaceUsed int, @IndexSpaceUsed int
DECLARE @TableName varchar(255)
DECLARE Table_Cursor CURSOR FOR
SELECT	user_name(o.uid) + '.' + o.name AS table_name
FROM	dbo.sysobjects o, dbo.sysindexes i 
WHERE	OBJECTPROPERTY(o.id, N'IsTable') = 1 
	AND i.id = o.id 
	AND i.indid < 2 
	AND o.name NOT LIKE N'#%' 
	AND xtype = 'U'
ORDER BY 1
OPEN Table_Cursor
---------------------------------
--Set Data
FETCH NEXT FROM Table_Cursor INTO @TableName
INSERT INTO #TableSpace (Rows, DataSpaceUsed, IndexSpaceUsed)
EXEC sp_MStablespace @TableName
SELECT	@Rows = Rows, 
	@DataSpaceUsed = DataSpaceUsed,
	@IndexSpaceUsed = IndexSpaceUsed
FROM	#TableSpace
INSERT INTO @TableSpace (TableName, Rows, DataSpaceUsed, IndexSpaceUsed)
VALUES (@TableName, @Rows, @DataSpaceUsed, @IndexSpaceUsed)
DELETE FROM #TableSpace
--------------------------------
WHILE @@FETCH_STATUS = 0


    BEGIN
    	---------------------------------
    	--Set Data
    	FETCH NEXT FROM Table_Cursor INTO @TableName
    	
    	INSERT INTO #TableSpace (Rows, DataSpaceUsed, IndexSpaceUsed)
    	EXEC sp_MStablespace @TableName
    	
    	SELECT	@Rows = Rows, 
    		@DataSpaceUsed = DataSpaceUsed,
    		@IndexSpaceUsed = IndexSpaceUsed
    	FROM	#TableSpace
    	
    	INSERT INTO @TableSpace (TableName, Rows, DataSpaceUsed, IndexSpaceUsed)
    	VALUES (@TableName, @Rows, @DataSpaceUsed, @IndexSpaceUsed)
    	
    	DELETE FROM #TableSpace
    	--------------------------------
END

CLOSE Table_Cursor
DEALLOCATE Table_Cursor
DROP TABLE #TableSpace
SELECT * 
FROM @TableSpace
ORDER BY Rows DESC

Über die Herkunft dieses Skripts (ich weiß dass ich zumindest die Idee von irgendwoher hatte) bin ich mir nicht mehr sicher. Eine gerade durchgeführte Recherche im Internet hat mir auch keinen direkten Aufschluss gegeben woher ich das Skript oder die Idee dazu hatte.

Sollte also jemand die Herkunft kennen, bitte einfach per Kommentar posten.

T-SQL – Select * from < table > where < datetimeField > < 2007

Da mein Hauptgeschäft nicht in der Erstellung von SQL Abfragen liegt, kommt es immer wieder vor, dass ich nach ein und der gleichen Lösung für ein Problem immer wieder mal recherchieren muss, da ich mir die Lösungen nicht (bzw. nicht immer) merken kann.

Dieser Beitrag fällt unter diese Kategorie (Und nachdem ich es nun aufgeschrieben habe, werde ich es wohl nie mehr vergessen smile_shades, na dann hätte es ja was genutzt dies hier aufzuschreiben.).

Nun aber zum eigentlichen Problem bzw. zur Lösung:

Ich weiß nicht wie oft ich schon mal schnell eine SQL Abfrage machen musste, die alle Daten anzeigt, oder löscht oder was auch sonst immer, welches entweder vor, nach oder in einem bestimmten Jahr einen Eintrag in einem datetime Feld enthält

Bei eigenen Tabellen Entwürfen häufig das Feld „Created“, oder „LastChange“ – ein Datenbank Feld mit dem Datentyp datetime.

Nun ist die Abfrage „Select * from  <table> where <datetimeField>  < 2007“ thumbs_down wie in der Überschrift verwendet syntaktisch nicht falsch, also es wird kein SQL Fehler erzeugt, aber es wird auch nicht das gewünschte Ergebnis angezeigt.

So wie die Abfrage aufgebaut ist, wird das Ergebnis leer sein, da keiner der datetime Werte kleiner sein dürfte als der Wert 2007.

Die korrekte Abfrage (besser gesagt, eine korrekte Möglichkeit) dafür sieht wie folgt aus:

Select * from  <table> where DatePart(year,<datetimeField>)  <= 2007 thumbs_up

Hier wird nun nur nach dem Jahr innerhalb des datetime Ausdrucks verglichen und der Vergleich mit 2007 ist dann richtig.

Mehr über DatePart auf MS TechNet

SQL Profis mögen mir verzeihen wenn ich bei den verwendeten Begriffen nicht immer ganz getroffen habe, aber wie gesagt, SQL Abfragen ist nicht unbedingt mein Spezialgebiet. T-SQL – Select * from < table > where < datetimeField > < 2007 weiterlesen

SQL Datensicherung per BATCH Datei

Immer wieder taucht die Frage auf, wie man auf einfache Art und Weise eine Datensicherung einer SQL Datenbank anlegen kann.

Auch wenn Heute viele gar nicht mehr genau wissen wozu man die Eingabeaufforderung, Batch Dateien und Kommandozeilen (das sind Programme die keine GUI haben) Programme verwendet ist die Zeit dieser Tools noch lange nicht zu Ende, wie auch dieser Beitrag wieder einmal beweist.

Und eines dieser Kommandozeilen Programme, das osql tool des SQL Server, ist auch die Basis dieses Artikel.

Das osql tool des Microsoft SQL Server (sowohl im SQL Server 2000, der MSDE und auch in allen SQL 2005 Versionen enthalten) bietet die Möglichkeit Transact SQL Befehle (T-SQL) direkt von der Kommandozeile auszuführen.

Nutzen wir doch diese Möglichkeit und erstellen wir uns eine Batch Datei (Datei die auf Kommandozeilen Ebene ausgeführt werden kann), mit der wir eine Datenbank eines SQL Server’s sichern können.

Ich Denke nicht dass man jede Zeile der Batch Datei erklären muss, deshalb hier nun den Inhalt der Batch Datei:

@ECHO OFF
rem Für die Verwendung mit SQL Server 2000 oder der MSDE in der nachfolgenden Zeile  das rem am Anfang entfernen
rem set SQL_UTIL_HOME="%ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\osql"
rem Für die Verwendung mit SQL Server 2005 oder SQL Server 2005 Express in der nachfolgenden Zeile  das rem am Anfang entfernen
set SQL_UTIL_HOME="%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\osql"
rem Tragen Sie in der nachfolgenden Zeile den Namen des SQL Server ein z.B. (localhost)
set SQL_SERVERNAME=HP-DN01\SQL2005
rem Tragen Sie in der nachfolgenden Zeile den Namen der zu sichernden Datenbank ein
set SQL_DBNAME=FASC
rem Tragen sie den Namen inkl. Pfad der Backup Datei ein
set SQL_BACKUP_FILE='C:\temp\fasc.bak'
@ECHO ON
%SQL_UTIL_HOME% -E -S%SQL_SERVERNAME% -n -Q "BACKUP DATABASE %SQL_DBNAME% TO DISK = %SQL_BACKUP_FILE% WITH INIT"
Hier kann man die Datei herunterladen Backup.Bat.
Einfach herunterladen, das ZIP File entpacken und dann die Backu.BAT Datei öffnen und die darin gesetzten Environment Variablen auf die eigenen Bedürfnisse anpassen.

T-SQL Replace

Vermutlich ist das für die meisten nichts neues, aber ich habe nun schon einige male nach dem enrsprechenden Befehl gesucht und dabei Zeit vergeudet, aus diesem Grund mach ich nun für mich selbst diesen Blog Eintrag.


Hintergrund:


In einer Datenbank (MS-SQL) sind in einem Tabellenfeld Werte die gegen andere Werte ausgetauscht werden müssen.


Ein simples Update wie das hier:

UPDATE [TABLENAME] SET [FIELDNAME] = NEWVALUE

Nun ist es aber so, dass ich nur einen Teil des Inhaltes ändern muss, im speziellen Fall wird in der Spalte der Tabelle Informationen über den Speicherort einer Datei vorgehalten.


Hier ein Beispielinhalt:


C:\Program Files\hMailServer\Data\ihrportal\AB\{AB88191E-9A94-4C08-90B8-597A4B2FA3E2}.eml


Nun befinden sich die Daten aber nicht mehr auf dem Laufwerk C: sondern die Daten befinden sich nun auf Laufwerk D: und dort in dem Verzeichnis hMailServer\Data also auf D:\hMailServer\Data. Somit muss der Inhalt des Feldes wie folgt geändert werden:


D:\hMailServer\Data\ihrportal\AB\{AB88191E-9A94-4C08-90B8-597A4B2FA3E2}.eml


Dies muss nun bei tausenden von Einträgen durchgeführt werden.


Lösung:


Um nun die Daten in der Spalte messagefilename der Tabelle hm_messages zu ändern habe ich folgedes Query ausgeführt:

update hm_messages
set messagefilename = REPLACE(messagefilename,‘C:\Program Files\hMailServer\Data’,‘D:\hMailServer\Data’)

So und nun hoffe ich das ich das nächste mal wenn ich so etwas machen möchte, daran denke dass ich dazu einen Blog Eintrag verfasst habe.

Doppelte Datensätze finden – SQL

Hier ein einfaches SQL Skript um doppelte Datensätze in einer Tabelle zu finden und anzeigen zu lassen.

Im Beispiel gehen wir von folgenden Informationen aus:

Die Tabelle in der Datenbank heißt: Products

Das Referenzfeld in der Tabelle heißt: productcode

SELECT productcodes, COUNT(productcode) AS NumOccurrences
FROM Products
GROUP BY productcode
HAVING ( COUNT(productcode) > 1 )

Angezeigt bekommt man alle productcodes der Datensätze die mehr als einmal in der Tabelle vorhanden sind.

SQL 2000 Transaktionsprotokoll verkleinern

Das Transaktionsprotokoll wird größer und größer, die Festplatte droht zu platzen, was kann man tun ?.

Wenn trotz Datensicherung das Transaktionsprotokoll nicht abgeschnitten und verkleinert wird. (Voraussetzung dafür ist allerdings, dass auch in den Einstellungen automatisch verkleinern aktiviert ist), kann man mit dem nachfolgenden SQL Script das Transaktionsprotokoll der SQL 2000 Datenbank manuell verkleinern.

BACKUP LOG pubs WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(pubs_log, 10)
GO

Im Beispiel wurde die Datenbank pubs verwendet, ersetzen Sie einfach pubs gegen Ihre Datenbank und führen diese Befehle im SQL Query Analyzer aus.