Configurar conexiones remotas en SQL Server

Cuando se instala un servidor de SQL Server, en la mayoría de ocasiones deseamos hacer accesible ese servidor desde otros equipos de la red, es decir, no queremos que funcione sólo de manera local. Para conseguir esto hay que activar las conexiones remotas a SQL Server. Para hacer esto hemos de seguir los siguientes pasos (estos pasos se han seguido en la vesrsión 2012, pero es similiar en el resto de versiones):

1. Activar las conexiones remotas
   Abrir SQL Manangement Studio, y sobre la instancia instalada de SQL Server, dar con botón derecho-Propiedades, y dentro de la opción "Conexiones", comprobar que está marcado "Permitir conexiones remotas con este servidor".

2. Configurar las conexiones remotas
  Abrir el SQL Server Configuration Manager
  Dentro de SQL Server Network Configuration -> Protocols for <instanciaSQL>, comprobar que TCP/IP se encuentre en Enabled.
  Pulsar TCP/IP con botón derecho y dar a Propiedades. En la primera solapa, comprobar que "Listen All" esté en "Yes".
  En la segunda solapa, IP Addresses, bajar hasta donde pone IPAll, y en TCP Port indicar el puerto 1433. TPC Dynamic Ports se puede dejar vacío.
  Aplicar los cambios y reiniciar el servicio de SQL Server (Servicios-> SQL Server y SQL Browser).

2. Abrir el cortafuegos de Windows
   Si probamos a desactivarlo, comprobaremos como ya podemos conectar. Lo volvemos a activar y vamos a configurar las reglas de acceso a SQL Server.

3. Abrir el cortafuegos de Windows con seguridad avanzada.
   En el debemos crear 2 reglas de entrada: Una para el puerto TCP 1433, y otra para el puerto UDP 1434.

Con esto, SQL Server ya debe estar configurado para aceptar conexiones remotas.

Hay un video donde se explica muy bien aquí.

Leer más

Sistema de encuestas

La empresa en la que trabajamos dispone de distintos certificados de calidad. Una de las condiciones que se deben cumplir para estar en posesión de estos certificados, consiste en la realización de encuestas de satisfacción al cliente para conocer su opinión sobre los servicios ofrecidos. Para ello, se nos pide integrar esta posibilidad en el software de gestión.

La forma en que desea que funcionen las encuestas es la siguiente: periódicamente, cada viernes, el servidor creará de forma automática una serie de alertas para encuestar a clientes, basándose en ciertas condiciones de negocio (básicamente servicios entregados al cliente en los últimos X días). En este caso, no desean que el sistema seleccione aleatoriamente las encuestas, sino que desean que se notifique a un administrador de forma que sea él quien asigne las encuestas a la persona encargada de hacerlas, que será a su vez notificada mediante otra alerta.

Posteriormente, generaremos también de forma automática y periódica informes con las respuestas dadas por los clientes así como datos estadísticos sobre las mismas entre las fechas deseadas.
La solución ha sido la siguiente. Hemos creado una serie de tablas para la gestión de encuestas, que se detallan a continuación:

Encuestras.Preguntas
En esta tabla almacenaremos las preguntas a realizar para cada tipo de encuesta. Su estructura es la siguiente:

id / nombre / descripción / area / p1 / v1 / t1 / p2 / v2 / t2 / ..... / p20 / v20 / t20 / notas / recomedaciones

Donde p1 = pregunta1, v1 = posibles valores, separados por punto y coma, de la respuesta 1, t1 = peso de la pregunta en la encuesta

Encuestas.Rechazos
Esta tabla almacena las encuestas que se han descartado, de forma que no aparezcan más como posibles para encuestarse. Se compone de:

id / id_encuesta / id_asignacion / tabla_referencia / codigo_referencia / cod_usuario / fecha / tipo / cod_motivo / motivo_rechazo

Encuestas.MotivosRechazo
Es simplemente una tabla auxiliar para cuando se rechaza una encuesta, se indique el motivo. Los motivos pueden ser distintos según el tipo de encuesta, por lo que su estructura es esta:

cod_motivo / descripcion / id_encuesta

Encuestas.Respuestas
Almacena las respuestas de los clientes, y está formada por:

id / id_encuesta / cod_usuario / fecha / id_asignacion / tabla_referencia / codigo_referencia / nombre_encuestado / r1 / r2 / .... / r20 / notas / recomendaciones / fecha_auditoria

Encuestas.Asignacion
Por último tenemos esta tabla, que es la que asocia una encuesta a una persona para que la realice, y se compone de:

id / tipo / tabla_referencia / codigo_referencia / fecha_decision / cod_decisor / encuestar / cod_encuestador

Con esta estructura de tablas, es posible montar el sistema. El código del mismo será publicado en otra entrada.

El código asociado se encuentra aquí.

Leer más

Sencilla clase de acceso a datos (SQL Server) con .NET

Una de las cosas más básicas que necesitaremos hacer con nuestros proyectos .NET, es sin duda conectarnos a una fuente de datos. .NET dispone de una serie de clases que nos facilitan esta tarea. Habitualmente suelo utilizar SQL Server (aunque cualquier fuente de datos es aceptada), y a continuación vamos a crear una pequeña clase, muy simple, para obtener acceso a datos en este gestor.
El ejemplo crea un clase muy simple, que puede ser compilada como una DLL y que nos puede ayudar a ralizar aplicaciones en 3 capas. La clase que aquí vamos a crear, es simple pero genérica, de forma que podrá ser utilizada desde cualquier aplicación sin modificación alguna (salvo las funcionalidades extra que queramos añadirle).

public class AccesoDatos
{
public static string cadenaConexion = string.Empty;
private SqlConnection conexion;
private SqlDataAdapter da;

public static void SetCadenaConexion(string connString)
{
  if (connString != string.Empty)
    cadenaConexion = connString;
  else
    cadenaConexion = String.Empty;
}

public AccesoDatos()
{
  conexion = new SqlConnection(cadenaConexion);
}

~AccesoDatos()
{
  da.Dispose();
  conexion.Close();
  conexion.Dispose();
}


public DataSet GetDataSet(string SQL)
{
  try
  {
    da = new SqlDataAdapter(SQL, conexion);
    DataSet ds = new DataSet();
    da.Fill(ds);
    return ds;
  }
  catch (Exception ex)
  {
    throw ex;
  }
}

public void UpdateDataSet(DataSet ds)
{
  try
  {
    SqlCommandBuilder cb = new SqlCommandBuilder(da);
    da.InsertCommand = cb.GetInsertCommand();
    da.DeleteCommand = cb.GetDeleteCommand();
    da.UpdateCommand = cb.GetUpdateCommand();
    da.InsertCommand.Connection = conexion;
    da.DeleteCommand.Connection = conexion;
    da.UpdateCommand.Connection = conexion;
    da.Update(ds);
    cb.Dispose();
  }
  catch (Exception ex)
  {
    throw ex;
  }
}

public DataTable GetDataTable(string SQL)
{
  try
  {
    SqlDataAdapter comando = new SqlDataAdapter(SQL, conexion);
    DataSet ds = new DataSet();
    comando.Fill(ds);
    comando.Dispose();
    return ds.Tables[0];
  }
  catch (Exception ex)
  {
    throw ex;
  }
}

public SqlDataReader GetDataReader(string SQL)
{
  try
  {
    conexion.Open();
    SqlCommand comando = new SqlCommand(SQL, conexion);
    SqlDataReader dr = comando.ExecuteReader();
    //conexion.Close();
    comando.Dispose();
    return dr;
  }
  catch (Exception ex)
  {
    throw ex;
  }
}

public string GetScalarValue(string SQL)
{
  try
  {
    conexion.Open();
    SqlCommand comando = new SqlCommand(SQL, conexion);
    string sv = (string)comando.ExecuteScalar();
    conexion.Close();
    comando.Dispose();
    return sv;
  }
  catch (Exception ex)
  {
    throw ex;
  }
}

public int ExecuteSQL(string SQL)
{
  try
  {
    SqlCommand comando = new SqlCommand();
    int resul = 0;
    comando.CommandText = SQL;
    comando.CommandType = CommandType.Text;
    comando.Connection = conexion;
    conexion.Open();
    resul = comando.ExecuteNonQuery();
    conexion.Close();
    return resul;
  }
  catch (Exception ex)
  {
    throw ex;
  }
}

public string GetField(string tabla, string clavePrimaria, string valorBuscado, string campoDevuelto)
{
  DataTable dt = new DataTable();
  string SQL = "";

  try
  {
    SQL = "SELECT " + campoDevuelto + " FROM " + tabla + " WHERE " + clavePrimaria + "='" + valorBuscado + "'";
    dt = this.GetDataTable(SQL);
    if (dt.Rows.Count > 0)
      return dt.Rows[0][campoDevuelto].ToString();

    return "";
  }
  catch (Exception ex)
  {
    return "Error en la búsqueda " + ex.Message;
  }
}


#region " Metodos de obtención de nueva clave primaria "

public string GetPrimaryKey(string tabla, string campo, short longitud)
{
  try
  {
    // SQL para obtener ultimo codigo
    string SQL = "SELECT TOP 1 " + campo + " FROM " + tabla + " ORDER BY " + campo + " DESC";
    SqlDataReader dr = this.GetDataReader(SQL);
    string codigo;

    // Obtener codigo
    if (dr.HasRows)
    {
      dr.Read();
      codigo = Convert.ToString(dr[campo]);
    }
    else
      codigo = "0";

    codigo = Convert.ToString(int.Parse(codigo) + 1);

    // Rellenar de ceros y devolver
    codigo = this.Ceros(codigo, longitud);   // dtTabla.Columns(cCampo).MaxLength)

    dr.Close();
    dr.Dispose();
    return codigo;
  }
  catch (Exception e)
  {
    return e.Message;
  }
}

private string Ceros(string texto, short ceros)
{
  string cadena = texto;
  string _ceros;
  if (ceros > texto.Length)
    _ceros = new string('0', ceros - cadena.Length) + cadena;
  else
    _ceros = cadena.Substring(0, ceros);
  return _ceros;
}

public int GetLastId(string tabla)
{
  return Convert.ToInt32(GetScalarValue("SELECT ident_current('" + tabla + "')"));
}

#endregion

Espero que os sirva.

Leer más

Registros de SQL hacia XML/JSON

Nuestra aplicación de gestión en la empresa está desarrollada en .NET y utilizando base de datos SQL Server. Se trata de una aplicación de escritorio, y debido a ciertas necesidades, hemos construido un WebService para hacer disponibles ciertos datos desde el exterior, para que sean consumidos por la web de eCliente y por varias apps.

Hemos configurado el webservice para que devuelva los datos en el formato deseado, XML o JSON, así quien lo consuma podrá elegir cómo quiere los datos. En eCliente utilizamos .NET, por lo que se hace más sencillo obtener los resultados en un XML (directamente creamos un DataSet a partir de él se hace mucho más fácil su gestión), y en las apps lo consumimos con JSON.

En ambos casos nos hemos encontrado con un problema: tenemos registros que tienen caracteres que, bien hacen que se rompan el XML, o bien el HTML, debido a que contienen caracteres no permitidos para estos formatos.

Para evitar este problema, hemos creado una función escalar en SQL Server que nos reemplaza los caracteres no deseados para que el XML/JSON sea correcto. La función es la siguiente:

USE [BaseDatos]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CampoToHTML] (@texto varchar(256)) returns varchar(max)
begin
   declare @resul varchar(max)
   set @resul = @texto
   set @resul = REPLACE(@resul, char(34), char(39))
   set @resul = REPLACE(@resul, 'ñ' COLLATE Latin1_General_CS_AS,'n')
   set @resul = REPLACE(@resul, 'á' COLLATE Latin1_General_CS_AS,'a')
   set @resul = REPLACE(@resul, 'é' COLLATE Latin1_General_CS_AS,'e')
   set @resul = REPLACE(@resul, 'í' COLLATE Latin1_General_CS_AS,'i')
   set @resul = REPLACE(@resul, 'ó' COLLATE Latin1_General_CS_AS,'o' )
   set @resul = REPLACE(@resul, 'ú' COLLATE Latin1_General_CS_AS,'u')
   set @resul = REPLACE(@resul, 'Ñ' COLLATE Latin1_General_CS_AS,'N')
   set @resul = REPLACE(@resul, 'Á' COLLATE Latin1_General_CS_AS,'A')
   set @resul = REPLACE(@resul, 'É' COLLATE Latin1_General_CS_AS,'E')
   set @resul = REPLACE(@resul, 'Í' COLLATE Latin1_General_CS_AS,'I')
   set @resul = REPLACE(@resul, 'Ó' COLLATE Latin1_General_CS_AS,'O')
   set @resul = REPLACE(@resul, 'Ú' COLLATE Latin1_General_CS_AS,'U')
   set @resul = REPLACE(@resul, '&' COLLATE Latin1_General_CS_AS,'y')
   return @resul
END

De esta forma, la consulta SQL la haríamos de la siguiente forma:

SELECT id,dbo.CampoToHTML(apellidos) FROM Clientes

Esto es todo.

Leer más

Service Broker. Qué es y utilización en un sistema de alertas.

En nuestro software de gestión se deben llevar a cabo una importante cantidad de procesos en tiempo real. Además, existen distintos usuarios trabajando sobre las mismas parrillas de datos, y cuando un usuario cambia algo, todos los demás deben verlo instantáneamente. Hasta ahora, para solucionar esto utilizábamos Socket Broadcast, de forma que cuando un registro era modificado, enviábamos un mensaje codificado por la red (utilizando un puerto), que era escuchado por los demás y hacía un refresco de su pantalla. La utilización de sockets nos ha dado algunos problemas, relativos a cortafuegos o antivirus por ejemplo, ya que detectan que se abre un puerto no esperado. Además, si por algún motivo nos salimos del programa incorrectamente, al volver a entrar nos sale un mensaje diciendo que no se puede abrir ese puerto porque ya está abierto.

ServiceBroker se introduce a partir de SQL 2005, y nos permite solucionar este tipo de problemas. No voy a entrar en todos los detalles sobre esta tecnología, lo que hace y cómo funciona, para eso puede consultarse más información en la web de Microsoft. Para nuestro caso, lo interesante es que nos permite monitorizar registros, de forma que cuando haya un cambio en cualquier campo del mismo, se nos envíe una notificación. Tiene la ventaja de notificarnos siempre que haya un cambio, aunque este no se realice desde nuestro software. Pongámonos pués manos a la obra.

Lo primero que debemos hacer es activar Service Broker en nuestra base de datos. Para ello, debemos ejecutar sobre ella el siguiente comando:

ALTER DATABASE [Database Name] SET NEW_BROKER
ALTER DATABASE [Database Name] SET ENABLE_BROKER;

Otros comandos interesantes pueden ser, desactivar ServiceBroker:

ALTER DATABASE [Database Name] SET DISABLE_BROKER;

O comprobar si está activado:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name';

Si en alguna ocasión el comando, debido a los permisos de la base de datos, no se ejecuta correctamente, deberemos precederlo de lo siguiente:

ALTER AUTHORIZATION ON DATABASE::[Database name] TO [sa];

Con esto ya tenemos la base de datos preparada para notificar estos cambios. Ahora nos vamos a nuestro software. Desde .NET, podemos detectar los cambios producidos a través del objeto SqlDependency.

Nos vamos a nuestra capa de datos (trabajamos en 3 capas), y añadimos lo siguiente (existen otras formas de hacerlo, pero esta es la que nosotros utilizamos):

Private Structure sDependency
  Dim Tabla As String
  Dim ClavePrimaria As String
  Dim Registro As String
  Dim SeparadorClavePrimaria As String
End Structure
Dim rDependency As sDependency
Public Event RegistroModificado(ByVal cTabla As String, ByVal cRegistro As String)
Public Event TablaModificada(ByVal cTabla As String)
Public Event SQLModificada(ByVal cTabla As String)

Lo que vamos a hacer aquí es crear una estructura genérica para indicar que es lo que queremos monitorizar y crearemos un evento de notificación de cambios para envíar a quien use esta capa de datos. En este caso vamos a crear 3, para poder monitorizar un registro en concreto, un registro que cumpla las condiciones de una sentencia SQL, o un cambio en cualquier lugar de una tabla. Esto nos dará mucho juego, ya que podremos monitorizar y estar informados de los cambios en los que más nos interese. Para ello, definimos a continuación las rutinas para activar la monitorización y los eventos que se dispararán.

' Para un registro

Public Sub ActivarNotificacionCambiosRegistro(ByVal cTabla As String, ByVal cClavePrimaria As String, ByVal cRegistro As String, Optional ByVal cSeparadorClavePrimaria As String = "'")
  Try
    rDependency.Tabla = cTabla
    rDependency.ClavePrimaria = cClavePrimaria
    rDependency.Registro = cRegistro
    rDependency.SeparadorClavePrimaria = cSeparadorClavePrimaria

    Dim cmd As New SqlClient.SqlCommand
    cmd.CommandText = "SELECT " + cClavePrimaria + " FROM dbo." + cTabla + " WHERE " + cClavePrimaria + "=" + cSeparadorClavePrimaria + cRegistro + cSeparadorClavePrimaria
    cmd.Connection = oCon
    Dim dependency As New SqlClient.SqlDependency(cmd)
    dependency.AddCommandDependency(cmd)
    AddHandler dependency.OnChange, AddressOf OnDependencyChangeRegistro
    cmd.ExecuteNonQuery()

  Catch ex As Exception
    Throw New Exception(ex.Message)
  End Try
 End Sub

Private Sub OnDependencyChangeRegistro(ByVal sender As Object, ByVal e As SqlClient.SqlNotificationEventArgs)
  RaiseEvent RegistroModificado(rDependency.Tabla, rDependency.Registro)
End Sub

' Para cualquier registro de una tabla
Public Sub ActivarNotificacionCambiosTabla(ByVal cTabla As String, Optional ByVal cClavePrimaria As String = "")
  Try
    rDependency.Tabla = cTabla
    If cClavePrimaria = "" Then
      rDependency.ClavePrimaria = Me.ClavePrimaria(cTabla)
    Else
      rDependency.ClavePrimaria = cClavePrimaria
    End If
    rDependency.Registro = ""
    rDependency.SeparadorClavePrimaria = ""

    Dim cmd As New SqlClient.SqlCommand
    cmd.CommandText = "SELECT " + rDependency.ClavePrimaria + " FROM dbo." + cTabla
    cmd.Connection = oCon
    Dim dependency As New SqlClient.SqlDependency(cmd)
    AddHandler dependency.OnChange, AddressOf OnDependencyChangeTabla
     cmd.ExecuteNonQuery()

  Catch ex As Exception
    Throw New Exception(ex.Message)
  End Try
End Sub

Private Sub OnDependencyChangeTabla(ByVal sender As Object, ByVal e As SqlClient.SqlNotificationEventArgs)
  RaiseEvent TablaModificada(rDependency.Tabla)
End Sub


' Para un registro cualquiera de los que entren en una sentencia SQL

Public Sub ActivarNotificacionCambiosSQL(ByVal cSQL As String)
  Try
    rDependency.Tabla = cSQL
    rDependency.ClavePrimaria = ""
    rDependency.Registro = ""
    rDependency.SeparadorClavePrimaria = ""

    Dim cmd As New SqlClient.SqlCommand
    cmd.CommandText = cSQL
    cmd.Connection = oCon
    Dim dependency As New SqlClient.SqlDependency(cmd)
    AddHandler dependency.OnChange, AddressOf OnDependencyChangeSQL
    cmd.ExecuteNonQuery()

  Catch ex As Exception
    Throw New Exception(ex.Message)
  End Try
End Sub

Private Sub OnDependencyChangeSQL(ByVal sender As Object, ByVal e As SqlClient.SqlNotificationEventArgs)
  RaiseEvent SQLModificada(rDependency.Tabla)
  Call ActivarNotificacionCambiosSQL(rDependency.Tabla)
End Sub

Y con esto, ya tenemos lista nuestra capa de datos genérica con soporte para ServiceBroker. Leyendo un poco el código, necesita poca explicación. Para ver como se utiliza, vamos a hacerlo con un caso real que hemos implementado. En nuestro sistema, nos han pedido crear una serie de alertas que se disparan cuando suceden ciertos eventos. En esos casos, hay que notificar inmediatamente a uno o varios usuarios para que tomen ciertas decisiones. Para conseguir esto, lo primero que hacemos es definir una tabla para almacenar las alertas.

Con la estructura definida para esta tabla, podemos crear alertas relativas a ciertos registros, y con el campo acción podremos realizar diferentes acciones al pulsar sobre la alerta (esto lo hacemos en otro formulario, un visor de alertas). Desde abrir un registro para consultarlo o modificarlo, hasta realizar ciertas acciones de forma automática, pasando por tomar decisiones (alertas de decisión) que impliquen realizar ciertas modificaciones, o por la generación automática de informes programados desde un servicio.

Una vez definida, y con la utilización de ServiceBroker, sólo tendremos que, en los supuestos en los que los eventos deseados se producen, escribir en la tabla de alertas. En el software tenemos un formulario siempre abierto y visible (trabajamos con 2 pantallas) en el que se muestran las alertas pendientes que tiene el usuario logado. Utilizando ServiceBroker, ese formulario se actualizará en tiempo real, y el código es tan sencillo como el que se muestra a continuación.

  Dim WithEvents oCon As New VB3.Conexion

  Private Sub FrmAlertas_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Try
      Call CargarAlertas()

      Dim SQL_Monitorizacion As String
      SQL_Monitorizacion = "SELECT id FROM dbo.[Alertas.Alertas] WHERE cod_usuario='" + Constantes.Login.Codigo + "' AND fecha_lectura=''"
      oCon.ActivarNotificacionCambiosSQL(SQL_Monitorizacion)

    Catch ex As Exception
      oForm.ControlErrores(ex, Me.Name, "FrmAlertas_Load")
    End Try
  End Sub


  Private Sub SQLCambia(ByVal cSQL As String) Handles oCon.SQLModificada
    Dim oMetodoOriginal As MethodInvoker
    oMetodoOriginal = New MethodInvoker(AddressOf CargarAlertas)
    Me.Invoke(oMetodoOriginal)
  End Sub


  Public Sub CargarAlertas()
    Try
      Dim dt As New Data.DataTable, SQL As String

      SQL = "SELECT * FROM [Alertas.vAlertas] WHERE cod_usuario='" + Constantes.Login.Codigo + "' AND fecha_lectura=''"
      dt = oCon.GetDataTable(SQL)

      dg.AutoGenerateColumns = False
      dg.DataSource = dt

      dt.Dispose()

    Catch ex As Exception
      oForm.ControlErrores(ex, Me.Name, "CargarAlertas")
    End Try
  End Sub

Este sistema es muy versátil, muy fácil de utilizar, y nos puede servir para monitorizar agendas, parrillas de datos, para disparar ciertos servicios cuando se produzca algún cambio, ... El límite lo pone nuestra imaginación. Espero que este post os haya sido de utilidad. Hasta la próxima.

Leer más

TIPs de SQL

Este post se crea únicamente para tener a mano esas sentencias SQL que de vez en cuando se necesitan y que viene muy bién tener anotadas para utilizarlas cuando nos hace falta. A continuación se indican algunas de ellas.

Compactar una base de datos

BACKUP LOG BaseDatos WITH NO_LOG
BACKUP LOG BaseDatos WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (BaseDatos,0,TRUNCATEONLY)

También se puede utilizar:

BACKUP LOG BaseDatos WITH TRUNCATE_ONLY DBCC SHRINKDATABASE (BaseDatos,1)

Reiniciar campos autonumericos

DBCC CHECKIDENT ('tuTabla', RESEED, 1)

Programar una copia de seguridad
Meter como tarea programada lo siguiente en un .bat:

del "C:\ruta\BaseDatos.bak"
sqlcmd -S .\SQLEXPRESS -E -Q "BACKUP DATABASE BaseDatos TO DISK='C:\ruta\BaseDatos.bak'"

Esto puede conseguirse también desde SQL Management Express, el entorno gráfico para la gestión de la base de datos, bajo la opción Administración - Planes de mantenimiento, utilizando el asistente incluido, lo cual generará una sentencia como la siguiente:

BACKUP DATABASE [BaseDatos] TO  DISK = N'D:\BBDD\backup\BaseDatos.bak' WITH NOFORMAT, INIT,  NAME = N'BaseDatos_backup_2015_01_30_082549_1405325', SKIP, REWIND, NOUNLOAD,  STATS = 10

Además puede programarse para que realice este proceso como se haya decidido.

Se puede utilizar la siguiente sentencia para programa una copia que se sobreescriba cada vez:

BACKUP DATABASE [BaseDatos] TO  DISK = 'D:\ruta\BaseDatos.bak' WITH INIT

Consultar todas las tablas de la base de datos

select * from sys.tables

Consultar las tablas y la cantidad de registros de cada una de una base de datos

select substring(obj.name, 1, 50) as Tabla, ind.rows as Registros
from sysobjects as obj
inner join sysindexes as ind on obj.id = ind.id
where obj.xtype = 'u'
and ind.indid < 2

Consultar el tamaño en disco de una tabla de la base de datos

USE BaseDatos;
GO
EXEC sp_spaceused N'[Tabla]';
GO

Saber las conexiones abiertas con todas las bases de datos

SELECT DB_NAME(dbid) as 'Database Name',
COUNT(dbid) as 'Total Connections'
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid

Otra forma:

SELECT spid,STATUS,PROGRAM_NAME,LOGINAME=RTRIM(LOGINAME),HOSTNAME,CMD
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid = (SELECT DB_ID('Tabla') as [Database ID])

Y otra:

Select *
From master..sysprocesses p,master..sysdatabases d
Where p.status != 'BACKGROUND' And d.dbid = p.dbid And d.name = 'BaseDatos'
Order by hostname

Cerrar conexion abierta (por spid)

dbcc inputbuffer (1018)
kill 1018

Obtener el esquema de una tabla con propiedades extendidas de columna

select table_name,ordinal_position,column_name,data_type,is_nullable,character_maximum_length
,(
SELECT value
FROM sys.extended_properties AS ep
  INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
  INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
WHERE class=1 AND ep.name='MS_Description' AND t.name=ins.table_name AND c.name=ins.column_name
)  AS column_description
from information_schema.columns AS ins
where table_name = 'tuTabla'
order by ordinal_position

Búsqueda de huecos en campos autonuméricos

SELECT TOP(1) Id + 1 As PrimerHueco
FROM [Tabla] T
WHERE NOT EXISTS( SELECT * FROM [Tabla] WHERE Id = T.Id + 1)

Último día del mes actual

SELECT CONVERT(datetime,DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),103)

Primer día del mes anterior

select DATEADD(month, DATEDIFF(month, 0, DATEADD(month, -1,GETDATE())), 0)

Leer más