T-SQL: SET ROWCOUNT vs. SELECT TOP

“Si hay 2 formas de hacer lo mismo, una seguro que da problemas.”
No sé si esta es una de las Leyes de Murphy, pero debería serlo :P

SET ROWCOUNT es uno de esos settings “con trampa” que tiene T-SQL y que si no sabes usarlos adecuadamente te puede provocar muchos quebraderos de cabeza.

Según MSDN: Hace que SQL Server detenga el procesamiento de la consulta una vez que se han devuelto las filas especificadas.

Ah, qué bonito, si sólo quiero que una consulta me devuelva 20 registros pues pongo “SET ROWCOUNT 20;” antes de ejecutarla y listo.
Vamos, lo mismo que si hago un SELECT TOP 20 …

Bueno, sabiendo esto se puede elegir entre utilizar uno u otro método para limitar los resultados. Pero SET ROWCOUNT, al tratarse de un setting afecta a TODAS las consultas que se ejecutan dentro del mismo contexto y aquí es donde viene el problema.

Por ejemplo, supongamos que tenemos 2 tablas relacionadas y una función llamada MiFuncion() que hace lo siguiente:

SELECT idPadre FROM TablaPadre WHERE campoPadre = @parametro

Ahora bien, si hacemos esto:

SET ROWCOUNT 10;
SELECT * FROM TablaHija WHERE idPadre in (select idPadre from MiFuncion(@parametro))

La llamada a MiFuncion (@parametro) devolverá únicamente 10 registros, y a su vez la SELECT inicial también devolverá 10 registros, lo que puede provocar que no obtengamos el resultado deseado :(

¿Y cómo se puede solucionar esto? Pues es relativamente sencillo, cambiamos la llamada a la función por una subconsulta:

SET ROWCOUNT 10;
SELECT * FROM TablaHija WHERE idPadre in (SELECT idPadre FROM TablaPadre WHERE campoPadre = @parametro)

Y así, la subconsulta devuelve los registros que tenga que devolver, sin tener en cuenta el número de SET ROWCOUNT y la SELECT sí que devuelve 10 registros O_o El que lo entienda que me lo explique.

Así que ya sabéis, si vais a usar SET ROWCOUNT, hacerlo con cuidado y aseguraros de desactivarla usando SET ROWCOUNT 0. En cualquier caso, es menos peligroso usar SELECT TOP, ya que solo afecta a la consulta en la que se especifica.

Tampoco es muy aconsejable mezclar SELECT TOP … con SET ROWCOUNT … porque SET ROWCOUNT anula la palabra clave TOP de la instrucción SELECT si el número de filas es inferior.

Happy codding ;)

BOLA EXTRA: a partir de la próxima versión de SQL Server (la que siga a la 2008) la utilización de SET ROWCOUNT no afectará a las instrucciones DELETE, INSERT ni UPDATE.
No me quiero ni imaginar los problemas que puede provocar SET ROWCOUNT con un UPDATE O_o

Search in text fields with T-SQL: beyond LIKE

Versión en castellano

One of the first things you learn when begin with T-SQL is to use the LIKE clause to make comparisons in text fields (char, nchar, varchar, nvarchar or text). But this clause is quite limited and don’t have good performance. So, what can be used instead of LIKE?

A good option is to use CONTAINS(that to be honest, I discovered by accident: P), which allows us to refine more comparisons and offers much better performance.


To give an example of using CONTAINS I will use the PUBS database (Microsoft offers as an example with SQL Server and can be downloaded from here) and we will find records in the "jobs" table filtering by "job_desc" field.

To give you an idea (or if you don't want download example databases) the "jobs" table has the following records:

job_id job_desc                                            min_lvl  max_lvl
--------  --------------------------------------------------  -------  -------
1       New Hire - Job not specified                        10       10
2       Chief Executive Officer                             200      250
3       Business Operations Manager                         175      225
4       Chief Financial Officier                            175      250
5       Publisher                                           150      250
6       Managing Editor                                     140      225
7       Marketing Manager                                   120      200
8       Public Relations Manager                            100      175
9       Acquisitions Manager                                75       175
10      Productions Manager                                 75       165
11      Operations Manager                                  75       150
12      Editor                                              25       100
13      Sales Representative                                25       100
14      Designer                                            25       100

Before using CONTAINS we must create a FULLTEXT index in the table, some like this:

CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON Jobs(job_desc)
   KEY INDEX PK__jobs__173876EA
GO

PK__jobs__173876EA is the table’s PRIMARY KEY.


Once this is done we can start with examples:

Search exact word

Obtains all records containing the word 'manager' in "job_desc" field.
Note: is not case sensitive.

select * from jobs where CONTAINS( job_desc, 'manager' )

job_id job_desc                                            min_lvl  max_lvl
-------- --------------------------------------------------  -------  -------
3      Business Operations Manager                         175      225
7      Marketing Manager                                   120      200
8      Public Relations Manager                            100      175
9      Acquisitions Manager                                75       175
10     Productions Manager                                 75       165
11     Operations Manager                                  75       150

Also you can search for phrases (more than 1 word) and separators are ignored.

select * from jobs where CONTAINS (job_desc, '"hire job"');

job_id job_desc                                            min_lvl  max_lvl
-------- --------------------------------------------------  -------  -------
1      New Hire - Job not specified                        10       10

Search by approximation with several options

Finds the records containing any word beginning with 'pub' or 'edit'.

select * from jobs where CONTAINS(job_desc, '"pub*" OR "edit*"');

job_id job_desc                                            min_lvl  max_lvl
-------- --------------------------------------------------  ------- -------
5      Publisher                                            150      250
6      Managing Editor                                      140      225
8      Public Relations Manager                             100      175
12     Editor                                               25       100

CONTAINS supports AND, AND NOT, OR.

Search a word near another one

Finds records that contain a word beginning with 'pub' and that word is near 'manager'.

select * from jobs where CONTAINS (job_desc, '"pub*" NEAR manager')

job_id job_desc                                            min_lvl  max_lvl
-------- --------------------------------------------------  -------  -------
8      Public Relations Manager                            100      175

CONTAINS can also search for synonyms and derived words, but this requires using a thesaurus.

Happy codding ;)

Extra ball
If you find CONTAINS interested, see also FREETEXT and CONTAINSTABLE.

3 ways to find SQL Server version with T-SQL

Versión en castellano

In a perfect world all our facilities have the same versions of the applications, database engines and auxiliary libraries, but this world is a utopia, and we sometimes have to find out what version of the database engine is working or what dll version is installed.

In the case of SQL Server I have discovered 3 ways to find the version from T-SQL, with any of the 3 options will get the full version, but in most cases only need the "major release". For example if you want to execute features of the new versions, or ones that are obsolete in the latest version but still work in older versions.

Let’s go:

@@VERSION

Is a system variable, so is useful for any version of SQL Server, but from the 2005 version returns a nvarchar instead of varchar from previous versions.

declare @version char(2)

select @version = substring(@@version, charindex('- ', @@version) + 2, charindex('.',@@version) - charindex('- ', @@version)-2)

SERVERPROPERTY(‘ProductVersion’)

This option is only available after SQL Server 2000 and returns a value of type sqlvariant, so you have to convert it to varchar or nvarchar to get the main version.

declare @productversion varchar(50)

declare @version char(2)

select @productversion = CAST(SERVERPROPERTY('productversion') AS VARCHAR(50))

set @version = SUBSTRING(@productversion,1,CHARINDEX('.',@productversion)-1)

xp_msver ‘ProductVersion’

Perhaps the most complicated, because it returns a result set like this:

Index Name Internal_Value Character_Value
------ -------------------------------- -------------- ----------------------
2 ProductVersion 589824 9.00.1406.00

(1 row(s) affected)

But you can also get the version with something like this:

CREATE TABLE #VersionTable(
      [Index] int,
      Name varchar(30),
      Internal_Value int,
      Character_Value varchar(50))
GO

INSERT INTO #VersionTable
EXEC master..xp_msver 'ProductVersion'

DECLARE @Version char(2)

SELECT @Version = (
SELECT SUBSTRING(Character_Value,1,CHARINDEX('.', Character_Value)-1)
FROM #VersionTable)

drop table #VersionTable

With any of the 3 options we get the following in @Version:

‘7’ -> SQL Server 7
‘8’ -> SQL Server 2000 y MSDE
‘9’ -> SQL Server 2005
‘10’ -> SQL Server 2008

Happy coding ;)

Converting rows in columns with T-SQL: PIVOT() and things that will brighten the day :)

Versión en castellano

Sometimes things that seem very complex have a easy solution, but this isn’t the first one that we think (yes, sound like Occam’s razor, but isn’t the same).

Well, the case is that I had a logging table in which some external process inserted the results of passing for different states, something like this:

Process State Timestamp

Process1  10   2008/12/21 12:22:05.320
Process2  10   2008/12/21 12:22:05.358
Process1  11   2008/12/21 12:22:06.210
Process1  12   2008/12/21 12:22:06.588
Process2  11   2008/12/21 12:22:10.100
Process2  12   2008/12/21 12:22:10.048
Process3  10   2008/12/21 14:30:05.358
Process3  11   2008/12/21 14:30:20.052

To monitor processes and identify performance problems at certain times: many processes at once, backups were began, the server runs out of memory, ... I get a query grouping by process and hour.


SELECT process, count(*) as requests, datepart(hh,Timestamp) as hour
FROM MiLOG with (nolock)
GROUP BY process, datepart(hh,Timestamp)
ORDER BY 1,3


With this query we retrieve a result like this:

Process    Requests Hour

Process1   10         12
Process1   2          13
Process2   5          12
Process2   3          13
Process3   25         14
Process3   4          15

Of course, this output is a bit complicated to treat because even though they are grouped by the process and the time, when you have lots of data becomes too cumbersome to check them, and just bringing the results to a spreadsheet you can see more clearly. Then I wondered, is there not a way to do it from T-SQL convert rows into columns, with a column for each hour and a single row for each process?

As always the first solution to a complicated query is a sub-query (I don't know why but it's true, someone would have to study it :P) but in this case was not a sub-query, but rather 24 sub-queries, all attacking the same table, and all using the function datepart (hh, TimeStamp). Surely you would get the expected result, but at the expense of a very poor performance.

As this situation is very common, I think that should have an easy way to fix it, so in an initial Google search did not surprise me the first results show the same resolution of sub-queries (for the principle that the first solution in a complicated query is a sub-query: P) and I was doing a huge query with a lot of sub-queries, JOINs to the same table. But on the first page of results I found a much simpler solution: the PIVOT clause (that is new SQL Server 2005, so don't try in earlier versions).

So, with a query like that:


WITH Processes(process, requests, hour)
AS(
SELECT process, count(*) as requests, datepart(hh,Timestamp) as hour
FROM MiLOG with (nolock)
GROUP BY process, datepart(hh,Timestamp)
)
SELECT * FROM Processes PIVOT(SUM(requests) FOR Hour IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])) AS Result
ORDER BY process


we have the expected result:

Process   0   1   2 ... 21  22  23

Process1  0  10  23      4   5  21
Process2 12  11  15     22  17   8
Process3  9   7   2     19   9  13

Note: PIVOT() statement works only with agregate functions (like COUNT, SUM, MAX, AVG, …).


Another interesting thing, is:


WITH Processes( process, requests, hour) AS(...)


that Specifies a temporary named result set, known as a common table expression (CTE).


Happy codding ;)


Extra ball: for this case I also thought of using CUBE, that is other summarizing tool provided by T-SQL.

Truco rápido: Histórico de cambios en una tabla usando un trigger

No me voy a enrollar explicando que es un trigger ni para que sirve, tenéis información de sobra en este link: CREATE TRIGGER (Transact-SQL)

Lo que voy a explicar aquí es como usar un trigger para que nos guarde en una tabla de históricos los cambios (INSERT y UPDATE) que se producen en una tabla. Para ello supongamos que tenemos una tabla con un identificador (campo Id) y un nombre (campo nombre), y queremos almacenar en la tabla Historico (Id,nombre_antiguo,nombre_nuevo), los cambios que se van produciendo.

Dado que se pueden ejecutar updates sobre varios registros a la vez, será necesario que nuestro trigger obtenga todos los cambios a la vez y los procese uno a uno, eso lo haremos con un cursor.

El truco está en cómo distinguiremos que se trata de un INSERT o de un UPDATE, es muy sencillo, en caso de ser un INSERT el motor de base de datos almacena un registro en la tabla temporal "INSERTED", y en el caso de un update, habrá 2 registros: uno en la tabla "DELETED" y otro en "INSERTED", es como si para actualizarlo, primero se borrase y después se volviese a insertar, cosas del SQL Server :P

Vamos con el código:

CREATE TRIGGER [dbo].[prueba_insert_update]
   ON  [dbo].[Pruebas]
 AFTER INSERT, UPDATE
AS 
BEGIN

 SET NOCOUNT ON;


 -- Identificador del registro
 DECLARE @Id int 


 -- Variable para almacenar el nombre anterior
 DECLARE @nombre_antiguo varchar(20) 

 
 -- Declaramos el cursor "Cursorito" para que contenga únicamente
 -- los ID de la tabla temporal "INSERTED"
 DECLARE Cursorito CURSOR FORWARD_ONLY 
 FOR 
  SELECT Id FROM INSERTED


 -- Abrimos el cursor
 OPEN Cursorito 


 -- Movemos el puntero al primer registro del cursor
 FETCH NEXT FROM Cursorito into @Id


 -- Recorremos el cursor, cuando @@Fetch_Status sea diferente de 0
 -- habremos llegado al final
 WHILE @@Fetch_Status = 0 
 BEGIN

   
  SET @nombre_antiguo = ''
  

  -- Buscamos en la tabla temporal "DELETED" si hay un registro con 
  -- el Id que toca en esta pasada, lo que significaría que 
  -- se ha producido un UPDATE
  SELECT @nombre_antiguo = ISNULL(nombre,'')
  FROM DELETED 
   WHERE Id = @Id


  -- Si no hay ningún registro que cumpla la condición la 
  -- variable @nombre_antiguo contendrá '' y se tratará 
  -- de una inserción
  

  -- Añadimos un registro en la tabla de históricos
  INSERT INTO Historico
   (Id,nombre_antiguo,nombre_nuevo) 
  SELECT  @Id, @nombre_antiguo, nombre    
   FROM INSERTED 
   WHERE Id = @Id
  

  -- Nos movemos al siguiente registro
  FETCH NEXT FROM Cursorito into @Id

 END


 -- Cerramos el cursor y liberamos el recurso
 CLOSE Cursorito
 DEALLOCATE Cursorito 

END

Happy coding ;)

Buscar en campos de texto con T-SQL: mucho más que LIKE

Una de las primeras cosas que se aprende cuando se empieza con T-SQL es a usar la cláusula LIKE para hacer comparaciones en campos de tipo texto (char, varchar o text). Pero esta cláusula está bastante limitada y no ofrece un buen rendimiento, así que ¿qué podemos utilizar en vez de LIKE?

Una muy buena opción es CONTAINS (que para ser sincero, descubrí de casualidad :P), que nos permite afinar bastante más las comparaciones y ofrece mucho mejor rendimiento.

Para poner un ejemplo del uso de CONTAINS voy a usar la base de datos PUBS (que ofrece Microsoft como ejemplo con SQL Server y se puede descargar desde aquí) y vamos a buscar registros en la tabla jobs filtrando por el campo job_desc.

Para que os hagáis una idea (y si no os apetece descargaros las bases de datos de ejemplo) la tabla jobs tiene los siguientes registros:

job_id job_desc                                            min_lvl  max_lvl
--------  --------------------------------------------------  -------  -------
1       New Hire - Job not specified                        10       10
2       Chief Executive Officer                             200      250
3       Business Operations Manager                    175      225
4       Chief Financial Officier                            175      250
5       Publisher                                           150      250
6       Managing Editor                                     140      225
7       Marketing Manager                                   120      200
8       Public Relations Manager                            100      175
9       Acquisitions Manager                                75       175
10      Productions Manager                                 75       165
11      Operations Manager                                  75       150
12      Editor                                              25       100
13      Sales Representative                                25       100
14      Designer                                            25       100

Para poder usar CONTAINS primero debemos crear un índice FULLTEXT en la tabla, que se hace con las siguientes instrucciones:

CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON Jobs(job_desc)
   KEY INDEX PK__jobs__173876EA
GO

Siendo PK__jobs__173876EA el índice principal (PRIMARY KEY) de la tabla.

Una vez hecho esto podemos empezar con los ejemplos:

Buscar palabra exacta

Obtendremos todos los registros que contengas ‘manager’ en el campo job_desc. Fijaros que no distingue mayúsculas y minúsculas:

select * from jobs where CONTAINS( job_desc, 'manager' )

job_id job_desc                                            min_lvl  max_lvl
-------- --------------------------------------------------  -------  -------
3      Business Operations Manager                         175      225
7      Marketing Manager                                   120      200
8      Public Relations Manager                            100      175
9      Acquisitions Manager                                75       175
10     Productions Manager                                 75       165
11     Operations Manager                                  75       150

Además se pueden buscar frases (más de 1 palabra) y en las búsquedas se omiten los separadores.

select * from jobs where CONTAINS (job_desc, '"hire job"');
job_id job_desc                                            min_lvl  max_lvl
-------- --------------------------------------------------  -------  -------
1      New Hire - Job not specified                        10       10

Buscar por aproximación con varias opciones

Buscamos los registros que contengan alguna palabra que empiece por ‘pub’ o por ‘edit’.

select * from jobs where CONTAINS(job_desc, '"pub*" OR "edit*"');

job_id job_desc                                            min_lvl  max_lvl
-------- --------------------------------------------------  ------- -------
5      Publisher                                            150      250
6      Managing Editor                                      140      225
8      Public Relations Manager                             100      175
12     Editor                                               25       100

CONTAINS admite los operadores AND, AND NOT, OR.

Buscar palabra aproximada cerca de otra

Obtenemos registros que contengan una palabra que empiece por ‘pub’ y que esa palabra esté cerca de ‘manager’.

select * from jobs where CONTAINS (job_desc, '"pub*" NEAR manager')

job_id job_desc                                            min_lvl  max_lvl
-------- --------------------------------------------------  -------  -------
8      Public Relations Manager                            100      175

CONTAINS permite además buscar por palabras derivadas y sinónimos, pero para eso es necesario usar un diccionario de sinónimos.

Happy codding ;)

Bola extra
Además de CONTAINS tenemos otras opciones para este tipo de búsquedas como FREETEXT
y CONTAINSTABLE

3 formas de obtener la versión de SQL Server desde T-SQL

En un mundo perfecto todas nuestras instalaciones tendrían las mismas versiones de las aplicaciones, motores de bases de datos y librerías auxiliares, pero como este mundo es una utopía, a veces tenemos que averiguar con qué versión del motor de base de datos está trabajando la aplicación.

En el caso de SQL Server he descubierto 3 formas de averiguar la versión desde T-SQL, con cualquiera obtendremos la versión completa pero en la mayoría de los casos únicamente nos interesará la major release para ejecutar o no características de las nuevas versiones o que están obsoletas en la última versión pero siguen funcionando en versiones antiguas.

Vamos con las 3 opciones:

@@VERSION
http://msdn.microsoft.com/es-es/library/ms177512.aspx

Como es una variable de sistema sirve para cualquier versión de SQL Server, pero a partir de la versión 2005 devuelve un nvarchar en vez del varchar de las versiones anteriores.

declare @version char(2)

select @version = substring(@@version, charindex('- ', @@version) + 2, charindex('.',@@version) - charindex('- ', @@version)-2)

SERVERPROPERTY(‘ProductVersion’)

http://msdn.microsoft.com/es-es/library/ms174396.aspx

Esta opción sólo está disponible a partir de SQL Server 2000 y devuelve un valor de tipo sqlvariant, por lo que habrá que convertirlo a varchar o nvarchar para sacar la versión principal.

declare @productversion varchar(50)

declare @version char(2)

select @productversion = CAST(SERVERPROPERTY('productversion') AS VARCHAR(50))

set @version = SUBSTRING(@productversion,1,CHARINDEX('.',@productversion)-1)

xp_msver ‘ProductVersion’

http://technet.microsoft.com/es-es/library/ms187372.aspx

Quizás sea el método más complicado porque devuleve un conjunto de resultados con la siguiente forma:

Index Name Internal_Value Character_Value
------ -------------------------------- -------------- ----------------------
2 ProductVersion 589824 9.00.1406.00

(1 filas afectadas)

Pero también se puede obtener la versión con algo como esto:

CREATE TABLE #VersionTable(
      [Index] int,
      Name varchar(30),
      Internal_Value int,
      Character_Value varchar(50))
GO

INSERT INTO #VersionTable
EXEC master..xp_msver 'ProductVersion'

DECLARE @Version char(2)

SELECT @Version = (
SELECT SUBSTRING(Character_Value,1,CHARINDEX('.', Character_Value)-1)
FROM #VersionTable)

drop table #VersionTable

Con cualquiera de las 3 opciones obtendremos lo siguiente en la variable @Version:

‘7’ -> SQL Server 7
‘8’ -> SQL Server 2000 y MSDE
‘9’ -> SQL Server 2005
‘10’ -> SQL Server 2008

Happy coding ;)

Truco rápido: todos los mensajes de error de SQL Server a mano

Cuando trabajas con SQL Server estás acostumbrado a que los mensajes de error que aparecen en el fichero ERRORLOG o en el visor de sucesos sean bastante crípticos y lo normal es acabar tirando de [ponga aquí su buscador favorito] para saber de qué se trata.

Pues bien, hace tiempo que descubrí ,dentro de la enormidad que son los libros en pantalla de SQL Server, una página con los códigos de error organizados por número, y desde entonces es mi primera opción a la hora de buscar, así que aquí la tenéis:

http://technet.microsoft.com/es-es/library/cc645603.aspx (en castellano)

http://technet.microsoft.com/en-us/library/cc645603.aspx (en inglés)


Espero que os sirva de ayuda ;)

Obtener instancias de SQL Server y bases de datos disponibles desde .NET (VB.NET)

Aquí os dejo el código en VB.NET del formulario con los orígenes de datos de SQL Server disponibles, vamos, lo mismo que este ejemplo pero para los que no os gusta el ";" :P

Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class frmConexiones
 Private servidores As SqlDataSourceEnumerator
 Private tablaServidores As DataTable
 Private servidor As String


 Public Sub New()

  ' Llamada necesaria para el Diseñador de Windows Forms.
  InitializeComponent()

  ' Agregue cualquier inicialización después de la llamada a InitializeComponent().
  servidores = SqlDataSourceEnumerator.Instance
  tablaServidores = New DataTable()
 End Sub



 Private Sub cmbServidores_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbServidores.Click


  ' Comprobamos que no se haya cargado ya el combobox
  If tablaServidores.Rows.Count = 0 Then
   ' Obtenemos un dataTable con la información sobre
   ' las instancias disponibles de SQL Server 2000 y 2005
   tablaServidores = servidores.GetDataSources()

   ' Creamos una lista para que sea el origen de datos
   ' del combobox
   Dim listaServidores As List(Of String) = New List(Of String)

   ' Recorremos el dataTable y añadimos un valor nuevo la
   ' lista con cada fila
   For Each rowServidor As DataRow In tablaServidores.Rows

    ' La instancia de SQL Server puede tener nombre
    ' de instancia o únicamente el nombre del servidor,
    ' comprobamos si hay nombre de instancia para
    ' mostrarlo
    If String.IsNullOrEmpty(rowServidor("InstanceName").ToString()) Then
     listaServidores.Add(rowServidor("ServerName").ToString())
    Else
     listaServidores.Add(rowServidor("ServerName") & "\\" & rowServidor("InstanceName"))
    End If

   Next

   'Asignamos la lista de servidores como origen de datos
   ' del combobox
   Me.cmbServidores.DataSource = listaServidores
  End If

 End Sub


 Private Sub cmbBasesdeDatos_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbBasesdeDatos.Click

  Dim listaBasesDatos As List(Of String) = New List(Of String)
  Dim cadenaConexion As String
  Dim selectSQL As String

  ' Se comprueba que haya un servidor seleccionado para
  ' poder conectarnos
  If Me.cmbServidores.Text = "" Then
   MsgBox("Debe seleccionar un servidor")
   Return
  End If

  servidor = Me.cmbServidores.Text

  'Componemos la cadena de conexión con el servidor seleccionado y
  ' seguridad integrada
  ' si la autenticación se hace con usuario y password hay
  ' que cambiar la cadena
  cadenaConexion = "Data Source=" & servidor & " ;Integrated Security=True;Initial Catalog=master"


  Using con As New SqlConnection(cadenaConexion)

   ' Abrimos la conexión
   con.Open()

   'Obtenemos los nombres de las bases de datos que
   ' haya en el servidor
   selectSQL = "select name from sys.databases;"

   Dim com As SqlCommand = New SqlCommand(selectSQL, con)
   Dim dr As SqlDataReader = com.ExecuteReader()

   ' Recorremos el dataReader
   While (dr.Read())
    listaBasesDatos.Add(dr(0).ToString())
   End While

   'Asignamos la lista de bases de datos como origen
                        'de datos del combobox
   Me.cmbBasesdeDatos.DataSource = listaBasesDatos


  End Using

 End Sub

End Class

Happy codding ;)

Obtener instancias de SQL Server y bases de datos disponibles desde .NET (C#)

Por si alguna vez se os ha planteado la necesidad de mostrar un formulario con los orígenes de datos de SQL Server disponibles (algo como el formulario Agregar conexión de Visual Studio) voy a plantear un pequeño ejemplo.

Creamos un nuevo proyecto de Windows Forms al que llamaremos Conexiones, y en él un formulario frmConexiones en el que añadimos 2 combobox: cmbServidores y cmbBasesdeDatos (esta vez me estoy currando los nombres, no os quejaréis :P).

En el código del formulario añadimos lo siguiente:

using System;
using System.Windows.Forms;
using System.Data;
using System.Data.Sql;
using System.Collections.Generic;

namespace Conexiones
{
      public partial class frmConexiones : Form
      {
            SqlDataSourceEnumerator servidores;
            System.Data.DataTable tablaServidores;
            String servidor;

            public frmConexiones()
            {
                  InitializeComponent();
                  servidores = SqlDataSourceEnumerator.Instance;
                  tablaServidores = new DataTable();
            }
      }
}

Ahora hay que añadir el código para el evento click de cada uno de los combobox:
Para obtener la lista de instancias de SQL Server disponibles usaremos el objeto SqlDataSourceEnumerator.Instance, concretamente el método GetDataSources() que nos devuelve un DataTable con las características de las instancias: nombre del servidor, nombre de instancia, si forma parte de un clúster y la versión (8.00.x para SQL Server 2000 y 9.00.x para SQL Server 2005):

private void cmbServidores_Click(object sender, EventArgs e)
{

      // Comprobamos que no se haya cargado ya el combobox
      if (tablaServidores.Rows.Count == 0)
      {
      // Obtenemos un dataTable con la información sobre las instancias visibles
      // de SQL Server 2000 y 2005
      tablaServidores = servidores.GetDataSources();


      // Creamos una lista para que sea el origen de datos del combobox
      List listaServidores = new List();


      // Recorremos el dataTable y añadimos un valor nuevo a la lista con cada fila
      foreach (DataRow rowServidor in tablaServidores.Rows)
      {

            // La instancia de SQL Server puede tener nombre de instancia 
            //o únicamente el nombre del servidor, comprobamos si hay 
            //nombre de instancia para mostrarlo
            if (String.IsNullOrEmpty(rowServidor["InstanceName"].ToString()))
                  listaServidores.Add(rowServidor["ServerName"].ToString());
            else
                  listaServidores.Add(rowServidor["ServerName"] + "\\" + rowServidor["InstanceName"]);
       }

      // Asignamos al origen de datos del combobox la lista con 
      // las instancias de servidores
      this.cmbServidores.DataSource = listaServidores;
      }
}

Y para el que muestra las bases de datos tenemos que ejecutar una consulta sobre la vista sys.databases:

private void cmbBasesdeDatos_Click(object sender, EventArgs e)
{
      string select;
      string cadenaConexion;
      List listaBasesdatos = new List();

      this.servidor = this.cmbServidores.Text;

      // Componemos la cadena de conexión con el servidor seleccionado 
      // con seguridad integrada
      // Si se conecta con usuario de SQL Server hay que cambiar 
      // la cadena de conexión
      cadenaConexion = "Data Source=" + this.servidor + ";Integrated Security=True;Initial Catalog=master";
               
      using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(cadenaConexion))
      {
            // Abrimos la conexión
            con.Open();

            // Obtenemos los nombres de las bases de datos que haya en el servidor
            // se pueden filtrar para no mostrar las bases de datos de sistema
            select = "select name from sys.databases;";

            // Obtenemos un dataReader con el resultado
            System.Data.SqlClient.SqlCommand com = 
                         new System.Data.SqlClient.SqlCommand(select, con);
            System.Data.SqlClient.SqlDataReader dr = com.ExecuteReader();

            // Recorremos el dataReader y añadimos un elemento nuevo 
            // por cada registro
            while (dr.Read())
            {
                  listaBasesdatos.Add(dr[0].ToString());
            }                         
               
            // Asignamos la lista de bases de datos como origen de datos del combobox
            this.cmbBasesdeDatos.DataSource = listaBasesdatos;
      }
}

Y ya está, con esto tenemos un formulario que permite seleccionar la instancia de SQL Server y la base de datos a la que nos queremos conectar :)

Si en vez de seguridad integrada usamos un usuario y password de SQL Server sólo hay que cambiar la cadena de conexión.

Happy codding ;)

PD: Si no os gusta C# tenéis el mismo código en VB.NET aquí.

Pasar filas a columnas con T-SQL: PIVOT() y las cosas que te alegran el día :)

English version

A veces las cosas que parecen complicadas tienen una solución muy fácil pero que no siempre es la primera que se nos ocurre (no es exactamente el principio de la navaja de Ockham, pero se le parece).

Veamos, el caso es que hay una tabla de logs en la que varios procesos van insertando el resultado de su paso por diferentes estados, algo como esto:

Proceso Estado Timestamp

Proceso1  10   2008/12/21 12:22:05.320
Proceso2  10   2008/12/21 12:22:05.358
Proceso1  11   2008/12/21 12:22:06.210
Proceso1  12   2008/12/21 12:22:06.588
Proceso2  11   2008/12/21 12:22:10.100
Proceso2  12   2008/12/21 12:22:10.048
Proceso3  10   2008/12/21 14:30:05.358
Proceso3  11   2008/12/21 14:30:20.052

Para poder hacer un seguimiento de los procesos y así identificar problemas de rendimiento en ciertos momentos: entran muchos procesos a la vez, se hacen copias de seguridad, el servidor se queda sin memoria, ... sacamos una consulta agrupando por proceso y hora.

SELECT proceso, count(*) as peticiones, datepart(hh,Timestamp) as hora
FROM MiLOG with (nolock)
GROUP BY proceso, datepart(hh,Timestamp)
ORDER BY 1,3

Con esta consulta teníamos un resultado parecido a este:

Proceso    Peticiones Hora
Proceso1   10         12
Proceso1   2          13
Proceso2   5          12
Proceso2   3          13
Proceso3   25         14
Proceso3   4          15

Pero claro, esta salida es un poco complicada de tratar porque aunque estén agrupados por el proceso y la hora cuando hay muchos datos se vuelve muy farragoso comprobarlos, y acabamos llevando los resultados a una hoja de cálculo para que se viesen más claro. Entonces me pregunté, ¿no habrá una forma de hacerlo desde T-SQL para que me convierta las filas en columnas? Vamos, que haya una columna por cada hora y una única fila para cada proceso.

Como siempre la primera solución en una consulta complicada es una sub-consulta (no sé porqué pero es así, alguien tendría que estudiarlo :P) pero en este caso no se trataba de una sub-consulta, sino más bien de 24 sub-consultas, todas atacando a la misma tabla y todas usando la función datepart(hh,Timestamp). Seguro que con eso obtendríamos en resultado esperado, pero a costa de un rendimiento muy muy pobre.

Como esta situación es muy común tenía que haber una forma fácil que solucionarlo, así que en una primera búsqueda de Google no me sorprendió los primeros resultados mostrasen la misma solución de las sub-consultas (por el principio de que la primera solución en una consulta complicada es una sub-consulta :P) ya me veía haciendo una mega-consulta con sub-consultas, JOINs sobre la misma tabla y demás, pero ya en la primera página de resultados encontré una solución mucho más simple: la cláusula PIVOT (que es una novedad SQL Server 2005, así que no lo intentéis con versiones anteriores).

Así que con una consulta como esta:

WITH Procesos( proceso, peticiones, hora)
AS(
SELECT proceso, count(*) as peticiones, datepart(hh,Timestamp) as hora
FROM MiLOG with (nolock)
GROUP BY proceso, datepart(hh,Timestamp)
)
SELECT * FROM Procesos PIVOT(SUM(peticiones) FOR Hora IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])) AS Resultado
order by proceso

sí que tenemos el resultado esperado:

Proceso   0   1   2 ... 21  22  23

Proceso1  0  10  23      4   5  21
Proceso2 12  11  15     22  17   8
Proceso3  9   7   2     19   9  13

Como curiosidad decir que la cláusula PIVOT() únicamente acepta funciones de agregado (COUNT, SUM, MAX, AVG, ...), lo cual es totalmente lógico.

Otra cosa, igual os extraña ese

WITH Procesos( proceso, peticiones, hora) AS(...)

se trata de una expresión de tabla común (CTE) que es una forma de especificar un conjunto de resultados temporal con un nombre que puede ser usado en otras consultas.

Bueno, para empezar el año creo que está bien :D

Happy codding ;)



Bola extra: después de pensar en las sub-consultas y antes de descubrir la cláusula PIVOT, pensé en usar CUBE, que también podía servir, pero no era exactamente lo que buscaba, aquí tenéis un muy buen tutorial sobre CUBE.

Try ...Catch y RAISERROR en T-SQL: ¿es un bug o una feature?

No voy a entrar a explicar cómo se usan los bloques Try...Catch en T-SQL, que para eso ya está el MSDN: http://msdn.microsoft.com/es-es/library/ms179296(SQL.90).aspx

si no que os voy a contar un caso curioso que nos ha pasado estos últimos días:

Tenemos una aplicación en VB 6.0 (sí, tiene que ser en VB 6.0 por narices, así que es lo que hay) que tiene que llamar a store procedures de una base de datos SQL Server 2005 usando ADO (sí, el clásico no el .NET), bueno, el caso es que en algunos store procedures tenemos algo parecido a esto:

CREATE PROCEDURE Mi_Procedure
AS
BEGIN
BEGIN TRY
                ... Código antes del RAISERROR
                RAISERROR(...) -- Este RAISERROR provoca que se vaya al bloque CATCH
                ... Código después del RAISERROR que no se ejecuta   
END TRY
BEGIN CATCH
                Exec Otro_Procedure @parametro1, @parametro2
                RAISERROR(...)  -- Este RAISERROR debería propagar el error al que ha llamado al store procedure
END CATCH
END

Bien, es algo bastante básico, pero lo curioso del tema es que tal y como está, desde ADO no se captura el error, ni se dispara un error interceptable ni está en la colección de errores de la conexión.

Investigando un poco descubrí que en Otro_Procedure que se llama desde aquí había una llamada a sp_executesql y esto provoca que por algún motivo se genere un entorno de ejecución diferente, lo que a su vez provoca que el ADO no capture el error.

¿Cuál puede ser la solución?
Teniendo en cuenta que no podíamos obviar la llamada a Otro_Procedure y tampoco a sp_executesql se me ocurrió la idea de darle la vuelta al bloque CATCH y hacer el RAISERROR antes de la llamada a Otro_Procedure. La idea parece un poco absurda si pensamos que el bloque TRY...CATCH funcionase como, por ejemplo, en C#, pero aquí viene el origen del post, el bloque TRY...CATCH en T-SQL te permite ejecutar cualquier instrucción después de hacer un RAISERROR.

O_o

¿¿¿ Eing???

Pues sí, la lógica dice que en un CATCH cuando hagas un RAISERROR este se propagará hacia arriba hasta llegar al llamador del procedure, pero la implementación en T-SQL hace que esto no sea así y que no se finalice la ejecución hasta llegar al END CATCH, con lo que después del RAISERROR podemos poner la/s instrucciones que queramos que se van a ejecutar.

Por supuesto, haciendo la misma llamada al store procedure desde .NET con ADO.NET siempre se intercepta el error, esté el RAISERROR del CATCH antes o después de la llamada a Otro_Procedure, con lo que +1 para ADO.NET :)

Happy coding ;)

Poison Messages en SQL Server Service Broker, ¿qué son y cómo evitarlos?

En varios proyectos en los que estoy trabajando estamos utilizando una característica que se introdujo en SQL Server 2005, Service Broker, SSB para los amigos. Brevemente diré que SSB proporciona una plataforma de comunicaciones basada en mensajes que forma parte del motor de base de datos, si queréis saber algo más tenéis información detallada aquí y aquí.

El caso es que cuando empezamos a hacer pruebas de nuestra aplicación nos encontramos que a veces se detenía el servicio sin motivo aparente, y buscando información nos encontramos con los poison messages.

¿Qué es un poison message?
Un poison message (traducido literalmente “mensaje envenenado”) es un mensaje en una cola que ha excedido el número máximo de intentos de ser recibido por una aplicación. Esta situación puede producirse, por ejemplo, cuando una aplicación lee un mensaje de una cola como parte de una transacción, pero debido a errores (error de conversión, duplicidad de clave,...) no puede procesarlo. Si la aplicación aborta la transacción en la que se recibió el mensaje, éste es automáticamente devuelto a la cola. Si el problema con el mensaje no es solucionado puede provocarse un bucle infinito, provocando un poison message.

Service Broker provee detección automática de poison messages que deshabilita la cola (y te aseguro que no hace ninguna gracia que te pase esto en producción) si una transacción que recibe mensajes de una cola hace rollback 5 veces. Si se llega a dar este caso, el mensaje debe ser eliminado por una aplicación diferente o por un administrador de forma manual.

La detección automática también genera un evento de tipo Broker: Queue Disabled, que puede ser interceptado por un procedimiento almacenado que reactive la cola (vuelva a establecer su STATUS a ON). Además se pueden usar las alertas de SQL Agent para notificar al administrador que la cola se ha desactivado para que actúe en consecuencia.

¿Cómo tratar los poison message?
Debido a que la detección automática de poison messages provoca la detención de la cola, y por tanto, de todos los procesos asociados a ella, las aplicaciones deberían, como parte de su lógica, detectar y sacar de las colas de forma segura los mensajes que sean susceptibles de convertirse en poison messages.

- Usar bloques TRY ... CATCH para capturar todos los errores. Comprobar si la transacción (en caso de que exista) se pueda confirmar con la función XACT_STATE().

- Cuando se detecte un error que potencialmente pueda provocar un poison message, asegurarnos de que el mensaje ha sido sacado de la cola y finalizar la conversación con END CONVERSATION ... WITH ERROR.

- También es muy recomendable guardar el mensaje que provocó el poison message para su posterior tratamiento (podemos corregirlo y volver a introducirlo en el ciclo normal) y guardarnos un log.

Un control de poison messages podría ser algo así:

BEGIN TRY
    BEGIN TRANSACTION
    RECEIVE
    .
    .
    .  
    COMMIT TRANSACTION
END TRY

BEGIN CATCH
DECLARE @state INT;
    SET @state = XACT_STATE();
    SELECT @error = ERROR_NUMBER(), @error_message  = ERROR_MESSAGE();

    IF @state = 1 -- Hay transacción de usuario activa y se puede confirmar
    BEGIN
          -- Se finaliza la conversación con error
          END CONVERSATION @conversation_handle
WITH ERROR = @error description = @error_message;
          COMMIT;
    END
    ELSE IF @state = -1
    -- Hay transacción de usuario activa y no se puede confirmar, sólo revertir
    BEGIN
          ROLLBACK TRANSACTION; -- Se revierte la transacción     
       
          -- Se saca de la cola el mensaje y se finaliza la conversación con error
          BEGIN TRANSACTION;
          RECEIVE TOP(1) @msgBody = message_body
                FROM [queue]
                WHERE conversation_handle = @conversation_handle;
          END CONVERSATION @conversation_handle
WITH ERROR = @error description = @error_message;
          COMMIT;
    END
END CATCH

He de reconocer que no es una solución perfecta pero por ahora es lo mejor que he encontrado y funciona bastante bien. En todo caso os recomiendo el blog de Remus Rusanu para profundizar en el uso de Service Broker.

Bola extra

Por si a alguien le interesa, buscando información sobre los poison messages en Service Broker me encontré que MSMQ realiza un tratamiento parecido, tenéis más información en esta página.

Edito: (08/09/08) hasta hoy no me di cuenta que lo de Bola Extra es una copia descarada, digo ... homenaje a los camaradas de Halon disparado, si es que de lo que se come se cría :p