Nombres aleatoris T-Sql

Tornem a l'SQL Server.
Avui veurem com generar números aleatoris diferents en una consulta.

Si executem:
select o.object_id, rand() as random
from sys.objects o

Veiem que el valor de random sempre és el mateix per a tots els objectes, però nosaltres volem un valor diferent per a cadascun.

La funció Rand (https://docs.microsoft.com/en-us/sql/t-sql/functions/rand-transact-sql) ens permet posar un seed, per al mateix seed el valor aleatori sempre és el mateix.

Bot Telegram 2: cosetes útils

En el passat post vam veure com crear un bot de Telegram programat en python. Ara veurem algunes coses que ens poden ser útils.

A la funció start d'inici de bot se li passen 2 paràmetres: bot i update
Bot té les dades del bot:
Ex:
{'username': u'ElJordifaBIBot', 'first_name': u'ElJordifaBIBot', 'id': 515033793}

I update té els dades de l'usuari:
Ex:
{'message': {'delete_chat_photo': False, 'new_chat_photo': [], 'from': {'first_name': u'Jordi', 'is_bot': False, 'id': 353926538, 'language_code': u'es-ES'}, 'text': u'/start', 'caption_entities': [], 'entities': [{'length': 6, 'type': u'bot_command', 'offset': 0}], 'channel_chat_created': False, 'new_chat_members': [], 'supergroup_chat_created': False, 'chat': {'first_name': u'Jordi', 'type': u'private', 'id': 353926538}, 'photo': [], 'date': 1517151466, 'group_chat_created': False, 'message_id': 12, 'new_chat_member': None}, 'update_id': 541194784}

De l'update podem extrure informació molt útil com el:
    - update.message.chat.id: és l'id del chat, és sempre el mateix per usuari i ens permetrà identificar-lo. També ens permetrà enviar-li missatges al seu telegram. Per exemple, per respondre a una acció o bé per enviar un missatge en boradcast a tots els usuaris connectats.
    - update.message.chat.first_name: és el nom de l'usuari, per referir-nos a ell.
    etc.
   

Bot Telegram 1: primers pasos

Avui canviarem de tema: farem un bot de telegram.
I que te a veure això amb el BI? Doncs a priori no massa, però com que estarà fet amb python podem posar-li una BBDD i uns gràfics i ja ho podem donar per bo.

Com funciona un bot de telegram?

Telegram fa d'intermediari entre el nostre mòbil i una aplicació que ha d'estar corrent en un servidor. En el nostre cas l'aplicació estarà feta en Python i el servidor serà una Raspberry Pi. Utilitzarem també una BBDD en SQLite ja que per aquesta prova de concepte no fa falta grans recursos, però el servidor en comptes d'una Raspberry pdria ser molt més gros i el SGBD també podrem triar el que més ens agradi.

Telegram només fa de cua de missatgeria entre dispositius. S'ha de tenir en compte que l'espai que ens dedica Telegram per guardar els missatges que s'envien és limitat i pot ser que es perdin missatges.

Cursors SQL Server

Avui un post senzill. Com declarar i utilitzar un cursor en SQL Server controlant errors, evitant que es quedi memòria assignada, etc.

--Variable estat del cursor
declare @cur_status int

--variables on assignarem els resultats del cursor
declare @a int
-- ..
-- ..

-- Si el cursor no existeix el creem
SELECT @cur_status=CURSOR_STATUS('global','cur')

if @cur_status=-3 begin
    --declarem el cursor
    declare cur cursor for
    -- query SQL
    select 1 as a union all select 5

    --Si el cursor no està obert, l'obrim
    SELECT @cur_status=CURSOR_STATUS('global','cur')
    if @cur_status=-1
        OPEN cur

    -- assignem les variables al cursor
    FETCH cur INTO @a --,@b, ..., @z
    --Mentre hi hagi files al cursor
    WHILE (@@FETCH_STATUS = 0)
    BEGIN   
        -- codi a executar per cada fila del cursor
        print @a
        -- assignem les següents variables al cursor
        FETCH cur INTO @a --,@b, ..., @z
    END --final del bucle

    --Si el cursor està obert, el tanquem
    SELECT @cur_status=CURSOR_STATUS('global','cur')
    if @cur_status>=0
        close cur

    --Si el cursor està assignat, el desassignem per deixar net l'stack de variables
    SELECT @cur_status=CURSOR_STATUS('global','cur')
    if @cur_status<>-3
        deallocate cur

end
else
    print 'El cursor ja existeix '

Amb aquesta estructura d'script podem crear i utilitzar un cursor en SQL Server validant que no existeixi, obrint-lo només si fa falta i assegurant-nos que el tanquem i dessasignem per evitar futurs errors al reobrir-lo i evitant que es quedi memòria reservada que no utilitzem.

Windows authentication en SQL Server

Avui va un post no estrictament de SQL Server.
M'he trobat vegades que necessito entrar al SQL Server a través de Windows Authentication per que no estan activats, o no es volen activar, els logins de SQL Server, i la màquina des de la que vull entrar no està dins de domini.
Per aquests casos es pot executar la comanda runas de CMD.
Serveix tant per el SSMS, SSDT, Excel per connectar-nos a SSAS o MDS.

Les comandes a executar serien del tipus:
SSMS
runas /netonly /user:domini\usuari "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe"
SSDT
 runas /netonly /user:domini\usuari "C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\devenv.exe"

 
Un cop executada la comanda ens demanarà el password, i ja haurem entrat al programa autenticats amb l'usuari de Windows indicat.
S'ha de tenir en compte que l'usuari que apareix al SSMS segueix sent l'original en el que estem a la màquina, però internament té l'usuari que li hem indicat a la comanda.

 

Guardar SP d'SQL Server en una taula

A vegades ens trobem amb Stored Procedures d'SQL Server que ens retornen dades en format taula i ens agradaria poder guardar-los en una taula. La forma d'invocar un procediment i que el guardi en una taula és el següent:
insert into #la_nostra_taula EXEC sp_spaceused 'Taula'

La funció sp_spaceused és molt útil per que ens dóna informació sobre l'espai que ocupa una taula.

Però si volem saber aquesta informació de totes les taules hauríem d'invocar el procediment per cadascuna d'elles per separat. Si ho fem des del SSMS el resultat no és còmode de tractar.

En aquest cas serà útil invocar la funció per cada taula i guardar-lo en una taula final per després consultar-la.

Primer crearem una taula temporal per guardar les dades de la funció sp_spaceused. La funció sp_spaceused retorna varchars, però per nosaltres és més còmode tenir les dades en format nuèric per poder-les aggregar. Aleshores crearem una taula final amb els mateixos valors en format int.


create table #TaulaTemporalSpaceUSed (
    Name varchar(255),
    [rows] int,
    reserved varchar(255),
    data varchar(255),
    index_size varchar(255),
    unused varchar(255))
       
create table TaulaFinalMida (
    Name varchar(255),
    [rows] int,
    reservedKb int,
    dataKb int,
    reservedIndexSize int,
    reservedUnused int)
   
   
Per no haver d'executar la funció sp_spaceused per cada taula manualment executarem el procediment sp_MSforeachtable que recorre totes les taules de la BBDD on estem.
   
EXEC sp_MSforeachtable @command1="insert into #TaulaTemporalSpaceUSed
EXEC sp_spaceused '?'"
insert into TaulaFinalMida (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused)
select name, [rows],
SUBSTRING(reserved, 0, LEN(reserved)-2),
SUBSTRING(data, 0, LEN(data)-2),
SUBSTRING(index_size, 0, LEN(index_size)-2),
SUBSTRING(unused, 0, LEN(unused)-2)
from #TaulaTemporalSpaceUSed

select * from TaulaFinalMida
order by reservedKb desc

drop table #TaulaTemporalSpaceUSed

Amb aquest script podem extreure de forma eficient i simple l'espai ocupat per les taules d'una BBDD. Si hi afegim el procediment sp_MSforeachdb podem recòrrer totes les BBDD i executant l'script anterior podrem etreure l'espai ocupat per totes les taules de totes les BBDD.

MDS: problema models sense accés

Tornem amb MDS.
Recentment m'he trobat amb un problema amb els permisos. Quan es crea un model, per defecte, només té permisos la persona/grup que l'ha creat. Si s'esborra el grup ningú té accés a aquest model i queda penjat.
Hi ha una forma de recuperar-ho per la porta del darrera, a través de la BBDD MDS.
La taula que té els models és: [mdm].[tblModel]. D'aquí extraurem l'id del model que volem recuperar permisos.
Amb la consulta:
    select *
    from [mdm].[tblSecurityRole]
    where name like 'Role for UserAccount %\<<usuari>>'
    obtindrem l'id de l'usuari al que volem donar permisos.
La taula amb els usuaris és: [mdm].[tblUser]
La taula on s'ha de fer insert és: [mdm].[tblSecurityRoleAccess]

L'insert ha de ser del tipus:
    insert into [mdm].[tblSecurityRoleAccess]
    (Role_ID, Privilege_ID, Model_ID, Securable_ID, Object_ID, Description, Status_ID, EnterDTM, EnterUserID, LastChgDTM, LastChgUserID, MUID)
    values
    (<<ROLE_ID>>, 2, <<MODEL_ID>>, <<MODEL_ID>>, 1, <<Nom model>> (Update), 1, getdate(), <<USER_ID>, getdate(),<<USER_UD>>, newid())

Un cop fet aquest insert l'usuari ja tindrà accés al model penjat.
Crec que és un error del sistema el fet que no hi hagi un superusuari que tingui accés a tots els models i que hi hagi la possiblitat que quedin models penjats.
També és un error (subsanable amb permisos de BBDD) que es puguin fer inserts directament a les taules de sistema, sobretot tenint en compte que preten ser un sistema amb auditoría de canvis.