Comment faire pour utiliser Excel avec des serveurs liés et des requêtes distribuées SQL Server

Résumé

Microsoft SQL Server prend en charge les connexions à d'autres sources de données OLE DB sur une base permanente et ad hoc. La connexion permanente est appelée serveur lié ; une connexion ad hoc qui est effectuée au profit d'une seule requête est appelée requête distribuée.

Les classeurs Microsoft Excel sont un type de source de données OLE DB que vous pouvez ainsi effectuer comme une requête via SQL Server. Cet article décrit la syntaxe qui est nécessaire à la configuration d'une source de données Excel en tant que serveur lié, ainsi que la syntaxe nécessaire pour utiliser une requête distribuée qui effectue une requête sur une source de données Excel.

Informations supplémentaires

Requête sur une source de données Excel sur un serveur lié

Vous pouvez utiliser SQL Server Management Studio ou Enterprise Manager, une procédure stockée système, SQL-DMO (Distributed Management Objects) ou SMO (SQL Server Management Objects) pour configurer une source de données Excel en tant que serveur lié SQL Server. (Les SMO ne sont disponibles que pour Microsoft SQL Server 2005.) Dans tous ces cas, vous devez toujours définir les quatre propriétés suivantes :

Le nom à utiliser pour le serveur lié.
Le fournisseur OLE DB à utiliser pour la connexion.
La source de données ou le chemin et le nom de fichier complets du classeur Excel.
La chaîne fournisseur, qui identifie la cible en tant que classeur Excel. Par défaut, le fournisseur Jet Provider attend une base de données Access.

La procédure stockée système sp_addlinkedserver attend également la propriété @srvproduct, qui peut être n’importe quelle valeur de chaîne.

Remarque : Si vous utilisez SQL Server 2005, vous devez indiquer une valeur qui ne soit pas vide pour la propriété Nom du produit dans SQL Server Management Studio ou pour la propriété @srvproduct dans la procédure stockée pour une source de données Excel.

Utilisation de SQL Server Management Studio ou Enterprise Manager pour configurer une source de données Excel en tant que serveur lié

SQL Server Management Studio (SQL Server 2005)

Dans SQL Server Management Studio, développez Objets serveur dans Explorateur d’objets.
Cliquez avec le bouton droit sur Serveurs liés, puis cliquez sur Nouveau serveur lié.
Dans le volet gauche, sélectionnez la page Général, puis procédez comme suit :
1. Dans la première zone de texte, tapez un nom pour le serveur lié.
2. Sélectionnez l’option Autre source de données.
3. Dans la liste Fournisseur, cliquez sur Fournisseur Microsoft Jet 4.0 OLE DB.
4. Dans la zone Nom du produit, tapez Excel comme nom de la source de données OLE DB.
5. Dans la zone Source de données, tapez le chemin et le nom de fichier complets du fichier Excel.
6. Dans la zone Chaîne du fournisseur, tapez Excel 8.0 pour un classeur Excel 2002, Excel 2000 ou Excel 97.
7. Cliquez sur OK pour créer le nouveau serveur lié.

Remarque : Dans SQL Server Management Studio, vous ne pouvez pas développer le nom du nouveau serveur lié afin d’afficher la liste des objets qu’il contient.

Enterprise Manager (SQL Server 2000)

Dans Enterprise Manager, développez le dossier Sécurité.
Cliquez avec le bouton droit sur Serveurs liés, puis cliquez sur Nouveau serveur lié.
Sous l’onglet Général, procédez comme suit :
1. Dans la première zone de texte, tapez un nom pour le serveur lié.
2. Dans la zone Type de serveur, cliquez sur Autre source de données.
3. Dans la liste Nom du fournisseur, cliquez sur Fournisseur Microsoft Jet 4.0 OLE DB.
4. Dans la zone Source de données, tapez le chemin et le nom de fichier complets du fichier Excel.
5. Dans la zone Chaîne du fournisseur, tapez Excel 8.0 pour un classeur Excel 2002, Excel 2000 ou Excel 97.
6. Cliquez sur OK pour créer le nouveau serveur lié.
Développez le nouveau nom de serveur lié afin d'afficher la liste des objets qu'il contient.
Sous le nom du nouveau serveur lié, cliquez sur Tables. Notez que vos feuilles de calcul et plages nommées apparaissent dans le volet droit.

Utilisation d'une procédure stockée pour configurer une source de données Excel en tant que serveur lié

Vous pouvez également utiliser la procédure stockée système sp_addlinkedserver pour configurer une source de données Excel en tant que serveur lié :

DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)
-- Set parameter values
SET @server = 'XLTEST_SP'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'c:\book1.xls'
SET @provstr = 'Excel 8.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider, 
@datasrc, @location, @provstr, @catalog

Comme indiqué plus haut, cette procédure stockée requiert une chaîne arbitraire supplémentaire pour l’argument @srvproduct, laquelle apparaît sous la forme de « Nom de produit » dans la configuration Enterprise Manager et SQL Server Management Studio. Les arguments @location et @catalog ne sont pas utilisés.

Utilisation de SQL-DMO pour configurer une source de données Excel en tant que serveur lié

Vous pouvez utiliser SQL Distributed Management Objects pour configurer une source de données Excel en tant que serveur lié par programmation à partir de Microsoft Visual Basic ou d'un autre langage de programmation. Vous devez fournir les quatre mêmes arguments qui sont requis dans la configuration Enterprise Manager et SQL Server Management Studio.

Private Sub Command1_Click()
    Dim s As SQLDMO.SQLServer
    Dim ls As SQLDMO.LinkedServer
    Set s = New SQLDMO.SQLServer
    s.Connect "(local)", "sa", "password"
    Set ls = New SQLDMO.LinkedServer
    With ls
        .Name = "XLTEST_DMO"
        .ProviderName = "Microsoft.Jet.OLEDB.4.0"
        .DataSource = "c:\book1.xls"
        .ProviderString = "Excel 8.0"
    End With
    s.LinkedServers.Add ls
    s.Close
End Sub

Utilisation de SMO pour configurer une source de données Excel en tant que serveur lié

Dans SQL Server 2005, vous pouvez utiliser SQL Server Management Objects (SMO) pour configurer une source de données Excel en tant que serveur lié par programmation. Pour cela, vous pouvez utiliser Microsoft Visual Basic .NET ou un autre langage de programmation. Vous devez fournir les arguments qui sont requis dans la configuration SQL Server Management Studio. Le modèle d'objet SMO étend et remplace le modèle d'objet SQL-DMO (Distributed Management Objects). Étant donné que SMO est compatible avec SQL Server version 7.0, SQL Server 2000 et SQL Server 2005, vous pouvez aussi utiliser SMO pour la configuration de SQL Server 2000.

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim s As Server
        Dim conn As ServerConnection
        Dim ls As LinkedServer

        conn = New ServerConnection("ServerName\InstanceName", "YourUesrName", "YourPassword")
        s = New Server(conn)
        Try
            ls = New LinkedServer(s, "XLTEST_DMO")
            With ls
                .ProviderName = "Microsoft.Jet.OLEDB.4.0"
                .ProductName = "Excel"
                .DataSource = "c:\book1.xls"
                .ProviderString = "Excel 8.0"
            End With
            ls.Create()
            MessageBox.Show("New linked Server has been created.")
        Catch ex As SmoException
            MessageBox.Show(ex.Message)
        Finally
            ls = Nothing
            If s.ConnectionContext.IsOpen = True Then
                s.ConnectionContext.Disconnect()
            End If
        End Try

    End Sub
End Class

Requête sur une source de données Excel sur un serveur lié

Après avoir configuré une source de données Excel en tant que serveur lié, vous pouvez facilement effectuer une requête sur ses données à partir de l'Analyseur de requêtes ou d'une autre application client. Par exemple, pour extraire les lignes de données qui sont stockées dans la Feuille1 de votre fichier Excel, le code suivant utilise le serveur lié que vous avez configuré à l'aide de SQL-DMO :

SELECT * FROM XLTEST_DMO...Sheet1$

Vous pouvez également utiliser OPENQUERY pour effectuer une requête sur le serveur lié Excel en mode « passthrough », comme suit :

SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')

Le premier argument attendu par OPENQUERY est le nom du serveur lié. Des délimiteurs sont requis pour les noms de classeur, comme illustré ci-dessus.

Vous pouvez également obtenir une liste de toutes les tables qui sont disponibles sur le serveur lié Excel à l'aide de la requête suivante :

EXECUTE SP_TABLES_EX 'XLTEST_DMO'

Requête sur une source de données Excel à l'aide de requêtes distribuées

Vous pouvez utiliser des requêtes SQL Server distribuées et la fonction OPENDATASOURCE ou OPENROWSET pour effectuer une requête sur des sources de données Excel rarement utilisées sur une base ad hoc.

Remarque : Si vous utilisez SQL Server 2005, assurez-vous d’avoir activé l’option Requêtes distribuées ad hoc à l’aide de Configuration de la surface d’exposition SQL Server, comme dans l’exemple suivant :

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$

Notez que OPENROWSET utilise une syntaxe qui n'est pas courante pour le second argument (« Chaîne fournisseur ») :

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Excel 8.0;Database=c:\book1.xls', Sheet1$)

Syntaxe que peut s'attendre à utiliser un développeur ActiveX Data Objects (ADO) pour le second argument (« Fournisseur de chaîne ») avec OPENROWSET :

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)

Cette syntaxe génère l'erreur suivante du fournisseur Jet :

Pilote ISAM introuvable.

Remarque : Cette erreur se produit également si vous entrez DataSource au lieu de Data Source. Par exemple, l'argument suivant est incorrect :

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)

Références

Étant donné que les serveurs liés et les requêtes distribuées SQL Server utilisent le Fourniseur OLE DB, les consignes générales et les précautions concernant l'utilisation d'ADO avec Excel s'appliquent ici.
Pour plus d’informations, cliquez sur le numéro ci-dessous pour afficher l’article correspondant dans la Base de connaissances Microsoft :

257819 Comment faire pour utiliser ADO avec des données Excel à partir de Visual Basic ou de VBA

Pour plus d'informations sur SQL Server Management Objects, reportez-vous au site Web MSDN à l'adresse suivante :

http://msdn2.microsoft.com/fr-fr/library/ms162169(ide).aspxPour plus d’informations sur la façon d’activer l’option Requêtes distribuées ad hoc, reportez-vous au site web de MSDN à l’adresse suivante :

http://msdn2.microsoft.com/fr-fr/library/ms189978(ide).aspx

Comment faire pour utiliser Excel avec des serveurs liés et des requêtes distribuées SQL Server

Résumé

Informations supplémentaires

Requête sur une source de données Excel sur un serveur lié

Utilisation de SQL Server Management Studio ou Enterprise Manager pour configurer une source de données Excel en tant que serveur lié

SQL Server Management Studio (SQL Server 2005)

Enterprise Manager (SQL Server 2000)

Utilisation d'une procédure stockée pour configurer une source de données Excel en tant que serveur lié

Utilisation de SQL-DMO pour configurer une source de données Excel en tant que serveur lié

Utilisation de SMO pour configurer une source de données Excel en tant que serveur lié

Requête sur une source de données Excel sur un serveur lié

Requête sur une source de données Excel à l'aide de requêtes distribuées

Références

Besoin d’aide ?

Vous voulez plus d’options ?

Ces informations vous ont-elles été utiles ?

Nous vous remercions de vos commentaires.