Conocer la estructura de una BD SQL

–TABLAS y COLUMNAS

SELECT SO.NAME, SC.NAME
FROM sys.objects SO INNER JOIN sys.columns SC
ON SO.OBJECT_ID = SC.OBJECT_ID
WHERE SO.TYPE = ‘U’
ORDER BY SO.NAME, SC.NAME

–TABLAS
SELECT SO.NAME
FROM sys.objects SO
WHERE SO.TYPE = ‘U’
ORDER BY SO.NAME

–COLUMANS Y TIPO
SELECT COLUMN_NAME AS NombreCampo,DATA_TYPE as TIPO FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘Audit_Trails’

— FK
EXEC sp_fkeys ‘Clientes’

–FK por query
select distinct name from sys.objects where object_id in
( select fk.constraint_object_id from sys.foreign_key_columns as fk
where fk.referenced_object_id =
(select object_id from sys.tables where name = ‘Clientes’)
)

–TABLAS QUE TIENEN FK A UNA TABLA
select t.name as TableWithForeignKey, fk.constraint_column_id as FK_PartNo , c.name as ForeignKeyColumn
from sys.foreign_key_columns as fk
inner join sys.tables as t on fk.parent_object_id = t.object_id
inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
where fk.referenced_object_id = (select object_id from sys.tables where name = ‘Clientes’)
order by TableWithForeignKey, FK_PartNo

–PRIMARY KEY
SELECT column_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), ‘IsPrimaryKey’) = 1
AND table_name = ‘Clientes’

Obtener los Triggers de una BD SQL

La siguiente query permite obtener todos los Triggers de una Base de Datos, es bastante util cuando recibes una BD llenas de Triggers 🙂

SELECT
sysobjects.name AS trigger_name
,USER_NAME(sysobjects.uid) AS trigger_owner
,s.name AS table_schema
,OBJECT_NAME(parent_obj) AS table_name
,OBJECTPROPERTY( id, ‘ExecIsUpdateTrigger’) AS isupdate
,OBJECTPROPERTY( id, ‘ExecIsDeleteTrigger’) AS isdelete
,OBJECTPROPERTY( id, ‘ExecIsInsertTrigger’) AS isinsert
,OBJECTPROPERTY( id, ‘ExecIsAfterTrigger’) AS isafter
,OBJECTPROPERTY( id, ‘ExecIsInsteadOfTrigger’) AS isinsteadof
,OBJECTPROPERTY(id, ‘ExecIsTriggerDisabled’) AS [disabled]
FROM sysobjects
INNER JOIN sys.tables t
ON sysobjects.parent_obj = t.object_id

INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE sysobjects.type = ‘TR’
order by 1

Generic Sql Error en CRM Dynamics y usar SQL profiler para el CRM

Hace unos días me toco hacer un plugin para MS Dynamics CRM y me daba este error, después de googlear un rato di como personalizar el SQL Profiler para leer los errores del CRM de base de datos el link es el siguiente

http://www.avanadeblog.com/xrm/2011/04/troubleshooting-crm-generic-sql-error.html

si bien en mi caso el error era por otra causa, se puede sacar mucha información de lo que dice el Profiler

para mi caso el error sql era el siguiente

Cannot insert duplicate key row in object ‘dbo.EmailSearchBase’ with unique index ‘ndx_for_forward_update’

lo que se resuelve cambiando la forma de hacer el update de la entidad que da problemas

como dice en el siguiente link

http://community.dynamics.com/crm/b/hardworkdays/archive/2012/04/24/mscrm-2011-error-during-creation-of-account-record-system-data-sqlclient-sqlexception-0x80131904-cannot-insert-duplicate-key-row-in-object-dbo-emailsearchbase-with-unique-index-ndx-for-forward-update.aspx#.UiagKTZLNV5

 

mi código quedo de la siguiente forma

Dim target As Entity = DirectCast(context.InputParameters(“Target”), Entity)
Dim updateTarget As Entity = New Entity(target.LogicalName)
updateTarget.Id = target.Id

updateTarget(“new_rutencriptado”) = Encripta(contacto.New_rut)

service.Update(updateTarget)

PD: y para colmo tuve que hacer esto en VB  grrr….

 

Error al Cargar respaldo SQL Server Enterprise a SQL Standar Edition

Bueno este error se produce porque estamos tratando montar un respaldo Enterprise en una versión standar de SQL Server…

Hay varias opciones… la más cara es hacer Upgrade del motor a Enterprise… lo que se puede revisar aquí

lo otro es que antes de sacar el respaldo que se desea montar de Enterprise a Standar y ejecutar el siguiente script

IF EXISTS (SELECT name FROM sys.partition_schemes WHERE name=’AuditPScheme’)

BEGIN

SELECT

CASE WHEN ind.type != 1

THEN

‘DROP INDEX [dbo].[AuditBase].’ + QUOTENAME(ind.name) + ‘ ‘

ELSE ‘ ‘

END +

‘CREATE ‘ + CASE is_unique WHEN 1 THEN ‘UNIQUE ‘ ELSE ” END  +

ind.type_desc + ‘ INDEX ‘ + QUOTENAME(ind.name  COLLATE SQL_Latin1_General_CP1_CI_AS )  + ‘ ON [dbo].’ +  QUOTENAME(OBJECT_NAME(object_id)) + ‘ (‘ +

REVERSE(SUBSTRING(REVERSE((

SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ‘ DESC’ ELSE ‘ ASC’ END + ‘,’

FROM

sys.index_columns sc

JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id

WHERE

OBJECT_NAME(sc.object_id) = ‘AuditBase’ AND

sc.object_id = ind.object_id AND

sc.index_id = ind.index_id

ORDER BY index_column_id ASC

FOR XML PATH(”)

)), 2, 8000)) + ‘)’ +

CASE WHEN ind.type = 1

THEN

‘ WITH (DROP_EXISTING = ON) ON [PRIMARY]’

ELSE

‘ ‘

END  as Script

INTO #indexesScript

FROM sys.indexes ind

JOIN sys.partition_schemes ps on ind.data_space_id=ps.data_space_id

WHERE

OBJECT_NAME(object_id) = ‘AuditBase’

AND ps.name = ‘AuditPScheme’

AND is_unique_constraint = 0

SELECT * FROM #indexesScript

DECLARE @recreateScript nvarchar(max)

DECLARE indScript CURSOR FOR

SELECT Script FROM #indexesScript

OPEN indScript

FETCH NEXT FROM indScript INTO @recreateScript

WHILE @@FETCH_STATUS = 0

BEGIN

BEGIN TRANSACTION t1

Execute sp_executesql @recreateScript

IF @@ERROR > 0

BEGIN

ROLLBACK TRAN t1

declare @message varchar(max)

set @message = ‘Audit history recreate index failed. SQL: ‘ + @recreateScript

RAISERROR (@message, 10,1)

END

ELSE

BEGIN

COMMIT TRAN

END

FETCH NEXT FROM indScript INTO @recreateScript

END

DROP PARTITION SCHEME AuditPScheme

DROP PARTITION FUNCTION AuditPFN

CLOSE indScript

DEALLOCATE indScript

DROP TABLE #indexesScript

END

Con eso eliminamos las funciones que no son soportadas por una base de Datos Standar.

Referencias aca : http://support.microsoft.com/kb/2567984

Llamar un Web Services desde una ETL

Entre las cosas que me ha tocado hacer ultimamente, me toco llamar un Webservice, desde una ETL… bueno la verdad es que no resulto tan complejo… pero se requieren una serie de pasos casi de receta…

lo primero que se requiere es claro el Webservice 🙂

image2

Estes es un código de pruebas….

public class SSISService : System.Web.Services.WebService
{
    [WebMethod]
    public string HolaMundo()
    {
        return "Hola Mundo";
    }
    [WebMethod]
    public int Multiply(int nParam1, int nParam2)
    {
        return nParam1 * nParam2;
    }
}

Luego se hace el package SSIS (SQL Server Information Service)

image3

Eso con el SQL Server Business Intelligence Development Studio, debería estar en el menu del SQL Server. Luego se agrega una Web Service Task.

image4

image5

Luego Editamos la tarea..

image6

Con eso vemos las propiedades..

image7

En httpConnection, agregamos nuestro WSDL del servicio.. si esta publicado… la dirección web del WSDL

image9

en Input, se definen los parámetros del servicio..

image10

Luego deben definirse los parámetros de salida del webservice. en este caso lo enviaremos a un archivo para luego poder hacer un file Connection

image11

El archivo se llamará test.txt

image12

la salida del archivo debe quedar algo así…

<?xml version="1.0" encoding="utf-16"?>
<int>200</int>

En realidad lo más complejo es le map de variables… Espero que ayude

saludos.

El articulo original lo saque de aqui

Obtener tablas y columnas en un SQL Server

Un par de consultas útiles cuando se requiere conocer la base o si se desea hacer algo dinámico para la base de datos.

Para sql 2000 es

 

SELECT SO.NAME, SC.NAME

FROM sysobjects SO INNER JOIN syscolumns SC

ON SO.ID = SC.ID

WHERE SO.XTYPE = ‘U’

ORDER BY SO.NAME, SC.NAME

 

de 2005 en adelante

SELECT SO.NAME, SC.NAME

FROM sys.objects SO INNER JOIN sys.columns SC

ON SO.OBJECT_ID = SC.OBJECT_ID

WHERE SO.TYPE = ‘U’

ORDER BY SO.NAME, SC.NAME

saludos.

Respaldar una base de datos

El siguiente script sirve para respaldar una base de datos, esto se agrega a un job, se corre el sql Agent y … respaldos automatizados…

 

-- Parametros de entrada

DECLARE @strDatabase nvarchar (50)
DECLARE @strFolder nvarchar (500)

-- Seleccione el nombre de la base de datos y su ubicacion
SET @strDatabase=N'Comun'

-- Seleccione el direcorio donde se creara la copia de seguridad 
SET @strFolder = N'G:\Respaldos Diarios'

-- Por defecto, se creara un achivo .BAK con el nombre de la base de datos
-- mas la fehca y hora actual
-- Ejemplo : Comun 200703201535.bak
-- Backup de la base de datos de Comun creado el 04 de marzo de 2009 a las 17:30

DECLARE @tToday datetime
SET @tToday = GETDATE()

DECLARE @strBackupName nvarchar(100)
SET @strBackupName = @strDatabase + N' '

+STR(DATEPART(year,@tToday), 4, 0)
+REPLACE(STR(DATEPART(month, @tToday), 2, 0), N' ', N'0')
+REPLACE(STR(DATEPART(day, @tToday), 2, 0), N' ', N'0')
+REPLACE(STR(DATEPART(hour, @tToday), 2, 0), N' ', N'0')
+REPLACE(STR(DATEPART(minute, @tToday), 2, 0), N' ', N'0')

DECLARE @strBackUpFile nvarchar(600)
SET @strBackupFile = @strFolder + N'\' + @strBackupName + N'.bak'

BACKUP DATABASE @strDatabase
TO DISK = @strBackupFile
WITH
NOFORMAT,
INIT,
SKIP,
NAME = @strBackupName