Einleitung
1.1 Beispieldaten
1.2 Testumgebung

2 Sql Datenbanken mit ADO.Net bearbeiten
2.1 Verbindung zu einer Sql Datenbank herstellen
      Beispiel 1: Anzeige der installierten Datenprovider  
2.1.1 ServerConnection Class     
         Beispiel 1: ServerConnection Constructor (String)
         Beispiel 2: ServerConnection Constructor ($SqlConnection))
         Beispiel 3a: Anlage eines neuen Schemas in der SQL-Datenbank
         Beispiel 3b: Löschen eines Schemas in der SQL-Datenbank
2.1.2 SqlCommand Class
         Beispiel 1: Die Klassen SqlConnection, SqlCommand und SqlDataReader
         Beispiel 2: Die Klassen SqlConnection, SqlCommand und SqlDataReader
Praxis
      Beispiel 1: Einfaches Auslesen von Daten aus der AdventureWorks2008-Datenbank (Select From *)
      Beispiel 2: Komplexes Auslesen von Daten aus der AdventureWorks2008-Datenbank (Select From *)
 
3 Verwaltungsbefehle (Transact-SQL)
3.1 DBCC (Database Console Commands)
      Beispiel 1: Konsistenz einer Datenbank prüfen (DBCC CHECKDB)
3.2 Tabellen
      Beispiel 1: Tabellen einer Datenbank auslesen
      Beispiel 2: Alle Datenbanken auf einem Server abfragen
3.3 BCP.exe (Bulk Copy Program)
      Beispiel 1: Abfrage gegen eine SQLDatenbank mit BCP.exe

Sql Datenbanken mit dem Powershellprovider bearbeiten
    Beispiel 1: Anzeige aller Tabellen eines Schemas
5.1  Bereitstellen des SqlProviders
       Beispiel 1: Add the SQL Server Provider
       Beispiel 2: Ergänzen des Powershellprofils um den SQLProvider
 


1 Einleitung

Auf dieser Seite gehe ich auf die Möglichkeiten ein, mit Powershell auf SqlServer und SqlDatenbanken zuzugreifen und dort Daten lesen und zu manipulieren. Einen Überblick über die verschiedenen Möglichkeiten auf eine Datenbank zuzugreifen findet ihr in diesem Whitepaper unter: MSDN: Understanding and Using PowerShell Support in SQL Server 2008

 

1.1 Beispieldaten

Für viele Beispiele in diesem Kapitel benütze ich die AdventureDatenbank, die man sich hier kostenlos herunterladen kann:
Codeplex: Microsoft SQL Server Community Projects & Samples

 

1.2 Testumgebung

Ich habe meine Skripte entweder von einem Windows7-Domänenclient gegen einen SQL2008R2-Datenbank unter Windows2008R2 getestet oder lokal auf einem Windows7-Client mit lokaler SqlServer2008R2-Express (Advanced).
DownloadCenter: Microsoft® SQL Server® 2008 R2 SP2 - Express Edition

 

2 ADO.Net und Sql Server - Grundlagen

2.1 Verbindung zu einer Sql Datenbank herstellen

Um auf Datenbanken zugreifen zu können, benötigt man zuerst eine Verbindung zu dieser Datenbank. In diesem Kapitel zeige ich für diesen Zweck
  • die .Net Klasse ServerConnection"  (-> Kapitel 2.1.1)
  • die .Net Klasse SqlCommand (-> Kapitel 2.1.2)  (Das ist der Standardweg!)
  • den SQL-Powershellprovider (-> Kapitel 2.1.3)
Daneben gibt es auch noch die Möglichkeit über OleDB und die Klasse OleDBConnection auf eine Sql-Datenbank zuzugreifen. Einige Beispiele zu OleDB, sowie Zugriffe auf andere Datenquellen wie Excel oder Access findet ihr unter Datenzugriffe über ADO.Net - 1 Connected Classes -> 1.2 ConnectionObject und den weiteren Kapiteln dort.


Die Standardklasse zum Zugriff auf eine Sql-Datenbank ist wie schon erwähnt die Klasse SqlCommand, die wie der Name schon vermuten lässt, für SqlDatenbanken optimiert ist. Da aber in vielen Beispielen im Netz die ServerConnection-Klasse (Kapitel 2.1.1) vorkommt, zeige ich auch deren Verwendung. Für eigene neue Skripte empfiehlt sich dennoch die SqlCommand-Klasse (-> Kapitel 2.1.2).
 

2.1.1 ServerConnection Class

Microsoft.SqlServer.Management.Common.ServerConnection

MSDN: ServerConnection Class

Die ServerConnection-Klasse ist hier die erste Möglichkeit, sich mit einer Sql-Datenbank zu verbinden. Im Vergleich zur im nächsten Kapitel beschriebenen SqlCommand Klasse muss die beinhaltende Assembly (Erklärung erfolgt sofort) mit einer einzigen Codezeile nachgeladen werden, Auf der anderen Seite muss die Verbindung nicht bei jedem Zugrif explzit geöffnet und geschlossen ($SqlConnection.Open / $SqlConnection.Close) werden. Beide genannten Faktoren sind aber weder ein großes Problem, noch ein besonderer Vorteil. Man muss diese Dinge nur einfach beachten.

Um die Klasse ServerConnection benutzen zu können, muss wiegesagt im Code das Assembly "Microsoft.SqlServer.ConnectionInfo" geladen werden. Ein Assembly ist eine dll- oder exe-Datei, die ein oder mehrere Klassen enthalten kann. Den Namen der Assembly findet man in der MSDN in der Klassenbeschreibung

Da diese Assembly für die ServerConnection Klasse nicht default von Windows7 oder Server2008R2 geladen wird, benötigt man im Skript die zusätzliche CodeZeile 
    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
Der Speicherort liegt übrigens unter %windir%/assembly ("c:\Windows\assembly")

Ein paar weitere, allgemeine Informationen zu Assemblies findet ihr unter Grundlagen - Automation Interfaces - .Net  -> 4 Asemblies

 

Einige Konstruktoren der Klasse ServerConnection

Einen Überblick über alle Constructoren findet ihr unter MSDN: ServerConnection Constructor

Ich habe in den folgenden Kapiteln versucht, mit verschiedenen Farben die Zusammenhänge hervorzuheben.

 

Beispiel 1: ServerConnection Constructor (String)

Diesem Constructor wird "Servername\ Instance" als Parameter der Klasse "System.String" übergeben

Set-StrictMode -Version "2.0"
Clear-Host
 
#define database, sqlserver

$DatabaseName = "AdventureWorks2008"
$SqlServerName = ".\SQLEXPRESS"  
#$SqlServerName = "Server01" #wenn die Instance "Default" ist, reicht nur der Servername

#add additional assembly for sqlserver
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

#define variables for connection to database
$ServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($SqlServerName)
$ServerConnection.DatabaseName = $DatabaseName

#define sqlquery
$QueryString = "SELECT TOP 3 Name, CostRate FROM Production.Location"

#execute the sqlquery
$Reader = $ServerConnection.ExecuteReader($QueryString)
While ($Reader.Read())
{
   $Name = $Reader.GetValue(0);
   $cRate = $Reader.GetValue(1);
   Write-Host $Name,"(",$cRate,")"
}
$Reader.Close()

#tested for Win7/ Powershell V2.0 / SQLServer2008R2
#tested for Win7/ Powershell V2.0 / SQLExpress2008R2
#AdventureWorks2008R2_Database.zip - http://msftdbprodsamples.codeplex.com/Releases/
#Ausgabe (sofern die Adventurebeispieldatenbank vorhanden ist)

Finished Goods Storage ( 0,0000 )
Frame Forming ( 22,5000 )
Frame Welding ( 25,0000 )

Man übergibt also diesem überladenen Constructor den String "Servername\ Instance", unter denen die eigentliche Datenbank liegt. Dadurch wird eine Instanz der Klasse erstellt, mit der man anschließend weiterarbeiten kann.

Auf die Readerklasse und die Ausgabe der gewonnenen Daten gehe ich später ein.

Beispiel 2: ServerConnection Constructor (SqlConnection)

Diesem Konstruktor wird nicht wie in Besipiel 1 ein String, sondern ein Object der Klasse SqlConnection übergeben. Sieht man sich die Klasse SqlConenction in der MSDN an, so erkennt man die zahlreichen zusätzlichen Stellschrauben beim Verbindungsaufbau zur SqlDatenbank (SecurityCredentials, Timeouts, Statistics und vieles mehr). Ähnliches gilt auch für die Klasse SqlConnectionInfo (MSDN: SqlConnectionInfo Class)

Ich habe leider zu wenig Erfahrung im SqlServer-Bereich, um die Methoden und Eigenschaften der Klassen SqlConnection und SqlConnectionInfo zu erklären oder zu bewerten.

Set-StrictMode -Version "2.0"
Clear-Host

#define database, sqlserver and instance

$DatabaseName = "AdventureWorks2008"
$SqlServerName = ".\SQLEXPRESS"  
#$SqlServerName = "Server01" #wenn die Instance "Default" ist, reicht nur der Servername

#add additional assembly for sqlserver
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

$ConnectionString = "Server=$SqlServerName;Database=$DataBaseName;Integrated Security=True"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
$ServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($SqlConnection)

#define sqlquery
$QueryString = "SELECT Name, CostRate FROM Production.Location"

$Reader = $ServerConnection.ExecuteReader($QueryString)
#[System.Data.SqlClient.SqlDataReader]$Reader = $ServerConnection.ExecuteReader($QueryString)

 
While ($Reader.Read())
{
   $Name = $Reader.GetValue(0);
   $cRate = $Reader.GetValue(1);
   Write-Host $Name,"(",$cRate,")"
}
$Reader.Close()

#tested for Win7/ Powershell V2.0 / SQLServer2008R2
#tested for Win7/ Powershell V2.0 / SQLExpress2008R2
#AdventureWorks2008R2_Database.zip - http://msftdbprodsamples.codeplex.com/Releases/
#Ausgabe  #identisch zum vorangegangenen Beispiel 1

Eine wichtige Eigenschaft der Klasse [System.Data.SqlClient.SqlConnection] ist der ConnectionString, mit dem man die Eigenschaften einer Connection steuern kann. Tiefere Infos unter

Beispiel 3a: Anlage eines neuen Schemas in der SQL-Datenbank
Hier wird wie in Beispiel 1 als Constructor der SqlServername  String übergeben

Set-StrictMode -Version "2.0"
Clear-Host


[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

$SqlServerName = ".\SqlExpress"
$ServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($SqlServerName)

$QueryString = "USE AdventureWorks2008" #Datenbankname
$Reader = $ServerConnection.ExecuteReader($QueryString)
$Reader.Close()

$QueryString = "Create SCHEMA MySchema01"
$Reader = $ServerConnection.ExecuteReader($QueryString)
$Reader.Close()

#getestet mit Win7/ Powershell V2.0 / SQLServer 2008R2
#getestet mit Win8/ Powershell V2.0 / SQLServer 2012Express

Query1: Verbinden zur Datenbank "AdventureWorks2008"
Query2: Anlage des Schemas "MySchema01"

Das Ergebnis sieht dann etwa so aus (in SQLServer 2012).

 

Beispiel 3b: Löschen des Schemas in der SQL-Datenbank

Set-StrictMode -Version "2.0"
Clear-Host


[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

$SqlServerName = "Dom1Srv01\SqlExpress"
$ServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($SqlServerName)

$QueryString = "USE TK432" #Datenbankname
$Reader = $ServerConnection.ExecuteReader($QueryString)
$Reader.Close()

$QueryString = "DROP SCHEMA mySchema01"
$Reader = $ServerConnection.ExecuteReader($QueryString)
$Reader.Close()

#getestet mit Win7/ Powershell V2.0 / SQLServer 2008R2
#getestet mit Win8/ Powershell V2.0 / SQLServer 2012Express

 

2.1.2 SqlCommand Class

System.Data.SqlClient.SqlCommand

MSDN: SqlCommand Class
 

Beispiel 1: Anzeige der installierten Datenprovider

Die aktuell auf dem System installierten ADO.NET Datenprovider bekommt man aus der Klasse "DBProviderFactories"

Set-StrictMode -Version "2.0"
Clear-Host


$Providers = [System.Data.Common.DBProviderFactories]::GetFactoryClasses()
$Providers | foreach {
 "Name: {0}`nInvariantName: {1}`nDescription: {2}`nAssemblyQualifiedName: {3} " -f ` $_.Name,$_.InvariantName,$_.Description,$_.AssemblyQualifiedName
 "{0}" -f "-"*40
}

#getestet mit Win7/ Powershell V2.0 / SQLServer 2008R2
#mögliche Ausgabe gekürzt

Name: Odbc Data Provider
InvariantName: System.Data.Odbc
Description: .Net Framework Data Provider for Odbc
AssemblyQualifiedName: System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral..
----------------------------------------
Name: OleDb Data Provider
InvariantName: System.Data.OleDb
Description: .Net Framework Data Provider for OleDb
AssemblyQualifiedName: System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, ...
----------------------------------------
Name: OracleClient Data Provider
InvariantName: System.Data.OracleClient
Description: .Net Framework Data Provider for Oracle
AssemblyQualifiedName: System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, ...
----------------------------------------
Name: SqlClient Data Provider
InvariantName: System.Data.SqlClient
Description: .Net Framework Data Provider for SqlServer
AssemblyQualifiedName: System.Data.SqlClient.SqlClientFactory, System.Data, 
----------------------------------------
Name: Microsoft SQL Server Compact Data Provider
InvariantName: System.Data.SqlServerCe.3.5
Description: .NET Framework Data Provider for Microsoft SQL Server Compact
AssemblyQualifiedName: System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, ...

Im Folgenden spielt der "SqlClient Data Provider" mit der Klasse "Sqlcommand" die Hauptrolle.



Anstelle der ServerCommand-Klasse wie im vorigen Kapitel  benutze ich nun wiegesagt die SqlCommand-Klasse, um die Verbindung zur Datenbank herzustellen. Sql-Command ist dafür ansich die Standardklasse. Allzugroß ist der Unterschied zu den Beispielen in Kapitel 2.1.1 ServerCommand Class gar nicht. Vergleicht einfach nur mal die grün eingefärbten Codeteile des nächsten Beispiels mit den vorigen Beispielen.
 

ConnectionPooling / Verbindungspooling

Auffällig im nächsten Beispiel sind die hier dunkelgrau eingefäbten Codestellen, um eine Datenbankverbindung zu öffnen "$SqlConnection.Open()" und wieder zu schließen "$SqlConnection.Close()". Solch ein open() und close() erfordert grundsätzlich immer einiges an Resourcen, was eventuell bei einem resourcenbewussten Programmierer ein paar Sorgenfalten entstehen lässt. Die Lösung dazu lautet Connection Pooling, siehe MSDN: Using Connection Pooling with SQL Server Mit anderen Worten kümmert sich .Net automatisch darum, geschlossene Verbindungen nicht sofort zu verwerfen, sondern zwischenzuspeichern, um damit erneute Verbindungen schnell über diesen Cache resourcenschonend erneut aufzubauen. Über den Connection-String könnte man das Poolin deaktivieren.

 

Beispiel 2a: Die Klassen SqlConnection, SqlCommand und SqlDataReader

In diesem Beispiel frage ich auf möglichst einfache Weise mit der Klassen "SqlCommand" eine Datenbank ab. Die Verbindung zur Datenbank wird mittels der Klasse "SqlConnection" erstellt und die Auswertung der einzelnen Datensätze über die Klasse SqlDataReader

Set-StrictMode -Version "2.0"
Clear-Host

#define database, sqlserver and instance

$DatabaseName=   "AdventureWorks2008"
#$DatabaseName=   "AdventureWorks2008R2"

$SqlServerName = ".\SQLEXPRESS"  
#$SqlServerName = "Server01" #wenn die Instance "Default" ist, reicht nur der Servername

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SqlServerName;Database=$DataBaseName;Integrated Security=True"
$SqlConnection.Open()
 
$QueryString = "SELECT Name, CostRate FROM Production.Location"  
$SqlCommand = New-Object System.Data.SqlClient.SqlCommand
$SqlCommand.CommandText = $QueryString
$SqlCommand.Connection = $SqlConnection

#[System.Data.SqlClient.SqlDataReader]$Reader = $SqlCommand.ExecuteReader()
$Reader =
$SqlCommand.ExecuteReader()

while ($Reader.Read())
{
   $Name = $Reader.GetValue(0);
   $cRate = $Reader.GetValue(1);
   Write-Host $Name,"(",$cRate,")"
}
$Reader.Close()
$SqlConnection.Close()

#tested for Win7/ Powershell V2.0 / SQLServer2008R2
#tested for Win7/ Powershell V2.0 / SQLExpress2008R2
#AdventureWorks2008R2_Database.zip - http://msftdbprodsamples.codeplex.com/Releases/

#Ausgabe gekürzt

Finished Goods Storage ( 0,0000 )
Frame Forming ( 22,5000 )
Frame Welding ( 25,0000 )

 

Kap2.1.2 SqlCommand Class-Beispiel 1.ps1.txt

Eine wichtige Eigenschaft der Klasse [System.Data.SqlClient.SqlConnection] ist der ConnectionString, mit dem man die Eigenschaften einer Connection steuern kann. Tiefere Infos unter


Beispiel 2b: Die Methode "GetOrdial" der Klasse SqlDataReader
MSDN: SqlDataReader.GetOrdinal Method

Im Vergleich zu Beispiel 1 ist dieser Code hier durch das Ausnützen der GetOrdial-Methode der SqlDataReader-Klasse bei der Ausgabe dynamischer. Anhand der Elemente des Arrays $Columnames erzeugt das Skript bei der Ausgabe einen Header und sucht sich selbst die richtige Spaltennummer.

In dem Skript braucht man neben den Datenbankdaten nur die benötigten Feldnamen sowie den QueryString einzutragen, Um die Ausgabe kümmert sich der Code selbst

Set-StrictMode -Version "2.0"
Clear-Host

#define database, sqlserver and instance
$DatabaseName=   "AdventureWorks2008"
$SqlServerName = ".\SQLEXPRESS"

 
$ColumnNames = @("CostRate","ModifiedDate","Name")
$ColumnNamestring = $ColumnNames -Join ","
$QueryString = "SELECT TOP 3 $ColumnNamestring FROM Production.Location"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SqlServerName;Database=$DataBaseName;Integrated Security=True"
$SqlConnection.Open()
 
$SqlCommand = New-Object System.Data.SqlClient.SqlCommand
$SqlCommand.CommandText = $QueryString
$SqlCommand.Connection = $SqlConnection

#[System.Data.SqlClient.SqlDataReader]$Reader = $SqlCommand.ExecuteReader()
$Reader = $SqlCommand.ExecuteReader()

#Header
$FieldCount = $Reader.FieldCount  
$Headers = @()
  For ($Field = 0;$Field -lt $FieldCount; $Field++){  #ColumnsNames
     $Headers += $Reader.GetName($Field)
     }
$Headers -Join ";"       

#Data
While ($Reader.Read())   
{
   $Result = @()
   $ColumnNames | ForEach {
      $OrdinalNumber = $Reader.GetOrdinal($_)
      $Result += $Reader.GetValue($OrdinalNumber)
      }
   Write-Host $($Result -Join ";")
}

$Reader.Close()

#Ausgabe

CostRate;ModifiedDate;Name
0,0000;01.06.2002 00:00:00;Tool Crib
0,0000;01.06.2002 00:00:00;Sheet Metal Racks
0,0000;01.06.2002 00:00:00;Paint Shop

MSDN: SqlDataReader.GetOrdinal Method

Noch praktikabler wird der Output, wenn man die Ergebnisse in eine Datenbank im Arbeitsspeicher schreibt (Dataset/ Datatable). Im folgenden Kapitel 2.2 Praktische Beispiele -> Beispiel 1 könnt ihr euch das ansehen.
 

2.1.3 SQL Datenbanken mit dem Powershellprovider bearbeiten

Mit dem SQLProvider präsentieren sich ein SQLServer und seine Datenbanken als Baum. Durch diesen bewegt man sich einfach mit den cmdlet Set-Location, zeigt Items im Baum mit Get-Childitem an, oder erstellt und löscht Items mit Set-Item und Delete-Item.

Der SqlProvider ist ein einfaches Hilfsmittel, um ohne tiefe Programmierkenntnisse von Schnittstellen wie ADO.Net oder OleDB auf Datenbanken mit der Powershell einfach zugreifen zu können. Wie man mit ADO.Net auf SQLDatenbanken zugreift, zeige ich in dann ab Kapitel 2.

Beispiel 1: Anzeige aller Tabellen eines Schemas 

Set-StrictMode -Version "2.0"
Clear-Host

$SQLServerName="Dom1SRV01"
Set-Location SQLSERVER:\SQL\$SQLServerName\DEFAULT\Databases\AdventureWorks\Tables
Get-ChildItem | where {$_.Schema -eq "Person"} | Format-Table -autosize
#Ausgabe gekürzt

Schema Name Created
------ ---- -------
Person Address 17.05.2011 12:08
Person AddressType 17.05.2011 12:08
Person Contact 17.05.2011 12:08
...

Schemas sind im SqlServer eine Strukturmöglichkeit, um Elemente wie Tabellen übersichtlicher zu gruppieren.


 

Bereitstellen des SqlProviders

Auf einem Server mit "SQL Server 2008 R2" kann man mit dem Aufruf von "sqlps" die Powershell aufrufen und SQLServer über den den SQLProvider lokal und remote bearbeiten.

Ist der SQLProvider auf dem Host noch nicht installiert, so lässt sich das über das Setupprogramm des SQL-Servers einfach bewerkstelligen:  

sqlps bietet allerdings einen eingeschränkten Funktionsumfang der Powershell an. 

Besser ist es daher mit dem unter MSDN: Ausführen von SQL Server PowerShell aufgeführten Skript den SQLProvider zu importieren.  Speichert das Skript dieser Seite wie beschrieben als InitializeSQLProvider.ps1 unter C:\Windows\System32\WindowsPowerShell\v1.0 oder einem sonstigen beliebigen Pfad ab.

 

Beispiel 1: Add the SQL Server Provider

Set-StrictMode -Version "2.0"
Clear-Host

$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Provider for Windows PowerShell is not installed."
}
else
{
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}


#
# Set mandatory variables for the SQL Server provider
#
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

#
# Load the snapins, type data, format data
#
Push-Location
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml 
update-FormatData -prependpath SQLProvider.Format.ps1xml 
Pop-Location

#Funktioniert nur, wenn die Verwaltungstools installiert sind

Anschließend könnt ihr durch Dot Sourcing siehe My Powershell -> 3.3.4 Dot Sourcing in jedem Skript den Provider verfügbar machen. Wenn ihr das Skript eurem Profil unter C:\Windows\System32\WindowsPowerShell\v1.0\profile.ps1 hinzufügt, ist der SQLProvider immer verfügbar.

 

Beispiel 2: Ergänzen des Powershellprofils um den SQLProvider

#cd\ soll auch ohne Leerzeichen funktionieren
Function cd\ {cd \}

#Import der SQLProviders
. InitializeSQLProvider.ps1

#Bereinigen des Bildschirms
Clear-host

ProfilDateien werden –sofern vorhanden– bei jedem Start der Powershell.exe oder der Powershell_ISE.exe automatisch ausgeführt. Die Profile sind gewöhnliche Powershellscripte (*.ps1), die mit jedem Texteditor erstellt und editiert werden können. siehe My Powershell -> 3.3.1 Profile

Ab SQLServer 2012 gibt es ein Modul sqlps. Dieses kann man dann einfach mittels 

Import-Module sqlps

laden

MSDN: Importieren des SQLPS-Moduls

 

2.2 DataReader Class

In diesem Beispiel schicke ich -wie im letzten Kapitel gezeigt- mit der SqlCommandKlasse eine Abfrage zur Datenbank. (Ich nutze nur einen anderen Konstruktor "$QueryString, $SqlConnection", um den Code etwas kürzer zu gestalten. MSDN: SqlCommand-Konstruktor (String, SqlConnection) 

In diesem Kapitel soll es nun um die DataReader-Klasse gehen, die in den letzten Beispielen schon eingemale im Einsatz war und jetzt genauer untersucht wird

MSDN: SqlDataReader-Klasse

 

Beispiel 1: Auslesen der zurückgegebenen Daten (GetValue/ GetOrdinal)

Set-StrictMode -Version "2.0"
Clear-Host

#define database, sqlserver and instance
$DatabaseName=   "AdventureWorks2008R2"
$SqlServerName = ".\SQLEXPRESS"
 
$ColumnNames = @("CostRate","ModifiedDate","Name")
$ColumnNamestring = $ColumnNames -Join ","
$QueryString = "SELECT Top 3 $ColumnNamestring FROM Production.Location"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SqlServerName;Database=$DataBaseName;Integrated Security=True"
$SqlConnection.Open()
 
$SqlCommand = New-Object System.Data.SqlClient.SqlCommand($QueryString, $SqlConnection)

[System.Data.SqlClient.SqlDataReader]$Reader = $SqlCommand.ExecuteReader()
While ($Reader.Read()) {
   $Result_1 = @()
   For($counter = 0; $Counter -lt $($ColumnNames.Count); $Counter++){
       $Result_1 += $Reader.GetValue($Counter)
      }
   Write-Host $($Result_1 -Join ";")
}
$Reader.Close()
""
[System.Data.SqlClient.SqlDataReader]$Reader = $SqlCommand.ExecuteReader()
While ($Reader.Read()){
   $Result_2 = @()
   $ColumnNames | ForEach {
      $OrdinalNumber = $Reader.GetOrdinal($_)
      $Result_2 += $Reader.GetValue($OrdinalNumber)
      }
   Write-Host $($Result_2 -Join ";")
}
$Reader.Close()

$SqlConnection.Close()
#Ausgabe

0,0000;01.06.2002 00:00:00;Tool Crib
0,0000;01.06.2002 00:00:00;Sheet Metal Racks
0,0000;01.06.2002 00:00:00;Paint Shop

0,0000;01.06.2002 00:00:00;Tool Crib
0,0000;01.06.2002 00:00:00;Sheet Metal Racks
0,0000;01.06.2002 00:00:00;Paint Shop

3 Praktische Beispiele

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  •  

3.1 SELECT

 

Beispiel 1a: Schrittweises Importieren von Daten aus der AdventureWorks2008-Datenbank in eine DataTable

# Update-SqlData2DataTable.ps1.txt
 
Set-StrictMode -Version "2.0"
Clear-Host

#define database, sqlserver and instance

$DatabaseName=   "AdventureWorks2008"
#$DatabaseName=   "AdventureWorks2008R2"

$SqlServerName = ".\SQLEXPRESS"  
#$QueryString = "SELECT * FROM Production.Location "
$QueryString = "SELECT Top 5 * FROM Production.Location ORDER BY CostRate Desc"

Function Update-SqlData2DataTable{
  Param ($DatabaseName, $SqlServername, $QueryString)
  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  $SqlConnection.ConnectionString = "Server=$SqlServerName;Database= `
                $DataBaseName;Integrated Security=True"
  $SqlConnection.Open()

  $SqlCommand = New-Object System.Data.SqlClient.SqlCommand
  $SqlCommand.CommandText = $QueryString
  $SqlCommand.Connection = $SqlConnection

  $Reader = $SqlCommand.ExecuteReader()
 
  $DataTable = New-Object System.Data.DataTable("Table")
  $FieldCount = $Reader.FieldCount
 
  For ($Field = 0;$Field -lt $FieldCount; $Field++){  #ColumnsNames
     $Column = New-Object System.Data.DataColumn($Reader.GetName($Field))
     $DataTable.Columns.Add($Column)

     }

  While ($Reader.Read()){  #Values 2 DataTable Rows
    $Row = $DataTable.NewRow()
    For ($Field = 0;$Field -lt $FieldCount; $Field++){
            $Row[$Field] = $Reader.GetValue($Field)
        }#For
    $DataTable.Rows.Add($Row)
  }#while
  $SqlConnection.Close()
  ,$DataTable
}#$Function

$DataTable01 =
Update-SqlData2DataTable $DataBaseName $SqlServername $QueryString
$DataTable01 | ft -auto

#tested for Win7/ Powershell V2.0 / SQLServer2008R2
#tested for Win7/ Powershell V2.0 / SQLExpress2008R2
#AdventureWorks2008R2_Database.zip - http://msftdbprodsamples.codeplex.com/Releases/
#mögliche Ausgabe

LocationID Name              CostRate Availability ModifiedDate        
---------- ----              -------- ------------ ------------        
20         Frame Welding     25.0000  108.00       6/1/2002 12:00:00 AM
10         Frame Forming     22.5000  96.00        6/1/2002 12:00:00 AM
45         Specialized Paint 18.0000  80.00        6/1/2002 12:00:00 AM
40         Paint             15.7500  120.00       6/1/2002 12:00:00 AM
30         Debur and Polish  14.5000  120.00       6/1/2002 12:00:00 AM

 Update-SqlData2DataTable.ps1.txt

Die Function in diesem Beispiel befüllt die DataTable, in dem sie mit der Reader-Klasse jede Zeile aus der Datenquelle ausliest und schrittweise in die DataTable hineinschreibt. Dadurch erhält man eine erneute Stelle um den Input eventuell noch weiter zu kontrollieren. 

Im nächsten Beispiel 1b übertrage ich das gesamte Ergebnis der Datenabfrage mit der AdapterKlasse in ein DataSet

Um eine Datenbank abzufragen und das Ergebnis auszugeben, braucht ihr nur den gelben und violetten Teil anpassen. Das Ergebnis erhaltet ihr in einer handlichen DataTable,

 

Beispiel 1b: Komplettes Importieren von Daten aus der AdventureWorks2008-Datenbank in eine DataTable (SqlDataAdapter.Fill)

# Update-SqlData2DataSet.ps1.txt
 
Set-StrictMode -Version "2.0"
Clear-Host

#define database, sqlserver and instance
$DatabaseName=   "AdventureWorks2008"
$SqlServerName = ".\SQLEXPRESS"  
$QueryString = "SELECT Top 5 * FROM Production.Location ORDER BY CostRate Desc"

Function Update-SqlData2DataSet{
  Param ($DatabaseName, $SqlServername, $QueryString)
  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  $SqlConnection.ConnectionString = "Server=$SqlServerName;Database= `
                $DataBaseName;Integrated Security=True"
  $SqlConnection.Open()

  $SqlCommand = New-Object System.Data.SqlClient.SqlCommand
  $SqlCommand.CommandText = $QueryString
  $SqlCommand.Connection = $SqlConnection

  #Daten in den Adapter laden
  $SqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  $SqlDataAdapter.SelectCommand = $SqlCommand

  #Definition eines DataSet
  $DataSet = New-Object System.Data.DataSet("DataSet")
 
  #Übertragen der Daten aus dem Adapter ins DataSet
  $SqlDataAdapter.Fill($DataSet)
  $SqlConnection.Close()

  #Rückgabe des DataSets
  ,$DataSet

}#$Function

$DataSet01 = $(  
Update-SqlData2DataSet  $DataBaseName $SqlServername $QueryString)[1]
$DataTable01 = $DataSet01.Tables[0]
$DataTable01  | Format-Table -auto
$DataTable01.Rows.Count

#tested for Win7/ Powershell V2.0 / SQLExpress2008R2
#AdventureWorks2008R2_Database.zip - http://msftdbprodsamples.codeplex.com/Releases/

 Update-SqlData2DataSet.ps1.txt

Die Ausgabe ist identisch zu, letzten Beispiel 1a

 

Beispiel 2: Komplexes Auslesen von Daten aus der AdventureWorks2008-Datenbank (Select From *)

Beim nächsten Beispiel "leihe" ich mir ich mir eine komplexere Sql-Abfrage von einem View der AdventureDatenbank aus und übergebe dessen SQL-Statement aus der unteren rechten Bildschirmhälfte ohne irgendeine Veränderung meiner Powershellfunktion aus Beispiel 1.

Set-StrictMode -Version "2.0"
Clear-Host

#define database, sqlserver and instance
$DatabaseName=   "AdventureWorks2008"
#$DatabaseName=   "AdventureWorks2008R2"

$SqlServerName = ".\SQLEXPRESS"  

$QueryString ="
SELECT     e.BusinessEntityID, p.Title, p.FirstName, p.MiddleName, p.LastName, p.Suffix, e.JobTitle, pp.PhoneNumber, pnt.Name AS PhoneNumberType, ea.EmailAddress,
                      p.EmailPromotion, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode, cr.Name AS CountryRegionName,
                      p.AdditionalContactInfo
FROM         HumanResources.Employee AS e INNER JOIN
                      Person.Person AS p ON p.BusinessEntityID = e.BusinessEntityID INNER JOIN
                      Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = e.BusinessEntityID INNER JOIN
                      Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN
                      Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID INNER JOIN
                      Person.CountryRegion AS cr ON cr.CountryRegionCode = sp.CountryRegionCode LEFT OUTER JOIN
                      Person.PersonPhone AS pp ON pp.BusinessEntityID = p.BusinessEntityID LEFT OUTER JOIN
                      Person.PhoneNumberType AS pnt ON pp.PhoneNumberTypeID = pnt.PhoneNumberTypeID LEFT OUTER JOIN
                      Person.EmailAddress AS ea ON p.BusinessEntityID = ea.BusinessEntityID"

Function Update-SqlData2DataTable{
 ...
}#$Function

$DataTable01 = Update-SqlData2DataTable $DataBaseName $SqlServername $QueryString
$DataTable01 | ft -auto

#Ausgabe gekürzt

BusinessEntityID Title FirstName   MiddleName       LastName          Suffix JobTitle              
---------------- ----- ---------   ----------       --------          ------ --------                  
1                      Ken         J                Sánchez                  Chief Executive Officer 
2                      Terri       Lee              Duffy                    Vice President of Engineering  
3                      Roberto                      Tamburello               Engineering Manager             
4                      Rob                          Walters                  Senior Tool Designer

 Get-ComplexQuery.ps1.txt

Abgesehen vom Sql-Statement hat sich zu Beispiel 1 nichts verändert. Den kompletten Code der Function habe ich der Übersichtlichkeit halber online weggelassen. Das gesamte lauffähige Skript könnt ihr aber hier natürlich schnell herunterladen.

Selbstverständlich bekommt man in Powershell dieselben Ergebnisse zurück, als wenn man am Sql-Server auf Ausfrage ausführen klickt.

Dieses Vorgehen funktioniert solange so einfach, wie keine Sql-Funktionen in der Abfrage vorkommen, wie beispielsweise
                    "WHERE  (GETDATE() BETWEEN edh.StartDate AND ISNULL(edh.EndDate, GETDATE()))"

Ein Lösungsansatz wäre dann, direkt mit der Powershell auf die zurückgegebene DataTable zuzugreifen.

 

3.2.Insert Into

Mit dem Insert Into schreibt man Daten in eine SQL-Datenbank hinein. Dazu einige Beispiele

Beispiel 1: Daten in eine SQL-Datenbank schreiben
Dies hier ist ein bewusst sehr einfach gehaltenes Beispiel! Voraussetzung ist eine Datenbank "Demo", bestehend aus den Spalten "forest" und "domain"

Set-StrictMode -Version "2.0"
Clear-Host

Function Main{

$SQLServerInstance = ".\SqlExpress"
    $SQLDataBaseName = "ADReplication"
    $SQLDataTable = "Demo"

    WriteData2SQLDataBase $SQLServerInstance $SQLDataBaseName $SQLDataTable
} #end Main

Function Write-Data2SQLDataBase{
     Param($SqlServerInstance,$DataBaseName,$DataTable)     
          
     #######Construction of SqlConnection
     $Server="Server=$SqlServerInstance"
     $DataBase="Database=$DataBaseName"
     $Security="Integrated Security=True"
     $ConnectionString ="$Server;$DataBase;$Security"

     #Create SqlConnection
     $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
     $SqlConnection.ConnectionString = "$Server;$DataBase;$Security"
 
     #Status der Connection verändern und ausgeben
     $SqlConnection.Open()
     $Command = $Sqlconnection.CreateCommand()
     #######
               
     #Create ColumnsCaptionString
     $ColumnHeaders =@("Domain")
     $ColumnHeaders += "Forest"
     [String]$ColumnHeaderString = $ColumnHeaders -join ","
     
     #Create ValueString
     $Values = @("Domain1")
     $Values += "Forest1"
     $ValueString = "'"  + $($Values -join "','") + "'"
         
     $Command.CommandText = "INSERT INTO $DataTable ($ColumnHeaderString) VALUES ($ValueString)"
     $Command.ExecuteNonQuery() | Out-Null
       
     $SqlConnection.Close()      
}# Function WriteData2DataBase

Main

Dieses nicht besonders spannende Beispiel schreibt die Werte des Arrays "Values" in die vorhandene Sql-Tabelle "Demo".

 

4 Transact-SQL

MSDN: Transact-SQL Reference (Database Engine)

In dem nun folgenden Kapitel geht es mir darum, SQL als Sprache zu verstehen. Wenn man sich den Link in die MSDN ansieht, wird schnell klar, dass es wieder mal nur ein kleiner Ausschnitt werden kann, dem man sich widmen kann.

Von den zahlreichen Unterkapiteln dieser Seite konzentriere ich mich auf die folgenden Unterkapitel und deren Umsetzung in Powershell

4.1 Data Definition Language

Mit der DDL legt man auf Sql-Servern Objekte wie Datenbanken und Tabellen an. Natürlich kann man das auch von der Powershell aus steuern

Beispiel 1a: Anlage einer einfachen Datenbank mit den Klassen SqlConnection/ SqlCommand

MSDN: CREATE DATABASE (Transact-SQL)

Set-StrictMode -Version "2.0"
Clear-Host

#DDL Query
$QueryString = "Create DataBase TestData04"

#define database, sqlserver and instance
$SqlServerName = ".\SQLEXPRESS"  

Function ExecuteDDL {
  Param($QueryString,$SqlServerName, $SqlDataBaseName="" )
  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  $SqlConnection.ConnectionString = "Server=$SqlServerName;Database=$SqlDataBaseName;Integrated Security=True"
  $SqlConnection.Open()
 
  $SqlCommand = New-Object System.Data.SqlClient.SqlCommand
  $SqlCommand.CommandText = $QueryString
  $SqlCommand.Connection = $SqlConnection

  Try{
     $Reader = $SqlCommand.ExecuteReader()
  }Catch{
     "Beim Ausführen ist ein Fehler aufgetreten"
  }
    $Reader.Close()
    $SqlConnection.Close()
}#End ExececuteDDL

ExecuteDDL $QueryString $SqlServerName

Mehr Infos zu den verwendeten SqlKlassen findet ihr im Kapitel 2.1.2 SqlCommand Class

 

Beispiel 1b: Anlage einer einfachen Datenbank mit der Klasse ServerConnection

Noch etwas kürzer als in Beispiel 1a kann man mit der Klasse "Microsoft.SqlServer.Management.Common.ServerConnection" einen DDL Befehl ausführen

Set-StrictMode -Version "2.0"
Clear-Host

$SqlServerName = ".\SQLEXPRESS"  
$QueryString = "Create DataBase TestData05"

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

Function ExecuteDDL{
   Param($QueryString,$SqlServerName)
   $ServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($SqlServerName)
   $Reader = $ServerConnection.ExecuteReader($QueryString)
   }End ExecuteDLL

ExecuteDDL $QueryString $SqlServerName

Mehr Infos zur Klasse ServerConnection findet ihr im Kapitel 2.1.1 ServerConnection Class

 

Beispiel 2: Anlage einer Datenbank mit mehreren Data- und TransactionFiles

Das Sql-Statement des folgenden Beispiels habe ich mir unverändert von MSDN: CREATE DATABASE (Transact-SQL) -> "Abschnitt: C. Creating a database by specifying multiple data and transaction log files" in das folgende Skript kopiert

Dieselbe Funktion "ExecuteDDL" wie in den Beispiel 1a führt dieses Sql-Statement dann aus.

Set-StrictMode -Version "2.0"
Clear-Host

#DDL Query
$SqlStatement = "   CREATE DATABASE Archive1132
                 ON PRIMARY
                  (NAME = Arch1,
                   FILENAME = 'D:\SalesData\archdat1.mdf' ,
                   SIZE = 100,
                   MAXSIZE = 200,
                   FILEGROWTH = 20),
                  (NAME = Arch2,
                   FILENAME = 'D:\SalesData\archdat2.ndf',
                   SIZE = 100MB,
                   MAXSIZE = 200,
                   FILEGROWTH = 20),
                  (NAME = Arch3,
                   FILENAME = 'D:\SalesData\archdat3.ndf',
                   SIZE = 100MB,
                   MAXSIZE = 200,
                   FILEGROWTH = 20)
                 LOG ON(
                   NAME = Archlog1,
                   FILENAME = 'D:\SalesData\archlog1.ldf',
                   SIZE = 100MB,
                   MAXSIZE = 200,
                   FILEGROWTH = 20),
                   (
                   NAME = Archlog2,
                   FILENAME = 'D:\SalesData\archlog2.ldf',
                   SIZE = 100,
                   MAXSIZE = 200,
                   FILEGROWTH = 20);
                 "

$SqlServerName = ".\SQLEXPRESS"  

Function ExecuteDDL {
  Param($QueryString,$SqlServerName, $SqlDataBaseName="" )
  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  $SqlConnection.ConnectionString = "Server=$SqlServerName;Database=$SqlDataBaseName;Integrated Security=True"
  $SqlConnection.Open()
 
  $SqlCommand = New-Object System.Data.SqlClient.SqlCommand
  $SqlCommand.CommandText = $QueryString
  $SqlCommand.Connection = $SqlConnection

  Try{
     $Reader = $SqlCommand.ExecuteReader()
  }Catch{
     "Beim Ausführen ist ein Fehler aufgetreten"
  }
    $Reader.Close()
    $SqlConnection.Close()
}#end ExecuteDLL

ExecuteDDL $SqlStatement $SqlServerName

Diese Datei herunterladen (Kap3.1 Bsp2.ps1.txt) Kap3.1 Bsp2.ps1.txt

An mehreren Stellen findet man Beispiele, die Files und Logfiles mit Hilfe der Klasse 'Microsoft.SqlServer.Management.Smo.Server' setzen MSDN: Understanding and Using PowerShell Support in SQL Server 2008  -> "SQL Server Management Objects - SMO"

Ich finde den Weg oben über die Reader-Klasse einfacher.

Bei meinen Tests mit dem obigen Skript, bei dem ich immer wieder Datenbanken und ihre Dateien anlege und lösche, "verhakt" sich gelegentlch der SqlExpressServer. Der Fehler lautet relativ unspezifisch, dass "die Variable Reader nicht abgerufen werden kann". Daneben lassen sich auch die Datenbankfiles nicht mehr löschen. In einem solchen Fall hilft es, den Dienst "Sql Server (SqlExpress)" durchzustarten.

 

4.2 DBCC (Database Console Commands)

MSDN: DBCC (Transact-SQL)

Die Programmiersprache Transact-SQL stellt DBCC-Anweisungen bereit, die als Datenbankkonsolenbefehle (Database Console Commands, DBCC) für SQL Server dienen. 
 

Beispiel 1: Konsistenz einer Datenbank prüfen (DBCC CHECKDB)

Set-StrictMode -Version "2.0"
Clear-Host

 
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

$DataAdapterBaseName="AdventureWorks"
$SqlServerName = "Dom1Srv01" 
$QueryString = "DBCC CHECKDB($DataAdapterBaseName) WITH TABLERESULTS, ALL_ERRORMSGS" 
 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection( ` 
   "Data Source=$SqlServerName;Integrated Security=SSPI;Initial Catalog=$DataAdapterBaseName"); 
$DataAdapterSet = New-Object System.Data.DataSet "TempResultTable" 
$DataAdapter = New-Object "System.Data.SqlClient.SqlDataAdapter" ($QueryString, $SqlConnection) 
[Void]$DataAdapter.Fill($DataAdapterSet) 
     
$TempResultTable = New-Object "System.Data.DataTable" "TempResultTable" 
$TempResultTable = $DataAdapterSet.Tables[0] 
        
#Beispiel für Ausgaben
"`nBeispiel für Ausgaben`n"
$Anzahl=$TempResultTable.Rows.Count
$TempResultTable.Columns | Select Caption
$TempResultTable.Rows[20].Error
"`n"

$i=0
For ($i -eq 0;$i -lt $($TempResultTable.Rows).Count; $i++)
{
  If($TempResultTable.Rows[$i].Error -eq 8997){
     "{0} {1}" -f $TempResultTable.Rows[$i].Error,$TempResultTable.Rows[$i].Messagetext
  }
}
#getestet mit Win7/ Powershell V3.0 / SQLServer 2008R2

#mögliche Ausgabe gekürzt

Beispiel für Ausgaben

Caption   
------- 
Error
..   
Level   
MessageText  
 
2593

8997 Service Broker-Meldung 9675, Status 1: Analysierte Nachrichtentypen: 14.
..
8997 Service Broker-Meldung 9670, Status 1: Analysierte Remotedienstbindungen: 0.
8997 Service Broker-Meldung 9605, Status 1: Analysierte Konversationsprioritäten: 0.

Anmerkungen

1.) Unter MSDN:  DBCC CHECKDB (Transact-SQL) findet ihr eine ausführliche Beschreibung von CHECKDB. Dort ist die Arbeitsweise der "interne Datenbankmomentaufnahme", automatische Reparturmechanismen und einiges mehr beschrieben.

2.) Die Ausgabe ist identisch, wenn ihr im SQLServer die Abfrage "DBCC CHECKDB($DataAdapterBaseName) WITH  ALL_ERRORMSGS" absendet.
Das PowershellSkript verwendet ein Dataset und eine Datatable um die Daten aufzubereiten. Daher lautet der Querystring hier "$QueryString = "DBCC CHECKDB($DataAdapterBaseName) WITH TABLERESULTS, ALL_ERRORMSGS"  mit der zusätzlichen Option "TABLERESULTS"

 

4.3 Tabellen

Beispiel 1: Tabellen einer Datenbank auslesen

Set-StrictMode -Version "2.0"
Clear-Host


$DataBaseServer="Dom1Srv01"
$DatabaseName="AdventureWorks"

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") 

$SMOServer =new-object Microsoft.SqlServer.Management.Smo.Server($DataBaseServer)
$DataBase =$SMOServer.DataBases[$DataBaseName]

$Tables=@()
$DataBase.Tables | ForEach { 
  $Table= "{0}.{1}" -f $_.Schema,$_.Name
  $Tables+=$Table
}

$Tables
#getestet mit Win7/ Powershell V2.0 / SQLServer 2008R2

#Ausgabe gekürzt

dbo.sysdiagrams
...
HumanResources.Department
..
Person.Address


Beispiel 2: Alle Datenbanken auf einem Server abfragen

Set-StrictMode -Version "2.0"
Clear-Host

 
$DataBaseServer = 'Dom1Srv01' 

$SqlConnection = New-Object Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$DataBaseServer;Integrated Security=True"
$SqlConnection.Open()
$SqlCommand = New-Object Data.SqlClient.SqlCommand
$SqlCommand.Connection = $SqlConnection
$SqlCommand.CommandType = 'StoredProcedure'
$SqlCommand.CommandText = 'sp_databases'
$SqlCommand.ExecuteReader() |foreach {$_.GetString(0)}

$SqlConnection.Open()
#Ausgabe gekürzt

AdventureWorks
AdventureWorks2008R2
AdventureWorksDW
...

The PowerShell Guy: listing all the Databases from a SQL Server from PowerShell

 

4 BCP.exe (Bulk Copy Program)

Beispiel 1: Abfrage gegen eine SQLDatenbank mit BCP.exe

Mit der Installation der SQL Server Management Console 2008R2 erhält man auch das Programm BCP.exe. Dieses Programm ist schon ein relativ alter Bestandteil von SqlServer, das für den Massenim- und Export von Daten auf Basis von Textdateien gedacht ist. 

MSDN: bcp (Hilfsprogramm)

Mit dem Parameter "queryout" kann BCP.exe aber auch für das Ausführen von Select-Queries innerhalb von Powershell oder eigenständig in einer Commandline benutzt werden.

Set-StrictMode -Version "2.0"
Clear-Host


$SQLServerName = "Dom1Srv01"
$DataBaseName = "AdventureWorks"
$QueryString = "SELECT Name, CostRate FROM $DatabaseName.Production.Location"

bcp $QueryString queryout c:\temp\production.txt -c -S dom1srv01 -T
# Ausgabe

Kopiervorgang wird gestartet...

14 Zeilen kopiert.
Netzwerkpaketgröße (Bytes): 4096
Zeit (ms.) gesamt: 1      Durchschnitt: (14000.00 Zeilen pro Sek.)

Die Ausgabe erfolgt in eine Textdatei. Sollen die Daten weiter verarbeitet werden, so muss die Textdatei eingelesen und analysiert werden.