Invoke-Sqlcmd

Syntax

Invoke-Sqlcmd
      [-ServerInstance <PSObject>]
      [-Database <String>]
      [-Encrypt <String>]
      [-EncryptConnection]
      [-Username <String>]
      [-AccessToken <String>]
      [-Password <String>]
      [-Credential <PSCredential>]
      [[-Query] <String>]
      [-QueryTimeout <Int32>]
      [-ConnectionTimeout <Int32>]
      [-ErrorLevel <Int32>]
      [-SeverityLevel <Int32>]
      [-MaxCharLength <Int32>]
      [-MaxBinaryLength <Int32>]
      [-AbortOnError]
      [-DedicatedAdministratorConnection]
      [-DisableVariables]
      [-DisableCommands]
      [-HostName <String>]
      [-ApplicationName <String>]
      [-ApplicationIntent <ApplicationIntent>]
      [-MultiSubnetFailover]
      [-FailoverPartner <String>]
      [-HostNameInCertificate <String>]
      [-TrustServerCertificate]
      [-NewPassword <String>]
      [-Variable <PSObject>]
      [-InputFile <String>]
      [-OutputSqlErrors <Boolean>]
      [-IncludeSqlUserErrors]
      [-SuppressProviderContextWarning]
      [-IgnoreProviderContext]
      [-OutputAs <OutputType>]
      [-StatisticsVariable <String>]
      [-ProgressAction <ActionPreference>]
      [<CommonParameters>]

Invoke-Sqlcmd
      [-AccessToken <String>]
      [[-Query] <String>]
      [-QueryTimeout <Int32>]
      [-ErrorLevel <Int32>]
      [-SeverityLevel <Int32>]
      [-MaxCharLength <Int32>]
      [-MaxBinaryLength <Int32>]
      [-AbortOnError]
      [-DisableVariables]
      [-DisableCommands]
      [-Variable <PSObject>]
      [-InputFile <String>]
      [-OutputSqlErrors <Boolean>]
      [-IncludeSqlUserErrors]
      [-OutputAs <OutputType>]
      -ConnectionString <String>
      [-KeyVaultAccessToken <String>]
      [-ManagedHsmAccessToken <String>]
      [-StatisticsVariable <String>]
      [-ProgressAction <ActionPreference>]
      [<CommonParameters>]

Beschreibung

Das cmdlet Invoke-Sqlcmd führt ein Skript aus, das die Sprachen und Befehle enthält, die vom SQL Server SQLCMD-Hilfsprogramm unterstützt werden.

Die unterstützten Befehle sind Transact-SQL Anweisungen und die Teilmenge der XQuery-Syntax, die vom Datenbankmodul unterstützt wird.

Dieses Cmdlet akzeptiert auch viele der Befehle, die von SQLCMD nativ unterstützt werden, z. B. GO und QUIT.

Dieses Cmdlet akzeptiert auch die SQLCMD-Skriptingvariablen, z. B. SQLCMDUSER. Standardmäßig legt dieses Cmdlet keine SQLCMD-Skriptingvariablen fest.

Dieses Cmdlet unterstützt nicht die Verwendung von Befehlen, die in erster Linie mit der interaktiven Skriptbearbeitung zusammenhängen.

Die Befehle werden nicht unterstützt: :!, :connect, :error, :out, :ed, :list, :listvar, :reset, :p erftrace und :serverlist.

Wenn dieses Cmdlet ausgeführt wird, wird das erste Ergebnisset, das das Skript zurückgibt, als formatierte Tabelle angezeigt.

Wenn nachfolgende Resultsets unterschiedliche Spaltenlisten enthalten als die erste, werden diese Resultsets nicht angezeigt.

Wenn nachfolgende Resultsets nach dem ersten Satz dieselbe Spaltenliste aufweisen, werden die Zeilen an die formatierte Tabelle angefügt, die die Zeilen enthält, die vom ersten Resultset zurückgegeben wurden.

Sie können die Ausgabe von SQL Server-Nachrichten anzeigen, z. B. solche, die aus der SQL PRINT-Anweisung resultieren, indem Sie den Verbose-Parameter angeben.

Beispiele

Beispiel 1: Herstellen einer Verbindung mit einer benannten Instanz und Ausführen eines Skripts

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery" -ServerInstance "MyComputer\MainInstance"

TimeOfQuery
-----------
9/21/2017 2:48:24 PM

Dieser Befehl stellt eine Verbindung mit einer benannten Instanz des SQL-Datenbankmoduls auf einem Computer und führt ein einfaches Transact-SQL Skript aus.

Beispiel 2: Aufrufen von Befehlen in einer Skriptdatei und Speichern der Ausgabe in einer Textdatei

Invoke-Sqlcmd -InputFile "C:\ScriptFolder\TestSqlCmd.sql" | Out-File -FilePath "C:\ScriptFolder\TestSqlCmd.rpt"
Output sent to TestSqlCmd.rpt.

Dieser Befehl liest eine Datei mit Transact-SQL Anweisungen und SQLCMD-Befehlen, führt die Datei aus und schreibt die Ausgabe in eine andere Datei.

Die Ausgabedatei kann proprietäre Informationen enthalten, daher sollten Sie die Ausgabedateien mit den entsprechenden NTFS-Berechtigungen sichern.

Beispiel 3: Aufrufen eines Skripts und Übergeben von Variablenwerten aus einer Zeichenfolge

$StringArray = "MYVAR1='String1'", "MYVAR2='String2'"
Invoke-Sqlcmd -Query "SELECT `$(MYVAR1) AS Var1, `$(MYVAR2) AS Var2" -Variable $StringArray

Var1     Var2
----     ----
String1  String2

Dieser Befehl verwendet ein Array von Zeichenfolgen als Eingabe für den Parameter Variable.

Das Array definiert mehrere SQLCMD-Variablen.

Die $-Zeichen in der SELECT-Anweisung, die die SQLCMD-Variablen identifizieren, werden mithilfe des Zeichens "Back-Tick (')" escapet.

Beispiel 4: Aufrufen eines Skripts und Übergeben von Variablen aus dem SQL-Datenbankmodul

Set-Location "SQLSERVER:\SQL\MyComputer\MainInstance"
PS SQLSERVER:\SQL\MyComputer\MainInstance> Invoke-Sqlcmd -Query "SELECT SERVERPROPERTY('MachineName') AS ComputerName" -ServerInstance (Get-Item .)

ComputerName
------------
MyComputer

Dieser Befehl verwendet Set-Location, um zum SQL ServerWindows PowerShell-Anbieterpfad für eine Instanz des SQL-Datenbankmoduls zu navigieren.

Anschließend wird Get-Item aufgerufen, um ein SQL Management Object Server-Objekt für die Verwendung als ServerInstance-Parameter von Invoke-Sqlcmd abzurufen.

Beispiel 5: Ausführen einer Abfrage und anzeigen ausführlicher Ausgabe

Set-Location "SQLSERVER:\SQL\MyComputer\MainInstance"
Invoke-SqlCmd -Query "PRINT N'abc'" -Verbose
VERBOSE: abc

Dieser Befehl verwendet den Windows PowerShellVerbose-Parameter, um die Nachrichtenausgabe des SQL PRINT-Befehls zurückzugeben.

Beispiel 6: Aufrufen eines Befehls mithilfe einer positionsbezogenen Zeichenfolge als Eingabe

Set-Location "SQLSERVER:\SQL\MyComputer\MainInstance\Databases\MyDatabase"
PS SQLSERVER:\SQL\MyComputer\MainInstance> Invoke-Sqlcmd "SELECT DB_NAME() AS DatabaseName"
WARNING: Using provider context. Server = MyComputer\MainInstance, Database = MyDatabase.

DatabaseName
------------
MyDatabase

Dieser Befehl verwendet eine positionale Zeichenfolge, um die Eingabe für den Abfrageparameter zu liefern.

Außerdem wird veranschaulicht, wie Invoke-Sqlcmd den aktuellen Pfad verwendet, um den Datenbankkontext auf MyDatabase festzulegen.

Beispiel 7: Erfassen von Daten in einem DataSet-Objekt

$DS = Invoke-Sqlcmd -ServerInstance "MyComputer" -Query "SELECT  ID, Item FROM MyDB.dbo.MyTable" -As DataSet
$DS.Tables[0].Rows | %{ echo "{ $($_['ID']), $($_['Item']) }" }

{ 10, AAA }
{ 20, BBB }
{ 30, CCC }

Dieser Befehl verwendet den Parameter "As DataSet", um die Daten in einem .Net System.Data.DataSet-Objekt zu erfassen und das Ergebnis in der Variablen "$DS" zu speichern. Das Objekt kann zur weiteren Verarbeitung verwendet werden.

Beispiel 8: Abrufen bestimmter Spaltensätze

$Tables = Invoke-Sqlcmd -ServerInstance "MyComputer" -Query "SELECT  Item, id FROM MyDatabase.dbo.MyTable; SELECT GETDATE() AS T" -As DataTables
$Tables[0].Rows | %{ echo $_.ID }
$Tables[1].Rows | %{ echo $_.T.DayOfWeek }

10
20
30

Monday

Der erste Befehl verwendet den Parameter "As DataTables", um die Daten in einer Auflistung von .Net System.Data.DataTable-Objekten zu erfassen. Der Befehl ruft zwei Tabellen mit unterschiedlichen Spaltensätzen ab.

Jede Tabelle kann einzeln basierend auf einem eigenen Schema verarbeitet werden.

Beispiel 9: Vollzugriff auf eine Verbindung

Invoke-Sqlcmd -Query "SELECT COUNT(*) AS Count FROM MyTable" -ConnectionString "Data Source=MYSERVER;Initial Catalog=MyDatabase;Integrated Security=True;ApplicationIntent=ReadOnly"
Count
-----
127432

Mit diesem Befehl wird der Parameter -ConnectionString, um die von diesem Cmdlet hergestellte Verbindung vollständig zu steuern, anstatt die Invoke-Sqlcmd, um die Verbindungszeichenfolge basierend auf den parametern zu erstellen, die in der Befehlszeile übergeben werden.

Dies ist nützlich für weniger häufige Eigenschaften, die Sie möglicherweise verwenden möchten.

Beispiel 10: Ausführen einer gespeicherten Prozedur und Erfassen der SQL-Fehler

$script_sp_with_errors = @'
CREATE PROCEDURE [dbo].[TestProcedure3]
AS 
BEGIN 
  CREATE TABLE [dbo].[TestTable] (col INT NOT NULL);
  INSERT INTO [dbo].[TestTable] VALUES (NULL); -- will cause an error
END
GO
'@

# Create a test database
Invoke-SqlCmd -ServerInstance MyServer -Query 'CREATE DATABASE TestDB'
# ... adds a stored procedure that has errors in it...
Invoke-SqlCmd -ServerInstance MyServer -Database 'TestDB' -Query $script_sp_with_errors
# ... executes the SP and collected the errors
Invoke-SqlCmd -ServerInstance MyServer -Database 'TestDB' -Query 'EXEC TestProcedure3' -OutputSqlErrors $true

Here's the output:
Invoke-SqlCmd : Cannot insert the value NULL into column 'col', table 'TestDB.dbo.TestTable'; column does not allow nulls. INSERT fails.
The statement has been terminated.
 Msg 515, Level 16, State 2, Procedure TestProcedure3, Line 5.
At line:1 char:1
...

Mit diesem Befehl wird der Parameter -OutputSqlErrors, um die Fehler an den Benutzer zu melden. Beachten Sie, dass die Fehlermeldung in diesem Fall zusätzliche Informationen wie den SP-Namen und die Zeilennummer bereitstellt, in der der Fehler aufgetreten ist.

Beispiel 11: Herstellen einer Verbindung mit einer Azure SQL-Datenbank (oder verwalteten Instanz) mithilfe eines Zugriffstokens

Import-Module SQLServer
Import-Module Az.Accounts -MinimumVersion 2.2.0

# Note: the sample assumes that you or your DBA configured the server to accept connections using
#       that Service Principal and has granted it access to the database (in this example at least
#       the SELECT permission).

### Obtain the Access Token: this will bring up the login dialog
Connect-AzAccount
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

# Now that we have the token, we use it to connect to the database 'mydb' on server 'myserver'
Invoke-Sqlcmd -ServerInstance myserver.database.windows.net -Database mydb -AccessToken $access_token`
              -query 'select * from Table1'

Beispiel 12: Herstellen einer Verbindung mit der Azure SQL-Datenbank (oder verwalteten Instanz) mithilfe eines Dienstprinzipals

Import-Module SQLServer

# Note: the sample assumes that you or your DBA configured the server to accept connections using
#       that Service Principal and has granted it access to the database (in this example at least
#       the SELECT permission).

$clientid = "enter application id that corresponds to the Service Principal" # Do not confuse with its display name
$tenantid = "enter the tenant ID of the Service Principal"
$secret = "enter the secret associated with the Service Principal"

$request = Invoke-RestMethod -Method POST `
           -Uri "https://login.microsoftonline.com/$tenantid/oauth2/token"`
           -Body @{ resource="https://database.windows.net/"; grant_type="client_credentials"; client_id=$clientid; client_secret=$secret }`
           -ContentType "application/x-www-form-urlencoded"
$access_token = $request.access_token

# Now that we have the token, we use it to connect to the database 'mydb' on server 'myserver'
Invoke-Sqlcmd -ServerInstance myserver.database.windows.net -Database mydb -AccessToken $access_token`
              -query 'select * from Table1'

Beispiel 13: Herstellen einer Verbindung mit der Azure SQL-Datenbank (oder verwalteten Instanz) mithilfe einer vom System zugewiesenen verwalteten Identität (SAMI)

Import-Module SQLServer

# Note: the sample assumes that you or your DBA configured the server to accept connections using
#       that VM Identity you are running on and has granted it access to the database (in this 
#       example at least the SELECT permission).

Connect-AzAccount -Identity
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

# Now that we have the token, we use it to connect to the database 'mydb' on server 'myserver'
Invoke-Sqlcmd -ServerInstance myserver.database.windows.net -Database mydb -AccessToken $access_token `
              -query 'select * from Table1'

Beispiel 14: Herstellen einer Verbindung mit der Azure SQL-Datenbank (oder verwalteten Instanz) mithilfe einer vom Benutzer zugewiesenen verwalteten Identität (UAMI)

Import-Module SQLServer

# Note: the sample assumes that you or your DBA configured the server to accept connections using
#       that VM Identity you are running on and has granted it access to the database (in this 
#       example at least the SELECT permission).

Connect-AzAccount -Identity -AccountId '<your-user-assigned-managed-identity-client-id>'
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

# Now that we have the token, we use it to connect to the database 'mydb' on server 'myserver'
Invoke-Sqlcmd -ServerInstance myserver.database.windows.net -Database mydb -AccessToken $access_token `
              -query 'select * from Table1'

Beispiel 15: Herstellen einer Für Read-Only Routing konfigurierten Verfügbarkeitsgruppe mithilfe von -ApplicationIntent

# In the following example:
# - MT_2009250511 is a listener for an AG configured for Read-Only Routing (port 5555)
# - AGDB_2_1 is the DB in the AG
# - VLM00226138 is the primary replica configured to only allow ReadWrite connections 
# - VLM00226137 is the secondary replica
# 
Invoke-Sqlcmd -ServerInstance "MT_2009250511,5555" -Database AGDB_2_1 `
     -HostName "PowershellBox1" -ApplicationName "ReadWrite" -ApplicationIntent ReadWrite `
     -Query "select HOST_NAME() AS HostName, APP_NAME() AS ApplicationIntent, @@SERVERNAME AS ServerName"

Invoke-Sqlcmd -ServerInstance "MT_2009250511,5555" -Database AGDB_2_1 `
     -HostName "PowershellBox2" -ApplicationName "ReadOnly" -ApplicationIntent ReadOnly `
     -Query "select HOST_NAME() AS HostName, APP_NAME() AS ApplicationIntent, @@SERVERNAME AS ServerName"  

# When you run the 2 cmdlets above, the output is going to be something like this:  
#  
# HostName       ApplicationIntent ServerName  
# --------       ----------------- ----------  
# PowershellBox1 ReadWrite         VLM00226138  
#  
# HostName       ApplicationIntent ServerName  
# --------       ----------------- ----------  
# PowershellBox2 ReadOnly          VLM00226137

dies zeigt, dass die Verbindung abhängig vom Wert des -ApplicationIntent Parameters an einen anderen Server in der AG weitergeleitet wird. Beobachten Sie übrigens die Verwendung der parameter -ApplicationName und -HostName, um die beiden Ergebnisse visuell zu unterscheiden: Dies ist eine gängige Technik, die verwendet werden kann, um Verbindungen und ihre Absichten nachzuverfolgen, über das hier dargestellte -ApplicationIntent Beispiel hinaus.

Beispiel 16: Erfassen von Verbindungsstatistiken über -StatisticsVariable Parameter

Import-Module SQLServer
Invoke-Sqlcmd -ServerInstance localhost -StatisticsVariable stats `
              -Query 'CREATE TABLE #Table (ID int); INSERT INTO #Table VALUES(1), (2); INSERT INTO #Table VALUES(3); SELECT * FROM #Table'

Write-Host "Number of rows affected......: $($stats.IduRows)"
Write-Host "Number of insert statements..: $($stats.IduCount)"
Write-Host "Number of select statements..: $($stats.SelectCount)"
Write-Host "Total execution time.........: $($stats.ExecutionTime)ms"

# When you run the code fragment above, is going to be something like this:  
#
# Number of rows affected......: 3
# Number of insert statements..: 2
# Number of select statements..: 1
# Total execution time.........: 5ms

In diesem Beispiel wird gezeigt, wie Sie den parameter -StatisticsVariable verwenden, um Informationen über die Verbindung, die ausgeführten Anweisungen und die Ausführungszeit beim Ausführen einiger T-SQL-Daten zu erfassen, die eine temporäre Tabelle erstellt, einen Wert einfügen und schließlich eine Auswahl ausgibt, um alle eingefügten Zeilen abzurufen.

Hinweis: Wenn dieselbe Abfrage für mehrere Server ausgeführt wird (z. B. durch Das Weiterleiten der Servernamen durch das Cmdlet), erfasst die StatisticsVariable ein Array von Statistiken, eine für jede Verbindung. Ergebnisse können dann mithilfe von ($stats.IduRows | Measure-Object -Sum).Sumaggregiert werden.

Weitere Informationen zu den verfügbaren Statistiken finden Sie unter Anbieterstatistiken für SQL Server-.

Beispiel 17: Ausführen einer Abfrage, die Daten entschlüsselt, die aus Spalten verschlüsselt werden, die mit Always Encrypted verschlüsselt wurden. Gehen Sie davon aus, dass der Spaltenmasterschlüssel in einem Schlüsseltresor im Azure Key Vault gespeichert ist.

# Connect to Azure account.
Import-Module Az.Accounts -MinimumVersion 2.2.0
Connect-AzAccount

# Obtain an access token for key vaults. 
$keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl https://vault.azure.net).Token

# Pass the token to the cmdlet, so that it can use it to authenticate to Azure when decrypting data protected with Always Encrypted.
$connString = 'Data Source=MYSERVER;Initial Catalog=MyDatabase;Integrated Security=True;ApplicationIntent=ReadOnly;Column Encryption Setting=Enabled'
Invoke-Sqlcmd -Query 'SELECT COUNT(*) AS Count FROM MyTable' -ConnectionString $connString -KeyVaultAccessToken $keyVaultAccessToken

Parameter

-AbortOnError

-AccessToken

-ApplicationIntent

-ApplicationName

-ConnectionString

-ConnectionTimeout

-Credential

-Database

-DedicatedAdministratorConnection

-DisableCommands

-DisableVariables

-Encrypt

-EncryptConnection

-ErrorLevel

-FailoverPartner

-HostName

-HostNameInCertificate

-IgnoreProviderContext

-IncludeSqlUserErrors

-InputFile

-KeyVaultAccessToken

-ManagedHsmAccessToken

-MaxBinaryLength

-MaxCharLength

-MultiSubnetFailover

-NewPassword

-OutputAs

-OutputSqlErrors

-Password

-ProgressAction

-Query

-QueryTimeout

-ServerInstance

-SeverityLevel

-StatisticsVariable

-SuppressProviderContextWarning

-TrustServerCertificate

-Username

-Variable

Eingaben

System.Management.Automation.PSObject

Ausgaben

System.Object

Ähnliche Themen

• SQLServer_Cmdlets
• Dienstprinzipal
• Verwaltete Identität
• Hohe Verfügbarkeit
• Anbieterstatistiken für SQL Server
• Strenge Verbindungsverschlüsselung