Invoke-Sqlcmd

Sintassi

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>]

Descrizione

Il cmdlet Invoke-Sqlcmd esegue uno script contenente i linguaggi e i comandi supportati dall'utilità SQLCMD di SQL Server.

I comandi supportati sono Transact-SQL istruzioni e il subset della sintassi XQuery supportata dal motore di database.

Questo cmdlet accetta anche molti dei comandi supportati in modo nativo da SQLCMD, ad esempio GO e QUIT.

Questo cmdlet accetta anche le variabili di scripting di SQLCMD, ad esempio SQLCMDUSER. Per impostazione predefinita, questo cmdlet non imposta le variabili di scripting di SQLCMD.

Questo cmdlet non supporta l'uso di comandi correlati principalmente alla modifica interattiva degli script.

I comandi non supportati includono :!, :connect, :error, :out, :ed, :list, :listvar, :reset, :p erftrace e :serverlist.

Quando questo cmdlet viene eseguito, il primo set di risultati restituito dallo script viene visualizzato come tabella formattata.

Se i set di risultati successivi contengono elenchi di colonne diversi rispetto al primo, tali set di risultati non vengono visualizzati.

Se i set di risultati successivi dopo il primo set hanno lo stesso elenco di colonne, le relative righe vengono aggiunte alla tabella formattata contenente le righe restituite dal primo set di risultati.

È possibile visualizzare l'output dei messaggi di SQL Server, ad esempio quelli risultanti dall'istruzione SQL PRINT, specificando il parametro Verbose.

Esempio

Esempio 1: Connettersi a un'istanza denominata ed eseguire uno script

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

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

Questo comando si connette a un'istanza denominata del motore di database SQL in un computer ed esegue uno script di base Transact-SQL.

Esempio 2: Richiamare i comandi in un file di script e salvare l'output in un file di testo

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

Questo comando legge un file contenente istruzioni Transact-SQL e comandi SQLCMD, esegue il file e scrive l'output in un altro file.

Il file di output può contenere informazioni proprietarie, pertanto è necessario proteggere i file di output con le autorizzazioni NTFS appropriate.

Esempio 3: Richiamare uno script e passare valori di variabile da una stringa

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

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

Questo comando usa una matrice di stringhe di caratteri come input per il parametro Variable.

La matrice definisce più variabili SQLCMD.

L'istruzione $ sign in the SELECT che identifica le variabili SQLCMD viene preceduta da un carattere di escape usando il carattere back-tick (') .

Esempio 4: Richiamare uno script e passare variabili dal motore di database SQL

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

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

Questo comando usa Set-Location per passare al percorso del provider SQL ServerWindows PowerShell per un'istanza del motore di database SQL.

Chiama quindi Get-Item per recuperare un oggetto SQL Management Object Server da usare come parametro ServerInstance di Invoke-Sqlcmd.

Esempio 5: Eseguire una query e visualizzare l'output dettagliato

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

Questo comando usa il parametro Windows PowerShellVerbose per restituire l'output del messaggio del comando SQL PRINT.

Esempio 6: Richiamare un comando usando una stringa posizionale come input

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

Questo comando usa una stringa posizionale per fornire l'input al parametro Query.

Viene inoltre illustrato come Invoke-Sqlcmd usa il percorso corrente per impostare il contesto del database su MyDatabase.

Esempio 7: Acquisire dati in un oggetto DataSet

$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 }

Questo comando usa il parametro As DataSet per acquisire i dati in un oggetto .Net System.Data.DataSet e archivia il risultato nella variabile '$DS'. L'oggetto può essere utilizzato per un'ulteriore elaborazione.

Esempio 8: Ottenere set di colonne specifici

$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

Il primo comando usa il parametro As DataTables per acquisire i dati in una raccolta di oggetti .Net System.Data.DataTable. Il comando ottiene due tabelle con set di colonne diversi.

Ogni tabella può essere elaborata singolarmente, in base al proprio schema.

Esempio 9: Ottenere il controllo completo di una connessione

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

Questo comando consente di usare il parametro -ConnectionString per ottenere il controllo completo della connessione stabilita da questo cmdlet, anziché il Invoke-Sqlcmd per compilare la stringa di connessione in base ai parametri passati nella riga di comando.

Ciò è utile per le proprietà meno comuni che è possibile usare.

Esempio 10: Eseguire una stored procedure e acquisire gli errori SQL

$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
...

Questo comando consente di usare il parametro -OutputSqlErrors per segnalare gli errori all'utente. Si noti che il messaggio di errore in questo caso fornisce informazioni aggiuntive, ad esempio il nome SP e il numero di riga in cui si è verificato l'errore.

Esempio 11: Connettersi al database SQL di Azure (o a Istanza gestita) usando un token di accesso

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'

Esempio 12: Connettersi al database SQL di Azure (o a Istanza gestita) usando un'entità servizio

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'

Esempio 13: Connettersi al database SQL di Azure (o a Istanza gestita) usando un'identità gestita assegnata dal sistema (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'

Esempio 14: Connettersi al database SQL di Azure (o a Istanza gestita) usando un'identità gestita assegnata dall'utente (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'

Esempio 15: Connettersi a un gruppo di disponibilità configurato per il routing Read-Only usando -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

che indica che, a seconda del valore del parametro -ApplicationIntent, la connessione viene instradata a un server diverso nel gruppo di disponibilità. Per inciso, osservare gli usi dei parametri -ApplicationName e -HostName per distinguere visivamente i due risultati: si tratta di una tecnica comune che può essere usata per tracciare le connessioni e le relative finalità, oltre all'esempio -ApplicationIntent illustrato qui.

Esempio 16: Acquisire le statistiche di connessione tramite -StatisticsVariable parametro

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 questo esempio viene illustrato come usare il parametro -StatisticsVariable per acquisire informazioni sulla connessione, le istruzioni eseguite e il tempo di esecuzione durante l'esecuzione di alcuni T-SQL che crea una tabella temporanea, inserire un valore e infine eseguire una selezione per ottenere tutte le righe inserite.

Nota: quando la stessa query viene eseguita su più server ,ad esempio eseguendo il piping dei nomi dei server tramite il cmdlet , il StatisticsVariable acquisisce una matrice di statistiche, una per ogni connessione. I risultati possono quindi essere aggregati usando, ad esempio, ($stats.IduRows | Measure-Object -Sum).Sum.

Per altre informazioni sulle statistiche disponibili, vedere Statistiche provider per SQL Server.

Esempio 17: eseguire una query che decrittografa i dati recuperati dalle colonne crittografate usando Always Encrypted. Si supponga che la chiave master della colonna sia archiviata in un insieme di credenziali delle chiavi in Azure Key Vault.

# 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

Parametri

-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

Input

System.Management.Automation.PSObject

Output

System.Object

Collegamenti correlati

• SQLServer_Cmdlets
• Entità servizio
• Identità gestita
• Disponibilità elevata
• Statistiche provider per SQL Server
• Crittografia della connessione strict