Partager via

OPENROWSET (Transact-SQL)

S’applique à : SQL Server 2016 (13.x) et versions ultérieures

OPENROWSET est une alternative à l’accès aux tables dans un serveur lié et est une méthode ponctuelle de connexion et d’accès à distance. Une OPENROWSET commande T-SQL inclut toutes les informations de connexion requises pour accéder aux données distantes à partir d’une source de données externe.

La OPENROWSET fonction peut être référencée dans la FROM clause d’une requête comme s’il s’agissait d’un nom de table. La OPENROWSET fonction peut également être référencée en tant que table cible d’une instruction ou d’une INSERTinstruction, UPDATEDELETE sous réserve des fonctionnalités du fournisseur de données. Bien que la requête puisse retourner plusieurs jeux de résultats, OPENROWSET ne retourne que le premier.

Conseil / Astuce

Pour obtenir des références plus fréquentes à des sources de données externes, utilisez plutôt des serveurs liés. Pour plus d’informations, consultez Serveurs liés (moteur de base de données).

OPENROWSET sans l’opérateur n’est BULK disponible que sur SQL Server. Détails et liens vers des exemples similaires sur d’autres plateformes :

Conventions de la syntaxe Transact-SQL

Syntaxe

OPENROWSET la syntaxe est utilisée pour interroger des sources de données externes :

OPENROWSET
(  'provider_name'
    , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
    , {  [ catalog. ] [ schema. ] object | 'query' }
)

Les arguments

'provider_name'

Chaîne de caractères qui représente le nom convivial (ou PROGID) du fournisseur de données tel que spécifié dans le Registre. provider_name n’a aucune valeur par défaut. Des exemples de noms de fournisseurs sont MSOLEDBSQL, Microsoft.Jet.OLEDB.4.0 ou MSDASQL.

'datasource'

Constante de chaîne qui correspond à une source de données particulière. la source de données est la DBPROP_INIT_DATASOURCE propriété à transmettre à l’interface IDBProperties du fournisseur pour initialiser le fournisseur. En règle générale, cette chaîne inclut le nom du fichier de base de données, le nom d’un serveur de base de données ou un nom que le fournisseur comprend pour localiser la base de données ou les bases de données.

La source de données peut être le chemin de fichier C:\SAMPLES\Northwind.mdb' pour le fournisseur Microsoft.Jet.OLEDB.4.0 ou la chaîne de connexion Server=Seattle1;Trusted_Connection=yes; pour le fournisseur MSOLEDBSQL.

'user_id'

Constante de chaîne qui correspond au nom d’utilisateur transmis au fournisseur de données spécifié. user_id spécifie le contexte de sécurité de la connexion et est transmis en tant que DBPROP_AUTH_USERID propriété pour initialiser le fournisseur. user_id ne peut pas être un nom de connexion Microsoft Windows.

'password'

Constante de chaîne qui correspond au mot de passe utilisateur à passer au fournisseur de données. le mot de passe est transmis en tant que propriété lors de l’initialisation DBPROP_AUTH_PASSWORD du fournisseur. le mot de passe ne peut pas être un mot de passe Microsoft Windows. Par exemple:

SELECT a.* FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'C:\SAMPLES\Northwind.mdb';
    '<user name>';
    '<password>',
    Customers
) AS a;

'provider_string'

Un chaîne de connexion spécifique au fournisseur qui est passé en tant que DBPROP_INIT_PROVIDERSTRING propriété 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 obtenir la liste des mots clés que le fournisseur OLE DB SQL Server Native Client reconnaît, consultez Les propriétés d’initialisation et d’autorisation (fournisseur OLE DB Native Client) . SQL Server Native Client (souvent abrégé en SNAC) a été supprimé dans SQL Server 2022 (16.x) et SQL Server Management Studio 19 (SSMS). Le fournisseur OLE DB pour SQL Server Native Client (SQLNCLI ou SQLNCLI11) et le fournisseur Microsoft OLE DB hérité pour SQL Server (SQLOLEDB) ne sont pas recommandés dans les nouveaux développements. Utilisez à la place le nouveau Microsoft OLE DB Driver (MSOLEDBSQL) pour SQL Server.

SELECT d.* FROM OPENROWSET(
    'MSOLEDBSQL',
    'Server=Seattle1;Trusted_Connection=yes;',
    Department
) AS d;

[ catalogue. ] [ schéma. ] objet

Table ou vue distante contenant les données devant être lues par OPENROWSET. Il peut s’agir d’un objet au nom en trois parties avec les composants suivants :

  • catalog (facultatif) : nom du catalogue ou de la base de données où réside l’objet spécifié.
  • schema (facultatif) : 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.
SELECT d.* FROM OPENROWSET(
    'MSOLEDBSQL',
    'Server=Seattle1;Trusted_Connection=yes;',
    AdventureWorks2022.HumanResources.Department
) AS d;

'query'

Constante de chaîne envoyée et exécutée par le fournisseur. L’instance locale de SQL Server ne traite pas cette requête, mais traite les résultats de requête retournés par le fournisseur, une requête directe. Les requêtes directes sont utiles lorsqu’elles sont utilisées sur les fournisseurs qui ne rendent pas disponibles leurs données tabulaires par le biais de noms de tables, mais uniquement par le biais 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 les interfaces SQL Server Native Client (OLE DB).

SELECT a.*
FROM OPENROWSET(
    'MSOLEDBSQL',
    'Server=Seattle1;Trusted_Connection=yes;',
    'SELECT TOP 10 GroupName, Name FROM AdventureWorks2022.HumanResources.Department'
) AS a;

Notes

OPENROWSET ne peut être utilisé pour accéder à des données distantes à partir de sources de données OLE DB uniquement si l’option de Registre DisallowAdhocAccess est explicitement définie sur 0 pour le fournisseur spécifié et que 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 ad hoc.

Lorsque vous accédez à des sources de données OLE DB distantes, l’identité de connexion des connexions approuvées n’est pas automatiquement déléguée à partir du serveur sur lequel le client est connecté au serveur interrogé. Il est nécessaire de configurer la délégation de l'authentification.

Les noms de catalogue et de schéma sont requis si le fournisseur de données prend en charge plusieurs catalogues et schémas dans la source de données spécifiée. Les valeurs pour catalog et schema peuvent être omises lorsque le fournisseur de données ne les prend pas en charge. Si le fournisseur prend uniquement en charge les noms de schéma, un nom en deux parties du formulaire schema.object doit être spécifié. Si le fournisseur prend uniquement en charge les noms de catalogue, un nom en trois parties du formulaire catalog.schema.object doit être spécifié. Pour plus d’informations, consultez Conventions de la syntaxe Transact-SQL.

Les noms en trois parties sont requis pour les requêtes directes qui utilisent le fournisseur OLE DB SQL Server Native Client.

OPENROWSET n’accepte pas les variables pour ses arguments.

Tout appel à OPENDATASOURCE, OPENQUERY ou OPENROWSET dans la clause FROM est évalué séparément et indépendamment de tout appel à ces fonctions utilisé comme cible de la mise à jour, même si des arguments identiques sont fournis aux deux appels. En particulier, les conditions de filtre ou de jointure appliquées au résultat de l’un de ces appels n’ont aucun effet sur les résultats de l’autre.

Autorisations

OPENROWSET les autorisations sont déterminées par les autorisations du nom d’utilisateur transmis au fournisseur de données.

Exemples

Cette section fournit des exemples généraux pour montrer comment utiliser OPENROWSET.

Remarque

Pour obtenir des exemples illustrant l’utilisation INSERT...SELECT * FROM OPENROWSET(BULK...), consultez OPENROWSET BULK (Transact-SQL).

SQL Server Native Client (souvent abrégé en SNAC) a été supprimé dans SQL Server 2022 (16.x) et SQL Server Management Studio 19 (SSMS). Le fournisseur OLE DB pour SQL Server Native Client (SQLNCLI ou SQLNCLI11) et le fournisseur Microsoft OLE DB hérité pour SQL Server (SQLOLEDB) ne sont pas recommandés dans les nouveaux développements. Utilisez à la place le nouveau Microsoft OLE DB Driver (MSOLEDBSQL) pour SQL Server.

R : Utiliser OPENROWSET avec SELECT et le fournisseur OLE DB SQL Server Native Client

L’exemple suivant utilise le fournisseur SQL Server Native Client OLE DB pour accéder à la table HumanResources.Department de la base de données AdventureWorks2022 sur le serveur distant Seattle1. (Utiliser MSOLEDBSQL pour le fournisseur de données OLE DB Microsoft SQL Server moderne qui a remplacé SQLNCLI.) Une SELECT instruction est utilisée pour définir le jeu 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 Server Native Client OLE DB.

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

B. Utiliser le fournisseur Microsoft OLE DB pour Jet

L'exemple suivant accède à la table Customers de la base de données Microsoft Access Northwind via le fournisseur Microsoft OLE DB pour Jet.

Remarque

Cet exemple suppose que Microsoft Access est installé. Pour exécuter cet exemple, vous devez installer la Northwind base de données.

SELECT CustomerID, CompanyName
FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
    'admin';'',
    Customers
);

Chapitre C. Utiliser OPENROWSET et une autre table dans une jointure INTERNE

L’exemple suivant sélectionne toutes les données de la Customers table à partir de l’instance locale de la base de données SQL Server Northwind et de la Orders table de la base de données Microsoft Access Northwind stockée sur le même ordinateur.

Remarque

Cet exemple suppose que Microsoft Access est installé. Pour exécuter cet exemple, vous devez installer la Northwind base de données.

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;

Contenu connexe