Invoke-Sqlcmd

Sintaxe

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

Description

O cmdlet Invoke-Sqlcmd executa um script que contém os idiomas e comandos compatíveis com o utilitário SQL Server SQLCMD.

Os comandos com suporte são instruções Transact-SQL e o subconjunto da sintaxe XQuery compatível com o mecanismo de banco de dados.

Esse cmdlet também aceita muitos dos comandos compatíveis nativamente pelo SQLCMD, como GO e QUIT.

Esse cmdlet também aceita as variáveis de script SQLCMD, como SQLCMDUSER. Por padrão, esse cmdlet não define variáveis de script SQLCMD.

Esse cmdlet não dá suporte ao uso de comandos relacionados principalmente à edição interativa de script.

Os comandos sem suporte incluem :!, :connect, :error, :out, :ed, :list, :listvar, :reset, :p erftrace e :serverlist.

Quando esse cmdlet é executado, o primeiro conjunto de resultados que o script retorna é exibido como uma tabela formatada.

Se os conjuntos de resultados subsequentes contiverem listas de colunas diferentes das primeiras, esses conjuntos de resultados não serão exibidos.

Se os conjuntos de resultados subsequentes após o primeiro conjunto tiverem a mesma lista de colunas, suas linhas serão acrescentadas à tabela formatada que contém as linhas retornadas pelo primeiro conjunto de resultados.

Você pode exibir a saída da mensagem do SQL Server, como aquelas resultantes da instrução SQL PRINT, especificando o parâmetro Verbose.

Exemplos

Exemplo 1: conectar-se a uma instância nomeada e executar um script

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

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

Esse comando se conecta a uma instância nomeada do Mecanismo de Banco de Dados SQL em um computador e executa um script de Transact-SQL básico.

Exemplo 2: invocar comandos em um arquivo de script e salvar a saída em um arquivo de texto

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

Esse comando lê um arquivo que contém instruções Transact-SQL e comandos SQLCMD, executa o arquivo e grava a saída em outro arquivo.

O arquivo de saída pode conter informações proprietárias, portanto, você deve proteger os arquivos de saída com as permissões NTFS apropriadas.

Exemplo 3: invocar um script e passar valores variáveis de uma cadeia de caracteres

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

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

Esse comando usa uma matriz de cadeias de caracteres como entrada para o parâmetro Variable.

A matriz define várias variáveis SQLCMD.

Os sinais $ na instrução SELECT que identificam as variáveis SQLCMD são escapados usando o caractere back-tick (').

Exemplo 4: Invocar um script e passar variáveis do mecanismo de banco de dados 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

Esse comando usa Set-Location para navegar até o caminho do provedor do SQL ServerWindows PowerShell para uma instância do Mecanismo de Banco de Dados SQL.

Em seguida, ele chama Get-Item para recuperar um objeto sql management object server para uso como o parâmetro ServerInstance de Invoke-Sqlcmd.

Exemplo 5: Executar uma consulta e exibir a saída detalhada

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

Este comando usa o parâmetro Windows PowerShellVerbose para retornar a saída da mensagem do comando SQL PRINT.

Exemplo 6: invocar um comando usando uma cadeia de caracteres posicional como entrada

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

Esse comando usa uma cadeia de caracteres posicional para fornecer a entrada para o parâmetro Query.

Ele também demonstra como Invoke-Sqlcmd usa o caminho atual para definir o contexto do banco de dados como MyDatabase.

Exemplo 7: capturar dados em um objeto 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 }

Esse comando usa o parâmetro As DataSet para capturar os dados em um objeto .Net System.Data.DataSet e armazena o resultado na variável '$DS'. O objeto pode ser usado para processamento adicional.

Exemplo 8: Obter conjuntos de colunas específicos

$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

O primeiro comando usa o parâmetro As DataTables para capturar os dados em uma coleção de objetos .Net System.Data.DataTable. O comando obtém duas tabelas com conjuntos de colunas diferentes.

Cada tabela pode ser processada individualmente, com base em seu próprio esquema.

Exemplo 9: Obter controle total de uma conexão

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

Esse comando usa o parâmetro -ConnectionString para obter controle total da conexão estabelecida por esse cmdlet, em vez do Invoke-Sqlcmd para criar a cadeia de conexão com base nos parâmetros passados na linha de comando.

Isso é útil para propriedades menos comuns que talvez você queira usar.

Exemplo 10: Executar um procedimento armazenado e capturar os erros do 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
...

Esse comando usa o parâmetro -OutputSqlErrors para relatar os erros ao usuário. Observe que a mensagem de erro neste caso fornece informações extras, como o nome SP e o número de linha em que o erro ocorreu.

Exemplo 11: Conectar-se ao Banco de Dados SQL do Azure (ou Instância Gerenciada) usando um Token de Acesso

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'

Exemplo 12: Conectar-se ao Banco de Dados SQL do Azure (ou Instância Gerenciada) usando uma Entidade de Serviço

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'

Exemplo 13: Conectar-se ao Banco de Dados SQL do Azure (ou Instância Gerenciada) usando uma SAMI (Identidade Gerenciada Atribuída pelo Sistema)

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'

Exemplo 14: Conectar-se ao Banco de Dados SQL do Azure (ou Instância Gerenciada) usando uma UAMI (Identidade Gerenciada Atribuída ao Usuário)

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'

Exemplo 15: Conectar-se a um grupo de disponibilidade configurado para roteamento de 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

o que mostra que, dependendo do valor do parâmetro -ApplicationIntent, a conexão é roteada para um servidor diferente no AG. A propósito, observe os usos dos parâmetros -ApplicationName e -HostName para diferenciar visualmente os dois resultados: essa é uma técnica comum que pode ser usada para rastrear conexões e suas intenções, além do exemplo de -ApplicationIntent ilustrado aqui.

Exemplo 16: capturar estatísticas de conexão por meio do parâmetro -StatisticsVariable

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

Este exemplo mostra como usar o parâmetro -StatisticsVariable para capturar informações sobre a conexão, as instruções executadas e o tempo de execução ao executar algum T-SQL que cria uma tabela temporária, inserir algum valor e, por fim, emite uma seleção para obter todas as linhas inseridas.

Observação: quando a mesma consulta é executada em vários servidores (por exemplo, canalizando os nomes do servidor por meio do cmdlet), o StatisticsVariable captura uma matriz de estatísticas, uma para cada conexão. Os resultados podem ser agregados usando, por exemplo, ($stats.IduRows | Measure-Object -Sum).Sum.

Consulte Provider Statistics for SQL Server para obter mais informações sobre as estatísticas disponíveis.

Exemplo 17: execute uma consulta que descriptografa dados recuperados de colunas criptografadas usando o Always Encrypted. Suponha que a chave mestra de coluna esteja armazenada em um cofre de chaves no 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

Parâmetros

-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

Entradas

System.Management.Automation.PSObject

Saídas

System.Object

Links Relacionados

• SQLServer_Cmdlets
• Entidade de Serviço
• Identidade Gerenciada
• Alta disponibilidade
• Estatísticas do provedor para SQL Server
• Criptografia de conexão estrita