Табличная функция SQL Server? PRAGMA AUTONOMOUS_TRANSACTION

Question

У нас есть стороннее программное обеспечение, которое выбирает данные из базы данных Oracle с использованием ODBC.

В Oracle мы предоставляем данные как конвейерную функцию (а не таблицу или представление), потому что мы хотим провести аудит SELECT, Конвейерная функция Oracle использует PRAGMA AUTONOMOUS_TRANSACTION переключитесь для того, чтобы мы могли записать строку в таблицу аудита для каждой строки, которую мы передаем обратно в ODBC Select.

Это работает блестяще! Но теперь нам нужно сделать то же самое с SQL Server, а не с Oracle.

Я думал, что мы могли бы сделать это, используя табличные функции SQL Server вместо конвейерных функций Oracle, но мы достигли закона "не выполняй DML в функции".

Мы попробовали это с процедурой, но у стороннего программного обеспечения этого не будет - оно падает, когда я говорю "EXEC"

Есть ли PRAGMA AUTONOMOUS_TRANSACTION эквивалент в SQL Server? Или есть другие варианты того, что я хочу сделать?

Answer 1

Есть решение, вызвав master..xp_cmdshell внутри вашей функции вопрос на dba.stackexchange.com

CREATE FUNCTION loophole(@i int) RETURNS varchar(20) AS
  BEGIN
     DECLARE @sql varchar(MAX),
             @cmd varchar(4000)
     SELECT @sql = ' UPDATE rsci ' +
                   ' SET b = CASE ' + ltrim(str(@i + 1)) +
                   ' WHEN 1 THEN ''Ett'' WHEN 2 THEN ''Två''' +
                   ' WHEN 3 THEN ''Tre'' WHEN 4 THEN ''Fyra''' +
                   ' WHEN 5 THEN ''Fem'' WHEN 6 THEN ''Sex''' +
                   ' WHEN 7 THEN ''Sju'' WHEN 8 THEN ''Åtta''' +
                   ' WHEN 9 THEN ''Nio'' WHEN 10 THEN ''Tio'' END' +
                   ' WHERE a = ' + ltrim(str(@i + 1))
     SELECT @cmd = 'sqlcmd -S ' + @@servername + ' -d ' + db_name() +
                   ' -Q "' + @sql + '"'
     EXEC master..xp_cmdshell @cmd, 'no_output'
     RETURN (SELECT b FROM rsci WHERE a = @i)
  END