Riepilogo
Microsoft SQL Server supporta le connessioni ad altre origini dati OLE DB in modo permanente o ad hoc. La connessione permanente è nota come server collegato. Una connessione ad hoc effettuata ai fini di una singola query è nota come query distribuita. Le cartelle di lavoro di Microsoft Excel sono un tipo di origine dati OLE DB su cui è possibile eseguire query tramite SQL Server in questo modo. In questo articolo viene descritta la sintassi necessaria per configurare un'origine dati di Excel come server collegato, nonché la sintassi necessaria per utilizzare una query distribuita che esegue query su un'origine dati di Excel.
Ulteriori informazioni
Esecuzione di query su un'origine dati di Excel in un server collegato
È possibile utilizzare SQL Server Management Studio o Enterprise Manager, una stored procedure di sistema, SQL-DMO (Distributed Management Objects) o SMO (SQL Server Management Objects) per configurare un'origine dati Excel come server collegato SQL Server. (SMO sono disponibili solo per Microsoft SQL Server 2005.) In tutti questi casi, è sempre necessario impostare le quattro proprietà seguenti:In all of these cases, you must always set the following four properties:
-
Nome che si desidera utilizzare per il server collegato.
-
Provider OLE DB da utilizzare per la connessione.
-
L'origine dati o il percorso completo e il nome del file per la cartella di lavoro di Excel.
-
Stringa del provider, che identifica la destinazione come cartella di lavoro di Excel. Per impostazione predefinita, il provider Jet prevede un database di Access.
La stored procedure di sistema sp_addlinkedserver prevede anche il @srvproduct proprietà, che può essere qualsiasi valore stringa. Nota: Se si utilizza SQL Server 2005, è necessario specificare un valore diverso per la proprietà Nome prodotto in SQL Server Management StudiooSQL Server Management Studio o per la proprietà @srvproduct nella stored procedure per un'origine dati Di Excel.
Utilizzo di SQL Server Management Studio o Enterprise Manager per configurare un'origine dati di Excel come server collegato
SQL Server Management Studio (SQL Server 2005)
-
In SQL Server Management Studio espandere Oggetti server in Esplora oggetti.
-
Fare clic con il pulsante destro del mouse su Server collegati, quindi scegliere Nuovo server collegato.
-
Nel riquadro sinistro selezionare la pagina Generale e quindi attenersi alla seguente procedura:
-
Nella prima casella di testo digitare un nome per il server collegato.
-
Selezionare l'opzione Altra origine dati.
-
Nell'elenco Provider fare clic su Provider OLE DB Microsoft Jet 4.0.
-
Nella casella Nome prodotto digitare Excel come nome dell'origine dati OLE DB.
-
Nella casella Origine dati digitare il percorso completo e il nome del file di Excel.
-
Nella casella Stringa provider digitare Excel 8.0 per una cartella di lavoro di Excel 2002, Excel 2000 o Excel 97.
-
Fare clic su OK per creare il nuovo server collegato.
-
Nota: In SQL Server Management StudioSQL Server Management Studio, è possibile espandere il nuovo nome del server collegato per visualizzare l'elenco di oggetti contenuti nel server.
Enterprise Manager (SQL Server 2000)
-
In Enterprise Manager, fare clic per espandere la cartella protezione .
-
Fare clic con il pulsante destro del mouse su Server collegati, quindi scegliere Nuovo server collegato.
-
Nella scheda Generale eseguire la procedura seguente:
-
Nella prima casella di testo digitare un nome per il server collegato.
-
Nella casella Tipo di server fare clic su Altra origine dati.
-
Nell'elenco Nome provider fare clic su Provider OLE DB Microsoft Jet 4.0.
-
Nella casella Origine dati digitare il percorso completo e il nome del file di Excel.
-
Nella casella Stringa provider digitare Excel 8.0 per una cartella di lavoro di Excel 2002, Excel 2000 o Excel 97.
-
Fare clic su OK per creare il nuovo server collegato.
-
-
Fare clic per espandere il nuovo nome del server collegato e espandere l'elenco di oggetti in esso contenuti.
-
Sotto il nuovo nome del server collegato fare clic su Tabelle. Si noti che i fogli di lavoro e gli intervalli denominati vengono visualizzati nel riquadro di destra.
Utilizzo di una stored procedure per configurare un'origine dati di Excel come server collegato
È inoltre possibile utilizzare la stored procedure di sistema sp_addlinkedserver per configurare un'origine dati Excel come server collegato:You can also use the system stored procedure sp_addlinkedserver to configure an Excel data source as a linked server:
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
Come indicato in precedenza, questa stored procedure richiede un valore stringa aggiuntivo e arbitrario per l'argomento @srvproduct, che viene visualizzato come "Nome prodotto" nella configurazione di Enterprise Manager e SQL Server Management Studio. Gli argomenti @location e @catalog non vengono utilizzati.
Utilizzo di SQL-DMO per configurare un'origine dati Excel come server collegato
È possibile utilizzare gli oggetti di gestione distribuita di SQL PER configurare un'origine dati Excel come server collegato a livello di programmazione da Microsoft Visual Basic o da un altro linguaggio di programmazione. È necessario fornire gli stessi quattro argomenti necessari nella configurazione di Enterprise Manager e 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.CloseEnd Sub
Utilizzo di SMO per configurare un'origine dati di Excel come server collegato
In SQL Server 2005, è possibile utilizzare SQL Server Management Objects (SMO) per configurare un'origine dati Excel come server collegato a livello di codice. A tale scopo, è possibile utilizzare Microsoft Visual Basic .NET o un altro linguaggio di programmazione. È necessario fornire gli argomenti necessari nella configurazione di SQL Server Management Studio. Il modello a oggetti SMO estende e sostituisce il modello a oggetti Distributed Management Objects (SQL-DMO). Poiché SMO è compatibile con SQL Server versione 7.0, SQL Server 2000 e SQL Server 2005, è anche possibile utilizzare SMO per la configurazione di 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 ClassEsecuzione di query su un'origine dati di Excel in un server collegato
Dopo aver configurato un'origine dati Excel come server collegato, è possibile eseguire facilmente query sui dati da Query Analyzer o da un'altra applicazione client. Ad esempio, per recuperare le righe di dati archiviate in Sheet1 del file di Excel, il codice seguente utilizza il server collegato configurato tramite SQL-DMO:
SELECT * FROM XLTEST_DMO...Sheet1$
È inoltre possibile utilizzare OPENQUERY per eseguire una query sul server collegato di Excel in modo "passthrough", come indicato di seguito:
SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')
Il primo argomento previsto da OPENQUERY è il nome del server collegato. I delimitatori sono necessari per i nomi dei fogli di lavoro, come illustrato in precedenza. È inoltre possibile ottenere un elenco di tutte le tabelle disponibili nel server collegato di Excel utilizzando la query seguente:
EXECUTE SP_TABLES_EX 'XLTEST_DMO'
Esecuzione di query su un'origine dati Excel tramite query distribuite
È possibile utilizzare le query distribuite di SQL ServerSQL Server e la funzione OPENDATASOURCE o OPENROWSET per eseguire query su origini dati Di Excel a cui si accede raramente in modo ad hoc. Nota: Se si utilizza SQL Server 2005, assicurarsi di aver abilitato l'opzione Query distribuite ad hoc utilizzando Configurazione superficie di attacco di SQL Server, come nell'esempio seguente:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$Si noti che OPENROWSET utilizza una sintassi non comune per il secondo argomento ("Provider String"):
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\book1.xls', Sheet1$)La sintassi che uno sviluppatore ActiveX Data Objects (ADO) può prevedere di utilizzare per il secondo argomento ("Provider String") con OPENROWSET:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)Questa sintassi genera il seguente errore dal provider Jet:
Impossibile trovare ISAM installabile.
Nota: Questo errore si verifica anche se si immette DataSource anziché Origine dati. Ad esempio, il seguente argomento non è corretto:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$) Riferimenti
Poiché i server collegati di SQL Server SQL Server e le query distribuite utilizzano il provider OLE DB, le linee guida generali e le avvertenze sull'utilizzo di ADO con Excel si applicano qui. Per ulteriori informazioni, fare clic sul seguente numero di articolo per visualizzare l'articolo della Microsoft Knowledge Base:
257819 come utilizzare ADO con dati di Excel da Visual Basic o VBAPer ulteriori informazioni su SQL Server Management Objects, visitare il seguente sito Web Microsoft Developer Network (MSDN):
http://msdn2.microsoft.com/en-us/library/ms162169(ide).aspxPer ulteriori informazioni su come attivare l'opzione query distribuite ad hoc , visitare il seguente sito Web MSDN:
