sp_executesql z typem tabeli zdefiniowanym przez użytkownika nie działa poprawnie

Problem : czy istnieje znany problem z typami tabel zdefiniowanych przez użytkownika jako parametrami sp_executesql ? Odpowiedź - nie, jestem idiotą.

Skonfiguruj skrypt

Ten skrypt tworzy po jednym stole, procedurze i typie tabeli zdefiniowanym przez użytkownika (tylko ograniczony SQL Server 2008+).

• Celem stosu jest zapewnienie audytu, że tak, dane weszły do ​​procedury. Nie ma żadnych ograniczeń, nie ma nic, co mogłoby uniemożliwić wstawienie danych.

• Procedura przyjmuje jako parametr zdefiniowany przez użytkownika typ tabeli. Wszystko, co robi proc, to wstawienie do tabeli.

• Zdefiniowany przez użytkownika typ tabeli jest również torfowiskowy, tylko jedna kolumna

Wystąpiłem przeciwko 11.0.1750.32 (X64) i 10.0.4064.0 (X64)tak, wiem, że to pole można załatać, nie kontroluję tego.

-- this table record that something happened
CREATE TABLE dbo.UDTT_holder
(
    ServerName varchar(200)
,   insert_time datetime default(current_timestamp)
)
GO

-- user defined table type transport mechanism
CREATE TYPE dbo.UDTT
AS TABLE
(
    ServerName varchar(200)
)
GO

-- stored procedure to reproduce issue
CREATE PROCEDURE dbo.Repro
(
    @MetricData dbo.UDTT READONLY
)
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO dbo.UDTT_holder 
    (ServerName)
    SELECT MD.* FROM @MetricData MD
END
GO

Problem z reprodukcją

Ten skrypt pokazuje problem i jego wykonanie powinno zająć pięć sekund. Tworzę dwa wystąpienia typów tabel zdefiniowanych przez użytkownika, a następnie wypróbowuję dwa różne sposoby przekazywania ich do sp_executesql. Odwzorowanie parametrów w pierwszym wywołaniu naśladuje to, co przechwyciłem z SQL Profiler. Następnie wywołuję procedurę bez sp_executesqlopakowania.

SET NOCOUNT ON
DECLARE 
    @p3 dbo.UDTT
,   @MetricData dbo.UDTT
INSERT INTO @p3 VALUES(N'SQLB\SQLB')
INSERT INTO @MetricData VALUES(N'SQLC\SQLC')

-- nothing up my sleeve
SELECT * FROM dbo.UDTT_holder

SELECT CONVERT(varchar(24), current_timestamp, 121) + ' Firing sp_executesql' AS commentary
-- This does nothing
EXECUTE sp_executesql N'dbo.Repro',N'@MetricData dbo.UDTT READONLY',@MetricData=@p3
-- makes no matter if we're mapping variables
EXECUTE sp_executesql N'dbo.Repro',N'@MetricData dbo.UDTT READONLY',@MetricData

-- Five second delay
waitfor delay '00:00:05'
SELECT CONVERT(varchar(24), current_timestamp, 121) + ' Firing proc' AS commentary
-- this does
EXECUTE dbo.Repro @p3

-- Should only see the latter timestamp
SELECT * FROM dbo.UDTT_holder

GO

Wyniki : poniżej są moje wyniki. Tabela jest początkowo pusta. Emituję aktualny czas i wykonuję dwa połączenia z sp_executesql. Czekam 5 sekund, aby upłynąć i emituję bieżący czas, a następnie wywołuję samą procedurę przechowywaną i wreszcie zrzucam tabelę kontroli.

Jak widać ze znacznika czasu, rekord dla rekordu B odpowiada wywołaniu prostej procedury składowanej. Ponadto nie ma rekordu SQLC.

ServerName                                       insert_time
------------------------------------------------------------------------

commentary
-------------------------------------------------
2012-02-02 13:09:05.973 Firing sp_executesql

commentary
-------------------------------------------------
2012-02-02 13:09:10.983 Firing proc

ServerName                                       insert_time
------------------------------------------------------------------------
SQLB\SQLB                                        2012-02-02 13:09:10.983

Zerwij skrypt

Ten skrypt usuwa obiekty we właściwej kolejności (nie można upuścić typu przed usunięciem odwołania do procedury)

-- cleanup
DROP TABLE dbo.UDTT_holder
DROP PROCEDURE dbo.Repro
DROP TYPE dbo.UDTT
GO

Dlaczego to ma znaczenie

Kod wydaje się głupi, ale nie mam dużej kontroli nad użyciem sp_execute w porównaniu z „prostym” wywołaniem proc. Powyżej łańcucha połączeń używam biblioteki ADO.NET i przekazuję TVP, tak jak wcześniej . Typ parametru jest poprawnie ustawiony na System.Data.SqlDbType.Structured, a CommandType jest ustawiony na System.Data.CommandType.StoredProcedure .

Dlaczego jestem noobem (edytuj)

Rob i Martin Smith zobaczyli to, czego nie widziałem - instrukcja przekazywana do sp_executesql nie używała tego @MetricDataparametru. Nieprzetworzony / zmapowany parametr nie będzie używany.

Tłumaczyłem mój działający kod C # wartości parametrów tabeli na PowerShell, a ponieważ się skompilował, nie mógł to być kod błędu; tyle że było. Pisząc tę kwestię, zdałem sobie sprawę, że nie było ustawić CommandTypesię StoredProceduretak dodałem tę linię i ponownie prowadził kod, ale ślad w profilera nie uległ zmianie, więc zakłada się, że nie było to problemem.

Zabawna historia - jeśli nie zapiszesz zaktualizowanego pliku, jego uruchomienie nie zrobi różnicy. Dostałem się rano i ponownie uruchomiłem (po zapisaniu), a ADO.NET przetłumaczył to, na EXEC dbo.Repro @MetricData=@p3co działa dobrze.

sp_executesqlsłuży do wykonywania ad-hoc T-SQL. Więc powinieneś spróbować:

EXECUTE sp_executesql N'exec dbo.Repro @MetricData',N'@MetricData dbo.UDTT READONLY',@MetricData=@p3

Kusiło mnie, aby usunąć to pytanie, ale pomyślałem, że ktoś inny może spotkać się z podobną sytuacją.

Główną przyczyną było to, $updateCommandże nie ustawiłem CommandType. Wartość domyślna to Tekst, więc moja procedura składowana została wywołana poprawnie. Moje parametry były tworzone i przekazywane, ale jak zauważono w innych odpowiedziach tylko dlatego, że parametry są dostępne , nie oznacza, że ​​są używane .

Kod na wypadek, gdyby ktoś był zainteresowany moją pomyłką

    $updateConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=localhost\localsqla;Initial Catalog=baseline;Integrated Security=SSPI;")
    $updateConnection.Open()

    $updateCommand = New-Object System.Data.SqlClient.SqlCommand($updateQuery)
    $updateCommand.Connection = $updateConnection

    # This is what I was missing
    $updateCommand.CommandType = [System.Data.CommandType]::StoredProcedure
    #$updateCommand.CommandType = [System.Data.CommandType]::Text
    #$updateCommand.CommandType = [System.Data.CommandType]::TableDirect

    $DataTransferFormat = $updateCommand.Parameters.AddWithValue("@MetricData", $dataTable)
    $DataTransferFormat.SqlDbType = [System.Data.SqlDbType]::Structured
    $DataTransferFormat.TypeName = $udtt
    $results = $updateCommand.ExecuteNonQuery()

Uruchamianie z wartością CommandType tekstu wymagałoby rozwiązania @ rob_farley polegającego na zmianie wartości $updateQueryna „EXEC dbo.Repro @MetricData”. W tym momencie sp_executesql poprawnie odwzoruje wartości i życie będzie dobre.

Jak wskazuje dokumentacja, uruchamianie za pomocą CommandTypeof TableDirectnie jest obsługiwane przez dostawcę danych SqlClient.

Używanie CommandTypeod StoredProcedureprzekłada się na bezpośrednie wywołanie procedury przechowywanej bez sp_executesqlowijki i działa świetnie.