Cómo utilizar Excel con servidores vinculados de SQL Server y consultas distribuidas
Los libros de Microsoft Excel son una fuente de datos OLE DB que SQL Server puede consultar de esta manera. Este artículo describe la sintaxis necesaria para configurar una fuente de datos de Excel como un servidor vinculado y para consultar una fuente de datos de Excel mediante consultas distribuidas.
Consultar Excel como un servidor vinculado
Puede configurar una fuente de datos de Excel como un vínculo de SQL Server usando Enterprise Manager, procedimientos almacenados del sistema o un servidor SQL-DMO (Objetos de administración distribuida). . En todos estos casos, siempre necesitará configurar las siguientes cuatro propiedades: El nombre utilizado por el servidor vinculado. El proveedor OLE DB que se utilizará para la conexión. La ruta completa y el nombre del archivo de la fuente de datos o del libro de Excel. Cadena de proveedor que identifica el libro de Excel de destino. De forma predeterminada, el proveedor Jet requiere una base de datos de Access.
El procedimiento almacenado del sistema sp_addlinkedserver también requiere el atributo @srvproduct, que puede ser cualquier valor de cadena.
Configuración del servidor vinculado de Excel mediante Enterprise Manager
1 En Enterprise Manager, haga clic para expandir Carpetas seguras.
2. Haga clic derecho en el servidor vinculado y haga clic en Nuevo servidor vinculado.
3. En la pestaña General, siga estos pasos: a. En el primer cuadro de texto, escriba cualquier nombre para este servidor vinculado. b. En el cuadro de texto Tipo de servidor, haga clic en Otras fuentes de datos. c. En el cuadro de lista desplegable Nombre del proveedor, haga clic en Proveedor OLE DB de Microsoft Jet 4.0. d. En el cuadro de texto Fuente de datos, escriba la ruta completa y el nombre del archivo de Excel. e. En el cuadro de texto Cadena de proveedor, escriba Excel 8.0 para el libro de Excel 97, 2000 o 2002. f. 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 Nuevo nombre de servidor vinculado, haga clic en Tabla. Observe que la hoja de trabajo y el rango con nombre aparecen en el panel derecho.
Utilice procedimientos almacenados para configurar servidores vinculados de Excel
También puede utilizar el procedimiento almacenado del sistema sp_addlinkedserver para configurar la fuente de datos de Excel como un servidor vinculado: DECLARE @RC int
DECLARAR @server nvarchar(128)
DECLARAR @srvproduct nvarchar(128)
DECLARAR @provider nvarchar(128)
DECLARAR@datasrc nvarchar( 4000)
p>DECLARAR @location nvarchar(4000)
DECLARAR @provstr nvarchar(4000)
DECLARAR @catalog nvarchar(128)
-- Valor del parámetro de configuración
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 también requiere un valor de cadena arbitrario como parámetro @srvproduct que aparece como "nombre de producto" en la configuración de Enterprise Manager. Los parámetros @location y @catalog no se utilizan.
Configuración de un servidor vinculado de Excel usando SQL-DMO
Puede configurar mediante programación una fuente de datos de Excel como un servidor vinculado desde Microsoft Visual Basic u otros lenguajes de programación usando SQL Distributed Management Objetos. También debe proporcionar los cuatro parámetros requeridos en la configuración de Enterprise Manager.
Private Sub Command1_Click()
Atenuar s como SQLDMO.SQLServer
Atenuar ls como SQLDMO.LinkedServer
Establecer s = Nuevo SQLDMO.SQLServer
s.Connect "(local)", "sa", "contraseña"
Establecer ls = Nuevo SQLDMO.LinkedServer
Con ls
.Name = "XLTEST_DMO"
.ProviderName = "Microsoft.Jet.OLEDB.4.0"
.DataSource = "c:/book1.xls"
.ProviderString = "Excel 8.0"
Finalizar con
s.LinkedServers.ls
s.Close
End Sub
Consultar servidor vinculado de Excel
Configure la fuente de datos de Excel como un servidor vinculado y sus datos se podrán consultar fácilmente a través de Query Analyzer u otras aplicaciones cliente. Por ejemplo, para recuperar filas de datos almacenados en el archivo de Excel Hoja1, el siguiente código utilizará un servidor vinculado que configuró usando SQL-DMO: SELECT * FROM XLTEST_DMO....Hoja1$
Puede también use OPENQUERY Consulte el servidor vinculado de Excel en modo "paso a través" de la siguiente manera: SELECT * FROM OPENQUERY(XLTEST_DMO, ' SELECT * FROM [Sheet1$]')
El primer parámetro requerido por OPENQUERY es el nombre del servidor vinculado. Los nombres de las hojas de trabajo deben estar separados por delimitadores como se muestra en la imagen de arriba.
También puede utilizar la siguiente consulta para obtener una lista de todas las tablas disponibles en un servidor vinculado de Excel: EXECUTE SP_TABLES_EX 'XLTEST_DMO'
Puede utilizar consultas distribuidas de SQL Server con OPENDATASOURCE o función OPENROWSET Para consultar algunas fuentes de datos de Excel, el método de acceso es el siguiente: SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:/book1.xls ;Propiedades extendidas= Excel 8.0') ... .Sheet1$
Tenga en cuenta que OPENROWSET utiliza una sintaxis no convencional para el segundo parámetro ("cadena de proveedor"): SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB . 4.0',
'Excel 8.0; Database=c:/book1.xls', Sheet1$)
Los desarrolladores de ActiveX Data Objects (ADO) a menudo tienen dudas sobre el parámetro OPENROWSET (" cadena de proveedor") para responder.
.0',
'DataSource=c:/book1.xls;Extended Properties=Excel 8.0',Sheet1$)
Resulta en el siguiente error en el proveedor Jet:< / p>
No se encontró ningún ISAM instalable.