Fent coses he necessitat comptar paraules en una frase. Aquesta frase és un camp en una BBDD d'SQL Server. Allò que et diuen: "talla la frase a partir de la 6 paraula". Doncs cal fer primer una funció d'usuari a SQL Server que compti quants espais ha saltat i et doni la posició de n-essim espai.

USE [LaTevaBBDD]
GO
/****** Object:  UserDefinedFunction [dbo].[CHARINDEX2]    Script Date: 02/07/2023 00:55:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Example:
SELECT dbo.CHARINDEX2('a', 'abbabba', 3)
returns the location of the third occurrence of 'a'
which is 7
*/
ALTER FUNCTION [dbo].[CHARINDEX2]
(
    @TargetStr varchar(8000), 
    @SearchedStr varchar(8000), 
    @Occurrence int
)
RETURNS int
as
begin

	declare @pos int, @counter int, @ret int
	set @pos = CHARINDEX(@TargetStr, @SearchedStr)
	set @counter = 1

	if @Occurrence = 1 set @ret = @pos

	else
	begin
		while (@counter < @Occurrence)
		begin
			select @ret = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1)
			set @counter = @counter + 1
			if @ret <> 0 set @pos = @ret
		end
	end	
		
	RETURN(@ret)		
end

La gracia d'aquesta versió vs. la que circula per Internet, es que si tu demanes la 8 posició i només té 6, retorna 0 enlloc d'un número aleatori. Un cop feta la funció cal posar aquesta sentència:

update UnaTaula set UnCamp=LEFT(UnCamp, dbo.CHARINDEX2(' ', UnCamp, 9)-1) WHERE dbo.CHARINDEX2(' ', UnCamp, 9)>0

Ja posats, si fem tractament de strings en SQLServer el REPLACE va força bé, però si es vol fer substitucions que estiguin a l'inici de la cadena de text:

update UnaTaula set UnCamp=SUBSTRING(UnCamp, 4, LEN(UnCamp)-3) where LEFT(UnCamp, 3)='un '

Feia temps que no trobava coses noves d'SQLServer per fer. Yummi!

A SQL Server es pot fer un UPDATE d'un camp de tipus text, char, nchar, nvarchar, nchar etc... amb la funció REPLACE. Té els típics 3 paràmetres: On buscar, que buscar i el nou text a posar. Però... sembla que REPLACE no funciona amb camps text, ntext (WTF! Si són texts!). Almenys en el SQL Server 2016. He mirat i no sembla que ho hagin millorat en versions superiors. Molt "Bussiness inteligence" però rascar coses bàsiques costa. En finssss. Però hi ha un forma de fer aquests UPDATES, fent servir el CAST, es a dir convertint el tipus de dada a un que sí li agradi al REPLACE.

UPDATE [tblPaginas] 
SET [Contenido] = CAST(REPLACE(CAST([Contenido] as NVarchar(MAX)),'TEXT QUE BUSQUES I VOLS MODIFICAR','NOU TEXT') AS NText)
WHERE [Contenido] LIKE '%ALGUNA CONDICIO%'

Es a dir cal fer un CAST d'anada per posar el text a nvarchar(MAX) i un altra de tornada. I arreglat!

Depenent del projecte on estem a vegades no cal muntar classes d'accés a dades, capes i capes de software inútil per executar una mera consulta. També cal valorar la mantenibilitat, allò de que les capes afavoreixen el manteniment, jo sempre he trobat que entorpeixen el manteniment... Però sí que crec necessari que les consultes, encara que siguin un string en el codi, siguin gestionades de forma centralitzada. És en aquest punt quan va sorgir la clsSQLServer. D'això ja fa 5 o més anys... però no se per que no l'he posat al blog fins ara. Bé de fet, l'he ampliat fa pocs dies, portava des del 2013 sense modificacions.

Aquesta classe com el seu nom indica gestiona les consultes al servidor de base de dades. De fet no se com Microsoft no l'han fet ells mateixos. Vull dir que com programador el que vull és executar una sentència SQL i tenir una taula de retorn, sense preocupar-me de si és un Adapter o un Command o X, però bé, ja que MS no s'inspira ho fet jo. Té uns quants mètodes principals i sobrecarregats:

• Exec: Executa un consulta SQL i retorna una taula. Es pot escollir entre no preocupar-se per la connexió, o sí, o afegir-hi una transacció.
• ExecInsertWithIdentity: Executa un INSERT contra una taula que tingui una clau primària (PK) de tipus identitat. I un cop fet l'INSERT retorna el valor de la PK que ha creat com un long.
• ExecNonQuery: Serveix per executar consultes que no retornen res, tipus UPDATEs, DELETEs o INSERTs que no cal recuperar la PK. Retorna els registres afectats per la consulta.
• ExecScalar: Serveix per executar consultes que retornen un valor sencer, com un màxim, un mínim, un COUNT etc... De moment no m'ha fet falta un altra tipus de dada en el retorn.
• FDateSQL: Aquesta és una utilitat per treballar amb dates en SQL Server. Retorna la data en un string en format ISO 8601: yyyy-mm-ddThh:mi:ss. Aquest format el bo que té és que no depèn dels regional settings.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Configuration;

namespace Viewer
{
    public class clsSQLServer
    {
        public clsSQLServer()
        {
            Conn = null;
        }

        public SqlConnection Conn { get; set; }

        private string _Ver = "07/03/2018";
        public string Ver
        {
            get
            {
                return _Ver;
            }
        }

        public string CadConn { get; set; }

        public string FDateSQL(DateTime D)
        {
            //ISO 8601: yyyy-mm-ddThh:mi:ss
            return D.ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss");
        }

        /// <summary>
        /// Executa una SQL que retorna resultats contra una conexió, no modifica l'estat de la conexió ni de la transaccio
        /// </summary>
        /// <param name="SQL">Sentència SQL a executar</param>
        /// <param name="SqlConn">Conexió a BBDD, ha d'estar oberta</param>
        /// <param name="SqlTrann">Transacció ja creada sobre la conexió (admet null)</param>
        /// <returns>Retorna un NEW DataTable</returns>
        public DataTable Exec(string SQL, SqlConnection SqlConn, SqlTransaction SqlTrann)
        {
            DataTable TBL = new DataTable();
            SqlCommand SqlComm = new SqlCommand();
            SqlDataReader SqlReader;

            SqlComm.Connection = SqlConn;
            if (SqlTrann != null) SqlComm.Transaction = SqlTrann;
            SqlComm.CommandText = SQL;
            SqlComm.CommandType = CommandType.Text;

            SqlReader = SqlComm.ExecuteReader();

            TBL.Load(SqlReader);
            return TBL;
        }

        /// <summary>
        /// Executa una SQL que retorna resultats contra una conexió, no modifica l'estat de la conexió
        /// </summary>
        /// <param name="SQL">Sentència SQL a executar</param>
        /// <param name="SqlConn">Conexió a BBDD, ha d'estar oberta</param>
        /// <returns>Retorna un NEW DataTable</returns>
        public DataTable Exec(string SQL, SqlConnection SqlConn)
        {
            return Exec(SQL, SqlConn, null);
        }

        /// <summary>
        /// Executa una sentència SQL que retorna resultats, si està informada la variable Conn la fa
		/// servir de conexio (ha d'estar oberta), si no crea una i la tanca
        /// </summary>
        /// <param name="SQL">Sentència SQL a executar</param>
        /// <returns>Retorna un NEW DataTable</returns>
        public DataTable Exec(string SQL)
        {
            DataTable TBL;
            SqlConnection SqlConn;
            bool Local = false;
            if (Conn != null)
            {
                SqlConn = Conn;
            }
            else
            {
                Local = true;
                SqlConn = new SqlConnection();
                SqlConn.ConnectionString = CadConn;
                SqlConn.Open();
            }
            TBL = Exec(SQL, SqlConn);
            if (Local) SqlConn.Close();
            return TBL;
        }

        /// <summary>
        /// Executa un INSERT en una taula que té PK identitat. Retorna la identitat creada. 
		/// Obre i tanca conexió a BBDD (la que retorna GetConnStr)
        /// </summary>
        /// <param name="SQL">Sentència SQL a executar</param>
        /// <returns>Identitat creada</returns>
        public long ExecInsertWithIdentity(string SQL)
        {
            long Identity = 0;
            SqlConnection SqlConn;
            bool Local = false;
            if (Conn != null)
            {
                SqlConn = Conn;
            }
            else
            {
                Local = true;
                SqlConn = new SqlConnection();
                SqlConn.ConnectionString = CadConn;
                SqlConn.Open();
            }
            Identity = ExecInsertWithIdentity(SQL, SqlConn);
            if (Local) SqlConn.Close();
            return Identity;
        }

        /// <summary>
        /// Executa un INSERT en una taula que té PK identitat. 
		/// Retorna la identitat creada. No modifica l'estat de la connexió
        /// </summary>
        /// <param name="SQL">Sentència SQL a executar</param>
        /// <param name="SqlConn">Conexió a BBDD, ha d'estar oberta</param>
        /// <returns>Identitat creada</returns>
        public long ExecInsertWithIdentity(string SQL, SqlConnection SqlConn)
        {
            long Identity = 0;

            Identity = ExecInsertWithIdentity(SQL, SqlConn, null);

            return Identity;
        }


        /// <summary>
        /// Executa un INSERT en una taula que té PK identitat. Retorna la identitat creada
        /// </summary>
        /// <param name="SQL">Sentència INSERT SQL a executar</param>
        /// <param name="SqlConn">Conexió a BBDD, ha d'estar oberta</param>
        /// <param name="SqlTrann">Transacció ja creada sobre la conexió (admet null)</param>
        /// <returns>Identitat creada</returns>
        public long ExecInsertWithIdentity(string SQL, SqlConnection SqlConn, SqlTransaction SqlTrann)
        {
            long Identity = 0;

            SqlCommand SqlComm = new SqlCommand();

            SqlComm.Connection = SqlConn;
            if (SqlTrann != null) SqlComm.Transaction = SqlTrann;

            if (!SQL.EndsWith(";")) SQL += ";";
            SQL += " SELECT SCOPE_IDENTITY();";

            SqlComm.CommandText = SQL;
            SqlComm.CommandType = CommandType.Text;

            Identity = (long)(decimal)SqlComm.ExecuteScalar();
            return Identity;
        }



        /// <summary>
        /// Executa una sentència SQL que no retorna resultats, només els registres afectats. 
		/// Obre i tanca conexió a BBDD (la que retorna GetConnStr)
        /// </summary>
        /// <param name="SQL">Sentència SQL a executar</param>
        /// <returns>Registres afectats</returns>
        public int ExecNonQuery(string SQL)
        {
            int Results = 0;
            SqlConnection SqlConn;
            bool Local = false;
            if (Conn != null)
            {
                SqlConn = Conn;
            }
            else
            {
                Local = true;
                SqlConn = new SqlConnection();
                SqlConn.ConnectionString = CadConn;
                SqlConn.Open();
            }
            Results = ExecNonQuery(SQL, SqlConn);
            if (Local) SqlConn.Close();
            return Results;
        }

        /// <summary>
        /// Executa una sentència SQL que no retorna resultats
        /// </summary>
        /// <param name="SQL">Sentència SQL a executar</param>
        /// <param name="SqlConn">Conexió a BBDD, ha d'estar oberta</param>
        /// <returns>Registres afectats</returns>
        public int ExecNonQuery(string SQL, SqlConnection SqlConn)
        {
            return ExecNonQuery(SQL, SqlConn, null);
        }

        /// <summary>
        /// Executa una sentència SQL que no retorna resultats
        /// </summary>
        /// <param name="SQL">Sentència SQL a executar</param>
        /// <param name="SqlConn">Conexió a BBDD, ha d'estar oberta</param>
        /// <param name="SqlTrann">Transacció ja creada sobre la conexió (admet null)</param>
        /// <returns>Registres afectats</returns>
        public int ExecNonQuery(string SQL, SqlConnection SqlConn, SqlTransaction SqlTrann)
        {
            int Results = 0;

            SqlCommand SqlComm = new SqlCommand();

            SqlComm.Connection = SqlConn;
            if (SqlTrann != null) SqlComm.Transaction = SqlTrann;
            SqlComm.CommandText = SQL;
            SqlComm.CommandType = CommandType.Text;

            Results = SqlComm.ExecuteNonQuery();
            return Results;
        }

        /// <summary>
        /// Executa una sentència SQL que no retorna resultats, només la primera columna del primer registre.
		/// Obre i tanca conexió a BBDD (la que retorna GetConnStr)
        /// </summary>
        /// <param name="SQL">Sentència SQL a executar</param>
        /// <returns>Registres afectats</returns>
        public int ExecScalar(string SQL)
        {
            int Results = 0;
            SqlConnection SqlConn;
            bool Local = false;
            if (Conn != null)
            {
                SqlConn = Conn;
            }
            else
            {
                Local = true;
                SqlConn = new SqlConnection();
                SqlConn.ConnectionString = CadConn;
                SqlConn.Open();
            }
            Results = ExecScalar(SQL, SqlConn);
            if (Local) SqlConn.Close();
            return Results;
        }

        /// <summary>
        /// Executa una sentència SQL que no retorna resultats, només la primera columna del primer registre.
        /// </summary>
        /// <param name="SQL">Sentència SQL a executar</param>
        /// <param name="SqlConn">Conexió a BBDD, ha d'estar oberta</param>
        /// <returns>Registres afectats</returns>
        public int ExecScalar(string SQL, SqlConnection SqlConn)
        {
            return ExecScalar(SQL, SqlConn, null);
        }

        /// <summary>
        /// Executa una sentència SQL que no retorna resultats, només la primera columna del primer registre.
        /// </summary>
        /// <param name="SQL">Sentència SQL a executar</param>
        /// <param name="SqlConn">Conexió a BBDD, ha d'estar oberta</param>
        /// <param name="SqlTrann">Transacció ja creada sobre la conexió (admet null)</param>
        /// <returns>Registres afectats</returns>
        public int ExecScalar(string SQL, SqlConnection SqlConn, SqlTransaction SqlTrann)
        {
            int Results = 0;
            object objResult;

            SqlCommand SqlComm = new SqlCommand();

            SqlComm.Connection = SqlConn;
            if (SqlTrann != null) SqlComm.Transaction = SqlTrann;
            SqlComm.CommandText = SQL;
            SqlComm.CommandType = CommandType.Text;

            objResult = SqlComm.ExecuteScalar();
            if (objResult != null && objResult != DBNull.Value)
            {
                Results = int.Parse(objResult.ToString());
            }
            return Results;
        }
    }
}

Recentment he tingut que crear un index per millorar el rendiment d'accés a una taula. El curiós és que el camp és únic a la taula. No és la "primary key" perque admet NULLs. De fet al ser un camp de nova creació té un munt de registres amb NULL i només uns pocs diferents de NULL.

Però el que interessa és que aquests registres que tenen un valor tots ells siguin diferents. De fet només m'interessa indexar aquests valors. Si s'intenta crear un index únic no deixa per que hi ha valors repetits, els NULL. És aquí on entren en joc els indexos filtrats. En la creació de l'index cal anar a "Filtro" i posar el filtre que exclogui els valors que no volem que formin part de l'index.

El SQL Server té instruccions de manteniment que a vegades triguen molt (per exemple ALTER INDEX ... REORGANIZE), i clar, apart d'un cercle que dóna voltes hi ha alguna forma de veure com evoluciona? I un cop es veu com evoluciona amb un Excel es poden fer 4 càlculs per saber quan acabarà.

La instrucció és aquesta:

SELECT percent_complete FROM sys.dm_exec_requests WHERE session_id = ZZ

Per veure les consultes en curs i obtenir el session_id que ens interessa es pot fer servir la consulta:

SELECT 
	   (SELECT TEXT FROM SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE)) AS CONSULTA,
	   S.SESSION_ID AS [ID SESIÓN], 
	   S.LOGIN_NAME AS [USUARIO],   
	   ISNULL(DB_NAME(R.DATABASE_ID), N'') AS [BASE DE DATOS], 
	   ISNULL(T.TASK_STATE, N'') AS ESTADO, 
	   ISNULL(R.COMMAND, N'') AS COMANDO, 
	   ISNULL(S.PROGRAM_NAME, N'') AS APLICACIÓN,
	   ISNULL(R.OPEN_TRANSACTION_COUNT, 0) AS TRANSACCIONES,  
	   ISNULL(CONVERT (VARCHAR, W.BLOCKING_SESSION_ID), '') AS [BLOQUEADO POR],
	   ISNULL(W.WAIT_TYPE, N'') AS [TIPO ESPERA],
	   W.WAIT_DURATION_MS AS [TIEMPO ESPERA MS],
	   S.LOGIN_TIME AS [INICIO SESIÓN]
	FROM SYS.DM_EXEC_SESSIONS AS S 
	LEFT OUTER JOIN SYS.DM_EXEC_CONNECTIONS AS C 
		ON (S.SESSION_ID = C.SESSION_ID)
	LEFT OUTER JOIN SYS.DM_EXEC_REQUESTS AS R 
		ON (S.SESSION_ID = R.SESSION_ID)
	LEFT OUTER JOIN SYS.DM_OS_TASKS AS T 
		ON (R.SESSION_ID = T.SESSION_ID 
			AND 
			R.REQUEST_ID = T.REQUEST_ID)
	LEFT OUTER JOIN 
	(
    
		SELECT *, 
		ROW_NUMBER() OVER (PARTITION BY WAITING_TASK_ADDRESS ORDER BY WAIT_DURATION_MS DESC) AS ROW_NUM
		FROM SYS.DM_OS_WAITING_TASKS 

	) AS W ON (T.TASK_ADDRESS = W.WAITING_TASK_ADDRESS) AND W.ROW_NUM = 1

	LEFT OUTER JOIN SYS.DM_EXEC_REQUESTS AS R2 ON (R.SESSION_ID = R2.BLOCKING_SESSION_ID)
	OUTER APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) AS ST

	WHERE S.SESSION_ID > 50 AND S.SESSION_ID NOT IN (@@SPID) AND T.TASK_STATE IS NOT NULL
	ORDER BY S.SESSION_ID;

Si s'han d'obtenir dades de les taules d'un SQL Server una opció que he trobat força còmode és fer servir la stored procedure de sistema: sys.sp_spaceused

EXEC sys.sp_spaceused @objname = N'dbo.NOM_DEL_OBJECTE'

És important la "N" perque indica que el paràmetre és Unicode, si no es posa no funciona bé. El resultat és algo com:

El resultat ofereix: número de files, espai reservat per la taula, espai ocupat per les dades, espai ocupat pels indexos, espai no utilitzat.

Per obtenir el número de camps que té una taula es pot fer servir (a TABLE_CATALOG posar el nom de la base de dades i a TABLE_NAME posar el nom de la taula):

SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE 
TABLE_CATALOG = 'database' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'table'

Resulta que cal passar una data com a paràmetre a una sentència SQL que està en una tasca "Execute SQL" d'un DTSX d'SQL Server. El tipus que funciona és el "DATE", no el DBTIMESTAMP, ni cap altra. El DBTIMESTAMP que es fa servir en el fluxe de dades aquí provoca un error del tipus:

Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "insert into dt values (?)" failed with the following error: "Invalid time format". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Cal posar els valors així:

Però això no és garantia de que funcioni sempre, ja que el DATE és un número en punt flotant que té menys rang de dates que un DT_DBTIMESTAMP. Però per representar dates que no siguin de molt, molt, molt del futur ja va bé.

Com a cosa curiosa normalment faig un ALTER TABLE ALTER COLUMN per modificar un camp, però i si s'ha de modificar el nom del camp? AAaaah...

EXEC sp_RENAME 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'

Curiós...

Fa uns dies m'han demanat que llisti els usuaris que s'han loginat al servidor d'SQL Server, de forma correcta o no.

El primer que cal fer és activar el log d'usuaris per casos correctes (per defecte només es registren els incorrectes).

Després cal obtenir la informació del log d'SQL, això es fa mitjançant la stored procedure xp_readerrorlog. Aquesta stored té els següents paràmetres:

• El fitxer de log que es vol llegir: 0 = actual, 1 = arxiu #1, 2 = arxiu #2, etc...
• El tipus de fitxer de log: 1 o NULL = error log, 2 = SQL Agent log
• String que vols buscar 1
• String que vols buscar 2, per refinar més la cerca
• Data d'inici
• Data de fi
• Ordenació dels resultats: N'asc' = ascending, N'desc' = descending

Aleshores es pot fer algo similar a:

DECLARE @tLog TABLE
(
   LogDate datetime,
   ProcessInfo nvarchar(50),	
   Value   nvarchar(max)
)
DECLARE @FechaActual datetime
SELECT @FechaActual=GETDATE()

INSERT INTO @tLog
   EXEC master.dbo.xp_readerrorlog 0, 1, N'Login', N'succeeded', '2015-03-26T00:00:00', @FechaActual, N'desc'

SELECT MIN(LogDate) AS FechaLogin, [VALUE] FROM @tLog GROUP BY [VALUE]

És molt important posar una "N" davant dels strings de cerca per que els converteix a Unicode, si no es posa no troba resultats. Quan recuperem la taula els valors tenen el nom de l'usuari entre cometes simples, es pot fer algo com:

Usuario = DR("VALUE").ToString()
//Treiem el text que hi ha abans i despres del nom de l'usuari. El nom de l'usuari va entre cometes simples
Inicio = Usuario.IndexOf("'") + 1
Fin = Usuario.IndexOf("'", Inicio)
Usuario = Usuario.Substring(Inicio, Fin - Inicio)

I llestos!

Per obtenir metadades del SQL Server s'han de fer consultes de les taules de sistema. Allí hi ha les descripcions de tota la BBDD. Per exemple per obtenir el nom de les taules:

SELECT name FROM sys.objects WHERE type='u' ORDER BY name

Seguint amb el tema de les stored procedures, per obtenir el nom, tipus, longituds etc... dels paràmetres de les storeds cal fer:

SELECT 
SO.name AS [ObjectName],
SO.Type_Desc AS [ObjectType],
P.name AS [ParameterName],
TYPE_NAME(P.user_type_id) AS [ParameterDataType],
P.max_length AS [ParameterMaxBytes],
P.precision AS [Precision],
P.scale AS [Scale],
P.is_output AS [IsOutPutParameter]
FROM sys.objects AS SO
INNER JOIN sys.parameters AS P
ON SO.OBJECT_ID = P.OBJECT_ID
WHERE SO.TYPE ='P'
ORDER BY SO.name, P.parameter_id

Amb aquesta informació es poden fer validacions en temps d'execució...