SQL Server Befehle mit PowerShell: Beispiel mehrere DB’s offline setzen

Mit Hilfe von PowerShell kann über die SQL Server Management Objekt (SMO) API direkt T-SQL Befehle auf einem SQL Server ausgeführt werden. Nachfolgend zeige ich ein Beispiel wie anhand einer Liste von Datenbanknamen (Textdatei) die entsprechenden Datenbanken offline gesetzen werden können.

# Voraussetzungen: SQL Server Management Objects (SMO) Assembly laden
 
# Für SQL Server 2012 nachfolgenden PowerShell Befehl nutzen:
Import-Module SQLPS -DisableNameChecking
# Für SQL Server 2008 R2 und SQL Server 2008 die folgenden Befehle ausführen:
add-type -AssemblyName “Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91″ -ErrorAction Stop
add-type -AssemblyName “Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91″ -ErrorAction Stop
add-type -AssemblyName “Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91″ -ErrorAction Stop
add-type -AssemblyName “Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91″ -ErrorAction Stop
add-type -AssemblyName “Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91″ -ErrorAction Stop
 
# Datenbankliste von Textdatei laden
$databases = Get-Content "C:\temp\DatabaseList.txt"
 
foreach ($db in $databases) {
    invoke-sqlcmd -ServerInstance localhost -Database master -Query "IF db_id('$db') is not null ALTER DATABASE $db SET OFFLINE WITH ROLLBACK IMMEDIATE"       
}
 
# Den PowerShell Provider wieder auf das Dateisystem zurücksetzen (optional)
cd C:

Carriage Return/Line Feed und Tabulator enfternen für Ansicht in Excel

Mit dem folgenden SQL Code können Carriage Return (CR) und Line Feeds (LF) entfernt werden. Dies ist z.B. hiflreich, wenn ich Daten vom SQL Server direkt in Excel anzeigen will. Entweder, wenn ich die Daten direkt innerhalb vom SQL Server Management Studio aus dem Results Tab mittels Copy/Paste in Excel transferieren will oder wenn ich z.B. direkt eine CSV-Datei, die mittels SQL Server erstellt wird, in Excel öffnen will. Damit Excel die Zuordnungen der Spalten richtig vornimmt und es keine Verschiebungen gibt, ist es notwendig, dass die entsprechenden Steuerzeichen im Inhalt bereits bei der Aufbereitung der Daten entfernt werden.

Wenn die Quelle mit einem Texteditor betrachtet wird (Beispiel CSV), sehen die Steuerzeichen zum Beispiel folgendermassen aus:

Carriage Return Line Feed

Bestehende CR/LF eliminieren und mit einem Komma ersetzen:

SELECT REPLACE(column_name, CHAR(13) + CHAR(10), ', ') FROM table_name;

CHAR(13): Carriage Return
CHAR(10): Line feed

Bestehende Tab (Tabulator-Zeichen) eliminieren und mit einem Leerzeichen ersetzen:

SELECT REPLACE(column_name, CHAR(9), ' ') FROM table_name;

CHAR(9): Tabulator

Ändern SQL Collation in bestehender Datenbank

Mit Hilfe des nachfolgenden Scripts kann die bestehende Collation innerhalb allen Tabellenfeldern bzw. der ganzen Datenbank geändert werden. Im nachfolgenden Beispiel wird die Collation auf Latin1_General_CI_AI geändert.

--1. Change Collation of the Database
Alter database YourDatabase collate Latin1_General_CI_AI
 
USE [YourDatabase]
GO
 
declare
@NewCollation varchar(255)
,@Stmt nvarchar(4000)
,@DBName sysname
 
--2. Change Collation of every column of every table in the database
set @NewCollation = 'Latin1_General_CI_AI' -- change this to the collation that you need
set @DBName = DB_NAME()
declare
@CName varchar(255)
,@TName sysname
,@OName sysname
,@Sql varchar(8000)
,@Size int
,@Status tinyint
,@Colorder int
 
declare curcolumns cursor read_only forward_only local
 
for select
QUOTENAME(C.Name)
,T.Name
,QUOTENAME(U.Name) + '.' +QUOTENAME(O.Name)
,C.Prec
,C.isnullable
,C.colorder
from syscolumns C
inner join systypes T on C.xtype=T.xtype
inner join sysobjects O on C.ID=O.ID
inner join sysusers u on O.uid = u.uid
where T.Name in ('varchar', 'char', 'text', 'nchar', 'nvarchar', 'ntext')
and O.xtype in ('U')
and C.collation != @NewCollation
and objectProperty(O.ID, 'ismsshipped')=0
order by 3, 1
 
open curcolumns
 
SET XACT_ABORT ON
begin tran
fetch curcolumns into @CName, @TName, @OName, @Size, @Status, @Colorder
 
while @@FETCH_STATUS =0
begin
set @Sql='ALTER TABLE '+@OName+' ALTER COLUMN '+@CName+' '+@TName+ isnull ('(' +convert(varchar,@Size)+')', '') +' COLLATE '+ @NewCollation +' '+case when @Status=1 then 'NULL' else 'NOT NULL' end
exec(@Sql) -- change this to print if you need only the script, not the action
fetch curcolumns into @CName, @TName, @OName, @Size, @Status, @Colorder
end
 
close curcolumns
deallocate curcolumns
commit tran

SQL Server 2012: Installation Business Intelligence Tools

Wenn Entwicklungen für SQL Server 2012 vorgenommen werden müssen (Reporting Services, SSIS etc.) werden die SQL Server Data Tools (vorheriger Name: Business Intelligence Development Studio) benötigt, die Bestandteil der SQL Server Installation sind.

Installation
Zu finden ist das Feature unter: Shared Features > SQL Server Data Tools
Nach erfolgreicher Installation ist ein neuer Punkt “Microsoft SQL Server 2012” > “SQL Server Data Tools” im Startmenü vorhanden. Unter der Haube wird das Feature im Visual Studio 2010 integriert.

SQL Server Data Tools – December 2012 update
SSDT for Visual Studio 2012: http://msdn.microsoft.com/en-us/jj650015
SSDT for Visual Studio 2010: http://msdn.microsoft.com/en-us/jj650014

SQL Server Management Studio: Das Speichern von Änderungen ist nicht zulässig

Bei jeder neuen Installation von SQL Server Mangement Studio suche ich jeweils wieder die Einstellung, die es mir erlaubt eine bestehende Tabelle anzupassen. Wer versucht über die Entwurfsansicht eine Tabelle in einer SQL Server Datenbank zu ändern, erhält per default eine Fehlermeldung, die da lautet:

Das Speichern von Änderungen ist nicht zulässig. Die vorgenommenen Änderungen erfordern das Löschen und Neuerstellen der folgenden Tabellen. Sie haben entweder Änderungen an einer Tabelle vorgenommen, die nicht neu erstellt werden kann, oder die Option “Speichern von Änderungen verhindern, die die Neuerstellung der Tabelle erfordern” aktiviert.

bzw. in Englisch:

Saving Changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be recreated

Leider ist dies die Standardeinstellung. Ändern lässt sich das unter Optionen > Designers > Tabellen- und Datenbank-Designer. Einfach Haken raus und alles ist wieder gut. Und hier noch im Detail:

  1. Öffnen von Microsoft SQL Server Management Studio
  2. Im Menu Tools > Options auswählen
  3. Designers auswählen
  4. Den Haken bei der Checkbox “Prevent saving changes that require table re-creation”  bzw. “Speichern von Änderungen verhindern, die die Neuerstellung der Tabelle erfordern” entfernen.
  5. OK klicken

Re-creating Tabelle mit Script

 

Windows Internal Database (WID)

Vielmals nutzen Microsoft Tools die interne Windows Internal Database (WID). Auf diese kann folgendermassen zugegriffen werden:

Mittels SQL Server Management Studio:

Servername: .\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

mittels der Command Line (cmd):

cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn
sqlcmd -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

Trigger in MS SQL Server

Mit Hilfe eines Triggers können zum Beispiel Änderungen an Tabellen geloggt werden.

Nachfolgendes Beispiel überwacht die Änderungen, die innerhalb der Tabelle Employee durchgeführt werden und schreibt den Wert vor der Änderung und den jeweiligen neuen Wert nach Durchführung des Updates in die Tabelle dbo.EmployeeAudit. In der Logtabelle werden im weiteren auch der Zeitpunkt der Änderung wie auch der Benutzer, der das Update durchgeführt hat, gespeichert.

CREATE TRIGGER tr_employeeaudit
ON dbo.Employee
FOR UPDATE
AS
DECLARE @now DATETIME
SET @now = getdate()
BEGIN TRY
      INSERT INTO dbo.EmployeeAudit
      (RowImage, PayRate, ChangeDate, ChangeUser)
      SELECT 'BEFORE', INSERTED.PayRate, @now, suser_sname()
      FROM DELETED
      INSERT INTO dbo.EmployeeAudit
      (RowImage, PayRate, ChangeDate, ChangeUser)
      SELECT 'AFTER', INSERTED.PayRate, @now, suser_sname()
      FROM INSERTED
END TRY
BEGIN CATCH
      -- Fehlerbehandlungscode
      ROLLBACK TRANSACTION
END CATCH

Cursor in SQL Server

Mit Hilfe eines Cursors kann auf einzelne Zeilen in einer Tabelle oder einer Ergebnismenge zugegriffen werden und erlaubt so relativ einfach grössere Änderungen an Daten.
Nachfolgendes Beispiel führt eine Aktualisierung (Update) für jeden Datensatz in der Tabelle Employee durch (das Attribut CursorModified wird mit dem aktuellen Systemdatum aktualisiert).

DECLARE @EmployeeID int
DECLARE curemp CURSOR FOR SELECT EmployeeID
FROM HumanResources.Employee
OPEN curemp
FETCH curemp INTO @EmployeeID
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE HumanResources.Employee
SET CursorModified = GETDATE()
WHERE EmployeeID = @EmployeeID
FETCH curemp INTO @EmployeeID
END
CLOSE curemp
DEALLOCATE curemp

 

Fehlerbehandlung in SQL Server

Nachfolgendes Konstrukt überprüft den Returncode der auszuführenden StoredProcedure (dbo.spImport). Falls der Vorgang erfolgreich durchgeführt werden konnte wird der Returncode 0 zurückgegeben. Im Fehlerfall wird der Variable @RetCode der Wert 1 zugewiesen und ein RAISERROR mit Schweregrad 16 zurückgegeben. Die Ausführung des Konstruktes wird dann unterbrochen.

DECLARE @RetCode int
EXECUTE @RetCode = dbo.spImport '\\Servername\d$\source.csv'
IF @RetCode = 1
RAISERROR ('Fehler: Die Quelldatei konnte nicht gefunden werden!', 16, 1)

Ab SQL Server 2005 kann die Fehlerbehandlung auch mit TRY und CATCH durchgeführt werden. Innerhalb des Catch-Blockes kann dann individuell auf den Fehler reagiert werden:

BEGIN TRY
-- Fehleranfälliger Code
END TRY
BEGIN CATCH
-- Fehlerbehandlungscode
END CATCH

Explizite Transaktionen

Die Einleitung einer expliziten Transaktion erfolgt durch die folgende Anweisung:

BEGIN TRANSACTION

Sind alle Anweisungen innerhalb der Transaktion erfolgreich bearbeitet worden, muss die Transaktion als gültig gekennzeichnet und in die Datenbank übernommen werden. Mit dem folgenden Befehl wird die Transaktion beendet:

COMMIT TRANSACTION

Ist während der Transaktion ein Fehler aufgetreten, der es erforderlich macht, alle bereits vorgenommenen Änderungen rückgängig zu machen, muss ein Rollback ausgeführt werden, was mit folgender Syntax erreicht wird:

ROLLBACK TRANSACTION