OPENROWSET, épisode 1

mercredi, juillet 13, 2011
By David Baffaleuf in SQL Server (dbaffaleuf@capdata-osmozium.com) [72 article(s)]

Alors là c’est un puits sans fond. OPENROWSET() est tellement vaste qu’on peut se demander si on touchera un jour les limites de la chose.

En gros c’est une fonction qui appelle un provider OLEDB, et qui retourne un résultat au format table-valué. Par exemple on l’utilise pour exécuter des requêtes sur des serveurs liés en passant le nom d’un provider (MSDAORA pour oracle, SQLNCLI pour le client natif MSSQL, etc…). Par exemple:

SELECT RemoteDepts.*
FROM OPENROWSET('SQLNCLI', 'Server=MS2K8-Win2008-1;Trusted_Connection=yes;',
     'SELECT * FROM CAPDATA.dbo.DEPARTEMENT') AS RemoteDepts;

Jusque là, rien de révolutionnaire.

Juste pour rire, on réinvente le CSV engine:

Il existe un certain nombre de providers non documentés sur lesquels s’appuient en général  les DMV/DMF et quelques fonctions table-valuées comme fn_dblog(), fn_helpcollations(), etc… qui feront l’objet de l’épisode 2. Il en existe un toutefois, BULK,  qui est parfaitement documenté et très pratique, et qui permet de faire du chargement massif notamment (l’ancêtre du BULK INSERT T-SQL) mais aussi de renvoyer à peu près n’importe quoi sous une forme table-valuée.

Par exemple, on peut reprendre à notre compte le concept du moteur CSV de MySQL, et créer des vues qui mappent des fichiers CSV sur disque, pour visualiser le contenu sans les stocker physiquement dans la base. Un exemple de fichier CSV et de fichier de format (pour plus d’infos voir la page MSDN concernant les fichiers de format):

bulktest.csv:

LOUISH,21941
DAVID,21940
LOUISP,21943
LAURENT,21942
JSEB,21944

bulktest.fmt:

9.0
2
1 SQLCHAR 0 10 "," 1 CurName ""
2 SQLCHAR 0 5 "\r\n" 2 ID ""

La vue T-SQL:

create view CSVENgine
as
    select * from openrowset(bulk 'V:\DBA2\MSSQL.1\MSSQL\Backup\bulktest.csv',
    FORMATFILE = 'V:\DBA2\MSSQL.1\MSSQL\Backup\bulktest.fmt') as CSV1
GO
select * from CSVENgine order by ID desc

CurName    ID
---------- -----
JSEB       21944
LOUISP     21943
LAURENT    21942
LOUISH     21941
DAVID      21940

(5 ligne(s) affectée(s))

Seule petite ombre au tableau, le provider BULK ne permet pas la mise à jour, on ne peut accéder aux données qu’en lecture seule. Mais ça reste quand même bien pratique pour attaquer une trace perfmon directement en SQL.

Charger des fichiers binaires:

Les options SINGLE_BLOB / SINGLE_CLOB/ SINGLE_NCLOB du provider BULK permettent en outre de charger des documents binaires (vidéos, images, PDF, documents word, etc…)  dans des tables, respectivement soit en varbinary(max) / varchar(max) / nvarchar(max) si les fichiers sont inférieurs à 2Gb comme c’est la cas dans l’exemple, soit dans du filestream pour les fichiers supérieurs.

create table Documents (ID numeric identity, title varchar(255), Filedata varbinary(max))

insert into Documents (title, Filedata)
SELECT 'SQL 2008 Licensing',
doc.* from OPENROWSET(BULK 'E:\CAPDATA\DOCUMENTATION\SGBD\SQL Server\WINWORDS\2008
SQL Licensing Overview final.docx', SINGLE_BLOB) as doc

insert into Documents (title, Filedata)
SELECT 'DBM Sharepoint Labs',
doc.* from OPENROWSET(BULK 'E:\CAPDATA\DOCUMENTATION\SGBD\SQL Server\WINWORDS\
DBM_Sharepoint_Labs.docx', SINGLE_BLOB) as doc

insert into Documents (title, Filedata)
SELECT 'DBM and Log Shipping',
doc.* from OPENROWSET(BULK 'E:\CAPDATA\DOCUMENTATION\SGBD\SQL Server\WINWORDS\
DBMandLogShipping.docx', SINGLE_BLOB) as doc

insert into Documents (title, Filedata)
SELECT 'Database Snapshot Performance',
doc.* from OPENROWSET(BULK 'E:\CAPDATA\DOCUMENTATION\SGBD\SQL Server\WINWORDS\
DBSnapshotPerf.docx', SINGLE_BLOB) as doc

Récupérer la sortie d’une procédure stockée sous la forme d’une table:

Une astuce monstrueuse révélée par Ward Pond en 2005, qui détourne l’utilisation d’OPENROWSET pour appeler une procédure stockée sur le serveur local et retransformer le résultat en format table-valué, un peu à la façon dont les tables MDA sont appelées sous Sybase ASE, et qui nous a sauvé la vie bien des fois sur SQL Server 2000 avant que n’apparaissent les DMV:

sp_configure 'Ad Hoc Distributed Queries',1
GO
reconfigure
GO
SELECT  w.SPID, w.Status, w.loginame, w.blk, w.dbname,w.cmd,
Lck.Objid, lck.Type, lck.Mode
FROM    OPENROWSET ('SQLNCLI','Server=(local);TRUSTED_CONNECTION=YES;',
                   'set fmtonly off exec master.dbo.sp_who') as w
INNER JOIN OPENROWSET ('SQLNCLI','Server=(local);TRUSTED_CONNECTION=YES;',
                       'set fmtonly off exec master.dbo.sp_lock') as lck
on lck.spid = w.spid
where w.blk <> 0
GO

SPID   Status       loginame            blk   dbname          cmd     Objid       Type Mode
------ ------------ ------------------- ----- --------------- ------- ----------- ---- --------
55     suspended    DBA2\dbaffaleuf     54    whitepaperz     SELECT  2073058421  TAB  IS
55     suspended    DBA2\dbaffaleuf     54    whitepaperz     SELECT  0           DB   S
55     suspended    DBA2\dbaffaleuf     54    whitepaperz     SELECT  2073058421  RID  S
55     suspended    DBA2\dbaffaleuf     54    whitepaperz     SELECT  2073058421  PAG  IS

Voir plus loin ?

MS fournit des providers OLEDB pour s’interfacer avec OPENROWSET, certains sont publics (providers relatifs aux serveurs liés, BULK), et certains sont non documentés et utilisés par des vues ou fonctions dynamiques dans SQL Server. C’est ceux-ci que nous essaierons de référencer et de comprendre dans l’épisode 2. Enfin dans l’épisode 3,  nous verrons comment créer son propre provider OLEDB avec Advanced Template Library et Visual C++, et nous essaierons de le faire communiquer avec SQL Server. Du pain sur la planche !

A+ David B.

Continuez votre lecture sur le blog :




Cliquer pour partager cet article sur Viadeo
Cliquer sur "CAPTURER" pour sauvegarder cet article dans Evernote Clip to Evernote

Tags: ,

Leave a Reply