Cómo utilizar Excel con SQL Server servidores vinculados y consultas distribuidas

Id. de artículo: 306397 - Ver los productos a los que se aplica este artículo
Traducción automáticaAdvertencia: Artículo de Traducción Automática, vea la exención de responsabilidad.
Expandir todo | Contraer todo

En esta página

Resumen

Microsoft SQL Server admite conexiones a otros orígenes de datos OLE DB en un objeto persistente o forma ad hoc. Conexión persistente se conoce como un servidor vinculado; una conexión ad hoc que se realiza por motivos de una sola consulta se conoce como una consulta distribuida.

Libros de Microsoft Excel son un tipo de origen de datos OLE DB que puede consultar a través de SQL Server de esta manera. Este artículo describe la sintaxis que es necesaria configurar un origen de datos de Excel como un servidor vinculado, así como la sintaxis que es necesaria utilizar una consulta distribuida que consulta un origen de datos de Excel.
Volver al principio | Enviar comentarios

Más información

Consultar un origen de datos de Excel en un servidor vinculado

Puede utilizar SQL Server Management Studio o el Administrador corporativo, un sistema de procedimiento almacenado, SMO (objetos de administración de SQL Server) para configurar un origen de datos de Excel como un servidor vinculado de SQL Server o SQL-DMO (objetos de administración distribuida). (SMO sólo están disponibles para Microsoft SQL Server 2005). En todos estos casos, debe establecer siempre las cuatro propiedades siguientes:
  • El nombre que desee utilizar para el servidor vinculado.
  • El de OLE DB proveedor que se puede utilizar para la conexión.
  • El origen de datos o nombre de archivo y ruta de acceso completado para el libro de Excel.
  • La cadena del proveedor , que identifica el destino como un libro de Excel. De forma predeterminada, el proveedor Jet espera una base de datos.
De procedimiento almacenado del sistema sp_addlinkedserver también espera que el @ srvproduct propiedad, que puede ser cualquier valor de cadena.

Nota Si utiliza SQL Server 2005, debe especificar un valor que no está vacío para la propiedad nombre del producto de SQL Server Management Studio o el @ srvproduct propiedad en el procedimiento almacenado para un origen de datos de Excel.

Mediante SQL Server Management Studio o el Administrador corporativo para configurar un origen de datos de Excel como un servidor vinculado

SQL Server Management Studio (SQL Server 2005)
  1. En SQL Server Management Studio, expanda Objetos de servidor en el Explorador de objetos .
  2. Haga clic con el botón secundario en Servidores vinculados y, a continuación, haga clic en nuevo servidor vinculado .
  3. En el panel izquierdo, seleccione la página General y, a continuación, siga estos pasos:
    1. En el primer cuadro de texto, escriba cualquier nombre para el servidor vinculado.
    2. Seleccione la opción otro origen de datos .
    3. En la lista proveedor , haga clic en Proveedor OLE DB de Microsoft Jet 4.0 .
    4. En el cuadro nombre del producto , escriba Excel para el nombre del origen de datos OLE DB.
    5. En el cuadro origen de datos , escriba el nombre completo de ruta de acceso y del archivo de Excel.
    6. En el cuadro cadena de proveedor , escriba Excel 8.0 para un libro de Excel 2002, Excel 2000 o Excel 97.
    7. Haga clic en Aceptar para crear el nuevo servidor vinculado.
Nota En SQL Server Management Studio, no se expanda el nombre del nuevo servidor vinculado para ver la lista de objetos que contiene el servidor.
Administrador corporativo (SQL Server 2000)
  1. En el Administrador corporativo, haga clic para expandir la carpeta seguridad .
  2. Haga clic con el botón secundario en Servidores vinculados y, a continuación, haga clic en nuevo servidor vinculado .
  3. En la ficha General , siga estos pasos:
    1. En el primer cuadro de texto, escriba cualquier nombre para el servidor vinculado.
    2. En el cuadro tipo de servidor , haga clic en otro origen de datos .
    3. En la lista nombre de proveedor , haga clic en Proveedor OLE DB de Microsoft Jet 4.0 .
    4. En el cuadro origen de datos , escriba el nombre de ruta de acceso y completo de Excel el archivo.
    5. En el cuadro cadena de proveedor , escriba Excel 8.0 para un libro de Excel 2002, Excel 2000 o Excel 97.
    6. Haga clic en Aceptar para crear el nuevo servidor vinculado.
  4. Haga clic para expandir el nombre del nuevo servidor vinculado para expandir la lista de objetos que contiene.
  5. En el nuevo nombre de servidor vinculado, haz clic en tablas . Observe que las hojas de cálculo de los rangos con nombre aparecen en el panel derecho.

Utilizar un procedimiento almacenado para configurar un origen de datos de Excel como un servidor vinculado

También puede utilizar de procedimiento almacenado del sistema sp_addlinkedserver para configurar un origen de datos de Excel como un servidor vinculado: 
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
como se indicó anteriormente, este procedimiento almacenado requiere un valor de cadena arbitrario adicionales para el @ srvproduct argumento, que aparece como "Nombre del producto" en la configuración del Administrador corporativo y SQL Server Management Studio. La ubicación @ y @ catálogo no se utilizan argumentos.

Mediante SQL-DMO para configurar un origen de datos de Excel como un servidor vinculado

Puede utilizar SQL Distributed Management Objects para configurar un origen de datos de Excel como un servidor vinculado mediante programación desde Microsoft Visual Basic o en otro lenguaje de programación. Debe proporcionar los mismos cuatro argumentos que son necesarios en la configuración del Administrador corporativo y 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

Utilizar SMO para configurar un origen de datos de Excel como un servidor vinculado

En SQL Server 2005, puede utilizar SQL Server Management Objects (SMO) para configurar un origen de datos de Excel como un servidor vinculado mediante programación. Para ello, puede utilizar Microsoft Visual Basic .NET o en otro lenguaje de programación. Debe proporcionar los argumentos que son necesarios en la configuración de SQL Server Management Studio. El modelo de objeto SMO amplía y reemplaza el modelo de objeto de objetos de administración distribuida (SQL-DMO). Because SMO is compatible with SQL Server version 7.0, SQL Server 2000, and SQL Server 2005, you can also use SMO for configuration of 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

Consultar un origen de datos de Excel en un servidor vinculado

Después de configurar un origen de datos de Excel como un servidor vinculado, puede consultar fácilmente sus datos de Analizador de consultas u otra aplicación cliente. Por ejemplo, para recuperar las filas de datos almacenada en la Hoja1 del archivo de Excel, el código siguiente utiliza el servidor vinculado que configura mediante SQL-DMO: 
SELECT * FROM XLTEST_DMO...Sheet1$
también se puede utilizar OPENQUERY para consultar el servidor vinculado de Excel en forma "passthrough", como sigue: 
SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')
el primer argumento que espera OPENQUERY es el nombre de servidor vinculado. Los delimitadores son necesarios para los nombres de hoja de cálculo, como se muestra anteriormente.

También puede obtener una lista de todas las tablas que están disponibles en el servidor vinculado de Excel utilizando la siguiente consulta: 
EXECUTE SP_TABLES_EX 'XLTEST_DMO'

Consultar un origen de datos de Excel utilizando las consultas distribuidas

Puede utilizar las consultas distribuidas de SQL Server y la función OPENDATASOURCE u OPENROWSET a consulta con frecuencia tener acceso a orígenes de datos Excel de forma ad hoc.

Nota Si utiliza SQL Server 2005, asegúrese de que ha habilitado la opción de Ad Hoc Distributed Queries utilizando SQL Server Surface Area Configuration, como en el siguiente ejemplo: 
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$
nota que OPENROWSET utiliza una sintaxis habitual para el segundo argumento ("cadena de proveedor"): 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Excel 8.0;Database=c:\book1.xls', Sheet1$)
la sintaxis que puede esperar un programador de ActiveX Data Objects (ADO) para el segundo argumento ("cadena de proveedor") con OPENROWSET: 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
esta sintaxis genera el error siguiente desde el proveedor de Jet:
No se pudo encontrar ISAM instalable.
Nota Este error también se produce si escribe DataSource en lugar de Origen de datos . Por ejemplo, el argumento siguiente es incorrecto: 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
Volver al principio | Enviar comentarios

Referencias

Porque SQL Server servidores vinculados y distribuido consultas utilice el proveedor OLE DB, las directrices generales y precauciones acerca de cómo utilizar ADO con Excel aplican aquí. Para obtener más información, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:
257819
(http://support.microsoft.com/kb/257819/ )
Cómo utilizar ADO con datos de Excel desde Visual Basic o desde VBA
Para obtener más información acerca de objetos de administración de SQL Server, visite el siguiente sitio Web de Microsoft Developer Network (MSDN):
http://msdn2.microsoft.com/en-us/library/ms162169(ide).aspx
(http://msdn2.microsoft.com/en-us/library/ms162169(ide).aspx)
Para obtener más información acerca de cómo habilitar la opción de Ad Hoc Distributed Queries , visite el siguiente sitio Web de MSDN:
http://msdn2.microsoft.com/en-us/library/ms189978(ide).aspx
(http://msdn2.microsoft.com/en-us/library/ms189978(ide).aspx)
Volver al principio | Enviar comentarios

Propiedades

Id. de artículo: 306397 - Última revisión: viernes, 02 de noviembre de 2007 - Versión: 6.4
La información de este artículo se refiere a:
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Workgroup Edition
  • Microsoft SQL Server 7.0 Standard Edition
Palabras clave: 
kbmt kbdatabase kbhowto kbjet KB306397 KbMtes
Traducción automática
IMPORTANTE: Este artículo ha sido traducido por un software de traducción automática de Microsoft (http://support.microsoft.com/gp/mtdetails) en lugar de un traductor humano. Microsoft le ofrece artículos traducidos por un traductor humano y artículos traducidos automáticamente para que tenga acceso en su propio idioma a todos los artículos de nuestra base de conocimientos (Knowledge Base). Sin embargo, los artículos traducidos automáticamente pueden contener errores en el vocabulario, la sintaxis o la gramática, como los que un extranjero podría cometer al hablar el idioma. Microsoft no se hace responsable de cualquier imprecisión, error o daño ocasionado por una mala traducción del contenido o como consecuencia de su utilización por nuestros clientes. Microsoft suele actualizar el software de traducción frecuentemente.
Haga clic aquí para ver el artículo original (en inglés): 306397
(http://support.microsoft.com/kb/306397/en-us/ )
Volver al principio | Enviar comentarios

Enviar comentarios

 
Volver al principioVolver al principio