Resumen
Microsoft SQL Server admite conexiones a otros orígenes de datos OLE DB de forma persistente o ad hoc. La conexión persistente 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. Los 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. En este artículo se describe la sintaxis necesaria para configurar un origen de datos de Excel como un servidor vinculado, así como la sintaxis necesaria para usar 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 usar SQL Server Management StudioSQL Server Management Studio o Enterprise Manager, un procedimiento almacenado del sistema, SQL-DMO (objetos de administración distribuidos) 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 solo está disponible 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.
-
El proveedor OLE DB que se va a usar para la conexión.
-
El origen de datos o la ruta de acceso completa y el nombre de archivo para el libro de Excel.
-
La cadena de 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 almacenado del sistema sp_addlinkedserver también espera la propiedad @srvproduct, que puede ser cualquier valor de cadena. Nota Si usa SQL Server 2005, debe especificar un valor que no esté vacío para la propiedad Nombre del producto en SQL Server Management StudioSQL Server Management Studio o para la propiedad @srvproduct en el procedimiento almacenado para un origen de datos de Excel.
Uso de SQL Server Management StudioSQL Server Management Studio o Enterprise Manager para configurar un origen de datos de Excel como servidor vinculado
SQL Server Management Studio (SQL Server 2005)
-
En SQL Server Management StudioSQL Server Management Studio, expanda Objetos de servidor en el Explorador de objetos.
-
Haga clic con el botón secundario en Servidores vinculadosy, a continuación, haga clic en Nuevo servidor vinculado.
-
En el panel izquierdo, seleccione la página General y, a continuación, siga estos pasos:
-
En el primer cuadro de texto, escriba cualquier nombre para el servidor vinculado.
-
Seleccione la opción Otro origen de datos.
-
En la lista Proveedor, haga clic en Proveedor OLE DB de Microsoft Jet 4.0.
-
En el cuadro Nombre del producto, escriba Excel para el nombre del origen de datos OLE DB.
-
En el cuadro Origen de datos, escriba la ruta de acceso completa y el nombre de archivo del archivo de Excel.
-
En el cuadro Cadena de proveedor, escriba Excel 8.0 para un libro de Excel 2002, Excel 2000 o Excel 97.
-
Haga clic en Aceptar para crear el nuevo servidor vinculado.
-
Nota En SQL Server Management StudioSQL Server Management Studio, no puede expandir el nuevo nombre de servidor vinculado para ver la lista de objetos que contiene el servidor.
Enterprise Manager (SQL Server 2000)
-
En Enterprise Manager, haga clic para expandir la carpeta Seguridad.
-
Haga clic con el botón secundario en Servidores vinculadosy, a continuación, haga clic en Nuevo servidor vinculado.
-
En la pestaña General, siga estos pasos:
-
En el primer cuadro de texto, escriba cualquier nombre para el servidor vinculado.
-
En el cuadro Tipo de servidor , haga clic en Otro origen de datos.
-
En la lista Nombre del proveedor, haga clic en Proveedor OLE DB de Microsoft Jet 4.0.
-
En el cuadro Origen de datos, escriba la ruta de acceso completa y el nombre de archivo del archivo de Excel.
-
En el cuadro Cadena de proveedor, escriba Excel 8.0 para un libro de Excel 2002, Excel 2000 o Excel 97.
-
Haga clic en Aceptar para crear el nuevo servidor vinculado.
-
-
Haga clic para expandir el nuevo nombre de servidor vinculado para expandir la lista de objetos que contiene.
-
En el nuevo nombre de servidor vinculado, haga clic en Tablas. Observe que las hojas de cálculo y los rangos con nombre aparecen en el panel derecho.
Uso de un procedimiento almacenado para configurar un origen de datos de Excel como servidor vinculado
También puede utilizar el procedimiento almacenado del sistema sp_addlinkedserver 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 valuesSET @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 adicional y arbitrario para el argumento @srvproduct, que aparece como "Nombre del producto" en la configuración de Enterprise Manager y SQL Server Management StudioSQL Server Management Studio. No se utilizan los argumentos @location y @catalog.
Uso de SQL-DMO para configurar un origen de datos de Excel como un servidor vinculado
Puede usar 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 de Enterprise Manager y SQL Server Management StudioSQL 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.CloseEnd Sub
Uso de SMO para configurar un origen de datos de Excel como servidor vinculado
En SQL Server 2005, puede usar objetos de administración de SQL Server (SMO) para configurar un origen de datos de Excel como un servidor vinculado mediante programación. Para ello, puede usar Microsoft Visual Basic .NET u otro lenguaje de programación. Debe proporcionar los argumentos necesarios en la configuración de SQL Server Management StudioSQL Server Management Studio . El modelo de objetos SMO amplía y reemplaza el modelo de objetos de administración distribuida (SQL-DMO). Dado que SMO es compatible con SQL Server versión 7.0, SQL Server 2000 y SQL Server 2005, también puede usar SMO para la configuración de SQL Server 2000.
Imports Microsoft.SqlServer.Management.SmoImports Microsoft.SqlServer.Management.CommonPublic 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 SubEnd ClassConsultar 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 Sheet1 del archivo de Excel, el código siguiente utiliza el servidor vinculado que configuró mediante SQL-DMO:
SELECT * FROM XLTEST_DMO...Sheet1$
También puede utilizar OPENQUERY para consultar el servidor vinculado de Excel de una manera "paso a través", de la siguiente manera:
SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')
El primer argumento que OPENQUERY espera es el nombre del servidor vinculado. Los 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 consultas distribuidas
Puede usar consultas distribuidas de SQL Server y la función OPENDATASOURCE u OPENROWSET para consultar orígenes de datos de Excel a los que se accede con poca frecuencia ad hoc. Nota Si usa SQL Server 2005, asegúrese de que ha habilitado la opción Ad Hoc Distributed Queries mediante la configuración de área de superficie de SQL Server, 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 poco común 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 un desarrollador de ActiveX Data Objects (ADO) puede esperar usar para el segundo argumento ("Provider String") 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 siguiente error del proveedor 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$) Referencias
Dado que los servidores vinculados de SQL ServerSQL Server y las consultas distribuidas usan el proveedor OLE DB, las directrices generales y las precauciones sobre el uso de ADO con Excel se 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 Cómo usar ADO con datos de Excel de Visual Basic o VBAPara 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):
http://msdn2.microsoft.com/en-us/library/ms162169(ide).aspxPara obtener más información acerca de cómo habilitar la opción Ad Hoc Distributed Queries, visite el siguiente sitio Web de MSDN:
