Augusto Humire Martinez web blog

algo de web-in

Cambiar Collation a una columna

ALTER TABLE Operacion
ALTER COLUMN CodigoCliente NVARCHAR(50)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO

agosto 10, 2020 Posted by | Base de Datos | Deja un comentario

Objetos de un esquema

SELECT 1–*
,(SCHEMA_NAME(schema_id) + ‘.’ + name) AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type
,type_desc
,create_date
,modify_date
FROM sys.objects A
WHERE 1 = 1
AND modify_date > GETDATE() – 3000
AND type = ‘U’ –[U:User Table|P:Procedure]
AND SCHEMA_NAME(schema_id) = ‘RC’
AND NAME NOT LIKE ‘x_%’
ORDER BY A.modify_date;
GO

agosto 9, 2020 Posted by | Base de Datos | Deja un comentario

Encontrar una columna en SQL Server

SELECT sysobjects.name AS table_name, syscolumns.name AS column_name,
systypes.name AS datatype, syscolumns.LENGTH AS LENGTH
FROM sysobjects INNER JOIN
syscolumns ON sysobjects.id = syscolumns.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype
WHERE (sysobjects.xtype = ‘U’)
and (UPPER(syscolumns.name) like upper(‘%IdAgencia%’))
ORDER BY sysobjects.name, syscolumns.colid

diciembre 6, 2019 Posted by | Base de Datos | Deja un comentario

Filas actualizadas o afectadas en un Update Sql Server

Para recordar, como saber que filas se actualizaron en un update masivo, normalmente conocemos cuantos fueron afectados pero como identificar cuales fueron los registros actualizados?… cuales exactamente se actualizaron?, para ello utilizamos output (salida de los actualizados) y into (destino de los actualizados) dentro de un update

CREATE TABLE #tablaResultado
(
[Empresa] [varchar](50) NULL,
[Anio] [varchar](50) NULL,
[numeroDocumento] [varchar](50) NULL,
[Estado] [BIT] DEFAULT  0
)

UPDATE DB..Facturas
SET CodigoElectronico = CC.CodigoElectronico
,NombreEstado = CC.NombreEstado
,Mensaje = CC.Mensaje
OUTPUT ‘EMP’, CC.Anio, CC.numeroDocumento, 1
INTO #tablaResultado
FROM DB..Facturas FA WHERE FA.id IN (’11’,’12’,’13’,’14’)

OUTPUT Define los campos de los registros actualizados
INTO Define el destino donde se registrara los campos afectados

Como vemos el update que se esta ejecutando es uno masivo, y con esto ya sabemos que registros fueron los que se actualizaron para seguramente hacer algo con ellos despues.

El siguiente es otro ejemplo mas complejo, puesto que la idea es saber que registros fueron modificados segun una lista enviada. Explicando mas claramente… Si tuviera 1000 registros para actualizar, cuales de los mil se actualizaron, para ellos utilizaremos un campo estado que define cuales se actualizaron y cuales no.

CREATE TYPE [dbo].[Comprobante] AS TABLE(
[Empresa] [varchar](3) NULL,
[Anio] [varchar](4) NULL,
[numeroDocumento] [varchar](10) NULL,
[CodigoElectronico] [varchar](3) NULL,
[NombreEstado] [varchar](255) NULL,
[Mensaje] [varchar](255) NULL
[Estado] [BIT] = 0
)

CREATE PROCEDURE [usp_Actualiza_Estado_Comprobantes]
@comprobantesList as [Comprobante] READONLY
AS

CREATE TABLE #tablaResultado
(
[Empresa] [varchar](50) NULL,
[Anio] [varchar](50) NULL,
[numeroDocumento] [varchar](50) NULL,
[Estado] [BIT] DEFAULT  0
)

UPDATE DB..Facturas
SET CodigoElectronico = CC.CodigoElectronico
,NombreEstado = CC.NombreEstado
,Mensaje = CC.Mensaje
OUTPUT ‘301’, CC.Anio, CC.numeroDocumento, 1
INTO #tablaResultado
FROM DB..Facturas FA
INNER JOIN @comprobantesList CC ON (CC.numeroDocumento = FA.Documento AND CC.Anio = FA.Anio)

INSERT INTO #tablaResultado
SELECT Empresa, Anio, numeroDocumento, 0
FROM
(
SELECT Empresa, Anio, numeroDocumento
FROM @comprobantesList CC
EXCEPT
SELECT Empresa, Anio, numeroDocumento
FROM #tablaResultado TR
) tablaNoActualizados

SELECT * FROM #tablaResultado

DROP TABLE #tablaResultado

GO

DECLARE @comprobantesList as dbo.[Comprobante]
INSERT INTO @comprobantesList VALUES(‘301′,’2015′,’1300002790′,’0′,’bla bla’,’bla bla’)
INSERT INTO @comprobantesList VALUES(‘301′,’2015′,’1377002790′,’0′,’bla bla’,’bla bla’)
INSERT INTO @comprobantesList VALUES(‘301′,’2015′,’1377002890′,’0′,’bla bla’,’bla bla’)
exec [usp_Actualiza_Estado_Comprobantes] @comprobantesList

go

junio 5, 2015 Posted by | Base de Datos | 2 comentarios

Asignar un diferente filegroup a una base de datos. Filestream Filegroup Database

Escenario: Dos Negocios (logica de negocio) diferentes un solo servidor de base de datos y los archivos a almacenar por lo tanto son diferente y no tienen que ver el uno con el otro.

Requerimiento: Crear un repositorio de archivos con ubicación diferente al por defecto instalado en la base de datos para la segunda aplicacion.

Previo: Un Filegroup define entre otras cosas la ubicacion de nuestro del repositorio de archivos a almacenar o filestream, asi entonces podemos definir por cada base de datos creada una diferente ubicacion fisica para almacenar archivos, es decir, por cada base de datos su propio directorio de archivos o filestream.

Solución: Crear una base de datos que defina su propia ubicacion fisica de archivos , para lograrlo solo necesitamos especificar que file group usaremos y en que dirección se encuentra.

Codigo:

–<<Codigo a ejecutar>>

DECLARE @data_path nvarchar(256) = ‘K:\BSLDriveDB\’;
DECLARE @dbName nvarchar(256) = ‘bslDriveTest’;
DECLARE @query nvarchar(1000) =
— Execute the CREATE DATABASE statement.
‘CREATE DATABASE ‘ + @dbName
+ ‘ ON PRIMARY
(
NAME = ‘ + @dbName + ‘_data
,FILENAME = ”’ + @data_path + @dbName +’.mdf”
–,SIZE = 10MB
–,MAXSIZE = 50MB
–,FILEGROWTH = 15%
),
FILEGROUP FileStreamBSLDrive CONTAINS FILESTREAM DEFAULT
(
NAME = FSBSLDrive
,FILENAME = ”’ + @data_path + ‘Store”’
+ ‘)’
+ ‘LOG ON
(
NAME = ‘ + @dbName + ‘_log
,FILENAME = ”’ + @data_path + @dbName + ‘_log.ldf”
,SIZE = 1MB
,MAXSIZE = 5MB
,FILEGROWTH = 5MB
)’

–SELECT @query;
EXECUTE (@query);

–<<//Codigo a ejecutar>>

Este comando ejecutará el siguiente resultado

CREATE DATABASE bslDriveTest
ON PRIMARY
(
NAME = bslDriveTest_data
,FILENAME = ‘K:\BSLDriveDB\bslDriveTest.mdf’
–,SIZE = 10MB
–,MAXSIZE = 50MB
–,FILEGROWTH = 15%
)
,FILEGROUP FileStreamBSLDrive CONTAINS FILESTREAM DEFAULT
(
NAME = FSBSLDrive
,FILENAME = ‘K:\BSLDriveDB\Store’
)
LOG ON
(
NAME = bslDriveTest_log
,FILENAME = ‘K:\BSLDriveDB\bslDriveTest_log.ldf’
,SIZE = 1MB
,MAXSIZE = 5MB
,FILEGROWTH = 5MB
)

Y ya hemos creado una base de datos con su propio directorio de archivos o filegroup.

for Dummies:

Si desea insertar un archivo se sugiere que pueda leer el siguiente articulo

https://augustohumire.wordpress.com/2012/04/03/introduccion-a-sql-server-filestream/

febrero 24, 2013 Posted by | Base de Datos | | 1 comentario

Introducción a Sql Server FileStream

Esta es una introduccion a el uso de FILESTREAM en Sql Server que nos permite almacenar archivos de cualquier tipo en la base de datos. Una introcucción mas detallada la puede encontrar en este enlace.

Habilitar el FILESTREAM de la base de datos
Para utilizar FileStream en Sql Server se debe habilitar a traves del procedimiento llamado sp_configure segun las siguientes lineas

EXEC sp_configure filestream_access_level, 2
GO
RECONFIGURE
GO

Crear una Base de datos que use FILESTREAM
Una base de datos con FILESTREM deberia tener al meno los siguientes componentes:

Un Archivo MDF
Un Archivo LOG
Un Contenedor de datos FILESTREAM

Aqui el script para crear la base de datos:

CREATE DATABASE NorthPole
ON
PRIMARY (
      NAME = NorthPoleDB,
      FILENAME = ‘C:\Temp\NP\NorthPoleDB.mdf’
), FILEGROUP NorthPoleFS CONTAINS FILESTREAM(
      NAME = NorthPoleFS,
    FILENAME = ‘C:\Temp\NP\NorthPoleFS’)
LOG ON (                       
      NAME = NorthPoleLOG,
    FILENAME = ‘C:\Temp\NP\NorthPoleLOG.ldf’)
GO

Crear una tabla con una columna que use FILESTREAM

CREATE TABLE [dbo].[Items](
   [ItemID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
   [ItemNumber] VARCHAR(20),
   [ItemDescription] VARCHAR(50),
   [ItemImage] VARBINARY(MAX) FILESTREAM NULL
)

Es importante tener en cuenta que el identificador de cada registro debe de tener una columna UNIQUEIDENTIFIER con atributos ROWGUIDCOL y UNIQUE.

Insertar datos en el FILESTREAM
La inserción de datos se puede realizar de varias formas utilizando secuencias TSQL o utilizando comando especializados para el uso de FILESTREAM como BULK.
Para este proposito usamos OPENROWSET(BULK…) para cargar el contenido de un archivo desde el disco duro.

— Declare a variable to store the image data
DECLARE @img AS VARBINARY(MAX)
— Load the image data
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
      FROM OPENROWSET(
            BULK
            ‘C:\temp\MicrosoftMouse.jpg’,
            SINGLE_BLOB ) AS x

— Insert the data to the table         
INSERT INTO Items (ItemID, ItemNumber, ItemDescription, ItemImage)
SELECT NEWID(), ‘MS1001′,’Microsoft Mouse’, @img

Accesar a datos FILESTREAM con API

El acceso a la base de datos se puede realizar utilizando secuencias Transact SQL o API.
El siguiente ejemplo utiliza el API para obtener datos del Filestream

SELECT
      ItemNumber,
      ItemImage.PathName() AS FilePath
FROM Items

abril 3, 2012 Posted by | Base de Datos | 2 comentarios

asociar usuario a base de datos

Cuando se requiere migrar una base de datos y asociar un usuario de sql server ya sea 2005 o 2008 ud. verá un mensaje como el siguiente si no hace el registro correspondiente.

mensaje de error:
User group or role exists in the current database del español
usuario grupo o role ya existe en la base de datos actual

Esto sucede cuando migramos la base de datos a otra instancia de sql server pero en la base de datos migrada ya tenemos configurada usuario que pueden accesar a esta, y cuando creamos los login en el nuevo server y los tratamos de mapear obtenemos un mensaje como este…

para poder asociar el suario creado con las bases de datos migradas debe de ejecutarse el siguiente comando

use miBDMigrada
go
sp_change_users_login 'auto_fix', 'miUsuarioEnlaDBMigrada'

El resultado de la ejecucion de ese comando sebiera ser algo parecido a este

The row for user ‘miUsuario’ will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.

Esto asociará los usuarios establecidos de la base de datos migrada a los logins creados en el nuevo server.

y listo con eso deberias tener tus usuarios nuevamente funcionando.

agosto 11, 2011 Posted by | Base de Datos | 6 comentarios

Trim de cualquier caracter en SQL Server

Cualquier momento, cualquier lugar y cualquier circunstancia, siempre se aprende algo.

El uso de la funcion nativa de LTRIM (Left) o RTRIM (Right)  ha sido muy usada hasta ahora, y como sabras la funcion devuelve una cadena que contiene una copia de una cadena dada sin espacios iniciales (LTrim), sin espacios finales (RTrim) o sin espacios iniciales ni finales (Trim).

RTRIM(‘abcde      ‘)
retorna:   ‘abcde’

o

LTRIM(‘abcde      ‘)
retorna:   ‘abcde’

Pero como eliminarias un caracter determinado que se encuentra a la derecha o a la izquierda en SQL server?,  las soluciones podrian ser diversas sin embargo aqui les traigo lo que he usado.

…sencillo, crearemos una funcion propia que lo hace.

La siguiente funcion retira cualquier tipo de caracter de la izquierda, claro que se tiene que especificar cual es el caracter que se tiene que limpiar.

CREATE FUNCTION [dbo].[fn_CharLTrim]
(
@char CHAR(1)
, @cadena nvarchar(255)
)
RETURNS nvarchar(20)
AS
BEGIN
DECLARE @posicion INTEGER
SELECT @posicion = PATINDEX(‘[‘ + @char + ‘]%’, @cadena)
WHILE @posicion > 0
BEGIN
SELECT @cadena = STUFF(@cadena, @posicion, 1, ”)
SELECT @posicion = PATINDEX(‘[‘ + @char + ‘]%’, @cadena)
END
RETURN @cadena
END

Ejemplo

SELECT [dbo].fn_fn_CharLTrim(‘0′,’0000210215-152’)

nos devuelve

210215-152

Entonces hemos retirado un caracter de la izquierda, puede ser cualquiera que se necesite

La siguiente funcion retira cualquier tipo de caracter de la derecha, de la misma forma se tiene que especificar cual es el caracter que se tiene que limpiar.

Entonces para balancear el tema haremos el Trim pero de la derecha

CREATE FUNCTION [dbo].[fn_CharRTrim]
(
@char CHAR(1)
, @cadena nvarchar(255)
)
RETURNS nvarchar(20)
AS
BEGIN
DECLARE @posicion INTEGER
SELECT @posicion = PATINDEX(‘%[‘ + @char + ‘]’, @cadena)
WHILE @posicion > 0
BEGIN
SELECT @cadena = STUFF(@cadena, @posicion, 1, ”)
SELECT @posicion = PATINDEX(‘%[‘ + @char + ‘]’, @cadena)
END
RETURN @cadena
END

Ejemplo

SELECT [dbo].fn_CharRTrim(‘x’,’2541-2010xxxxxxxxxxx’)

nos devuelve

2541-2010

Y ahora biene lo mejor, tambien la pregunta era y como se puede quitar caracteres extraños de una cadena dejando solamente numeros y letras, quitando cualquier tipo de caracter que no sea letra o numero?, pues la solucion es similar, pero la pregutna podria ser y cuando necesitare retirar caracteres dentro? pues en mi caso toco almacenar numeros telefonicos y como sabras el almancenamiento de los telefonos es variado, y esta sujeto a que el usuario coloque el formato que el usuario establesca como parametro, mejor veamos primero la funcion y luego el ejemplo

ALTER FUNCTION [dbo].[fn_TrimInto]
(
@cadena nvarchar(255)
)
RETURNS nvarchar(20)
AS
BEGIN
DECLARE @posicion INTEGER
SELECT @posicion = PATINDEX(‘%[^a-zA-Z0-9_]%’, @cadena)
WHILE @posicion > 0
BEGIN
SELECT @cadena = STUFF(@cadena, @posicion, 1, ”)
SELECT @posicion = PATINDEX(‘%[^a-zA-Z0-9_]%’, @cadena)
END
RETURN @cadena
END

Ejemplo

SELECT [dbo].fn_Commit_TrimInto(‘+51-(054)-215739’)

nos devuelve

51054215739

Entonces como vez, hemos retirado cualquier caracter extraño de una cadena, usando expresiones regulares, el uso de expresiones regulares es una gran herramienta, dejando tan solo los caracteres que nos interesan.

En resumen la extension de TRIM puede ser muy diversa, espero les sirva esto asi como a mi me sirve cada solucion que encuentro.

Este post no pretende ser un manual sobre el uso de las funciones intrinsecas de SQL Server sino solo de extender la funcion TRIM dependiendo de cada necesidad.

Me gustaria hacerle un TRIM a los que contaminan el planeta para poder desaparecerlos.

Hagamoslo por los sueños de nuestras nuevas generaciones.

noviembre 23, 2010 Posted by | Base de Datos | 3 comentarios

Orden de columnas

ahora que ya a 2 años del lanzamiento de sql server 2008, he visto que muchos se preguntan como es que se puede cambiar el orden de las columnas de una tabla ya creada,  bien, se dice que las imagenes hablan mas que muchas palabras.

Sql server 2008

para que mas explicacion?

septiembre 6, 2010 Posted by | Base de Datos | Deja un comentario

CLR en Sql Server

Esta vez comentaré acerca de mi experiencia con respecto de como trabajar con los ensablados en SQL Server  integrando una CLR que realiza llamadas a una base de datos Oracle

Muchas veces hemos tenido que lidiar con integracion no solo entre aplicaciones si no entre bases de datos, esta vez comentaremos una integración entre bases de datos.

Escenario:
Se requiere dibujar un reporte (generado desde Sql Reporting Services) cuyos datos se encuentran en SQL Server y Oracle, adicionalmente en el gestor de Sql Server existe un link (linked server) entre Sql Server y Oracle.

Problema:
La lentitud y la complejidad de las consultas hacen que el link que existe entre Sql Server y Oracle no sea veloz (es decir es lento), (la razon se desconoce, habra que preguntarle al equipo de SqlServer el porque).

Planteamiento de la Solucion:
– Los datos que esten en Oracle que lo procese y los devuelva su propio gestor
– Los datos que esten en Sql Servecr lo procese y los devuelva su propio gestor (no se usara linked server)
– La interfaz de comunicacion entre los dos gestores sera un ensamblado CLR sobre Sql Server y este realizará llamadas de datos a Oracle.

Solucion:

– Crearemos un ensamblado .NET en Visual Studio que realize las llamadas de data a Oracle y se las entregue a SqlServer
– Configuraremos Sql para que permita trabajar con ensamblados que realizan llamadas fuera del entorno de Sql Server
– Instaleremos el ensamblado (instalar es un decir)
– Crearemos los objetos que hacen la llamada al ensamblado

Manos a la obra

1.- CREANDO EL ENSAMBLADO

using System.Data.OracleClient;
namespace Inspeccion
{

public class Interprete
{
//[Microsoft.SqlServer.Server.SqlProcedure]
[Microsoft.SqlServer.Server.SqlFunction()]
public static string GetCodigosCliente(SqlString nombre)
{
string result = “”;
using (OracleConnection cn = new OracleConnection(“Data Source=ORATEST.BD;Persist Security Info=True;User ID=MIUSER;PASSWORD=MIPASSWORD;Unicode=True”))
{
cn.Open();
using (OracleCommand cmd = new OracleCommand(“package.miprocedure”, cn))
{
cmd.CommandType = CommandType
.StoredProcedure;
string nombreParametro = nombre.ToString();
cmd.Parameter.Add(
new OracleParameter(“SPARAM”, OracleType.VarChar, 140)).Value = nombreParametro;
cmd.Parameter.Add(
new OracleParameter(“RC1”, OracleType.Cursor)).Direction = ParameterDirection.Output;
using (OracleDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
result +=
string.Format(“‘{0}’,”, reader[1].ToString());
}
}
}
return result.Trim(‘,’);
//SqlContext.Pipe.Send(mensaje);
}

Atencion al uso de SqlOracleClient, se uso este database provider puesto que viene nativo del Framework, si desea usar otra libreria primero deberia registrarla en sql server.

SqlString, parametro de la function es porque el tipo debe coindicir con el de la base de datos

Atención a la clase, puesto que se ha definido como static

Fijese que es importante definir desde aqui que funcionalidad de SqlServer usará en ensamblado, si un procedimiento almacenado o una funcion usando Microsoft.Sqlserver.server.SqlProcedure o Microsoft.SqlServer.Server.SqlFunction
Como vemos el resultado que necesito obtener es tan solo un escalar del tipo string.

2.- CONFIGURANDO SQL SERVER PARA QUE ACEPTE ENSAMBLADOS

Configurar Sql Server (ingrese a Management) y digite lo siguiente

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘clr enabled’, 1;
GO
RECONFIGURE;
GO

3.- INSTALANDO EL ENSAMBLADO

Compile su libreria (*.dll) y peguela en C:\

/*
USE master
GO
CREATE ASYMMETRIC KEY AsymmetricKeyName FROM EXECUTABLE FILE = ‘{put your .dll file path here}’
CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY AsymmetricKeyName
GRANT EXTERNAL ACCESS ASSEMBLY TO CLRLogin
GO
*/

ALTER DATABASE MiBaseDeDatos SET TRUSTWORTHY ON

–CREATE ASSEMBLY ass_InterpreteOracle from ‘c:\inspeccion.dll’ WITH PERMISSION_SET = SAFE
–CREATE ASSEMBLY ass_InterpreteOracle from ‘c:\inspeccion.dll’ WITH PERMISSION_SET = EXTERNAL_ACCESS
CREATE ASSEMBLY ass_InterpreteOracle from ‘c:\inspeccion.dll’ WITH PERMISSION_SET = UNSAFE

Donde:

– TRUSTWORTHY: Indica al gestor que esta es una base de datos de confianza
– UNSAFE: Indica que le permita hacer las llamadas al exterior del entorno de SQL Server (recuerde que haremos llamadas a Oracle)
– ass_InterpreteOracle: es el nombre que tendra el ensamblado
– ‘c:\inspeccion.dll’ : es el nombre del ensamblado generado en el punto anterior

– En caso de no querer utilizar TRUSTWORTHY y UNSAFE investigue acerca de ASIMETRIC KEY

4.- CREANDO LOS OBJETOS QUE REALIZAN LAS LLAMADAS AL ENSAMBLADO

A) Para usarlo desde una funcion, crearemos lo siguiente

CREATE FUNCTION ufn_GetCodigosCliente(@nombre nvarchar(max))
RETURNS nvarchar(max)
AS
EXTERNAL NAME ass_InterpreteOracle.[Inspeccion.Interprete].GetCodigosCliente;
GO

y para llamar al procedimiento recuerde que la sentencia es “select dbo.ufn_GetCodigosCliente(‘RODRIGUEZ’)”

B) Para usarlo desde un procedimiento, crearemos lo siguiente

CREATE PROCEDURE usp_Miprocedimiento
AS
EXTERNAL NAME ass_InterpreteOracle.[Inspeccion.Interprete].GetCodigosCliente;

y para llamar al procedimiento recuerde que la sentencia es “exec usp_Miprocedimiento”

enero 23, 2010 Posted by | Base de Datos | 1 comentario