MS SQL Server – Principal “utilizator” is not able to access the database “nume_baza_de_date” under the current security context.

Eroarea <MS SQL Server – Principal “utilizator” is not able to access the database “nume_baza_de_date” under the current security context.> provine de la Management Studio care in momentul in care incearca sa obtina lista bazelor de date de pe server, incearca sa “aduca” si cateva informatii aditionale (executand o interogare T-SQL pentru care nu are suficiente drepturi de securitate), printre ele si “Collation”. Aceasta informatie nu poate fi disponibila decat utilizatorilor care au acces la baza de date. Cu alte cuvinte, daca un utilizator nu are acces la toate bazele de date este foarte posibil sa intalneasca eroarea prezentata pentru ca Management Studio incearca sa aduca informatii aditionale despre toate bazele de date. Pentru a ocoli acest comportament se deschide Management Studio, se deschide fereastra ObjectExplorer), se intra in “Databases” iar apoi se da click cu butonul din dreapta al mouse-lui pe capul de tabel. Se debifeaza Collation si se apasa refresh. Aceasta comportament tine cont numai de Management Studio si se manifesta numai daca exista baze de date neaccesibile pt. utilizatorul care se conecteaza la server.

Functii de rang (RANK functions)

O data cu SQL Server 2005, Microsoft a introdus o serie de noi functionalitati. Aceste noi functionalitati usureaza munca unui administrator sau dezvoltator in scrierea codului T-SQL si a intretinerii bazelor de date.  In continuare vom discuta despre funtiile de rang. Functiile de rang intorc o valoare rang pentru fiecare rand dintr-un set de date. Aceste functii sunt nondeterministice. Functie de functia utilizata, mai multe randuri pot avea aceeasi valoare sau valori indivituale.

Functiile de rang permite inumararea secventiala a setului de date. Pentru a exemplifica rezultatul executiei acestor functii, se considera tabela si inregistrarile de mai jos:

CREATE TABLE Persoane(
            Nume VARCHAR(50),
            Varsta INT,
            SEX CHAR(1)
)

INSERT INTO Persoane VALUES (‘Ion’,53,’M')
INSERT INTO Persoane VALUES (‘Vasile’,45,’M')
INSERT INTO Persoane VALUES (‘Gheorghe’,89,’M')
INSERT INTO Persoane VALUES (‘Maria’,21,’F')
INSERT INTO Persoane VALUES (‘Stefan’,46,’M')
INSERT INTO Persoane VALUES (‘Adriana’,69,’F')
INSERT INTO Persoane VALUES (‘Mircea’,56,’M')
INSERT INTO Persoane VALUES (‘Mihai’,2,’M')
INSERT INTO Persoane VALUES (‘Daniela’,46,’F')
INSERT INTO Persoane VALUES (‘Cristina’,25,’F')
INSERT INTO Persoane VALUES (‘Andreea’,14,’F')

Transact – SQL furnizeaza urmatoarele functii: RANK(), DENSE_RANK(), NTILE(), ROW_NUMBER()

RANK

Uneori se doreste ca doua sau mai multe randuri care au aceasi clauza de ordonare (order by) ca aibe acelasi rang. Sintaxa generala este:

RANK ( )  OVER ( [ <partition_by_clause> ] <order_by_clause> )

Unde:
[ <partition_by_clause> ] – reprezinta coloana sau coloanele dupa care se creaza grupari in setul de date
<order_by_clause> – reprezinta coloana sau coloanele dupa care se realizeaza ordonarea pentru stabilirea valorilor rang in cadrul partitiei.

Functia RANK inumara secvential valorile din clauza ORDER BY. Cand doua sau mai multe randuri au aceeasi valoare in ORDER BY, primesc acelasi rang. Chiar si in acest caz, valoarea de rang este incrementata iar cand o noua valoare este determinate in ORDER BY, rangul determinat va fi mai mare cu 1 decat numarul de randuri aflat inaintea randului current. In exemplul urmator, s-a aplicat functia RANK peste coloana Varsta.

SELECT      RANK() OVER(ORDER BY Varsta) as RankNumber,
            Nume,
            Varsta
FROM Persoane

Se poate observa ca acolo unde varsta este aceeasi, si valoarea rang este identical. Atunci cand se intalneste o noua varsta, rangul devine numarul de randuri de dinaintea randului current incrementat cu 1. Cu alte cuvinte, Andreea are rangul 3 pentru ca inaintea ei sunt alte 2 persoane care avand aceeasi varsta, au primit acelasi rang.

SELECT      RANK() OVER(PARTITION BY Sex ORDER BY Varsta) as RankNumber,
            Nume,
            Varsta,
            Sex
FROM Persoane

In exemplul 2, setul de date a fost grupat dupa sex iar apoi s-a atribuit valoarea de rang (individual pentru fiecare partitie a setului de date).

 DENSE_RANK

Aceasta functie este similara funtiei RANK cu exceptia faptului ca valorile rang sunt in ordine, nu lipseste niciuna. Cu alte cuvinte, o valoare rang este ori valoarea rang a randului precedent ori valoarea rang a randului precedent incrementat cu 1. Aceste valori sunt in ordine, fara valori “lipsa”.

SELECT      DENSE_RANK() OVER(ORDER BY Varsta) as RankNumber,
            Nume,
            Varsta
FROM Persoane

NTILE

Aceasta functie este similara celorlalte si imparte un set de date in subgrupuri.

SELECT      NTILE(3) OVER(ORDER BY Varsta) as RankNumber,
            Nume,
            Varsta
FROM Persoane

In exemplul de mai sus, setul de date a fost impartit in 3 subgrupuri (3 este valoarea parametrului functiei NTILE). Folosirea acestei functii va avea ca effect divizarea setului de date in subgrupuri de date cu acelasi rang.

ROW_NUMBER

Aceasta functie intoarce o valoare rang data de numarul randului. Fiecare rand din setul de date va fi cu 1 mai mare decat precedentul rand si cu 1 mai mic decat urmatorul rand. Primul rand din setul de date va avea valoarea 1. Acest rang se poate aplica sip e partitii de date ale setului de date (aduca pe grupuri de date).

SELECT      ROW_NUMBER() OVER(ORDER BY Varsta) as RankNumber,
            Nume,
            Varsta
FROM Persoane

In exemplul de mai sus, numerotarea randurilor s-a realizat dupa coloana Varsta. Daca vom dori afisarea setului de date in alta ordine, adica adaugarea clauzei Order by, in acest caz, numai afisarea o sa se realizeze dupa noua ordine, numerotarea randurilor se va realiza dupa clauza order by a functiei ROW_NUMBER.

SELECT      ROW_NUMBER() OVER(ORDER BY Varsta) as RankNumber,
            Nume,
            Varsta
FROM Persoane
ORDER BY NUME

Acum se poate observa ca afisarea s-a realizat dupa noua ordine dar valorile rang sunt aceleasi.

Parametrul de tip tabela (Table Valued Parameter)

Este un nou tip de parametru in SQL Server 2008 si permite trimiterea de randuri multiple catre o instructiune T-SQL sau folosirea ca parametru la apelarea unei functii sau proceduri stocate.
 
Crearea si utilizarea TVP
Pentru a fi creat este necesar sa se defineasca numele si structura: CREATE TYPE Nume AS TABLE (coloana tip). Pentru a fi utilizat se defineste o procedura sau functie cu unul sau mai multi parametrii de tipul nou creat. Accesarea datelor se realizeaza cu ajutorul instructiunilor DML (select, insert, update, delete).
 
Avantaje:
1.       Accepta PRIMARY KEY, UNIQUE si CHECK CONSTRAINTS
2.      Se reduce numarul de apeluri catre server
3.      Se foloseste READONLY ceea ce permite transmiterea unei referinte catre rutina (astfel nu se duplica datele irosind timp si spatiu de stocare)
4.      Nu se face LOCK pentru popularea cu date
5.      Aplicatia client poate preciza ordinea de sortare si cheile unice
6.      Reprezinta un model simplu de programare
7.      Se pot programa reguli de business complexe in rutine simple
8.       Sunt strongly typed
9.       Pot exista de cardinalitati diferite (adica cu numar diferit de coloane)
10.    Beneficiaza de avantajul temporary table caching
 
Restrictii:
1.      Nu accepta DEFAULT CONSTRAINT sau crearea directa de indecsi
2.      Nu se poate altera structura TVP
3.      Declararea parametrului se face precizand READONLY ceea ce va avea ca efect restrictia de a nu altera datele (nu se pot executa instructiuni DML cu exceptia SELECT)
4.      SQL Server nu creaza si nu mentine statistici pentru coloanele unui parametru de tip tabela
5.      Nu se poate folosi ca target pentru SELECT INTO
 
Securitate:
TVP se supune regulilor de securitate ale obiectelor: CREATE, GRANT, DENY, ALTER, CONTROL, TAKE OWNERSHIP, REFERENCES, EXECUTE, VIEW DEFINITION, si REVOKE.
 
Catalog:
1.      sys.table_types
2.      sys.parameters
3.      sys.types
 
Observatii:
1.       In spatele oricarui TVP se afla o tabela temporara stocata in Tempdb
2.       Disponibil in Framework 3.5
 
Recomandare Microsoft:
1.      A se folosi pentru mai putin de 1000 de randuri
2.      Pentru mai mult de 1000 de randuri a se folosi BULK_INSERT
 
Testare:
A fost creata o aplicatie client dezvoltata in C# avand doar o forma, 2 butoane, un textbox si un listbox. Unul dintre butoane apela o procedura si ii pasa un parametru de tip tabela iar cel de-al 2-lea buton crea o tabela temporara, adauga date in ea si apoi executa o procedura. Rolul textbox-ului a fost acela de a-mi permite introducerea unui numar intreg care sa reprezinte numarul de randuri generate pentru a testa executarea celor doua proceduri. Testarea a avut ce efect compararea timpilor de la apasarea butoanelor si pana la terminarea executiilor celor doua proceduri.
Rezultatele au fost in favoarea TVP insa timpul castigat la rulare nu a fost foarte mare, diferenta este infima insa daca s-ar folosi tabele cu un numar mare de coloane si un numar mai mare de randuri diferenta ar fi mai semnificativa. La o mie de inregistrari adaugate intr-o tabela prin cele doua metode, rezultatele au fost:
Metoda tabelei temporare: 0.156 secunde
Metoda TVP: 0.093
Timpii au diferit putin la fiecare rulare insa diferenta s-a pastrat.
 
Concluzie:
Utilizarea TVP ar aduce un avantaj prin:
1.      Timpi mai mici de executie
2.      Claritatea codului

Interogările recursive folosind Common Table Expressions (CTE)

Marele avantaj al introgărilor recursive este acela că se pot referii  singure. Care este rostul acestora? Pentru a reprezenta date ierarhice aşa cum este o organigramă a departamentelor dintro companie sau un meniu pentru un site web etc.
Pentru a se rezolva acest aspect au apărut mai mulţi algoritmi pe care nu o să-i discut aici însă utilizând motoarele de căutare puteţi găsi singuri foarte mulţi algoritmi. Prin interogările recursive avem posibilitatea de a standardiza modul de lucru cu datele ierarhice şi cu interogări asupra acestora.
Sintaxa:
WITH nume_CTE (optional lista_coloane) AS
 (
   Membru_ancora
   UNION ALL
   Membru_recursiv
 )
INTEROGARE ASUPRA nume_CTE
OPTION (MAXRECURSION n)
 
Se poate observa că interogarea recursivă este formată din două interogări numite membru ancoră şi  membru recursiv. Membrul ancoră este interogarea al cărei rezultat  va fi folosit ca intrare pentru membrul recursiv. Aceasta se va autoapela până când niciun rând nu mai este întors (condiţie de încheiere). Ar mai fi de menţionat ca reguli pentru interogările recursive că “UNION ALL” este singura construcţie permisă între cei doi membrii şi că membrul recursiv poate referii doar o singura interogare recursivă (CTE).
Un exemplu folosit pentru a înţelege recursivitatea este calculul factorialului. Spre exemplificare se poate folosi urmatorul script:
CREATE FUNCTION dbo.Factorial(@Numar int)
RETURNS int AS BEGIN
 
      IF @Numar = 0 BEGIN
            RETURN 1
      END
 
      RETURN @Numar * dbo.Factorial(@Numar – 1)
END
 
Şi testarea:
SELECT  dbo.Factorial(1) AS [1!],          
                  dbo.Factorial(2) AS [2!],
                  dbo.Factorial(3) AS [3!],
                  dbo.Factorial(4) AS [4!],
                  dbo.Factorial(5) AS [5!]
Se poate observa că funcţia se autoapeleaza până când @Numar = 0 (pentru cine nu ştie: n! = 1*2*..(n-1)*n)
Să analizăm comparativ exemplul de mai sus cu CTE. Avem un parametru de intrare iar în CTE avem membru de intrare. Apoi după condiţia de continuitate se autoapelează funcţia cam în acelaşi mod în care membrul recursiv se autoapelează decrementând valoarea parametrului (în CTE se preia valoarea părintelui astfel se avanseaza cu un nivel). Când parametrul funcţiei va avea valoarea 0, se opreşte autoapelarea, iar în CTE ne oprim când nu mai avem părinte, am ajuns la condiţia de ieşire. Când s-a ajuns la nivelul maxim de iterare, valorile din stivă se vor asigna variabilei rezultat, se va combina cu membrul ancora iar rezultatul este afisat.
Exemplu:
CREATE TABLE Arbore (
      ID_Nod                    int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
      ID_NodParinte     int NULL
            CONSTRAINT [FK_Arbire_ID_Nod] FOREIGN KEY REFERENCES dbo.Arbore(ID_Nod),
      Informatie              nvarchar(200) NOT NULL,
            CONSTRAINT  [CK_ID_NodParinte] CHECK (ID_Nod <> ID_NodParinte)
)
 
GO
INSERT INTO Arbore (ID_NodParinte, Informatie)
VALUES (NULL, N’Rădăcină’)
 
INSERT INTO Arbore (ID_NodParinte, Informatie)
VALUES (1, N’Nivelul 1 – primul nod’)
 
INSERT INTO Arbore (ID_NodParinte, Informatie)
VALUES (1, N’Nivelul 1 – al 2-lea nod’)
 
INSERT INTO Arbore (ID_NodParinte, Informatie)
VALUES (3, N’Nivelul 2 – primul nod’)
 
INSERT INTO Arbore (ID_NodParinte, Informatie)
VALUES (4, N’Informatie căutată’)
GO
Am creat şi populat o tabelă în care nodul părinte referă o valoare din cheia tabelei. Această structură ajută la reprezentarea unui arbore definit unic printro cheie, părinte şi informaţie (valoare). Ne propunem să aflăm toate nodurile prin care se trece de la rădăcină până la frunza cu o anumită informaţie. Un exemplu mai concret ar fi acela prin care dorim să aflăm toţi şefii unui angajat plecând de la seful imediat superior până la directorul general sau putem afla structura ierarhică a unui departament pornind de la acesta şi până la ultimul nivel de conducere.
Utilizare CTE pentru exemplul propus (frunza cu o informaţie dată):
WITH Arb (ID_Nod, ID_NodParinte, Informatie, Nivel) AS
(
      SELECT ID_Nod, ID_NodParinte, Informatie, 0
      FROM dbo.Arbore
      WHERE Informatie = N’Informatie căutată’
     
      UNION ALL
     
      SELECT a.ID_Nod, a.ID_NodParinte, a.Informatie, Nivel + 1 
      FROM dbo.Arbore a
      INNER JOIN Arb b ON b.ID_NodParinte = a.ID_Nod
      WHERE B.ID_NodParinte IS NOT NULL
)
SELECT ID_Nod, ID_NodParinte, Informatie, Nivel
FROM Arb
ORDER BY ID_Nod
 
Să analizăm:
Se crează membrul ancoră în care se crează o pseudocoloană (Nivel) ce ne va ajuta să identificăm nivelul ierarhic; rezultatul membrului ancoră se foloseşte ca intrare pentru membrul recursiv; se incrementează nivelul; se afişează rezultatul:
ID_Nod      ID_NodParinte Informatie                  Nivel
———– ————- ————————— ——–
1           NULL          Rădăcină                    3
3           1             Nivelul 1 – al 2-lea nod    2
4           3             Nivelul 2 – primul nod      1
5           4             Informatie căutată          0
 
(4 row(s) affected)

Executarea de interogari peste mai multe servere (multiple server query execution).

SQL Server permite executarea de script-uri peste mai multe servere fara a fi necesara schimbarea conexiuni si rerularea sciptului. Pentru a intelege mai bine despre ce este vorba, am sad au un exemplu: vrem sa rulam un script in contextul mai multor baze de date aflate pe servere diferite. Avem mai multe solutii insa spre exemplificare am sa ofer numai una: scriptul respective il putem salva ca procedura stocata in fiecare din bazele de date, fiecare server sa-l adaugam ca linked server intr-o instanta separate de SQL Server iar in aceasta instant sa cream o noua procedura care executa procedura create mai devreme pe fiecare server. Principalul dezavantaj este acela ca daca avem nevoie sa modificam procedura stocata, va trebui facut deployment pe fiecare server  in parte. Aici ne putem ajuta de executarea de interogari asupra mai multor  servere sau mai bine zis asupra unui grup de servere. Vom crea un grup de servere, vom adauga serverele in grup si apoi vom executa scriptul. Pentru aceasta deschidem Management Studio iar apoi pentru a crea un grup de servere putem apasa combinatia de taste CTRL+ALT+G sau mergem in meniul VIEW de unde alegem Registered Servers. Click dreapta pe Local Server Groups si vom selecta New Server Group, introducem denumirea noului grup. Click dreapta pe Local Servers Groups si alegem New Registration Server Registration. In fereastra nou deschisa selectam instant pe care vrem sa o adaugam in grup, setam elementele de securitate si numele instantei eventual si descrierea iar apoi din tab-ul Connection Properties  trebuie sa selectam baza de date in al carei context se va executa scriptul. Repetam acesti pasi pentru fiecare server. Dupa ce am adaugat toate instantele de SQL Server suntem gata sa executam scriptul. Click dreapta pe denumirea grupului si alegem New Query. In fereastra deschisa vom scrie:

SELECT DB_NAME() AS BazaDeDateContextuala

Acesta interogare va avea ca efect afisarea bazei de date curente, veti observa ca desi in interogare este o singura coloana, rezultatul va avea 2 coloane si anume coloanele “Server Name” si “bazaDeDateContextuala” iar rezultatul rularii va contine randuri pentru fiecare server in parte (denumirea server-ului de unde vin randurile este trecuta in prima coloana)

Backup Compression (compresie backup)

In SQL Server 2008 a fost introdus backup-ul compresat. Acesta se realizeaza cu comanda BACKUP la care se adauga clauza WITH COMPRESSION.

Exemplu: BACKUP DATABASE [MASTER] TO DISK=’C:\Master.bak’ WITH COMPRESSION
Backup-ul compresat se poate realiza din editiile enterprise si developer insa restaurarea dintr-un backup compresat se poate realiza pe oricare dintre editiile SQL Server.
La nivel de server exista optiunea “backup-compression default” care atunci cand este setata are ca efect stabilirea modului implicit de backup(compresat/necompresat). Aceasta optinea poate fi suprascrisa de WITH COMRESSION si WITH NO_COMPRESSION in T-SQL sau din Management Studio in fereastra Back Up Database setand Set backup compresion (care prevede 3 posibilitati:  Use the default server setting – care are ca efect folosirea optiunii de la nivel de server; Compress backup; Do not compress backup).

Avantaje: reducerea timpilor la operatiunile de backup / restore si reducerea spatiului de stocare.

Dezavantaj: se creste utilizarea procesorului.

Pentru a se vedea rata de compresie se poate rula interogarea de mai jos:

SELECT backup_size/compressed_backup_size AS RataCompresie, * 
FROM msdb..backupset; 

Studiu de caz:

A fost folosita o baza de date (XXX) de 4,4GB

Spatiu de stocare pentru fisierul de backup:

Compresat: 556,209 KB
Necompresat: 1,401,256 KB

Timp de realizare al backup-ului:

Compresat: 40 secunde
Necompresat: 64 secunde

Timp de restaurare:

Compresat: 95 secunde
Necompresat: 140 secunde
Observatie: ambele fisiere de backup au fost apoi arhivate cu WinRar 3.71 iar rezultatele au fost in favoarea backup-ului necompreasat (273,222 KB pentru necompresat in comparatie cu 531,449 KB cat a avut cel compresat in conditiile in care ambele backup-uri au fost arhivate cu aceleasi setari precum compression method: normal).
Concluzie: tehnica de backup poate reduce timpii destinati operatiunilor administrative si pot imbunatatii procese precum log shipping sau mirroring.

Recovery model

Modelul de recuperare (recovery model) reprezintă o opţiune de configurare care controlează modul în care transacţiile sunt păstrate în log, dacă log-ul de tranzacţii poate fi salvat ca şi copie de siguranţă, şi ce opţiuni de recuperare sunt disponibile pentru baza de date. Modelul ales are implicaţii asupra asupra recuperării din copiile de siguranţă şi implicaţii privind performaţa.

În MS SQL Server sunt disponibile 3 tipuri de modele: FULL, SIMPLE şi BULK-LOGGED. Aceste modele determincă cum SQL Server lucrează cu logul de tranzacţii şi determină operaţiile pe care le salvează în log şi dacă trunchiază (truncate) logul de tranzacţii. Trunchierea logului de tranzacţii este procesul de înlăturare a tranzacţiilor comise lăsând spaţiul liber pentru noi tranzacţii.

• În modelul de recuperare FULL, SQL Server păstrează toate tranzacţiile în logul de tranzacţii şi nu-l trunchiază niciodată. Acest model permite recuperarea (restore) din copii de siguranţă a unei baze de date la momentul în care s-a salvat copia de siguranţă sau recuperare la un moment din timp (restore point in time).  Pentru SQL Server 2005 recuperarea în timp este disponibilă doar în versiunile Enterprise Edition şi Developer Edition.
• În modelul de recuperare SIMPLE, SQL Server salvează cât mai puţine tranzacţii (minimally log) cele mai multe operaţii şi trunchiază logul de tranzacţii după fiecare checkpoint. În acest model nu se pot salva şi recupera copii de siguranţă ale logului de tranzacţii. Nu pot fi restaurate nici pagini de date.
• În modelul de recuperare BULK-LOGGED, SQL Server salvează cât mai puţin în logul de tranzacţii operaţii bulk precum SELECT INTO şi BULK INSERT. În acest model, dacă un log conţine operaţii bulk, baza de date poate fi restaurată la momentul salvării copiei de siguranţă, nu şi în timp. Acest model este destinat a fi utilizat doar pe parcursul operaţiilor de import masiv de date.

Cum se modifică modelul de recuperare (recovery model)

Informaţii despre configurarea bazelor de date se poate obţine:

SELECT name, recovery_model_desc FROM SYS.DATABASES

Pentru modificarea acestei opţiuni se poate utiliza:

• SSMS (SQL Server Management Studio) – click dreapta pe o baza de date – properties – pagina Options
• ALTER DATABASE AdventureWorks SET RECOVERY FULL | SIMPLE | BULK_LOGGED

SQLParameter in .NET

Executia interogarilor la momentul executiei unei aplicatii este o necesitate fie ca este vorba de o aplicatie web, aplicatie desktop sau chiar o aplicatie de tip consola. Asadar, preluarea intrarilor din aplicatie si trimiterea lor ca parametrii pentru o procedura stocata sau functie este necesara atunci cand se doreste realizarea de operatii pe seturi de date.

In continuare se va presupune existenta unui obiect SQLConnection asociat cu obiectul SQLCommand.

Utilizarea obiectului SQLParameters faciliteaza transmitere valorilor catre interogari in .NET. Acesti parametrii pot avea orice directie (input, output, inputoutput). de asemenea se pot asocia tipuri de date, dimensiuni si valori. Sunt permise mai multe constructii pentru obiectele SQLParameters. Un exemplu este prezentat in cele ce urmeaza:

String SirSql = “interogarea dumneavoastra”;
SqlCommand SqlCmd = new SqlCommand(SirSql);
SqlParameter SqlPar = new SqlParameter(“@PrimulParam.”, SqlDbType.VarChar, 50)
SqlPar.Value = 100;
SqlPar.Direction = ParameterDirection.Input;
SqlCmd.Parameters.Add(SqlPar);

Exemplul de mai sus ne arata cum este declarat un parametru de tip INPUT cu numele @PrimulParametru de tip VARCHAR si dimensiune 50. Deasemenea i-a fost atribuita valoarea 100.

Cand construiti dinamic o interogare, puteti specifica valoarea pentru coloane. Valoare care se poate gasi in variabile si care prin concatenare si functii pe text (string) va poate ajuta sa compuneti interogari. Generarea de interogari la nivelul serverului de baze de date sau la nivelul aplicatiei client este un subiect care nu va fi dezbatut aici, poate intr-un alt articol. Pentru moment am sa exemplific constructia unei interogari la nivelul aplicatiei client (business layer) si executarea acesteia utilizand obiectul SQLCommand.

Este posibila constructia interogarilor asa cum este prezentata putin mai jos iar la momentul executarii, valoarea variabilei sa se gaseasca in constructie.

int VariabilaMea = “75”;
string sql  = ” SELECT * FROM TabelaMea WHERE ID = “ + VariabilaMea;
SqlCommand SqlCmd = new SqlCommand(sql);
SqlCmd.ExecuteNonQuery();

O metoda alternativa propun in cele ce urmeaza:

SqlCommand SqlCmd = new SqlCommand(sql);
SqlParameter SqlPar = new SqlParameter(“@PrimulParametru”, SqlDbType.Int);.
SqlPar.Value = “75”;
SqlCmd.Parameters.Add(SqlPar);
SqlCmd.ExecuteNonQuery();

De ce sa specificam separat parametrii? Sa luam cazul operatiilor de actualizare (adaugare, modificare) asupra unei tabele. Daca doriti adaugarea unui fisier in baza de date, nu veti putea realiza concatenarea interogarii; motivul este pentru ca fisierul va fi in format binar. Solutia este de a trimite continutul obiectului catre interogare prin specificarea valorii obiectului ca parametru. Ca exemplu:

byte[] fileData = new byte[lengthOfFile];

Apoi atribuim variabila de tip byte[] obiectului SQLParameter utilizand constructorul:

SqlParameter(string NumeParametru, Obiect valoare).

Codul de mai jos permite trimiterea continutului unui fisier ca parametru si adaugarea acestuia intro tabela.

//Creare parametru si executare interogare.
SqlCommand SqlCmd = new SqlCommand(sql);
SqlParameter SqlPar = new SqlParameter(“@binaryValue”, fileData);
SqlCmd.Parameters.Add(SqlPar);
SqlCmd.ExecuteNonQuery();

Sursa articol: Raj Vasant

Reguli privind valorile NULL

Navigand pe diverse site-uri am gasit un articol care se refera la NULL-uri. Pe aceasta cale tin sa precizez ca tot meritul il detine autorul (Michael Coles) prin articolul publicat la adresa http://www.sqlservercentral.com/articles/Advanced+Querying/2829/. Ceea ce mi-a atras foarte repede atentia au fost cele 4 reguli pe care autorul le considera ca fiind fundamentale in “gandirea” NULL-urilor si anume: 1.Utilizati NULL pentru a indica valori necunoscute(lipsa). Nu utilizati NULL pentru valoarea zero, sir de caractere de dimensiunea zero sau alte valori “cunoscute” ca fiind lipsa. Actualizati valorile NULL cat mai repede.2.In standardul ANSI SQL, NULL nu este egal cu nimic, nici macar cu alte valori NULL.
3.Utilizati SET ANSI_NULLS ON si utilizati intodeauna sintaxa din standardul ANSI SQL pentru NULL. Abaterea de la aceasta regula poate conduce la probleme de portabilitate, incompatibilitate a codului existent si a bazei de date, si poate intoarce rezultate eronate. 4.Standardul ANSI SQL pentru COALESCE() si CASE sunt preferate ISNULL() sau altor metode.Va rog sa cititi respectivul articol pentru ca autorul prezinta si cateva exemple despre comportamentul valorilor NULL.
 

 

 
 

Auditul grupurilor de utilizatori Windows in SQL Server

Asigurarea securităţii este o parte din treaba fiecărui DBA.  Cu SQL Server aveţi posibilitata să utilizaţi atât autentificare SQL Server, Windows cât şi mixtă. Avantajul utilizărrii autentificării Windows pentru SQL Server este acela că se poate beneficia de avantajele modelului de securitate Windows si a politicilor de securitate configurate pe domeniu. Un alt avantaj este acela că se poate confugura securitatea la nivel de domeniu in loc de nivelul de instanţă al SQL Server.  Bazându-ne pe grupurile de utilizatori care sunt configurate în Windows, se pot acorda acces grpurilor in SQL Server fără a crea login pentru fiecare utilizator.

Dezavantajul este că utilizatorii care aparţin grupurilor de utilizatori din Windows sunt “mascaţi” la nivelul SQL server-ului. In logins se pot vedea grupurile care au acces dar nu se poate vedea care utilizatori fac parte din respectivele grupuri. Cum se poate obţine această informaţie in SQL Server?

SQL Server oferă o procedură stocată extinsă (xp_logininfo) care permite o “privire” în această problemă. Această procedură se regăseşte atât în SQL Server 2000 cât şi în SQL Server 2005. Procedura are următorii parametrii:

• @acctname – numele utilizatorului windows sau a grupului 
• @option - informaţia de afişat 
  • ‘all’ – afişează informaţii pentru permisiuni
  • ‘members’ – afişează lista de membrii a grupului
• @privelege – variabilă de ieşire şi întoarce ‘admin’,'user’ sau null

Exemple:

EXEC xp_logininfo ‘BUILTIN\Administrators’  – Afişează informaţii despre grup (permission path)

EXEC xp_logininfo ‘BUILTIN\Administrators’, ‘members’  - Afişează lista de membrii din grupul Windows