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

Описание

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

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

Этот командлет также принимает многие команды, поддерживаемые в собственном коде SQLCMD, такие как GO и QUIT.

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

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

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

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

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

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

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

Примеры

Пример 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

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

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

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

Пример 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. Подключение к базе данных SQL Azure (или управляемому экземпляру) с помощью маркера доступа

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. Подключение к базе данных SQL Azure (или управляемому экземпляру) с помощью субъекта-службы

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. Подключение к базе данных SQL Azure (или управляемому экземпляру) с помощью управляемого удостоверения, назначаемого системой (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'

Пример 14. Подключение к базе данных SQL Azure (или управляемому экземпляру) с помощью управляемого удостоверения, назначаемого пользователем (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'

Пример 15. Подключение к группе доступности, настроенной для маршрутизации 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 подключение направляется на другой сервер в группе доступности. Кстати, обратите внимание на использование -ApplicationName и -HostName параметров для визуального отличия двух результатов: это распространенный метод, который можно использовать для трассировки соединений и их намерений, помимо примера -ApplicationIntent, показанного здесь.

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

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

-ProgressAction

-Query

-QueryTimeout

-ServerInstance

-SeverityLevel

-StatisticsVariable

-SuppressProviderContextWarning

-TrustServerCertificate

-Username

-Variable

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

System.Management.Automation.PSObject

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

System.Object

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

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