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,21944bulktest.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 docRé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 :
- Point-in-time recovery et fn_dump_dblog() (David Baffaleuf) [SQL Server]
- Modes de récupération et journal de transactions, épisode 2 (David Baffaleuf) [SQL ServerVintage]
- Modes de récupération et journal de transactions, épisode 1 (David Baffaleuf) [SQL ServerVintage]
- Pourquoi il faut sauvegarder les bases systèmes (David Baffaleuf) [SQL Server]
- SQL Server 2022 : stockage S3 sans AWS et fichiers Parquet (Capdata team) [AWSSQL Server]