sql-server - procedimiento - mostrar mensaje en stored procedure

Cómo IMPRIMIR un mensaje de la función SQL CLR?

Question

¿Hay un equivalente de

PRINT ''hello world''

que se puede llamar desde el código CLR (C #)?

Estoy tratando de generar alguna información de depuración en mi función. No puedo ejecutar el depurador VS porque este es un servidor remoto.

¡Gracias!

Answer 1

Deberías ser capaz de hacer:

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

Si está ejecutando esto dentro de una UDF de CLR, SqlContext.Pipe siempre será null como descubrió. Sin un SqlPipe válido, no creo que puedas hacer lo que quieras.

Si esto es solo para fines de depuración, siempre puede abrir un archivo dentro del código administrado y escribir allí su resultado. Sin embargo, esto requiere que su ensamblado tenga el permiso EXTERNAL_ACCESS , y esto a su vez requiere que la base de datos se marque como confiable. No necesariamente algo que haría o recomendaría.

Answer 2

Ahh veo ... Jsut para aclarar: si tiene una función Sql , SqlContext.Pipe no está disponible, sin embargo, en un SqlProcedure sí lo está y puede usar Send () para escribir mensajes.

Todavía no he encontrado una forma de generar información desde una función Sql aparte de un mensaje de excepción.

Answer 3

Serguei,

La respuesta es que no puedes hacer el equivalente de

PRINT ''Hello World''

desde dentro de [SqlFunction()] . Sin embargo, puede hacerlo desde un [SqlProcedure()] usando

SqlContext.Pipe.Send("hello world")

Esto es coherente con T-SQL, donde se obtendría el error "Uso no válido de un operador de efecto secundario ''IMPRESIÓN'' dentro de una función" si pega una IMPRESIÓN dentro de una función. Pero no si lo haces a partir de un procedimiento almacenado.

Para soluciones alternativas, sugiero:

1. Use Debug.Print de su código y adjunte un depurador al SQL Server (sé que esto no funciona para usted como usted explicó).
2. Guarde los mensajes en una variable global, por ejemplo, los List<string> messages , y escriba otra función con valores de tabla que devuelva el contenido de los messages . Por supuesto, el acceso a los messages debe sincronizarse porque varios hilos pueden intentar acceder a él al mismo tiempo.
3. Mueva su código a [SqlProcedure()]
4. Agregue un parámetro ''debug'' que cuando = 1 la función devolverá los mensajes como parte de la tabla devuelta (suponiendo que haya una columna con texto ..)

Answer 4

Puede tratar de poner esa información a través del procedimiento almacenado "xp_logevent". Puede configurar su información de depuración como "información", "advertencia" o "error" en el nivel diferente. También traté de poner esa información de errores / errores en el registro de eventos, pero eso requiere un poco de configuración en la seguridad, que dudo que no pueda usar en la producción.

Answer 5

Funciones SQLCLR: funciones escalares definidas por el usuario (UDF), funciones con valores de tabla (TVF), agregaciones definidas por el usuario (UDA) y métodos dentro de tipos definidos por el usuario (UDT) cuando se usa la conexión de contexto (es decir, ConnectionString = "Context Connection = true;" ), están sujetos a la mayoría de las mismas restricciones a las que están sujetas las funciones de T-SQL, incluido el no poder PRINT o RAISERROR(''message'', 10, 1) . Sin embargo, tienes algunas opciones.

Antes de llegar a esas opciones, se debe establecer que:

• no necesita cambiar a usar un Procedimiento almacenado. Si quiere una función, entonces quédese con una función.

• agregar un parámetro "debug" y cambiar la salida para esto parece un poco extremo ya que las funciones UDF (T-SQL y SQLCLR) no permiten la sobrecarga. Por lo tanto, el parámetro de depuración siempre estará en la firma. Si quieres desencadenar la depuración, simplemente crea una tabla temporal llamada #debug (o algo así) y prueba a través de SELECT OBJECT_ID(N''tempdb..#debug''); usando "Context Connection = true;" para ConnectionString (que es rápido y se puede hacer en modo SAFE y es parte de la misma sesión para que pueda ver la tabla temporal). Obtenga el resultado de eso de if (SqlCommand.ExecuteScalar() == DBNull.Value) .

• por favor no use una variable global (es decir, estática). eso es mucho más complicado de lo necesario, y requiere (típicamente) que la Asamblea se configure como UNSAFE , lo que debe evitarse si es posible.

Por lo tanto, si al menos puede establecer el ensamblaje en EXTERNAL_ACCESS , entonces tiene algunas opciones. Y hacer esto no requiere establecer la base de datos en TRUSTWORTHY ON . Ese es un malentendido muy común (y desafortunado). Solo necesita firmar el ensamblado (que es una buena práctica de todos modos), luego crear una clave asimétrica (en [master] ) desde el DLL, luego crear un inicio de sesión basado en esa clave asimétrica y finalmente otorgar el EXTERNAL ACCESS ASSEMBLY inicio de sesión. Después de hacer eso (una vez), puede hacer cualquiera de los siguientes:

• escriba los mensajes en un archivo usando File.AppendAllText (String path, String contents) . Por supuesto, si no tiene acceso al sistema de archivos, esto no es tan útil. Si hay una unidad compartida en la red a la que se puede acceder, siempre que la cuenta de servicio para el servicio SQL Server tenga permiso para crear y escribir archivos en ese recurso compartido, esto funcionará. Si hay un File.AppendAllText compartido que la cuenta de servicio no tiene permiso pero su cuenta de Dominio / Directorio Activo tiene, entonces puede ajustar esa llamada a File.AppendAllText :

  using (WindowsImpersonationContext _Impersonate = 
                        SqlContext.WindowsIdentity.Impersonate())
  {
     File.AppendAllText("path.txt", _DebugMessage);
      _Impersonate.Undo();
  }

• Conéctese a SQL Server y escriba los mensajes en una tabla. Puede ser el servidor SQL actual / local o cualquier otro servidor SQL. Puede crear una tabla en [tempdb] para que se limpie automáticamente la próxima vez que se reinicie SQL Server, pero de lo contrario durará hasta ese momento, o hasta que lo suelte. Hacer una conexión regular / externa le permite hacer declaraciones DML. Luego puede seleccionar de la tabla mientras ejecuta la función.

• escribe los mensajes a una variable de entorno. Las variables de entorno no están limitadas en tamaño desde Vista / Server 2008, aunque en realidad no manejan las nuevas líneas. Pero cualquier conjunto de variables desde el código .NET también sobrevivirá hasta que se reinicie el servicio de SQL Server. Y puede agregar un mensaje leyendo el valor actual y concatenando el nuevo mensaje hasta el final. Algo como:

  {
    string _Current = System.Environment.GetEnvironmentVariable(_VariableName,
                                    EnvironmentVariableTarget.Process);
  
    System.Environment.SetEnvironmentVariable(
        _VariableName,
        _Current + _DebugMessage,
        EnvironmentVariableTarget.Process);
  }

Cabe señalar que en cada uno de estos 3 casos, se supone que la prueba se realiza de una sola vez. Si la función se ejecutará desde varias sesiones al mismo tiempo, entonces necesita una forma de separar los mensajes. En ese caso, puede obtener el "transaction_id" actual (¡todas las consultas, incluso sin BEGIN TRAN son una transacción!) Que deberían ser consistentes para cualquier ejecución particular (a través de múltiples usos en la misma función, así como si la función se llama por cada fila en múltiples filas). Puede usar este valor como un prefijo para los mensajes si utiliza el archivo o los métodos de variables de entorno, o como un campo separado si se almacena en una tabla. Puede obtener la transacción haciendo lo siguiente:

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();
    }
}

Información adicional sobre las funciones T-SQL y SQLCLR

La siguiente lista fue tomada inicialmente de la página de MSDN para Crear Funciones definidas por el usuario (Motor de base de datos) y luego editada por mí, como se indica, para reflejar las diferencias entre las funciones de T-SQL y las funciones de SQLCLR:

• Las funciones definidas por el usuario no se pueden usar para realizar acciones que modifiquen el estado de la base de datos.
• Las funciones definidas por el usuario no pueden contener una cláusula OUTPUT INTO que tenga una tabla como su objetivo.
• Las funciones definidas por el usuario no pueden devolver múltiples conjuntos de resultados. Use un procedimiento almacenado si necesita devolver múltiples conjuntos de resultados.
• El manejo de errores está restringido en una función definida por el usuario. Una UDF no es compatible con TRY ... CATCH, @@ ERROR o RAISERROR. [ Nota: Esto es en términos de T-SQL, ya sea nativo o enviado desde una función SQLCLR. Puede usar try / catch / finally / throw en el código .NET. ]
• Las sentencias SET no están permitidas en una función definida por el usuario.
• La cláusula FOR XML no está permitida
• Las funciones definidas por el usuario se pueden anidar; ... El nivel de anidamiento se incrementa cuando la función llamada inicia la ejecución y disminuye cuando la función llamada finaliza la ejecución. Las funciones definidas por el usuario se pueden anidar hasta en 32 niveles.
• Las siguientes declaraciones de Service Broker no se pueden incluir en la definición de una función definida por el usuario de Transact-SQL:
  • INICIE LA CONVERSACIÓN DEL DIÁLOGO
  • TERMINAR LA CONVERSACIÓN
  • OBTENER GRUPO DE CONVERSACIÓN
  • MUDAR LA CONVERSACIÓN
  • RECIBIR
  • ENVIAR

Lo siguiente se refiere tanto a las funciones de T-SQL como a las funciones de SQLCLR:

• No se puede usar IMPRIMIR
• No se puede llamar a NEWID () [Bueno, a menos que SELECT NEWID() desde dentro de una Vista. Pero dentro del código .NET, puede usar Guid.NewGuid() . ]

Lo siguiente se refiere solo a las funciones de T-SQL:

• Las funciones definidas por el usuario no pueden llamar a un procedimiento almacenado, pero pueden llamar a un procedimiento almacenado extendido.
• Las funciones definidas por el usuario no pueden hacer uso de SQL tablas dinámicas o temporales. Las variables de tabla están permitidas.

Por el contrario, las funciones SQLCLR pueden:

• Ejecutar procedimientos almacenados, siempre que sean de solo lectura.
• Haga uso de SQL dinámico (todo SQL enviado desde SQLCLR es ad hoc / dinámico por su propia naturaleza).
• SELECCIONAR de tablas temporales.