Llamar a servicios web desde dentro de SQL Server 2005

SqlRanger — Thu, 06 Apr 2006 11:36:00 GMT

El otro día, por diversión, me dio por jugar a llamar a un servicio web desde un procedimiento almacenado de SQL Server 2005 escrito en C#. La cosa parecía fácil y divertida, pero de eso nada, no había manera de hacer funcionar aquello.... Por fin, después de buscar un rato por el Google encontré la solución a mis problemas. Para evitar que a vosotros también os pase lo mismo, quisiera compartir con todos vosotros esta experiencia que os permitirá saber de antemano los problemas que os vais a encontrar al hacer este tipo de cosas y como solucionarlos.

Para empezar supongamos que tenemos una solución en Visual Studio 2005 con un sitio web donde está definido el siguiente servicio web accesible mediante la url http://localhost/ServiciosWeb/ServicioWeb.asmx

public class ServicioWeb : System.Web.Services.WebService
{
[WebMethod]
  public string RealizarProceso(int IdProceso)
  {
    if (IdProceso % 5 == 0)
    {
      throw new SoapException("No me gustan los múltiplos de cinco",
                               SoapException.ClientFaultCode);
    }
    else
    {
      return "Proceso número " + IdProceso.ToString() +
             " realizado correctamente";
    }
  }
}

Supongamos también que creamos un proyecto de SQL Server de nombre ServiciosWebClr. Lo primero que se nos ocurre es añadir una referencia web al servicio, a la que llamamos ServiciosWeb. Luego añadirmos un procedimiento almacenado llamado RealizarProceso con este código:

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using ServiciosWebClr.ServiciosWeb;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
  public static void RealizarProceso(SqlInt32 IdProceso)
  {
    using (ServicioWeb sw = new ServicioWeb())
    {
      string Resultado = sw.RealizarProceso(IdProceso.Value);
    }
  }
};

Para que un procedimiento almacenado CLR pueda llamar a un servicio web, el ensamblado en el que reside tiene que tener permiso de EXTERNAL ACCESS. Para ello vamos a las propiedades del proyecto, ficha database y seleccionamos external en el cuadro combinado permission level. Pero esto no es suficiente. Si intentamos desplegar (deploy) el proyecto recibiremos el siguiente error:

CREATE ASSEMBLY for assembly 'ServiciosWebClr' failed because assembly 'ServiciosWebClr' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. ServiciosWebClr

Para solucionarlo ejecutamos la siguiente sentencia en el Management Studio:

ALTER DATABASE <base de datos> SET TRUSTWORTHY ON

Con esto ya podremos desplegar el proyecto en SQL Server. Pero si ejecutamos este procedimiento almacenado en el Management Studio, nos encontramos con otro problema, recibimos el siguiente mensaje de error:

Msg 6522, Level 16, State 1, Procedure RealizarProceso, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'RealizarProceso':
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
etc etc.

Los servicios web serializan y deserializan los objetos en formato XML. Para realizar estas operaciones utilizan un XmlSerializer el cual genera y carga en tiempo de ejecución un ensamblado con clases especializadas en serializar y deserializar los tipos de nuestro ensamblado. Por razones de seguridad y fiabilidad, SQL Server no permite cargar ensamblados en tiempo de ejecución, por eso recibimos esta excepción.

La solución, como sugiere el mesaje de error, es utitlizar un serializador XML pregenerado. Entre las herramientas de línea de comandos, tenemos SGen.exe que genera un ensamblado para serialización XML. Pero es más sencillo ir a las propiedades del proyecto, ficha build y seleccionar on en el cuadro combinado generate serialization assembly. Con esto, se generará el ensamblado ServiciosWebClr.XmlSerializers.dll cada vez que generemos el proyecto. Pero este ensamblado no se instala automáticamente en SQL Server, tenemos que hacerlo nosotros manualmente. Para ello añadimos los scripts predeployscript.sql y postdeployscript.sql en la raíz del proyecto (no en la carpeta Test Scripts) con el siguiente contenido:

Predeployscript.sql:

IF EXISTS (SELECT [name]
FROM sys.assemblies
WHERE [name] = N'ServiciosWebClr.XmlSerializers')
DROP ASSEMBLY [ServiciosWebClr.XmlSerializers]
WITH NO DEPENDENTS

Postdeployscript.sql:

CREATE ASSEMBLY [ServiciosWebClr.XmlSerializers]
FROM '<path>\ServiciosWebClr.XmlSerializers.dll'

Con esto ya debería funcionar.

Otra cuestión es que la mayoría de las veces no resulta adecuado llamar al servicio web de forma síncrona, ya que normalmente la llamada tarda algunos segundos. Así que veamos qué pasa cuando llamamos a los servicios web de forma asíncrona.

Supongamos que creamos la siguiente tabla y procedimiento almacenado para registrar todas las llamadas al servicio web, incluyendo el resultado, información de tiempos y errores:

CREATE TABLE RegistroProcesos
(
      IdRegistro int IDENTITY(1,1) PRIMARY KEY,
      IdProceso int NOT NULL,
      Comienzo datetime NOT NULL,
      Fin datetime NOT NULL,
      Correcto bit NOT NULL DEFAULT 1,
      Resultado varchar(100),
      DescripcionError varchar(1000)
)
GO

CREATE PROCEDURE RegistrarProceso
      @IdProceso int,
      @Comienzo datetime,
      @Fin datetime,
      @Correcto bit,
      @Resultado varchar(100),
      @DescripcionError varchar(1000)
AS
      INSERT INTO RegistroProcesos(
            IdProceso, Comienzo, Fin,
            Correcto, Resultado, DescripcionError)
      VALUES (
            @IdProceso, @Comienzo, @Fin,
            @Correcto, @Resultado, @DescripcionError)

Nuestra clase StoredProcedures podría ser la siguiente:

public partial class StoredProcedures
{

private class Estado
  {
    public int IdProceso;
    public ServicioWeb ServicioWeb;
    public DateTime Comienzo;

    public Estado(
        int IdProceso,
        ServicioWeb ServicioWeb,
        DateTime Comienzo)
    {
      this.IdProceso = IdProceso;
      this.ServicioWeb = ServicioWeb;
      this.Comienzo = Comienzo;
    }
  }

  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void RealizarProceso(SqlInt32 IdProceso)
  {
    ServicioWeb sw = new ServicioWeb();
    Estado Estado = new Estado(IdProceso.Value, sw, DateTime.Now);
    sw.BeginRealizarProceso(
          IdProceso.Value, DelegadoProcesoRealizado, Estado);
  }

private static readonly AsyncCallback DelegadoProcesoRealizado =
       new AsyncCallback(ProcesoRealizado);

private static void ProcesoRealizado(IAsyncResult ar)
{
Estado Estado = (Estado)ar.AsyncState;
    try
    {
    String Resultado = Estado.ServicioWeb.EndRealizarProceso(ar);
      RegistrarProceso(Estado.IdProceso, Estado.Comienzo,
                       DateTime.Now, true, Resultado, null);
    }
    catch (Exception ex)
    {
      RegistrarProceso(Estado.IdProceso, Estado.Comienzo,
                       DateTime.Now, false, null, ex.Message);
    }
   }

   private const string ConnectionString =
     "Data Source=(local);Integrated Security=SSPI;" +
    "Initial Catalog=AdventureWorks";

   private static void RegistrarProceso(
     int IdProceso,
     DateTime Comienzo,
     DateTime Fin,
     bool Correcto,
     string Resultado,
     string DescripcionError)
   {
     using (SqlConnection cn = new SqlConnection(ConnectionString))
     using (SqlCommand cmd = new SqlCommand("RegistrarProceso", cn))
     {
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.Parameters.Add("@IdProceso", SqlDbType.Int).Value =
           IdProceso;
       cmd.Parameters.Add("@Comienzo", SqlDbType.DateTime).Value =
           Comienzo;
       cmd.Parameters.Add("@Fin", SqlDbType.DateTime).Value = Fin;
       cmd.Parameters.Add("@Correcto", SqlDbType.Bit).Value =
           Correcto;
       cmd.Parameters.Add("@Resultado",SqlDbType.VarChar, 100).Value =
           Resultado == null ? (object) DBNull.Value :
                        (object) Resultado;
       cmd.Parameters.Add("@DescripcionError",
                          SqlDbType.VarChar, 1000).Value =
       DescripcionError == null ? (object) DBNull.Value :
                           (object) DescripcionError;
       cn.Open();
       cmd.ExecuteNonQuery();
     }

}
}

Observa que la cadena de conexión no es "context connection=true", sino una cadena de conexión regular. Esto es así porque el método de devolución de llamada ProcesoRealizado se ejecuta en un hilo diferente que el procedimiento almacenado, este hilo no es un hilo de ejecución de SQL Server así que no se puede usar la conexión de contexto.

Si desplegamos ahora el proyecto y ejecutamos lo siguiente en el ManagementStudio:

TRUNCATE TABLE RegistroProcesos
DECLARE @IdProceso int
SET @IdProceso = 1
WHILE @IdProceso <= 10
BEGIN
EXEC RealizarProceso @IdProceso
SET @IdProceso = @IdProceso + 1
END

Y luego ejecutamos;

SELECT * FROM RegistroProcesos

Veremos que el procedimiento almacenado funciona como se espera.

Pues nada más, espero que esto os sirva de ayuda y os quite algún que otro quebradero de cabeza si es que alguna vez necesitáis llamar a un servicio web desde dentro de SQL Server 2005.

Un saludo:

Jesús López