OPENROWSET (Transact-SQL)

Mis à jour : 12 décembre 2006

Inclut toutes les informations de connexion exigées pour accéder à des données distantes à partir d'une source de données OLE DB. Cette méthode est une autre façon d'accéder à des tables dans un serveur lié et constitue une méthode efficace pour vous connecter et accéder à des données distantes en utilisant OLE DB. Pour faire des références plus fréquentes à des sources de données OLE DB, utilisez plutôt des serveurs liés. Pour plus d'informations, consultez Liaison des serveurs. La fonction OPENROWSET peut être référencée dans la clause FROM d'une requête comme s'il s'agissait du nom d'une table. La fonction OPENROWSET peut également être référencée comme table cible d'une instruction INSERT, UPDATE ou DELETE, en fonction des capacités du fournisseur OLE DB. Bien que la requête puisse retourner plusieurs ensembles de résultats, OPENROWSET ne retourne que le premier.

OPENROWSET prend également en charge les opérations en bloc par l'intermédiaire d'un fournisseur BULK intégré qui permet de lire les données d'un fichier et de les retourner comme un ensemble de lignes.

Icône Lien de rubriqueConventions de la syntaxe de Transact-SQL

Syntaxe

OPENROWSET 
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password' 
   | 'provider_string' } 
      , {   [ catalog. ] [ schema. ] object 
       | 'query' 
     } 
   | BULK 'data_file' , 
       { FORMATFILE = 'format_file_path' [ <bulk_options> ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} ) 

<bulk_options> ::=
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ , ERRORFILE = 'file_name' ]
   [ , FIRSTROW = first_row ] 
   [ , LASTROW = last_row ] 
   [ , MAXERRORS = maximum_errors ] 
   [ , ROWS_PER_BATCH = rows_per_batch ] 

Arguments

  • 'provider_name'
    Chaîne de caractères représentant le nom convivial (ou PROGID) du fournisseur OLE DB tel que spécifié dans le Registre. provider_name n'a pas de valeur par défaut.
  • 'datasource'
    Constante de chaîne correspondant à une source de données OLE DB spécifique. datasource est la propriété DBPROP_INIT_DATASOURCE à transmettre à l'interface IDBProperties du fournisseur pour initialiser ce dernier. En général, cette chaîne comporte le nom du fichier de base de données, le nom d'un serveur de base de données ou un nom que comprend le fournisseur pour retrouver la ou les bases de données.
  • 'user_id'
    Constante de chaîne représentant le nom d'utilisateur passé au fournisseur OLE DB spécifié. user_id spécifie le contexte de sécurité de la connexion et est transmis en tant que propriété DBPROP_AUTH_USERID pour initialiser le fournisseur. user_id ne peut pas être un nom de connexion Microsoft Windows.
  • 'password'
    Constante de chaîne représentant le mot de passe utilisateur à passer au fournisseur OLE DB. password est transmis en tant que propriété DBPROP_AUTH_PASSWORD au moment de l'initialisation du fournisseur. password ne peut pas être un mot de passe Microsoft Windows.
  • 'provider_string'
    Chaîne de connexion spécifique au fournisseur qui est passée en tant que propriété DBPROP_INIT_PROVIDERSTRING pour initialiser le fournisseur OLE DB. En général, provider_string encapsule toutes les informations de connexion nécessaires à l'initialisation du fournisseur. Pour la liste des mots clés reconnus par le fournisseur SQL Native Client OLE DB, consultez Initialization and Authorization Properties.
  • catalog
    Nom du catalogue ou de la base de données où réside l'objet spécifié.
  • schema
    Nom du propriétaire du schéma ou de l'objet pour l'objet spécifié.
  • object
    Nom d'objet qui identifie de façon unique l'objet à manipuler.
  • 'query'
    Constante de chaîne envoyée au fournisseur en vue de son exécution. L'instance locale SQL Server ne traite pas cette requête, mais traite les résultats de la requête retournés par le fournisseur (requête directe). Les requêtes directes sont utiles lorsque les fournisseurs mettent leurs données tabulaires à disposition non pas par l'intermédiaire de noms de tables, mais uniquement au moyen d'un langage de commande. Les requêtes directes sont prises en charge sur le serveur distant à condition que le fournisseur de requêtes prenne en charge l'objet OLE DB Command et ses interfaces obligatoires. Pour plus d'informations, consultez SQL Native Client (OLE DB) Reference.
  • BULK
    Utilise le fournisseur d'ensembles de lignes BULK pour que OPENROWSET lise les données dans un fichier. Dans SQL Server 2005, OPENROWSET peut lire dans un fichier de données sans charger les données dans une table cible. Cela permet d'utiliser OPENROWSET avec une instruction SELECT simple.

    Les arguments de l'option BULK permettent un contrôle significatif sur le début et la fin de la lecture des données, sur le traitement des erreurs et sur l'interprétation des données. Vous pouvez par exemple, spécifier que le fichier de données soit lu comme un ensemble d'une seule ligne et d'une seule colonne du type varbinary, varchar ou nvarchar. Le comportement par défaut est indiqué dans les descriptions des arguments ci-dessous.

    Pour plus d'informations sur l'utilisation de l'option BULK, consultez la section « Remarques » plus loin dans cette rubrique. Pour plus d'informations sur les autorisations requises par l'option BULK, consultez la section « Autorisations » plus loin dans cette rubrique.

    ms190312.note(fr-fr,SQL.90).gifRemarque :
    Lorsqu'elle est utilisée pour importer des données avec le mode de restauration complète, la fonction OPENROWSET (BULK ...) n'optimise pas la journalisation.

    Pour plus d'informations sur la préparation des données pour l'importation en bloc, consultez Préparation des données en vue d'une exportation ou d'une importation en bloc.

  • 'data_file'
    Chemin d'accès complet au fichier dont les données doivent être copiées dans la table cible.
  • FORMATFILE ='format_file_path'
    Spécifie le chemin d'accès complet d'un fichier de format. SQL Server 2005 prend en charge deux types de fichiers de format : XML et non-XML.

    Un fichier de format est requis pour définir les types des colonnes dans l'ensemble de résultats, excepté lorsque SINGLE_CLOB, SINGLE_BLOB ou SINGLE_NCLOB est spécifié ; dans ce cas, le fichier de format n'est pas requis.

    Pour plus d'informations sur les fichiers de format, consultez Utilisation d'un fichier de format pour importer des données en bloc.

  • < bulk_options >
    Spécifie un ou plusieurs arguments pour l'option BULK.
  • CODEPAGE = { **'**ACP '| **'**OEM '| **'**RAW '| 'code_page' }
    Indique la page de codes des données dans le fichier. L'utilisation de CODEPAGE n'est justifiée que si les données contiennent des colonnes du type char, varchar ou text dont les valeurs des caractères sont supérieures à 127 ou inférieures à 32.

    Valeur CODEPAGE Description

    ACP

    Convertit les colonnes dont les données sont du type char, varchar ou text de la page de codes ANSI/Microsoft Windows en page de codes SQL Server.

    OEM (valeur par défaut)

    Convertit les colonnes dont les données sont du type char, varchar ou text de la page de codes du système OEM en page de codes SQL Server.

    RAW

    Aucune conversion n'a lieu d'une page de codes à une autre. Il s'agit de l'option la plus rapide.

    code_page

    Indique la page de codes source sur laquelle est basée le codage des données caractères du fichier de données, par exemple : 850. La page de codes est requise pour que le moteur de base de données SQL Server 2005 interprète correctement les données d'entrée.

  • ERRORFILE ='file_name'
    Fichier utilisé pour collecter les lignes comportant des erreurs de mise en forme et impossibles à convertir en un ensemble de lignes OLE DB. Ces lignes sont copiées « en l'état » du fichier de données vers ce fichier d'erreur.

    Le fichier d'erreur est créé au début de l'exécution de la commande. Une erreur est signalée si le fichier existe déjà. De plus, un fichier de contrôle portant l'extension .ERROR.txt est créé. Il fait référence à chacune des lignes du fichier d'erreur et propose un diagnostic. Lorsque les erreurs sont corrigées, les données peuvent être chargées.

  • FIRSTROW **=**first_row
    Numéro de la première ligne à charger. La valeur par défaut est 1, c'est-à-dire la première ligne du fichier de données spécifié. Les numéros des lignes sont déterminés en comptant les indicateurs de fin de ligne.
  • LASTROW **=**last_row
    Numéro de la dernière ligne à charger. La valeur par défaut est 0, c'est-à-dire la dernière ligne du fichier de données spécifié.
  • MAXERRORS **=**maximum_errors
    Spécifie le nombre maximal d'erreurs de syntaxe ou de lignes non conformes (défini dans le fichier de format) qui peuvent se produire avant que OPENROWSET lève une exception. Tant que la valeur de MAXERRORS n'est pas atteinte, OPENROWSET ignore les lignes incorrectes, ne les charge pas, et les compte comme des erreurs.

    La valeur par défaut de maximum_errors est 10.

    ms190312.note(fr-fr,SQL.90).gifRemarque :
    MAX_ERRORS ne s'applique pas aux contraintes CHECK ou à la conversion dans les types de données money et bigint.
  • ROWS_PER_BATCH **=**rows_per_batch
    Spécifie le nombre approximatif de lignes de données que compte le fichier de données. Cette valeur doit être du même ordre que le nombre réel de lignes.

    OPENROWSET importe toujours un fichier de données en un seul lot. Cependant, si vous spécifiez rows_per_batch avec la valeur >0, le processeur de requêtes se base sur la valeur de rows_per_batch pour allouer les ressources dans le plan de requêtes.

    Par défaut, ROWS_PER_BATCH est inconnu. Si vous spécifiez ROWS_PER_BATCH = 0, le résultat est le même que si vous omettez ROWS_PER_BATCH.

  • SINGLE_BLOB
    Retourne le contenu de data_file sous forme d'un ensemble d'une seule ligne et d'une seule colonne du type varbinary(max).

    ms190312.note(fr-fr,SQL.90).gifImportant :
    Nous vous recommandons d'importer des données XML seulement au moyen de l'option SINGLE_BLOB, au lieu de SINGLE_CLOB et SINGLE_NCLOB, parce que seule SINGLE_BLOB prend en charge toutes les conversions de codage de Windows.
  • SINGLE_CLOB
    La lecture de data_file au format ASCII retourne le contenu sous la forme d'un ensemble d'une seule ligne et d'une seule colonne du type varchar(max) en utilisant le classement de la base de données active.
  • SINGLE_NCLOB
    La lecture de data_file au format UNICODE retourne le contenu sous la forme d'un ensemble d'une seule ligne et d'une seule colonne du type nvarchar(max) en utilisant le classement de la base de données active.

Notes

OPENROWSET peut s'utiliser pour accéder à des données distantes à partir de sources OLE DB uniquement lorsque l'option du Registre DisallowAdhocAccess prend la valeur 0 pour le fournisseur spécifié et si l'option de configuration avancée Ad Hoc Distributed Queries est activée. Lorsque ces options ne sont pas définies, le comportement par défaut n'autorise pas l'accès d'égal à égal.

Lors de l'accès à des sources de données OLE DB distantes, l'identité des connexions approuvées n'est pas automatiquement déléguée du serveur auquel le client est connecté au serveur qui est interrogé. Il est nécessaire de configurer la délégation de l'authentification. Pour plus d'informations, consultez Configuration des serveurs liés pour la délégation.

Les noms de catalogue et de schéma sont requis si le fournisseur OLE DB prend en charge plusieurs catalogues et schémas dans la source de données spécifiée. Les valeurs de catalog et schema peuvent être omises si le fournisseur OLE DB ne les prend pas en charge. Si le fournisseur prend en charge uniquement les noms de schémas, il est nécessaire de spécifier un nom en deux parties, sous la forme schema**.object doit être spécifié. Si le fournisseur prend en charge uniquement les noms de catalogues, il est nécessaire de spécifier un nom en trois parties, sous la forme catalog.schema.**object doit être spécifié. Des noms en trois parties doivent être spécifiés pour les requêtes directes qui utilisent le fournisseur OLE DB SQL Native Client. Pour plus d'informations, consultez Conventions de syntaxe de Transact-SQL (Transact-SQL).

OPENROWSET n'accepte pas de variables pour ses arguments.

Utilisation de OPENROWSET avec l'option BULK

Les améliorations Transact-SQL suivantes prennent en charge la fonction OPENROWSET(BULK…) :

  • Une clause FROM utilisée avec SELECT peut appeler OPENROWSET(BULK…) au lieu d'un nom de table, tout en conservant une fonctionnalité SELECT complète.
    OPENROWSET utilisée avec l'option BULK nécessite un nom de corrélation, également baptisé variable de plage ou alias, dans la clause FROM. Vous pouvez définir des alias de colonnes. Si une liste d'alias de colonnes n'est pas spécifiée, le fichier de format doit comporter les noms des colonnes. La spécification des alias de colonnes remplace les noms de colonnes dans le fichier de format, par exemple :
    FROM OPENROWSET(BULK...) AS table_alias
    FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
  • Une instruction SELECT…FROM OPENROWSET(BULK...) interroge directement les données d'un fichier, sans les importer dans une table. Les instructions SELECT…FROM OPENROWSET(BULK…) peuvent aussi énumérer les alias de colonnes en bloc en utilisant un fichier de format pour spécifier les noms de colonnes ainsi que les types de données.
  • Une instruction INSERT...SELECT * FROM OPENROWSET(BULK...) importe en bloc les données d'un fichier de données dans une table SQL Server. Pour plus d'informations, consultez Importation de données en bloc à l'aide de BULK INSERT ou OPENROWSET(BULK...).
  • Lorsque l'option OPENROWSET BULK est utilisée avec une instruction INSERT, la clause BULK prend en charge les indicateurs de table. Outre les indicateurs de table usuels, tels que TABLOCK, la clause BULK accepte les indicateurs spécialisés suivants : IGNORE_CONSTRAINTS (ignore uniquement les contraintes CHECK et FOREIGN KEY), IGNORE_TRIGGERS, KEEPDEFAULTS et KEEPIDENTITY. Pour plus d'informations, consultez Indicateur de table (T-SQL).

Pour plus d'informations sur l'utilisation des instructions INSERT...SELECT * FROM OPENROWSET(BULK...), consultez Importation et exportation de données en bloc. Pour obtenir des informations sur les opérations d'insertion de lignes qui sont exécutées par importation en bloc et enregistrées dans le journal des transactions, consultez Conditions requises pour une journalisation minimale dans l'importation en bloc.

ms190312.note(fr-fr,SQL.90).gifRemarque :
Lorsque vous utilisez OPENROWSET, il est important que vous compreniez la manière dont SQL Server 2005 gère l'emprunt d'identité. Pour plus d'informations sur les questions de sécurité, consultez Importation de données en bloc à l'aide de BULK INSERT ou OPENROWSET(BULK...).

Importation et exportation en bloc de documents SQLXML

Pour exporter ou importer en bloc des données SQLXML, utilisez l'un des types de données ci-dessous dans votre fichier de format.

Type de données Effet

SQLCHAR ou SQLVARYCHAR

Les données sont envoyées dans la page de codes du client ou dans la page de codes impliquée par le classement.

SQLNCHAR ou SQLNVARCHAR

Les données sont envoyées au format Unicode.

SQLBINARY ou SQLVARYBIN

Les données sont envoyées sans conversion.

Autorisations

Les autorisations OPENROWSET sont conditionnées par les autorisations associées au nom d'utilisateur passé au fournisseur OLE DB. L'utilisation de l'option BULK requiert l'autorisation ADMINISTER BULK OPERATIONS.

Exemples

A. Utilisation de OPENROWSET avec SELECT et le fournisseur SQL Native Client OLE DB

Le code exemple suivant utilise le fournisseur SQL Native Client OLE DB (SQLNCLI) pour accéder à la table HumanResources.Department de la base de données AdventureWorks sur le serveur distant Seattle1. Une instruction SELECT définit l'ensemble de lignes retourné. La chaîne de caractères du fournisseur contient les mots clés Server et Trusted_Connection. Ces mots clés sont reconnus par le fournisseur SQL Native Client OLE DB.

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
     'SELECT GroupName, Name, DepartmentID
      FROM AdventureWorks.HumanResources.Department
      ORDER BY GroupName, Name') AS a;

B. Utilisation du fournisseur Microsoft OLE DB pour Jet

Le code exemple suivant accède à la table Customers de la base de données Northwind Microsoft Access par l'intermédiaire du fournisseur Microsoft OLE DB pour Jet.

ms190312.note(fr-fr,SQL.90).gifRemarque :
L'exécution de ce code exemple suppose que Microsoft Access est installé. Pour exécuter ce code exemple, vous devez installer la base de données Northwind. Pour plus d'informations sur l'installation de la base de données Northwind, consultez Téléchargement des exemples de bases de données Northwind et pubs.
SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
      'admin';'',Customers)
GO

C. Utilisation de OPENROWSET avec une autre table dans une jointure interne INNER JOIN

Le code exemple suivant sélectionne toutes les données de la table Customers dans la base de données Northwind installée sur l'instance locale de SQL Server et dans la table Orders de la base de données Northwind Access stockée sur le même ordinateur.

ms190312.note(fr-fr,SQL.90).gifRemarque :
L'exécution de ce code exemple suppose que Microsoft Access est installé. Pour exécuter ce code exemple, vous devez installer la base de données Northwind. Pour plus d'informations sur l'installation de la base de données Northwind, consultez Téléchargement des exemples de bases de données Northwind et pubs.
USE Northwind  
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c 
   INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'', Orders)    
   AS o 
   ON c.CustomerID = o.CustomerID 
GO

D. Utilisation de OPENROWSET pour insérer en bloc un fichier de données dans une colonne de type varbinary(max)

Le code exemple suivant crée une petite table aux fins de démonstration et insère dans une colonne varbinary(max) les données du fichier Text1.txt situé dans le répertoire racine C:.

USE AdventureWorks
GO
CREATE TABLE myTable(FileName nvarchar(60), 
  FileType nvarchar(60), Document varbinary(max))
GO

INSERT INTO myTable(FileName, FileType, Document) 
   SELECT 'Text1.txt' AS FileName, 
      '.txt' AS FileType, 
      * FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document
GO

E. Utilisation du fournisseur OPENROWSET BULK avec un fichier de format pour extraire des lignes d'un fichier texte

Le code exemple suivant utilise un fichier de format pour extraire des lignes d'un fichier texte dont les données sont délimitées par des tabulations, values.txt, qui contient les données suivantes :

1     Data Item 1
2     Data Item 2
3     Data Item 3

Le fichier de format, values.fmt, décrit les colonnes du fichier values.txt :

9.0
2
1  SQLCHAR  0  10 "\t"        1  ID                SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 "\r\n"      2  Description        SQL_Latin1_General_Cp437_BIN

Voici la requête qui extrait ces données :

SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt', 
   FORMATFILE = 'c:\test\values.fmt') AS a;

Autres exemples

Pour voir d'autres exemples illustrant l'utilisation des instructions INSERT...SELECT * FROM OPENROWSET(BULK...), consultez les rubriques suivantes :

Voir aussi

Référence

DELETE (Transact-SQL)
FROM (Transact-SQL)
INSERT (Transact-SQL)
OPENDATASOURCE (Transact-SQL)
OPENQUERY (Transact-SQL)
Fonctions Rowset (Transact-SQL)
SELECT (Transact-SQL)
sp_addlinkedserver (Transact-SQL)
sp_serveroption (Transact-SQL)
UPDATE (Transact-SQL)
WHERE (Transact-SQL)

Autres ressources

Requêtes distribuées
Importation et exportation de données en bloc
Fonctions définies par l'utilisateur (Moteur de base de données)

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications

Version Historique

12 décembre 2006

Nouveau contenu :
  • Ajout de l'indication selon laquelle l'option de la table IGNORE_CONSTRAINTS ignore les contraintes CHECK et FOREIGN KEY.

17 juillet 2006

Nouveau contenu :
  • Ajout de la phrase spécifiant qu'il faut utiliser des serveurs liés au lieu de OPENROWSET pour faire des références plus fréquentes à des sources de données OLE DB.
  • Les modifications suivantes ont été effectuées dans la section « Remarques » :
    • Ajout du paragraphe concernant la nécessité de configurer la délégation de l'authentification lors de l'accès à des sources de données OLE DB par le biais d'un double saut.
    • Ajout de la mention indiquant que des noms en trois parties doivent être spécifiés pour les requêtes directes qui utilisent le fournisseur OLE DB SQL Native Client.
    • Ajout d'informations supplémentaires à propos de l'utilisation de l'option BULK.