Заполнение временной таблицы базы данных SQL с помощью PowerShell с использованием хранимой процедуры с табличными параметрами

Я изучаю SQL, хотя знаком с Powershell.

Я пытаюсь вызвать хранимую процедуру, используя параметр с табличным значением, чтобы вставить массив данных из PowerShell во временную таблицу базы данных SQL.

Я использую модуль SqlServer PowerShell: https://docs.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-ver15

БД называется WAGS, таблица называется AzureADUsers, а мой определяемый пользователем тип называется AzureADUser.

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

Мой определяемый пользователем тип выглядит следующим образом (как и столбцы в моей таблице AzureADUser).

01: ImmutableId         varchar            255 UNIQUE
02: DisplayName         varchar            255
03: UserPrincipalName   varchar            255 UNIQUE
04: ObjectId            uniqueidentifier       16  UNIQUE

Моя хранимая процедура выглядит так (я включил весь сценарий ALTER на случай, если я допустил здесь ошибку).

 USE [WAGS]
GO
/****** Object:  StoredProcedure [dbo].[spUpdateAzureADUsersTable]    Script Date: 20/10/2020 15:59:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spUpdateAzureADUsersTable] (@users [dbo].[AzureADUser] READONLY)
    AS
    INSERT INTO [dbo].[AzureADUsers]
        (
            ImmutableId,
            DisplayName,
            UserPrincipalName,
            ObjectId
        )
    SELECT * FROM @users

Я пытаюсь вызвать хранимую процедуру с помощью командлета PowerShell Invoke-SqlCmd следующим образом:

$azureAdUsers = Get-AzureADUser -All $true | ? { $_.ImmutableId }

$data = $azureAdUsers | Select ImmutableId,DisplayName,UserPrincipalName,ObjectId

Invoke-Sqlcmd -ServerInstance 'MERCURY\SQLEXPRESS' -Database 'WAGS' -QUERY "EXEC spUpdateAzureADUsersTable $data"


sql sql-server powershell stored-procedures table-valued-parameters
person supermerio    schedule 21.10.2020    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Оказывается, в модуле Microsoft SQL PowerShell много ошибок/ограничений. Хранимая процедура, тип и таблица были созданы правильно, проблема заключалась в том, как я вызывал ее из PowerShell, используя (точнее, как я передавал массив значений).

В итоге я перешел к ADO.NET с помощью Powershell с помощью этого главы: http://tomaslind.net/2016/05/27/from-powershell-to-sql-tables/

ПРИМЕЧАНИЕ. При создании больших таблиц данных быстрее использовать [void], а не передавать в Out-Null.

Мой PowerShell теперь выглядит так:

$azureAdUsers = Get-AzureADUser -All $true | ? { $_.ImmutableId }

$dataTable = [System.Data.DataTable]::New()

[void]($dataTable.Columns.Add("ObjectId","System.Guid"))
[void]($dataTable.Columns.Add("ImmutableId","System.String"))
[void]($dataTable.Columns.Add("DisplayName","System.String"))
[void]($dataTable.Columns.Add("UserPrincipalName","System.String"))

foreach($u in $azureAdUsers)
{
    [void]($dataTable.Rows.Add($u.ObjectId,$u.ImmutableId,$u.DisplayName,$u.UserPrincipalName))
}

$ServerInstance = "MERCURY\SQLEXPRESS"

$DatabaseName = "WAGS"
 
$ConnString = "Server=$ServerInstance;Database=$DatabaseName;Integrated Security=SSPI;"
 
$conn = New-Object System.Data.SqlClient.SqlConnection $ConnString

$conn.Open()
 
$query = "dbo.spUpdateAzureADUsersTable"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn
$cmd.CommandType = [System.Data.CommandType]"StoredProcedure"
$cmd.CommandText = $Query
$cmd.Parameters.Add("@users", [System.Data.SqlDbType]::Structured) | Out-Null
$cmd.Parameters["@users"].Value = $dataTable

$cmd.ExecuteNonQuery() | Out-Null
$conn.Close()

person supermerio    schedule 21.10.2020