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

Resumen

Microsoft SQL Server admite conexiones a otros orígenes de datos OLE DB en una persistente o forma ad-hoc. La conexión permanente se conoce como un servidor vinculado; una conexión ad hoc que se realiza en aras 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 necesaria utilizar una consulta distribuida que consulta un origen de datos de Excel.

Más información

Consultar un origen de datos de Excel en un servidor vinculado

Puede utilizar SQL Server Management Studio o Administrador corporativo, un sistema de procedimiento almacenado, SQL-DMO (objetos de administración distribuida) o SMO (objetos de administración de SQL Server) para configurar un origen de datos de Excel como un servidor vinculado de SQL Server. (SMO sólo están disponibles para Microsoft SQL Server 2005). En todos estos casos, siempre debe establecer las cuatro propiedades siguientes:
  • El nombre que desea utilizar para el servidor vinculado.
  • OLE DB proveedor que se utiliza para la conexión.
  • El origen de datos o el nombre completo de ruta de acceso y 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 Access.
El procedimiento de almacenado del sistema sp_addlinkedserver también espera que la propiedad @srvproduct , que puede ser cualquier valor de cadena.

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

Utilizando SQL Server Management Studio o 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. (Ratón) en Servidores vinculadosy, 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 la ruta y el nombre completo del archivo 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 puede expandir el nuevo nombre de 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. (Ratón) en Servidores vinculadosy, 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 la ruta y el nombre completo del archivo Excel.
    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 nuevo nombre de servidor vinculado para expandir la lista de objetos que contiene.
  5. En el nuevo nombre de servidor vinculado, haga clic en tablas. Observe que aparecen sus hojas de cálculo y los rangos con nombre en el panel derecho.

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

También puede utilizar el procedimiento de almacenado del sistema sp_addlinkedserver para configurar un origen de datos de Excel como un servidor vinculado:
DECLARE @RC intDECLARE @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 mencionó anteriormente, este procedimiento almacenado requiere un valor de cadena arbitrario adicional para el argumento @srvproduct , que aparece como "Product name" en la configuración del Administrador corporativo y SQL Server Management Studio. No se utilizan los argumentos @location y @catalog .

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

Puede utilizar objetos de administración distribuida de SQL para configurar un origen de datos de Excel como un servidor vinculado mediante programación desde Microsoft Visual Basic u otro lenguaje de programación. Debe proporcionar los mismos cuatro argumentos 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

Con 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 necesarios en la configuración de SQL Server Management Studio. El modelo de objetos SMO amplía y reemplaza el modelo de objetos de objetos de administración distribuida (SQL-DMO). SMO es compatible con SQL Server versión 7.0, SQL Server 2000 y SQL Server 2005, también se puede utilizar SMO para la configuración de SQL Server 2000.
Imports Microsoft.SqlServer.Management.SmoImports 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 desde el analizador de consultas u otra aplicación cliente. Por ejemplo, para recuperar las filas de datos que se almacenan en la Hoja1 del archivo de Excel, el código siguiente utiliza el servidor vinculado que configura utilizando SQL-DMO:
SELECT * FROM XLTEST_DMO...Sheet1$
También puede utilizar OPENQUERY para consultar el servidor vinculado de Excel de una manera "passthrough", como sigue:
SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')
El primer argumento que espera OPENQUERY es el nombre del servidor vinculado. Delimitadores son necesarios para los nombres de hoja de cálculo, como se muestra arriba.

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

Consultar un origen de datos de Excel mediante el uso de consultas distribuidas

Puede utilizar la función OPENDATASOURCE u OPENROWSET y consultas de SQL Server que se distribuyen a orígenes de datos de Excel de consulta que se accede con poca frecuencia en forma ad-hoc.

Nota: Si está utilizando 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 ejemplo siguiente:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$

Tenga en cuenta 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 se puede esperar un programador de ActiveX Data Objects (ADO) que se utilizará 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 se especifica el origen de datos en el 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$) 

Referencias

Porque SQL Server servidores vinculados y distribuye las consultas utilizan el proveedor OLE DB, las orientaciones generales y precauciones acerca del uso de ADO con Excel aplican aquí.
Para obtener más información, haga clic en el siguiente número de artículo para verlo en Microsoft Knowledge Base:

257819 cómo usar ADO con datos de Excel desde Visual Basic o VBA

Para obtener más información acerca de los objetos de administración de SQL Server, visite el siguiente sitio Web de Microsoft Developer Network (MSDN):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:
Propiedades

Id. de artículo: 306397 - Última revisión: 22 ene. 2017 - Revisión: 1

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

Comentarios