Mes: junio 2007

En este apartado vamos a ver una de las novedades más significativas de SQL Server a partir de 2005 que es la integración con el Common Language Runtime (CLR) de .NET.

Como ejemplo, vamos a crear un procedimiento almacenado muy sencillo y de paso a configurar nuestro SGBD para ello.

Primeramente creamos un Proyecto de Base de Datos SQL Server. Eso lo hacemos partiendo del lenguaje con el que queramos programar (por ejemplo, C#) y de ahí Database (Base de datos), Proyecto de SQL Server. A partir de este momento nos va a aparecer el cuadro de diálogo típico de conexión a un servidor SQL Server donde además elegiremos la base de datos a la que conectarnos. Normalmente nos aparecerá un aviso preguntándonos si deseamos habilitar la depuración SQL/CLR, le vamos a decir que sí.

Una vez creada la solución, vamos a añadir un Procedimiento Almacenado (en Agregar Nuevo Elemento). Le vamos a llamar (¡como no!) HolaMundo.cs. Nos aparecerá una plantilla de código similar a esta:

[Microsoft.SqlServer.Server.SqlProcedure]

public static void HolaMundo()

{

// Put your code here

}

Como podemos observar, tenemos un atributo llamado SqlProcedure al cual le vamos a añadir un atributo llamado Name con el nombre de nuestro procedimiento almacenado (HolaMundo). Este nombre será el que realmente usemos desde T-SQL y puede ser diferente a la función. Nos quedará algo como esto:

[Microsoft.SqlServer.Server.SqlProcedure(Name=“HolaMundo”)]

En el cuerpo de la función vamos a introducir la siguiente línea de código:

SqlContext.Pipe.Send(“Hola Mundo”);

SqlContext nos permite tener comunicación con SQL Server, en este caso, mediante un pipe. Esta línea nos permite enviar entre otras cosas, un mensaje al servidor, pero podemos enviar entre otras cosas, un DataReader.

A continuación compilamos con Generar Solución y después hacemos lo que sería la implementación en SQL Server (Implementar Solución). Una vez hecho esto, en nuestro IDE en el explorador de servidores en el item Ensamblados, podemos ver el generado por nuestra solución. Por otra parte, si abrimos Management Studio de SQL Server en el mismo apartado Ensamblados dentro de Programación, aparece también, y en el de Procedimientos Almacenados se muestra con el nombre que previamente le dimos en el atributo Name.

Si ahora intentamos ejecutar este procedimiento con EXEC HolaMundo, el Management Studio nos va a lanzar el error siguiente:

La ejecución de código de usuario en .NET Framework está deshabilitada. Habilite la opción de configuración “clr enabled”.

Esto se debe a que la integración CLR no está habilitada por defecto en SQL Sercer (por razones de seguridad). Para habilitarla no tenemos más que entrar en la aplicación de Configuración de Superficie de SQL Server y en Configuración de superficie para características. Dentro de este apartado en el árbol Motor de base de datos-> Integración CLR marcamos Habilitar integración CLR. Si ahora ejecutamos el procedimiento almacenado, funcionará perfectamente, aunque inicialmente tardará un poco debido a que se está compilando.

La verdadera novedad a nivel funcional de todo esto es que podemos llamar a clases externas .NET o webservices por ejemplo desde este tipo de procedimientos.

Ahora vamos a probar creando un archivo, para lo cual procedemos a importar el namespace System.IO. A partir de aquí, nuestro procedimiento ahora va a necesitar un argumento de entrada que consiste en el texto que vamos a grabar dentro del fichero. Veamos el código del ejemplo:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.IO;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure(Name=“HolaMundo”)]

public static void HolaMundo(SqlString valor)

{

using (StreamWriter escritor = File.CreateText(@”c:\test\holamundo.txt”))

{

escritor.Write(valor.ToString());

}

SqlContext.Pipe.Send(valor.ToString());

}

};

Como puede comprobarse, ahora definimos el procedimiento de forma que espere un SqlString valor y con un StreamWriter lo metemos en el archivo test en el directorio C:\

Volvemos a compilar e implementar y a ejecutar desde SQL Server 2005. Ahora, como podemos comprobar, nos vuelve a dar un error:

Mens 6522, Nivel 16, Estado 1, Procedimiento HolaMundo, Línea 0

Error de .NET Framework durante la ejecución de la rutina o agregado definido por el usuario “HolaMundo”:

System.Security.SecurityException: Request for the permission of type ‘System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.CodeAccessPermission.Demand()

at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)

at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)

at System.IO.StreamWriter.CreateFile(String path, Boolean append)

at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)

at System.IO.StreamWriter..ctor(String path, Boolean append)

at System.IO.File.CreateText(String path)

at StoredProcedures.HolaMundo(SqlString valor)

La explicación es que esta rutina intenta acceder a procedimientos externos a SQL Server y no tiene permiso para ello, a diferencia del procedimiento anterior que únicamente mostraba un texto sin necesidad de recurrir a, como en este caso, operaciones de escritura sobre un fichero.

Para solucionar esto, lo primero que hemos de hacer en nuestras propiedades de proyecto es, en el apartado Base de datos, ajustar el nivel de permisos a External (por defecto está en Safe), aunque con esto sólo no basta. También hemos de ajustar un parámetro de configuración de nuestra base de datos:

alter database MiBD

set trustworthy on

Volvemos a implementar nuestra solución y ejecutamos-. Ahora si vamos a la carpeta test en C:\ veremos como se ha creado el archivo con el texto que le dimos. De esta forma hemos accedido a un namespace de .NET desde SQL Server lo que demuestra que podemos interactuar de forma fácil con el CLR .NET.

* Transcrito de un screencast creado por Misael Monterroca.

Un servidor vinculado consiste en un enlace a una fuente de datos externa (remota). La fuente de datos remota puede ser Oracle, Access, Excel o cualquier fuente de datos a la que se pueda acceder mediante OLE DB (por ejemplo, Index Server).

Servidores Remotos

• No usan proveedores OLE DB
• Una configuración de servidor remoto permite a un cliente conectado a una instancia SQL Server ejecutar un procedimiento almacenado en otra instancia de SQL Server sin establecer otra conexión.
• Tanto procedimientos almacenados como consultas distribuidas están permitidas contra servidores vinculados, sin embargo, sólo se permiten procedimientos almacenados contra servidores remotos.
• Conviene usar Servidores Vinculados en vez de Servidores Remotos. Estos últimos existen para mantener la compatibilidad hacia atrás.

Nota: Los servidores enlazados pueden ejecutar procedimientos almacenados remotos de SQL Server mientras que no pueden ejecutar procedimientos almacenados de Oracle.

Añadiendo Servidores Vinculados

Sintaxis

sp_addlinkedserver [ @server= ] ‘server‘ [ , [ @srvproduct= ] ‘product_name‘ ]

[ , [ @provider= ] ‘provider_name‘ ]

[ , [ @datasrc= ] ‘data_source‘ ]

[ , [ @location= ] ‘location‘ ]

[ , [ @provstr= ] ‘provider_string‘ ]

[ , [ @catalog= ] ‘catalog‘ ]

En este ejemplo vamos a ver como crear una conexión a Oracle como servidor enlazado a SQL Server. Crearemos un servidor vinculado llamado OraLinkServ el cual usa el proveedor Microsoft OLE DB y asume que el alias SQL*Net para la base de datos es OraProduction.

USE masterGOEXEC sp_addlinkedserver @server = ‘OraLinkServ’, @srvproduct = ‘Oracle’, @provider = ‘MSDAORA’, @datasrc = ‘OraProduction’

GO

Añadiendo datos de login a un Servidor Vinculado

sp_addlinkedsvrlogin

Crea o actualiza un mapeado entre logins de la instancia local de SQL Server y logins remotos en el Servidor Vinculado.

Sintaxis

sp_addlinkedsrvlogin [ @rmtsrvname = ] ‘rmtsrvname‘

[ , [ @useself = ] ‘useself‘ ]

[ , [ @locallogin = ] ‘locallogin‘ ]

[ , [ @rmtuser = ] ‘rmtuser‘ ]

[ , [ @rmtpassword = ] ‘rmtpassword‘

Consultando Servidores Vinculados

A continuación podemos ver varios métodos de ejecutar una consulta sobre diferentes servidores vinculados:

NombreServidor.NombreBD.Propietario.NombreObjeto

Select * from LinkSqlServ.Northwind.dbo.Employees

Select * from OraLinkServ..SCOTT.Stores

Select * from LinkMdb…customers

OpenQuery: Ejecuta una consulta de paso a través en el servidor vinculado proporcionado el cual debe ser una fuente de datos OLE DB (por ejemplo, para su uso con Index Server).

Sintaxis:

OpenQuery (servidor_vinculado, ‘consulta’)

Ejemplos:

SELECT * FROM OPENQUERY(OraLinkServ, ‘SELECT Name, Title FROM Scott.Titles’)

INSERT OPENQUERY(LinkSqlServ, ‘select * from pubs.dbo.jobs’) values (15, ‘Technical Editor’, 100, 300)

Ejemplo de consulta sobre Index Server:

SELECT Path, Filename FROM OpenQuery(IDX, ‘Select Path, Directory, FileName, Size FROM SCOPE() ORDER BY Rank, Size DESC’)

OpenRowSet: Este método consiste en una forma ad-hoc de conectarse y accceder a datos remotos usando OLE DB. Crea un servidor vinculado al vuelo.

Sintaxis

OPENROWSET ( ‘provider_name‘
, { ‘datasource‘ ; ‘user_id‘ ; ‘password‘
| ‘provider_string‘ }
, { [ catalog. ] [ schema. ] object
| ‘query‘ }
) 

Eliminando Servidores Vinculados y sus Logins

sp_dropserver : Elimina un servidor de la lista de servidores vinculados y remotos en el SQL Server local.

sp_droplinkedsvrlogin: Elimina un mapeado existente entre un login en el servidor local con SQL Server y un login en el servidor vinculado.

Obteniendo Metadatos

sp_tables_ex: Devuelve una tabla con información acerca de las tablas del servidor vinculado especificado.

sp_columns_ex: Devuelve información de columna, una fila por columna, para la/s tabla/s dada/s de un servidor vinculado. Sólo devuelve información de una sola columna siempre que haya sido especificada.

sp_table_privileges_ex: Devuelve información de privilegios acerca de la tabla especificada del servidor vinculado.

sp_column_privileges_ex: Deuelve privilegios de columna para la tabla especificada del servidor vinculado.

sp_linkedservers: Devuelve la lista de servidores vinculados en el servidor local.

sp_helpserver: Muestra información sobre un servidor remoto o de réplica, o sobre todos los servidores de ambos tipos.  Proporciona el nombre del servidor, el nombre de red del servidor, el estado de réplica del servidor, el número de identificación del servidor, nombre de la intercalación y valores de time-out, tanto de conexión como de consultas.

sysservers: Contiene una fila para cada servidor al que SQL Server puede acceder como fuente de datos OLEDB. Esta tabla es almacenada en la base de datos master.

sysoledbusers: Contiene una fila por cada mapeado de usuario y contraseña para el servidor vinculado especificado. Esta tabla está contenida en la base de datos master.

xp_enum_oledb_providers: Proporciona información sobre proveedores OLEDB

sp_serveroption: Ajusta opciones en servidores remotos y vinculados.

sp_serveroption se ha ampliado con dos nuevas funcionalidades, use remote collation y collation name, que permiten soporte para intercalación en servidores vinculados.

sp_setnetname: Ajusta nombres de red en sysservers para los nombres actuales de equipos de red y nombres de instancias remotas de SQL Server. Este procedimiento puede ser usado para permitir ejecución de llamadas a procedimientos almacenados remotos para ordenadores que tienene nombres de red que contienen identificadores SQL Server no válidos.

* Traducción propia de «An Introduction to Linked Servers» por Krishna Sonti publicado en www.sqlservercentral.com

http://www.sqlservercentral.com/columnists/ksonti/anintroductiontolinkedservers.asp

Una de las utilizaciones más comunes de genéricos se basa en la implementación y manipulación de colecciones fuertemente tipadas. En este apartado veremos muestras de cómo trabajar con los tipos de colecciones genéricas disponibles a partir de la versión 2.0 de NET Framework: la clase Dictionary<>, la clase List<>, la clase Queue<> y la clase Stack<>.

Clase Dictionary

Esta clase está diseñada para almacenar valores asociados a un valor de búsqueda. Tradicionalmente se ha usado esta clase para almacenar valores con cadenas, tales como nombres, palabras clave o GUIDs, pero además ahora podemos usar un objeto de cualquier tipo como clave y otro objeto de cualquier tipo como valor.

Ahora podemos especificar la clave y el tipo del valor al mismo tiempo de la creación del objeto usando parámetros de tipo, como se muestra en el siguiente fragmento de código:

// Diccionario de Prueba

Dictionary<string, int> diasMes = new Dictionary<string, int>();

diasMes[“Enero”] = 31;

diasMes[“Febrero”] = 28;

diasMes[“Marzo”] = 31;

Console.WriteLine(“Marzo tiene “ + diasMes[“Marzo”].ToString() + ” días.”);

Este ejemplo crea y manipula una clase Dictionary<> en la que todas las claves son cadenas, y todos los valores son números, almacenando el número de dias en cada mes.

Clase List

La clase List<> se explica por si misma fácilmente. Su único propósito es almacenar una lista de items. Sin genéricos, las listas eran responsabilidad de un conjunto de tipos System.Object y cuando el desarrollador necesitaba un tipo de datos fuera de ella, debía incluir operaciones de conversión de tipos y código potencialmente enrevesado. En el siguiente ejemplo vemos como crear y usar arbitrariamente listas de datos fuertemente tipados nunca había sido tan fácil:

// Clase List

List<Cliente> listaClientes = new List<Cliente>();

Cliente nuevoCli = new Cliente();

nuevoCli.Apellido = “Hoffman”;

nuevoCli.Nombre = “Kevin”;

nuevoCli.IDCliente = 1;

listaClientes.Add(nuevoCli);

Console.WriteLine(string.Format(“Hay {0} clientes, el primero es {1} {2}”, listaClientes.Count, listaClientes[0].Nombre, listaClientes[0].Apellido));

El código precedente produce la siguiente salida:

Hay un clientes, el primero es Kevin Hoffman

Conviene fijarse en el hecho de que ahora podemos escribir código como el siguiente sin tener que escribir ninguna de nuestras propias clases para ello:

listaClientes[0].Nombre

Antes, para obtener una lista como la mostrada en este ejemplo, los desarrolladores solían pasar muchas horas creando sus propias clases de colección fuertemente tipadas. Ahora podemos crearlas en tiempo de ejecución sin pérdida de rendimiento y sin necesidad de hacer conversión de tipos de forma que el código es más sencillo y reutilizable.

Clase Queue

La clase Queue no es más que una cola FIFO (First In First Out). Esta clase nos permite especificar el tipo de datos de los elementos en una cola al momento de la instanciación, como se muestra en el siguiente ejemplo:

Queue<Cliente> cClientes = new Queue<Cliente>();

cClientes.Enqueue(nuevoCli);

Cliente dqCli = cClientes.Dequeue();

Console.WriteLine(string.Format(“Extraido de la cola el siguiente cliente: {0} {1}”,dqCli.Nombre, dqCli.Apellido));

Clase Stack

Al contrario que la clase Queue<>, la clase Stack<> es una colección LIFO (Last-in First-out), es decir, una pila.  El siguiente código muestra como colocar elementos en una pila genérica y como obtenerlos. Para asegurarnos de saber cómo trabaja la pila, podemos jugar con el cambio de orden en el que colocamos los items para ver los resultados:

Stack<Cliente> pilaClientes = new Stack<Cliente>();

Cliente cli1 = new Cliente();

cli1.Nombre = “John”;

cli1.Apellido = “Doe”;

cli1.IDCliente = 99;

pilaClientes.Push(nuevoCli);

pilaClientes.Push(cli1);

Cliente popCli = pilaClientes.Pop();

Console.WriteLine(string.Format(“El Cliente extraido de la pila es {0} {1}”,popCli.Nombre, popCli.Apellido));

 

* Basado en traducción propia de Visual C# 2005 Unleashed de Kevin Hoffman