Как ПЕЧАТЬ сообщение из функции SQL CLR?

Существует ли эквивалент

PRINT 'hello world'

который можно вызвать из кода CLR (С#)?

Я пытаюсь вывести некоторую отладочную информацию в свою функцию. Я не могу запустить отладчик VS, потому что это удаленный сервер.

Спасибо!

Ответ 1

Сергей,

Ответ заключается в том, что вы не можете сделать эквивалент

PRINT 'Hello World'

изнутри a [SqlFunction()]. Вы можете сделать это, однако, из [SqlProcedure()], используя

SqlContext.Pipe.Send("hello world")

Это согласуется с T-SQL, где вы получите ошибку "Недопустимое использование оператора" PRINT "в функции", если вы вставляете PRINT внутри функции. Но если вы сделаете это из хранимой процедуры.

Для обходных решений я предлагаю:

Используйте Debug.Print из своего кода и присоедините отладчик к SQL Server (я знаю, что это не работает для вас, как вы объяснили).
Сохраните сообщения в глобальной переменной, например List<string> messages, и напишите другую функцию, которая возвращает содержимое messages. Конечно, доступ к messages должен быть синхронизирован, потому что несколько потоков могут пытаться получить к нему доступ в одно и то же время.
Переместите свой код на [SqlProcedure()]
Добавьте параметр 'debug', который, когда = 1, функция вернет сообщения как часть возвращенной таблицы (при условии, что есть столбец с текстом..)

Ответ 2

Вы должны просто иметь возможность:

SqlContext.Pipe.Send("hello world");

Если вы используете это в UDF CLR, SqlContext.Pipe всегда будет null, как вы обнаружили. Без действительного SqlPipe я не верю, что вы можете делать то, что хотите.

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

Ответ 3

Ahh, я вижу... Jsut, чтобы уточнить: если у вас есть SqlFunction, то SqlContext.Pipe недоступен, однако в SqlProcedure он есть, и вы можете использовать Send() для записи сообщений.

Я все еще не нашел способ вывода информации из SqlFunction, кроме сообщения об исключении.

Ответ 4

Вы можете попытаться поместить эту информацию через хранимую процедуру "xp_logevent". Вы можете настроить свою отладочную информацию как "информацию", "предупреждение" или "ошибку" на другом уровне. Я также попытался поместить эту информацию об ошибках/ошибках в журнал событий, но для этого требуется небольшая конфигурация при безопасности, и я сомневаюсь, что не могу ее использовать при производстве.

Ответ 5

Функции SQLCLR - Скалярные пользовательские функции (UDF), Табличные функции (TVF), Пользовательские агрегаты (UDA) и методы в пользовательских типах (UDT) - при использовании контекстного соединения ( т.е. ConnectionString = "Context Connection = true;"), привязаны к большинству тех же ограничений, с которыми связаны функции T-SQL, включая невозможность PRINT или RAISERROR('message', 10, 1). Однако у вас есть несколько вариантов.

Прежде чем перейти к этим опциям, следует сказать, что:

вам не нужно переключаться на использование хранимой процедуры. Если требуется функция, то придерживайтесь функции.
добавление параметра "debug" и изменение вывода для этого кажется немного экстремальным, поскольку функции UDF (T-SQL и SQLCLR) не допускают перегрузки. Следовательно, параметр отладки всегда будет в сигнатуре. Если вы хотите вызвать отладку, просто создайте временную таблицу с именем #debug (или что-то в этом роде) и протестируйте ее через SELECT OBJECT_ID(N'tempdb..#debug'); с помощью "Context Connection = true;" для ConnectionString (которая быстро и может быть выполнена в режиме SAFE и является частью той же сессии, чтобы она могла видеть таблицу temp). Получите результат от if (SqlCommand.ExecuteScalar() == DBNull.Value).
пожалуйста, не используйте глобальную (то есть статическую) переменную. это намного сложнее, чем необходимо, и требует (как правило), чтобы установка была установлена на UNSAFE, чего следует избегать, если это вообще возможно.

Итак, если вы можете хотя бы установить сборку на EXTERNAL_ACCESS, у вас есть несколько вариантов. И для этого не требуется установка базы данных на TRUSTWORTHY ON. Это очень распространенное (и неудачное) недоразумение. Вам просто нужно подписать сборку (что в любом случае является хорошей практикой), а затем создать асимметричный ключ (в [master]) из DLL, а затем создать вход на основе этого асимметричного ключа и, наконец, предоставить Login EXTERNAL ACCESS ASSEMBLY. После этого (один раз) вы можете выполнить любое из следующих действий:

напишите сообщения в файл, используя File.AppendAllText(String path, String contents). Конечно, если у вас нет доступа к файловой системе, это не так полезно. Если в сети есть общий диск, доступ к которому возможен, то до тех пор, пока учетная запись службы для службы SQL Server имеет разрешение на создание и запись файлов на этом ресурсе, это будет работать. Если есть доля, которую учетная запись службы не имеет разрешения, но ваша учетная запись "Домен/Active Directory", вы можете обернуть этот вызов File.AppendAllText в:
```
using (WindowsImpersonationContext _Impersonate = 
                      SqlContext.WindowsIdentity.Impersonate())
{
   File.AppendAllText("path.txt", _DebugMessage);
    _Impersonate.Undo();
}
```
подключиться к SQL Server и записать сообщения в таблицу. Это может быть текущий/локальный SQL Server или любой другой SQL Server. Вы можете создать таблицу в [tempdb], чтобы она автоматически очищалась в следующий раз, когда SQL Server перезапускается, но в остальном продолжается до этого времени или пока вы не отпустите ее. Создание регулярного/внешнего соединения позволяет выполнять инструкции DML. Затем вы можете выбрать из таблицы при запуске функции.
записывать сообщения в переменную среды. Переменные среды не точно ограничены по размеру с Vista/Server 2008, хотя они действительно не обрабатывают новые строки. Но любой набор переменных из кода .NET также сохранится до перезапуска службы SQL Server. И вы можете добавить сообщение, прочитав текущее значение и конкатенируя новое сообщение до конца. Что-то вроде:
```
{
  string _Current = System.Environment.GetEnvironmentVariable(_VariableName,
                                  EnvironmentVariableTarget.Process);

  System.Environment.SetEnvironmentVariable(
      _VariableName,
      _Current + _DebugMessage,
      EnvironmentVariableTarget.Process);
}
```

Следует отметить, что в каждом из этих трех случаев предполагается, что тестирование выполняется однопоточным способом. Если функция будет выполняться одновременно с нескольких сеансов, вам нужен способ разделить сообщения. В этом случае вы можете получить текущий "transaction_id" (все запросы, даже без BEGIN TRAN, являются транзакцией!), Которые должны быть согласованы для любого конкретного исполнения (для нескольких применений в той же функции, а также если функция для каждой строки в нескольких строках). Вы можете использовать это значение в качестве префикса для сообщений, если используете методы переменной файла или среды, или как отдельное поле, если оно хранится в таблице. Вы можете получить транзакцию, выполнив следующие действия:

int _TransactionID;

using (SqlConnection _Connection = new SqlConnection("Context Connection = true;"))
{
    using (SqlCommand _Command = _Connection.CreateCommand())
    {
        _Command.CommandText = @"
SELECT transaction_id
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;
";

        _Connection.Open();
        _TransactionID = (int)_Command.ExecuteScalar();
    }
}

Дополнительная информация о функциях T-SQL и SQLCLR

Следующий список был первоначально взят со страницы MSDN для Создать пользовательские функции (механизм базы данных), а затем отредактирован мной, как указано, чтобы отразить различия между функциями T-SQL и функциями SQLCLR:

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

Пользовательские функции не могут содержать предложение OUTPUT INTO, для которого в качестве цели используется таблица.

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

Обработка ошибок ограничена в пользовательской функции. UDF не поддерживает TRY... CATCH, @@ERROR или RAISERROR. [Примечание. Это относится к T-SQL, либо на основе, либо из функции SQLCLR. Вы можете использовать try/catch/finally/throw в .NET-коде. ]

Операторы SET не допускаются в пользовательской функции.

Предложение FOR XML не разрешено

Пользовательские функции могут быть вложенными;... Уровень вложенности увеличивается, когда вызываемая функция запускает выполнение и уменьшается, когда вызываемая функция завершает выполнение. Пользовательские функции могут быть вложены до 32 уровней.

Следующие операторы Service Broker не могут быть включены в определение пользовательской функции Transact-SQL:
НАЧАТЬ ДИАЛОГ СООБЩЕНИЯ

КОНЕЦ КОНВЕРСА

ПОЛУЧИТЬ ГРУППУ РАЗГОВОРОВ

ДВИЖЕНИЕ ДВИЖЕНИЯ

ПОЛУЧИТЬ

SEND

Следующее относится как к функциям T-SQL, так и к функциям SQLCLR:

Невозможно использовать PRINT

Невозможно вызвать NEWID() [Ну, если вы не SELECT NEWID() из представления. Но внутри кода .NET вы можете использовать Guid.NewGuid(). ]

Ниже перечислены только функции T-SQL:

Пользовательские функции не могут вызывать хранимую процедуру, но могут вызывать расширенную хранимую процедуру.

Пользовательские функции не могут использовать динамические таблицы SQL или temp. Разрешены переменные таблицы.

Напротив, функции SQLCLR могут:

Выполнять хранимые процедуры, если они доступны только для чтения.

Использовать динамический SQL (все SQL, представленные из SQLCLR, являются ad hoc/dynamic по самой своей природе).

SELECT из временных таблиц.