Il blog di Andrea Benedetti

SchemaBinding e tempo perso

abenedetti — Tue, 31 Jan 2012 14:03:15 GMT

E un bel giorno alcuni report smisero di funzionare…

Faccio un passo indietro, ma lo scenario appena descritto è già molto chiaro.

Reports che accedono ad un db, lo interrogano andando direttamente sulle tabelle e ne presentano i dati.

Cosa succede se viene modificato il nome di una colonna utilizzata dal report?

Il report smette di funzionare, of course.

Ok, la domanda: abbiamo modo di dichiarare l’esistenza di una dipendenza di una certa struttura dati (ad esempio una tabella) rispetto a qualche altro oggetto (ad esempio un report)?

Si, utilizzando delle viste che, grazie all’opzione SCHEMABINDING, possano informare SQL Server di controllare ogni azione che verrà fatta sulle strutture fisiche sottostanti.

In questo modo, se la modifica invalida la vista, l’operazione di alter (o di drop) table fallisce (ed i nostri report continuano a funzionare).

Righe duplicate, righe univoche e Physloc

abenedetti — Mon, 16 Jan 2012 17:08:32 GMT

Chi si è trovato, almeno una volta, di fronte a tabelle modellate male? ( quante mani alzate … :-) )

Ad esempio, di fronte a tabelle con righe duplicate, qualcosa come (un esempio stupido):

USE tempdb;
GO

CREATE TABLE test(
             col1 varchar( 3 ) , 
             col2 varchar( 3 ));
GO

INSERT INTO test
VALUES( 'aaa' , 'bbb' ) , 
      ( 'aaa' , 'bbb' ) , 
      ( 'aaa' , 'bbb' );
GO

SELECT
       *
  FROM test; 
GO

Come posso cancellare le righe che sono duplicate?

Direttamente, apparentemente, non posso…

Cosa intendo? Intendo dire che, per poter recuperare univocamente ciascuna riga, ho la necessità di “costruire” qualcosa che mi renda identificabile ciascuna tupla dalle altre.

Potrei usare la funzione ROW_NUMBER, ad esempio:

SELECT
       ROW_NUMBER( )
       OVER( ORDER BY col1 DESC )AS rowNum , 
       *
FROM test;

Oppure, prendendo spunto da qui, potrei usare la colonna virtuale [Physloc] in questo modo:

SELECT
       %%Physloc%% AS Physloc , 
       col1 , 
       col2
  FROM test;
GO

In questo modo, quindi, ottenendo il binary( 8 ) che rappresenta il PageID (primi 4 bytes), FileID (2 bytes) e Slot (2 bytes) ho sicuramente un’informazione univoca per ciascuna riga.

Datapage, physical storage location e fn_PhysLocFormatter

abenedetti — Mon, 16 Jan 2012 09:10:24 GMT

In alcuni casi potrebbe essere utile conoscere la posizione fisica delle nostre righe.

In sostanza poter conoscere quale sia il “contenitore” che mantiene al suo interno la nostra informazione, le datapages che contengono i dati.

USE tempdb;
GO

CREATE TABLE anagrafica(
             idAnagrafica smallint , 
             cognome varchar( 35 ) , 
             nome varchar( 35 ));
GO

INSERT INTO anagrafica
VALUES( 1 , 'aaa' , 'bbb' ) , 
      ( 2 , 'ccc' , 'ddd' ) , 
      ( 3 , 'eee' , 'fff' );
GO

SELECT
       *
  FROM anagrafica; 
GO

A questo punto mettiamo il naso dentro le strutture che abbiamo creato:

SELECT
      %%Physloc%% as Physloc,
       sys.fn_PhysLocFormatter( %%Physloc%% )AS SysStorageLocation , 
       idAnagrafica, cognome, nome
  FROM anagrafica;

La “pseudo” colonna [Physloc] ci permette di vedere l’indirizzo univoco di ciascuna riga.

Grazie alla funzione (non documentata) fn_PhysLocFormatter possiamo formattare questo indirizzo in modo da visualizzare l’indirizzo fisico della riga.

Il risultato viene esposto come:

file num
datapage
slot

Una volta che otteniamo queste info, possiamo vedere le nostre pagine grazie al comando DBCC PAGE passando:

dbname / dbid
file num
page num
livello di informazioni che vogliamo (printopt)

Dove il “livello di informazioni” può essere:

0 = header
1 = header and hex dump for rows
2 = header and the page dump
3 = header and detail row information

DBCC TRACEON( 3604 );
DBCC PAGE('TempDB',1,114,2)
DBCC TRACEOFF( 3604 );
GO

SQL Server Consolidation Guidance

abenedetti — Tue, 10 Jan 2012 18:23:26 GMT

Giusto per non perdere il link giusto, un withepaper molto interessante :-)

SQL Server Consolidation Guidance: qui.

“The goal of this white paper is to provide a framework for choosing among virtualization, multi-database, and multi-instance consolidation strategies for SQL Server Database Engine OLTP applications.”

SQL Server 2012 Filetable e Fulltext

abenedetti — Mon, 09 Jan 2012 15:42:25 GMT

SQL Server 2012 aggiunge, alla funzionalità di FILESTREAM (tempo fa ne avevo parlato qui), la possibilità di interagire direttamente con le API di Windows, quindi consente ad un’applicazione di copiare dei files (e/o folder) in un path e lasciare al database engine tutta la sua gestione.

Diciamo che è il pezzo che mancava… :-)

Le cose interessanti, poi, sono diverse. Ne cito, per iniziare, due:

costruire dei trigger che intercettino copie di file “non voluti” e quindi intervenire “al volo”
gestire un catalogo fulltext e, quindi, indicizzare il contenuto dei files copiati e gestiti

Vediamo come fare…

/*
Enable and Configure FILESTREAM
http://msdn.microsoft.com/en-us/library/cc645923(v=sql.110).aspx

EXEC sp_configure Filestream_Access_level , 2;
RECONFIGURE;
GO
*/

USE MASTER;
GO

IF DB_ID( 'FileTableDB' )IS NOT NULL
    BEGIN
        DROP DATABASE FileTableDB;
    END;
GO

/*
Creazione del database
con FILESTREAM
*/
CREATE DATABASE FileTableDB ON PRIMARY
( 
    NAME = FileTableDB , 
    FILENAME = 'C:\SQL2012\db\FileTableDb\FileTableDB_Data.mdf' 
    ) , 
    FILEGROUP FileTableDBFS CONTAINS 
        FILESTREAM
        ( 
            NAME = FileTableDBFS , 
            FILENAME = 'C:\SQL2012\db\FileTableDb\FileTableDbFileStream'
        )
    LOG ON
    ( 
        NAME = FileTableDBLog , 
        FILENAME = 'C:\SQL2012\db\FileTableDb\FileTableDB_Log.ldf' 
    );
GO


/*
Specifico il livello di accesso non transazionale a livello di database
Questo per poter ottenere un file handle senza la necessità di una transazione
*/
ALTER DATABASE FileTableDB 
    SET FILESTREAM 
    ( 
    NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileTableDbFileStream'
    )
GO

/*
Passo al database utente
*/
USE FileTableDB;
GO

/*
Creo una tabella FileTable
*/
CREATE TABLE tblFileTableTest AS FileTable
WITH
(
        FileTable_Directory = 'tblFileTableTest',
        FileTable_Collate_Filename = database_default
)
GO

/*
A questo punto posso aprire una share di rete composta come:
\\nomeMacchina\nomeistanza\directoryName\fileTableDirectory

Quindi, nel mio caso, posso aprire:
\\Abalien\sql2012\FileTableDbFileStream\tblFileTableTest
*/

/* Ecco come posso recuperare il path alla document library: */
SELECT FileTableRootPath() AS 'FileTablePath' 
GO

/*
vedo la tabella (vuota)
*/
SELECT *
  FROM tblFileTableTest;
GO


/*
Aggiungo un trigger alla tabella perchè, per ipotesi,
voglio interrompere operazioni di copia se contenenti file TXT
*/
CREATE TRIGGER dbo.ControlFileTXT ON tblFileTableTest
    FOR INSERT , UPDATE
AS
IF EXISTS( SELECT 1
             FROM inserted
             WHERE inserted.file_type = 'txt' )
    BEGIN
        ROLLBACK;
    END;
GO

A questo punto posso fare drag & drop di files all’interno del mio folder e ritrovarli in tabella:

Oppure posso provare a copiare dei TXT e ricevere un errore di copia:

Fatto questo abilitiamo l’indicizzazione FULLTEXT sul nostro database e sulla nostra tabella:

/* Abilitiamo catalogo e indicizzazione FULLTEXT */
CREATE FULLTEXT CATALOG filetableCat;
GO

CREATE UNIQUE INDEX ui_document ON tblFileTableTest( stream_id );
GO

CREATE FULLTEXT INDEX ON tblFileTableTest
( file_stream TYPE COLUMN name LANGUAGE 1033 )
KEY INDEX ui_document ON filetableCat WITH CHANGE_TRACKING AUTO;
GO


/* Vediamo lo stato di indicizzazione */
SELECT CASE FULLTEXTCATALOGPROPERTY( 'filetableCat' , 'Populatestatus' )
       WHEN 0 THEN 'Idle'
       WHEN 1 THEN 'Full population in progress '
       WHEN 2 THEN 'Paused'
       WHEN 3 THEN 'Throttled'
       WHEN 4 THEN 'Recovering'
       WHEN 5 THEN 'Shutdown'
       WHEN 6 THEN 'Incremental population in progress'
       WHEN 7 THEN 'Building index'
       WHEN 8 THEN 'Disk is full. Paused.'
       WHEN 9 THEN 'Change tracking'
       END AS populateStatus , 
       CASE FULLTEXTCATALOGPROPERTY( 'filetableCat' , 'AccentSensitivity' )
       WHEN 0 THEN 'Accent insensitive'
       WHEN 1 THEN 'Accent sensitive'
       END AS AccentSensitivity , 
       FULLTEXTCATALOGPROPERTY( 'filetableCat' , 'ItemCount' )AS ItemCount;
GO

Attenzione: ricordiamoci che NON tutti i tipi di file sono immediatamente indicizzabili (per alcuni, come il PDF, serve installare il suo iFilter)

Per vedere le estensioni conosciute:

SELECT * FROM sys.fulltext_document_types;

Una volta copiati files nella nostra tabella filetable possiamo farne una ricerca testuale con una query come:

/* Test di ricerca */
DECLARE @SearchWord nvarchar( 300 );
SET @SearchWord = '"enabling end users"';

SELECT name
  FROM dbo.tblFileTableTest FT 
  JOIN CONTAINSTABLE
    ( dbo.tblFileTableTest , file_stream , @SearchWord ) AS T 
        ON FT.stream_id = T.[key];
GO

Upgrade Assistant for SQL Server 2012

abenedetti — Mon, 09 Jan 2012 12:42:35 GMT

E’ disponibile in download qui il nuovo Upgrade Assistant per SQL Server 2012.

La pagina wiki del tool qui.

Database mail e dimensione query result

abenedetti — Sun, 08 Jan 2012 14:41:13 GMT

Facendo un pò di manutenzione su alcuni job dei nostri server, trovo questo errore su uno di essi:

Executed as user: XXXXXXXXXXXXXx. File attachment or query results size exceeds allowable value of 1000000 bytes. [SQLSTATE 42000] (Error 22050). The step failed.

Il job in questione effettua una semplice query ed invia, via mail, il suo risultato.

Qualcosa come questo:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'XXXXXXXXXXXXxx',
@recipients = 'XXXXXXXXXXXXX',
@query = 'use myDatabase; SELECT … ‘,
@subject = '…..',
@query_result_no_padding =0;

Evidentemente (il messaggio è chiaro) il resultset è cresciuto a tal punto da eccedere la dimensione massima spedibile.

Come risolvo?

Apro la configurazione della mail (sul nodo “Management del SSMS, “Database Mail”, tasto dx, “Configure Database Mail”), “next” sulla prima videata, e poi “view or change system parameters”:

Quindi modifico il valore a mia descrizione:

SQL Server 2012, GEOMETRY, circonferenze

abenedetti — Wed, 21 Dec 2011 16:37:41 GMT

Chi ha avuto la necessità di lavorare con il tipo GEOMETRY in SQL Server 2008 potrebbe aver avuto la necessità di utilizzare delle forme circolari.

Un paio di metodi per costruire cerchi:

DECLARE @g geometry;
SET @g = 'Point(10 5)';
SELECT @g.STBuffer( 5 );

Oppure:

DECLARE @p AS float = 360;
DECLARE @count AS int = 0;
DECLARE @tmpTable TABLE( geo geometry );
WHILE(@count < @p)
    BEGIN
        INSERT INTO @tmpTable
        SELECT geometry::STGeomFromText( 'POINT(' + CAST(SIN( RADIANS( CAST(@count AS float) / CAST(@p AS float) * @p ))AS varchar) + ' ' + CAST(COS( RADIANS( CAST(@count AS float) / CAST(@p AS float) * @p ))AS varchar) + ')' , 4326 )AS Posn;
        SET @count = @count + 1;
    END;
SELECT geo
  FROM @tmpTable;

SQL Server 2012 introduce il tipo (subtype) “curva”, evidentemente utile anche per costruire circonferenze:

declare @g geometry = 'CIRCULARSTRING(0 2, 2 0, 4 2, 2 4, 0 2)'
select @g

Lascio, di seguito, anche il link per scaricare il whitepaper “New Spatial Features in SQL Server Code-Named "Denali": qui.

Merry SQL Christmas

abenedetti — Mon, 19 Dec 2011 17:04:00 GMT

Quasi, quasi, mi spavento a vedere il numero di mesi che è trascorso dall’ultimo mio post…

E quanta acqua (o quanto SQL…) è passata sotto i ponti…

Allora, quale migliore motivo per ripartire alla grande, con buoni propositi di scrittura, con un post di auguri (o di “SQL Auguri”)?

Prendiamo il tipo GEOMETRY e… Merry Christmas a tutti voi!

Giusto per giocare dietro le quinte (se vi piace un albero con un livello minore di rami vi basta cambiare una variabile) … un pò di TSQL:

/*
    
********************
Happy SQL Christmas!
********************

Andrea Benedetti, SQL Server MVP

Twitter: @anbenedetti
Mail: abenedetti@absistemi.it
*/

SET NOCOUNT ON;

/* please choose the level of the tree... :-) */
DECLARE @level smallint = 10;


DECLARE @i tinyint = 1;
DECLARE @Offset smallint = 10;
DECLARE @x1 smallint = 100;
DECLARE @y1 smallint = 100;
DECLARE @x2 smallint = 150;
DECLARE @y2 smallint = 100;
DECLARE @x3 smallint = 125;
DECLARE @y3 smallint = 115;
DECLARE @x4 smallint = 100;
DECLARE @y4 smallint = 100;

DECLARE @Tree TABLE( Id tinyint IDENTITY(1 , 1) , 
                       Triangle geometry );
DECLARE @Palline TABLE( Id tinyint IDENTITY(1 , 1) , 
                        Ball geometry ); 

WHILE @i <= @level
    BEGIN

        INSERT INTO @Tree( Triangle )
        VALUES( geometry::STGeomFromText( 'POLYGON ((' + CAST(@x1 AS varchar( 5 )) + ' ' + CAST(@y1 AS varchar( 5 )) + ',' + CAST(@x2 AS varchar( 5 )) + ' ' + CAST(@y2 AS varchar( 5 )) + ',' + CAST(@x3 AS varchar( 5 )) + ' ' + CAST(@y3 AS varchar( 5 )) + ',' + CAST(@x4 AS varchar( 5 )) + ' ' + CAST(@y4 AS varchar( 5 )) + '))' , 0 ));

        INSERT INTO @Palline( Ball )
        VALUES( geometry::STGeomFromText( 'POINT(' + CAST(@x1 AS varchar( 5 )) + ' ' + CAST(@y1 AS varchar( 5 )) + ')' , 0 ));
        INSERT INTO @Palline( Ball )
        VALUES( geometry::STGeomFromText( 'POINT(' + CAST(@x2 AS varchar( 5 )) + ' ' + CAST(@y2 AS varchar( 5 )) + ')' , 0 ));
        INSERT INTO @Palline( Ball )
        VALUES( geometry::STGeomFromText( 'POINT(' + CAST(@x3 AS varchar( 5 )) + ' ' + CAST(@y3 AS varchar( 5 )) + ')' , 0 ));


        SET @x1-=@Offset;
        SET @x2+=@Offset;
        SET @x4-=@Offset;

        SET @y1-=@Offset;
        SET @y2-=@Offset;
        SET @y3-=@Offset;
        SET @y4-=@Offset;

        SET @i+=1;

    END;

SET @x1 = @x3 - @Offset;
SET @x2 = @x3 + @Offset;
SET @x3 = @x3 + @Offset;
SET @x4 = @x2;


INSERT INTO @Tree( Triangle )
VALUES( geometry::STGeomFromText( 'POLYGON ((' + CAST(@x1 AS varchar( 5 )) + ' ' + CAST(@y1 AS varchar( 5 )) + ',' + CAST(@x2 AS varchar( 5 )) + ' ' + CAST(@y2 AS varchar( 5 )) + ',' + CAST(@x2 AS varchar( 5 )) + ' ' + CAST(@y3 AS varchar( 5 )) + ',' + CAST(@x1 AS varchar( 5 )) + ' ' + CAST(@y3 AS varchar( 5 )) + ',' + CAST(@x1 AS varchar( 5 )) + ' ' + CAST(@y1 AS varchar( 5 )) + '))' , 0 ));

SELECT 'Happy SQL Christmas !!!';
SELECT Triangle
  FROM @Tree
UNION ALL
SELECT Ball.STBuffer( 3 )
  FROM @Palline;

[SQL Conference] Self Service Reporting with SSRS 2008 R2

abenedetti — Sun, 18 Apr 2010 19:47:42 GMT

Ho appena terminato le demo per la sessione Reporting della SQL Conference.

Appena in tempo :-)

Sono soddisfatto, credo di riuscire a far vedere un pò di fuochi d’artificio:

pubblicazione / condivisione di report parts
utilizzo di shared datasets
nuove visualizzazioni grafiche:

sparklines
maps e dati geografici

join di datasets

WCP: è andata

abenedetti — Fri, 27 Nov 2009 09:17:03 GMT

Si è concluso ieri l’ormai classico appuntamento annuale di WPC.

Personalmente la migliore WPC a cui abbia mai partecipato: molto emozionante e ricca di sorprese!

Per me: indimenticabile.

Ottime le sessioni, ottimi i feedback, ottima l’aria che si respirava!

Ringrazio i partecipanti, lo staff e tutta l’organizzazione per l’immenso lavoro svolto.

WPC 2009, si parte

abenedetti — Mon, 23 Nov 2009 11:59:00 GMT

WPC, come tutti gli anni, è arrivata.
Domani, 24 Novembre, si apriranno i cancelli della più grande conferenza ICT italiana sulle tecnologie Microsoft.

Come al solito saremo al gran completo insieme a Davide e Gianluca.

Ecco di cosa parlerò:

Come scrivere T-SQL efficiente ed evitare i problemi più comuni

La sessione, che è un pò l'antipasto del nostro corso "SQL Server Development Best Practices", cercherà di far luce su alcuni aspetti basilari per sviluppare applicazioni migliori e più performanti.

Nella seconda sessione giocherò con i Reporting Services:
Dieci buoni motivi per utilizzare al massimo Microsoft SQL Server 2008 Reporting Services

Questa è la lista che mi sono fatto e di cui discuteremo:

Datasources
Integration
Extensible platform
User eXperience
Data region
SQL Server 2008 Features
Performance, scalability. From Express to Enterprise
Configuration, logging
Report Builder: self service reporting solution
RS without RS

Se qualcuno passasse dalla conferenza... si faccia riconoscere!

BIDS Helper

abenedetti — Thu, 19 Nov 2009 23:03:11 GMT

Non ricordavo questo link.

Lo segno a beneficio di tutti :-)

A Visual Studio.Net add-in with features that extend and enhance the functionality of the SQL Server 2005 and SQL Server 2008 BI Development Studio (BIDS).

Qui: http://www.codeplex.com/bidshelper

Filestream Best Practice

abenedetti — Sun, 08 Nov 2009 21:18:50 GMT

Chi segue questo blog saprà che, ormai da mesi, stiamo lavorando pesantemente con il FILESTREAM di SQL Server 2008.

Lo scopo di questo post è raccogliere e mettere in ordine alcune best practices il cui scopo è solo e soltanto quello di massimizzare le performance:

se utilizziamo diversi filegroup per oggetti FILESTREAM: dischi separati
ragionare attentamente sull’utilizzo di volumi compressi (carico CPU)
deframmentare periodicamente i dischi
utilizzare un corretto RAID a seconda dell’utilizzo dell’applicazione (read / write intensive)

disabilitare i nomi 8.3 su NTFS

questa opzione crea, per il file, un secondo nome. Solo per retro-compatibilità con applicazioni 16-bit

NB:

il comando richiede privilegi amministrativi
il comando disabilita la generazione dei nomi su tutti i volumi NTFS della macchina
effettuare un restart della macchina

disabilitare “last access time tracking” su NTFS

probabilmente è un’informazione che non ci serve

NB:

effettuare un restart della macchina

RS 2005, toolbar, reportserver, file axd e problemi di render

abenedetti — Thu, 30 Jul 2009 15:25:51 GMT

Come direbbe un mio amico, un comportamento inquietante mi ha rovinato quasi un’intera giornata…

Scenario: macchina SQL Server 2005 SP2 con Reporting Services acceduti via URL.

I report si vedono perfettamente dal Report Manager (http://srvsql/Reports) ma non dal Report Server (http://srvsql/Reportserver)

Questo quello che si ottiene:

La toolbar non viene renderizzata ed il report non viene caricato / visualizzato (il report viene invece elaborato correttamente se viene specificato sulla URL il parametro rc:toolbar=false).

Oltre a questo decine di errori javascript, come questi:

Error: 'RSClientController' is undefined
Error: 'ReportViewerHoverButton' is undefined

La macchina è un Win 2003 Srv R2 e quindi non si tratta del “noto” problema di IIS7:

Report Viewer Toolbar does not render properly on IIS 7.0

La soluzione, dopo diversi deliri, è stata la seguente:

1. accedere ad IIS ed entrare nelle proprietà dell’applicazione ReportServer

2. accedere alla sua configurazione

3. verificare la “wildcard application maps” per il filto ISAPI aspnet_isapi.dll

4. deselezionare (perchè era selezionato!) il checkbox “verify that file exists”

Ok, tutto regolare!

Perchè questo flag deve essere disabilitato?

Perchè le richieste che arrivano ad IIS sono (basta aprire il sorgente HTML di un report) anche di questo tipo:

<script src="/ReportServer/Reserved.ReportViewerWebControl.axd?…

Questo file axd, in realtà, non esiste ma viene trattato dai binari del report server :-)

La toolbar, infatti, non esiste fisicamente da nessuna parte, ma si trova all’interno del compilato dell’applicazione.

Mi resta solo una domanda (per cui ora non ho risposta): chi / cosa aveva selezionato il checkbox incriminato?!?!