CELDA A VARIAS FILAS

A continuación se presenta en código una forma de pasar "bloques" de una sola cadena separada por comas (u otro caracter) a un formato de filas(a una tabla).

Una imagen podría ayudar a entender mejor lo que se quiere hacer.


Una cadena de Instrumentos:

Una cadena a varias filas

para pasar una cadena a varias filas como primer paso la cadena debe ser convertida a un XML, Hagamos un ejemplo con una cadena en una variable...

DECLARE @STR_INSTRUMENTOS  VARCHAR (500)

DECLARE @XML_INSTRUMENTOS  XML

la primer variable tendrá el texto con información separada por comas
la segunda variable es un tipo de dato XML donde se vaciará el texto ya separado

SET @STR_INSTRUMENTOS   = 'IPA BONOS IM, IPA BONOS IP, IPA BONOS IQ, IPA BONOS IS, IPA BONOS IT';

 -- PARA COVERTIR A XML

SET @XML_INSTRUMENTOS   = CAST(('<Instrumento>' + replace(@STR_INSTRUMENTOS,',','</Instrumento><Instrumento>')+'</Instrumento>') AS XML) 

Al revisar la variable, vemos que ya contiene la separación en un formato XML

SELECT @XML_INSTRUMENTOS

Dentro del visor del XML se puede ver el formato del XML





Ahora lo único que se debe hacer es convertir el XML en Table,
es muy fácil, si quieres más detalle aquí te dejo un link  XML to TABLE 

SELECT A.value ('text()[1]','varchar(30)') as Instrumentos

FROM @XML_INSTRUMENTOS.nodes ('//Instrumento') as T(A)

EJEMPLO COMPLETO:

DECLARE @STR_INSTRUMENTOS  VARCHAR (500)

DECLARE @XML_INSTRUMENTOS  XML

  

SET @STR_INSTRUMENTOS   = 'IPA BONOS IM, IPA BONOS IP, IPA BONOS IQ, IPA BONOS IS, IPA BONOS IT';

-- PARA COVERTIR A XML

SET @XML_INSTRUMENTOS   = CAST(('<Instrumento>'+replace(@STR_INSTRUMENTOS,',','</Instrumento><Instrumento>')+'</Instrumento>') AS XML) 

SELECT A.value ('.','varchar(30)') as Instrumentos

FROM @XML_INSTRUMENTOS.nodes ('Instrumento') as T1(A)

EJEMPLO 2

En éste ejemplo se van a separar en filas las columnas:
- ASOCIATE_INSTRUMENT
de la tabla que se muestra a continuación

Donde podemos ver que ya se tiene el XML a un lado de esa columa

ESTA ES LA CONSULTA DE LA TABLA SUPERIOR

SELECT [CLIENT_ID]   

      ,[ASOCIATE_INSTRUMENT]

      ,INSTRUMENTS = cast(('<IdItem>' + replace([ASOCIATE_INSTRUMENT],',','</IdItem><IdItem>') + '</IdItem>') AS XML)

      ,[INITIAL_AMOUNT_A]

      ,[FINAL_AMOUNT_A]

      ,[RATE_A]     

      ,[TOTAL_FEE]

  INTO #TEMP

  FROM [Avispa].[Avispa].[TARIFAS_COMISION_ACUMULADA]

LA CONSULTA SE METE EN UNA TABLA TEMPORAL, PARA POSTERIORMENTE EXTRAER UNA CONSULTA..-

  

El query anterior ya nos entrega los registros

SELECT  T2.Instruments,T1.*

FROM #TEMP          AS T1

CROSS APPLY (

                     SELECT A.value ('text()[1]','varchar(30)')                 as Instruments

                     FROM   T1.INSTRUMENTS.nodes ('IdItem')        as T(A)

                     ) AS T2

TDE - ENCRIPTAR BASE DE DATOS

ENCRIPTAR BASE DE DATOS CON TDE

To use TDE to encrypt a database, you must perform the following steps:

  MASTER
    1.- Create the master encryption key.

    2.- Create the Server Certificate protected by the master key.

    3.- Backup the Server Certificate

  USER DATABASE

    4.- Create Database Encryption Key

    5.- Encrypt the database.

--     1:     CREATE THE MASTER ENCRYPTION KEY

USE MASTER;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword'

GO

-- 2: CREATE THE SERVER  CERTIFICATE PROTECTED BY THE MASTER KEY

CREATE CERTIFICATE MyMasterServerCertificate WITH SUBJECT = 'Master Server Certificate'

GO

--  3:  BACKUP MASTER SERVER CERTIFICATE

--Warning:

--The certificate used for encrypting the databases encryption key has

--not been backed up. You should immediately back up the server certificate and the private

--key associated with the certificate. If the certificate ever becomes unavailable or

--if you must restore or attach the database on another server,

--you must have backups of both the certificate and the private key or you will

--not be able to open the database anymore.

 BACKUP CERTIFICATE MyMasterServerCertificate

 TO FILE = 'C:\BASURA\MyMasterServerCertificate.cer'

 WITH PRIVATE KEY (

                    FILE = 'C:\BASURA\MyMasterServerCertificate.pvk', 

                    ENCRYPTION BY PASSWORD = 'PrivateKeyPassword'

                   );

 GO

--  4: CREATE DEK (DATABASE ENCRYPTION KEY) AND PROTECT IT BY USING THE MASTER SERVER CERTIFICATE, THIS CERTIFICATE MUST BE CREATED IN THE USER DATABASE

USE MyDataBase;

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE MyMasterServerCertificate

GO

-- 5:  ENCRYPT THE DATABASE

ALTER DATABASE MyDataBase SET ENCRYPTION ON;

GO

 --                          ELIMINAR POR COMPLETO TDE 

 --                                            use MyDataBase;

-- 1.- QUITAR LA ENCRIPTACIÓN

ALTER DATABASE MyDataBase SET ENCRYPTION OFF

-- 2.-

DROP DATABASE ENCRYPTION KEY

 -- 3.- (ONLY IF YOU HAVE ONE)

DROP SYMMETRIC KEY PasswordTableKey;   -- llave simétrica que pertenece al certificado

-- Para borrar el certificado de base de datos de usuario borre no deben existir llaves simétricas

-- 4.-

DROP CERTIFICATE [Remate2G_Data_Certificate];   -- certificado de la base de datos

-- 5.- (ONLY IF YOU HAVE ONE)

-- para borrar la MASTER KEY antes debe borrar el certificado

DROP MASTER KEY;   

------------------------------------- eliminar el certificado de la MASTER

USE master;

GO

 -- Para eliminar el certificado, de la MASTER DB, no deben existir Bases de datos de usuario utilizando TDE

 -- Borrar certificados de otras bases antes de borrar el certificado de la master

DROP CERTIFICATE [MyMasterServerCertificate]; 

-- para borrar la MASTER KEY antes debe borrar el certificado

DROP MASTER KEY;

 -- SOME HELP QUERIES

SELECT * FROM SYS.dm_database_encryption_keys

VINCULAR SERVIDORES

A continuación se muestra el código SQL-T para crear un link (vinculo) desde un servidor SQL2014 hacia una versión de servidor SQL 2000

Script para vincular un servidor SQL2000 en un SQL2014
Ejecutar sobre el servidor SQL2014

--------------------------------------------Vincular un servidor SQL 2000

EXEC sp_addlinkedserver

   @server = 'SERVERNAME',

   @srvproduct = '',

   @provider = 'MSDASQL', -- PARA VERSIONES DE SQL2014 A SQL200 UTILIZAR EL SIGUIENTE PROVEEDOR: N'MSDASQL',

   @provstr = 'DRIVER={SQL Server};SERVER=SERVERNAME;User ID=LinkUser;Password=********'';'

go

sp_addlinkedsrvlogin @rmtsrvname='SERVERNAME'

        , @useself ='False'

        , @rmtuser='LinkUser'

        , @rmtpassword='*********'

GO

EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'data access',@optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'rpc',          @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'rpc out',      @optvalue=N'true'

GO


USER ID:    NOMBRE DE USUARIO (CUENTA AUTENTICACIÓN EN SQL)
SERVERNAME: NOMBRE DEL SERVIDOR A VINCULAR

REQUISITOS: AMBOS SERVIDORES DEBEN ESTAR EN DOMINIO.

-------------------------------------------------
-- Procedimientos para mirar status del servidor

-- exec sp_helpserver
-- EXEC sp_linkedservers
-- exec sp_helplinkedsrvlogin

-------------------------------------------------
--PROCEDIMIENTOS PARA BORRAR LA CONFIGURACIÓN HECHA ANTERIORMENTE

--EXEC sp_droplinkedsrvlogin 'REPLICASD',null
--EXEC sp_DropServer 'REPLICASD'









Script para vincular un servidor SQL2005 desde un servidor SQL2014


Ejecutar sobre el servidor SQL2014

-------------------------------------------- Vincular un servidor SQL 2005

EXEC sp_addlinkedserver

   @server = 'SERVERNAME',

   @srvproduct = '',

   @provider = 'SQLOLEDB', 

   @provstr = 'DRIVER={SQL Server};SERVER=SERVERNAME;User ID=LinkUser;Password=******;'

go

sp_addlinkedsrvlogin @rmtsrvname='SERVERNAME'

        , @useself ='False'

        , @rmtuser='LinkUser'

        , @rmtpassword='*******'

GO

EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'data access',@optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'rpc',          @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'rpc out',      @optvalue=N'true'

GO

--EXEC sp_droplinkedsrvlogin 'SERVERNAME',null

--EXEC sp_DropServer 'SERVERNAME'

Como habilitar RPC en sql server en servicio de componentes

Propiedades del DTC (Coordinador de Transacciones Distribuidas)

Configurar ambos servidores con las siguientes propiedades:
 - Seguridad
   - Acceso a DTC desde la red
     - Permitir clientes remotos
      - Permitir Entrantes
      - Permitir Salientes

Propiedades en SQL

Para Windows XP

 Vincular un servidor SQL 2014 desde una versión anterior 

El siguiente Script vincula a un servidor SQL2014 desde un servidor SQL2005

EXEC sp_addlinkedserver

   @server = 'SERVERNAME',

   @srvproduct = '',

   @provider = 'SQLOLEDB',

   @provstr = 'DRIVER={SQL Server};SERVER=SERVERNAME;User ID=LinkUser;Password=*****;'

go

sp_addlinkedsrvlogin @rmtsrvname='SERVERNAME'

        , @useself ='False'

        , @rmtuser='LinkUser'

        , @rmtpassword='*****'

GO

EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'data access',  @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'rpc',                @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'rpc out',            @optvalue=N'true'

GO

TDE - DESENCRIPTAR DB

Cuando intentas restaurar una base de datos que está encriptada (TDE) en un servidor secundario, debes instalar el certificado que se encuentra en el servidor primario, de lo contrario, al intentar restaurar la base de datos te arrojará el siguiente error:

Cannot find server certificate with thumbprint '0xD6270CD6DBA0437B0054685723BAEB12B19D17EF'.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

• CREAR MASTER KEY
• CREATE CERTIFICATE
• RESTORE DATABASE

1.- CREATE MASTER KEY

Para implementar el certificado se debe crear una Master Key en el servidor secundario sobre la base de datos MASTER

USE MASTER;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**********';

GO

El password de la Master Key no necesariamente debe ser el mismo que el del servidor primario, preferentemente hágalo con el mismo.

2.- CREATE CERTIFICATE (restaurar certificado en servidor secundario)

A continuación se muestra el código SQL-T para restaurar el certificado que obtuvo del servidor primario.

Tome el backup del certificado del servidor primario, cópielo al servidor secundario y restaurelo como en el siguiente ejemplo:

USE MASTER;

GO

CREATE CERTIFICATE CertificateServerLevel

FROM FILE = 'G:\Authority\SERVER_Certificate.cer'

WITH PRIVATE KEY (FILE = 'G:\Authority\SERVER_CertificateKey.pvk',DECRYPTION BY PASSWORD = '********')

- CertificateServerLevel: Es un Nombre X del Certificado del MASTER DB (Certificado a nivel servidor), preferentemente que se llamen igual que tiene en el servidor primario

- Para abrir e implementar el certificado necesitará la contraseña que generó en el servidor primario.

3.- RESTORE DATABASE

Ahora ya puede restaurar su base de datos Encriptada por TDE como lo hace normalmente.

En este caso particular, se va a restaurar un FULL y un DIFFERENTIAL

Puede hacerlo mediante SQL-T o utilizando el Management Studio

USE [master]

RESTORE DATABASE [REMATE2G]

FROM  DISK = N'G:\Backups\BACKUP 2015 SEPTEMBER WEEK 39  -  REMATE2G.BAK'

WITH  FILE = 3, 

MOVE N'LOG_TRANSACTION' TO N'J:\LDF\REMATE2G_LOG.ldf', 

NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5

RESTORE DATABASE [REMATE2G]

FROM  DISK = N'G:\Backups\BACKUP 2015 SEPTEMBER WEEK 39  -  REMATE2G.BAK'

WITH  FILE = 7,  NOUNLOAD,  STATS = 5

Mover una base de datos protegida por TDE a otra instancia de SQL Server

Restaurar una base de datos Encriptada (Microsoft)

Qué es el TDE?

NOTAS: (SYMMETRIC KEY) Cuando se tiene una llave simétrica dentro de la DB

DEJAR ABIERTA LA MASTER KEY PARA EVITAR COLOCAR EL PASSWORD CONTINUAMENTE AL ABRIR LA LLAVE SIMÉTRICA

USE REMATE2G;

GO

-- AL RESTAURAR LA BASE DE DATOS EN OTRO SERVIDOR

-- EL MASTER KEY DEBE ABRIRSE PARA PODER ABRIR SU LLAVE SIMÉTRICA

-- CADA VEZ QUE SE DESEE ABRIR LA LLAVE SIMÉTRICA, SERÁ NECESARIO ABRIR LA MASTER KEY COLOCANDO SU PASSWORD

-- SIN EMBARGO, PUEDE ALTERARSE LA MASTER KEY PARA QUE ABRA EN AUTOMÁTICO

-- PRIMERO ABRIR LA MASTER KEY

OPEN MASTER KEY DECRYPTION BY PASSWORD = '*********'

-- CLOSE MASTER KEY

-- PARA PONER EN AUTOMÁTICO LA MASTER KEY, ES NECESARIO TENERLA SIEMPRE ABIERTA

-- CON EL SIGUIENTE CÓDIGO AUNQUE SE CIERRE LA MASTER KEY, ABRIRA AUTOMÁTICAMENTE

USE Remate2G;

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '**********';

GO

AHORA YA SE PUEDE MANDAR ABRIR LA LLAVE SIMÉTRICA...

       OPEN SYMMETRIC KEY PasswordTableKey

       DECRYPTION BY CERTIFICATE Nombre_Certificado

La SYMETRIC KEY se utiliza para encriptar datos, información dentro de las tablas.

Cannot find server certificate with thumbprint