Invoke-Sqlcmd

Синтаксис

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

Описание

Командлет Invoke-Sqlcmd запускает скрипт, содержащий языки и команды, поддерживаемые служебной программой SQL Server SQLCMD.

Поддерживаются команды Transact-SQL и подмножество синтаксиса XQuery, поддерживаемого ядром СУБД.

Этот командлет также принимает многие команды, которые изначально поддерживаются SQLCMD, например GO и QUIT.

Этот командлет также принимает переменные скрипта SQLCMD, такие как SQLCMDUSER. По умолчанию этот командлет не устанавливает переменные скрипта SQLCMD.

Этот командлет не поддерживает использование команд, связанных в первую очередь с интерактивным редактированием скриптов.

К неподдерживаемым командам относятся :!!, :connect, :error, :out, :ed, :list, :listvar, :reset, :perftrace и :serverlist.

При выполнении этого командлета первый результирующий набор, возвращаемый скриптом, отображается в виде форматируемой таблицы.

Если последующие результирующие наборы содержат списки столбцов, отличные от первых, эти результирующие наборы не отображаются.

Если последующие результирующие наборы после первого набора имеют одинаковый список столбцов, их строки добавляются в форматированную таблицу, содержащую строки, возвращенные первым результирующим набором.

Вы можете отобразить SQL Server выходные данные сообщения, например полученные в результате выполнения инструкции SQL PRINT, указав параметр Verbose.

Примеры

Пример 1. Подключение к именованным экземплярам и запуск скрипта

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

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

Эта команда подключается к именованный экземпляр подсистемы База данных SQL на компьютере и запускает базовый скрипт Transact-SQL.

Пример 2. Вызов команд в файле скрипта и сохранение выходных данных в текстовом файле

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

Эта команда считывает файл, содержащий инструкции Transact-SQL и команды SQLCMD, запускает файл и записывает выходные данные в другой файл.

Выходной файл может содержать проприетарную информацию, поэтому следует защитить выходные файлы с соответствующими разрешениями NTFS.

Пример 3. Вызов скрипта и передача переменных значений из строки

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

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

Эта команда использует массив символьных строк в качестве входных данных для параметра Variable.

Массив определяет несколько переменных SQLCMD.

Знаки $ в инструкции SELECT, которые идентифицируют переменные SQLCMD, экранируются с помощью символа обратного деления (').

Пример 4. Вызов скрипта и передача переменных из ядра СУБД 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

Эта команда использует Set-Location для перехода к пути поставщика SQL ServerWindows PowerShell для экземпляра обработчика База данных SQL.

Затем он вызывает Get-Item для получения объекта SQL Management Object Server для использования в качестве параметра ServerInstance объекта Invoke-Sqlcmd.

Пример 5. Выполнение запроса и отображение подробных выходных данных

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

Эта команда использует параметр Windows PowerShellVerbose для возврата выходных данных сообщения команды SQL PRINT.

Пример 6. Вызов команды с помощью позиционной строки в качестве входных данных

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

Эта команда использует позициональную строку для предоставления входных данных в параметр Query.

Здесь также показано, как Invoke-Sqlcmd использует текущий путь для установки контекста базы данных MyDatabase.

Пример 7. Запись данных в объект 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 }

Эта команда использует параметр As DataSet для записи данных в объект .NET System.Data.DataSet и сохраняет результат в переменной "$DS". Объект можно использовать для дальнейшей обработки.

Пример 8. Получение определенных наборов столбцов

$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

Первая команда использует параметр As DataTables для записи данных в коллекцию объектов .NET System.Data.DataTable. Команда возвращает две таблицы с разными наборами столбцов.

Каждую таблицу можно обрабатывать по отдельности на основе собственной схемы.

Пример 9. Получение полного контроля над подключением

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

Эта команда использует параметр -ConnectionString, чтобы получить полный контроль над соединением, которое устанавливает этот командлет, вместо Invoke-Sqlcmd для сборки строка подключения на основе параметров, переданных в командной строке.

Это полезно для менее распространенных свойств, которые можно использовать.

Пример 10. Выполнение хранимой процедуры и запись ошибок 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
...

Эта команда использует параметр -OutputSqlErrors , чтобы сообщить об ошибках пользователю. Обратите внимание, что в сообщении об ошибке в этом случае содержатся дополнительные сведения, такие как имя субъекта-службы и номер строки, в которой произошла ошибка.

Пример 11. Подключение к базе данных Azure SQL (или Управляемый экземпляр) с помощью маркера доступа

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'

Пример 12. Подключение к базе данных Azure SQL (или Управляемый экземпляр) с помощью субъекта-службы

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'

Пример 13. Подключение к базе данных Azure SQL (или Управляемый экземпляр) с помощью управляемого удостоверения

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

## Obtain access token from the machine
if (Get-Command -All az -ErrorAction SilentlyContinue -CommandType Application) {
  # Obtain the Access Token from the Azure provider for database resources (using az az cli)
  az login
  $access_token = az account get-access-token --resource https://database.windows.net/ --query accessToken -o tsv
} else {
  # az cli not around: fall back and assume this is a VM in Azure
  $response = Invoke-WebRequest `
    -Uri 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fdatabase.windows.net'`
    -Method GET`
    -Headers @{Metadata="true"}
  $access_token = ($response.Content | ConvertFrom-Json).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'

Этот пример аналогичен приведенному выше. Он показывает только гибкость параметра AccessToken .

Пример 14. Подключение к группе доступности, настроенной для маршрутизации Read-Only с помощью -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

Что показывает, что в зависимости от значения -ApplicationIntent параметра подключение направляется на другой сервер в группе доступности. Кстати, обратите внимание на использование параметров и -HostName для визуальной -ApplicationName дифференцировки двух результатов: это распространенный метод, который можно использовать для трассировки соединений и их намерений, помимо примера -ApplicationIntent, показанного здесь.

Пример 15. Сбор статистики подключения с помощью параметра -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

В этом примере показано, как использовать -StatisticsVariable параметр для сбора сведений о соединении, выполненных инструкциях и времени выполнения при выполнении некоторого T-SQL, который создает временную таблицу, вставляет некоторые значения и, наконец, выдает выборку, чтобы получить все вставленные строки.

Примечание. При выполнении одного и того же запроса к нескольким серверам (например, путем отправки имен серверов с помощью командлета StatisticsVariable ) объект записывает массив статистики, по одному для каждого подключения. Затем результаты можно агрегировать с помощью, например, ($stats.IduRows | Measure-Object -Sum).Sum.

Дополнительные сведения о доступной статистике см. в разделе Статистика поставщика для SQL Server.

Пример 16. Выполнение запроса, который расшифровывает данные, полученные из столбцов, зашифрованных с помощью Always Encrypted. Предположим, что столбец master ключ хранится в хранилище ключей в 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

Параметры

-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

-Query

-QueryTimeout

-ServerInstance

-SeverityLevel

-StatisticsVariable

-SuppressProviderContextWarning

-TrustServerCertificate

-Username

-Variable

Входные данные

System.Management.Automation.PSObject

Выходные данные

System.Object

Связанные ссылки

• SQLServer_Cmdlets
• Субъект-служба
• Управляемое удостоверение
• Высокий уровень доступности
• Статистика поставщика для SQL Server
• Строгое шифрование подключений