OPENROWSET (Transact-SQL)

Articolo
12/15/2008

Data aggiornamento: 12 dicembre 2006

Include tutte le informazioni di connessione necessarie per l'accesso remoto ai dati da un'origine dati OLE DB. Si tratta di un metodo alternativo per l'accesso alle tabelle di un server collegato e corrisponde a un metodo ad hoc eseguito una sola volta per la connessione e l'accesso ai dati remoti tramite OLE DB. Per ottenere riferimenti più frequenti alle origini dati OLE DB, utilizzare server collegati. Per ulteriori informazioni, vedere Collegamento di server. È possibile fare riferimento alla funzione OPENROWSET nella clausola FROM di una query come se fosse un nome di tabella. È inoltre possibile farvi riferimento come tabella di destinazione di un'istruzione INSERT, UPDATE o DELETE, a seconda delle capacità del provider OLE DB. Anche quando la query restituisce più set di risultati, la funzione OPENROWSET restituisce solo il primo set.

OPENROWSET supporta anche le operazioni di massa tramite un provider BULK predefinito che consente di leggere i dati da un file e restituirli come set di righe.

Icona di collegamento a un argomento Convenzioni della sintassi Transact-SQL

Sintassi

OPENROWSET 
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password' 
   | 'provider_string' } 
      , {   [ catalog. ] [ schema. ] object 
       | 'query' 
     } 
   | BULK 'data_file' , 
       { FORMATFILE = 'format_file_path' [ <bulk_options> ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} ) 

<bulk_options> ::=
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ , ERRORFILE = 'file_name' ]
   [ , FIRSTROW = first_row ] 
   [ , LASTROW = last_row ] 
   [ , MAXERRORS = maximum_errors ] 
   [ , ROWS_PER_BATCH = rows_per_batch ]

Argomenti

'provider_name'
Stringa di caratteri che rappresenta il nome descrittivo (o PROGID) del provider OLE DB specificato nel Registro di sistema. provider_name non prevede alcun valore predefinito.

'datasource'
Costante stringa che corrisponde a un'origine dati OLE DB specifica. datasource è la proprietà DBPROP_INIT_DATASOURCE da passare all'interfaccia IDBProperties del provider come stringa di inizializzazione. In genere questa stringa include il nome del file di database, il nome di un server di database o un nome riconosciuto dal provider per individuare il database o i database.

'user_id'
Costante stringa che rappresenta il nome utente passato al provider OLE DB specificato. user_id specifica il contesto di protezione per la connessione e viene passato come proprietà DBPROP_AUTH_USERID per l'inizializzazione del provider. user_id non può essere un nome di account di Microsoft Windows.

'password'
Costante stringa che rappresenta la password utente da passare al provider OLE DB. password viene passato come proprietà DBPROP_AUTH_PASSWORD durante l'inizializzazione del provider. password non può essere una password di Microsoft Windows.

'provider_string'
Stringa di connessione specifica del provider passata come proprietà DBPROP_INIT_PROVIDERSTRING per l'inizializzazione del provider OLE DB. provider_string include in genere tutte le informazioni di connessione necessarie per inizializzare il provider. Per un elenco di parole chiave riconosciute dal provider OLE DB di SQL Native Client, vedere Initialization and Authorization Properties.

catalog
Nome del catalogo o database contenente l'oggetto specificato.

schema
Nome dello schema o proprietario dell'oggetto specificato.

object
Nome dell'oggetto che identifica in modo univoco l'oggetto da utilizzare.

'query'
Costante stringa inviata ed eseguita dal provider. Nell'istanza locale di SQL Server non viene elaborata questa query, ma i risultati della query restituiti dal provider (query pass-through). Le query pass-through risultano utili quando vengono utilizzate in provider che non espongono i dati delle tabella tramite i nomi di tabella, ma solo attraverso un linguaggio di comando. Le query pass-through sono supportate nel server remoto, a condizione che il provider delle query supporti l'oggetto OLE DB Command e le relative interfacce obbligatorie. Per ulteriori informazioni, vedere SQL Native Client (OLE DB) Reference.

BULK
Utilizza il provider del set di righe BULK per OPENROWSET per leggere i dati da un file. In SQL Server 2005, OPENROWSET è in grado di leggere da un file di dati senza caricare i dati in una tabella di destinazione. Ciò consente di utilizzare OPENROWSET con un'istruzione SELECT semplice.

Gli argomenti dell'opzione BULK consentono un controllo significativo su dove iniziare e terminare la lettura dei dati, come gestire gli errori e come interpretare i dati. Ad esempio, è possibile specificare che il file di dati venga letto come riga singola, set di righe a colonna singola di tipo varbinary, varchar o nvarchar. Il comportamento predefinito viene descritto nelle descrizioni degli argomenti seguenti.

Per informazioni sull'utilizzo dell'opzione BULK, vedere la sezione "Osservazioni" di seguito in questo argomento. Per informazioni sulle autorizzazioni necessarie per l'opzione BULK, vedere la sezione "Autorizzazioni" di seguito in questo argomento.

[!NOTA] Quando utilizzata per importare i dati con il modello di recupero con registrazione completa, OPENROWSET (BULK ...) non ottimizza la registrazione.

Per informazioni sulla preparazione dei dati per l'importazione di massa, vedere Preparazione dei dati per l'importazione o l'esportazione di massa.

'data_file'
Percorso completo del file di dati i cui dati devono essere copiati nella tabella di destinazione.

FORMATFILE ='format_file_path'
Specifica il percorso completo di un file di formato. SQL Server 2005 supporta due tipi di file di formato: XML e non XML.

Un file di formato è necessario per definire i tipi di colonna nel set dei risultati. L'unica eccezione è quando viene specificato SINGLE_CLOB, SINGLE_BLOB o SINGLE_NCLOB. In questo caso il file di formato non è necessario.

Per ulteriori informazioni sui file di formato, vedere Utilizzo di un file di formato per l'importazione di massa dei dati.

< bulk_options >
Specifica uno o più argomenti per l'opzione BULK.

CODEPAGE = { **'**ACP '| **'**OEM '| **'**RAW '| 'code_page' }
Specifica la tabella codici dei dati contenuti nel file. CODEPAGE è rilevante solo se i dati contengono colonne di tipo char, varchar o text con valori di carattere maggiori di 127 o minori di 32.

Valore CODEPAGE	Descrizione
ACP	Converte le colonne di tipo char, varchar o text dalla tabella codici ANSI/Microsoft Windows (ISO 1252) nella tabella codici SQL Server.
OEM (predefinito)	Converte le colonne di tipo char, varchar o text dalla tabella codici OEM del sistema a quella di SQL Server.
RAW	Non vengono eseguite conversioni tra tabelle codici. Si tratta dell'opzione più rapida.
code_page	Indica la tabella codici di origine in cui vengono codificati i dati di tipo carattere del file di dati, ad esempio 850. La tabella codici è necessaria affinché Motore di database di SQL Server 2005 possa interpretare correttamente i dati di input.

ERRORFILE ='file_name'
Specifica il file in cui raccogliere le righe con errori di formattazione e che non possono essere convertite in un set di righe OLE DB. Tali righe vengono copiate nel file degli errori dal file di dati così come sono.

Il file di errori viene creato all'inizio dell'esecuzione del comando. Se il file esiste già viene generato un errore. Viene inoltre creato un file di controllo con estensione ERROR.txt. Questo file contiene un riferimento a ogni riga nel file degli errori e offre informazioni di diagnostica. Dopo la correzione degli errori, i dati possono essere caricati.

FIRSTROW **=**first_row
Specifica il numero della prima riga da caricare. Il valore predefinito è 1, che indica la prima riga del file di dati specificato. I numeri di riga sono determinati dal conteggio dei caratteri di terminazione.

LASTROW **=**last_row
Specifica il numero dell'ultima riga da caricare. Il valore predefinito è 0, che indica l'ultima riga nel file di dati specificato.

MAXERRORS **=**maximum_errors
Specifica il numero massimo di errori di sintassi o righe non conformi, definite nel file di formato, che possono verificarsi prima che OPENROWSET generi un'eccezione. Fino al raggiungimento di MAXERRORS, OPENROWSET ignora ogni riga non conforme, non caricandola, e conteggia la riga non conforme come un errore.

Il valore predefinito per maximum_errors è 10.

[!NOTA] MAX_ERRORS non si applica ai vincoli CHECK, o ai tipi di dati di conversione money e bigint.

ROWS_PER_BATCH **=**rows_per_batch
Specifica il numero approssimativo di righe di dati nel file di dati. Questo valore deve essere dello stesso ordine del numero effettivo di righe.

OPENROWSET importa sempre un file di dati come batch singolo. Tuttavia, se si specifica rows_per_batch con un valore > 0, Query Processor utilizza il valore di rows_per_batch come hint per allocare risorse nel piano di query.

Per impostazione predefinita, il valore ROWS_PER_BATCH è sconosciuto. La specifica di ROWS_PER_BATCH = 0 equivale all'omissione di ROWS_PER_BATCH.

SINGLE_BLOB
Restituisce il contenuto di data_file come set di righe a riga singola e colonna singola del tipo varbinary(max).

Importante:
Per l'importazione di dati XML è consigliabile utilizzare solo l'opzione SINGLE_BLOB anziché SINGLE_CLOB and SINGLE_NCLOB. Solo SINGLE_BLOB, infatti, supporta tutti i tipi di conversione di codifica di Windows.

SINGLE_CLOB
Leggendo data_file come ASCII, restituisce il contenuto come set di righe a riga singola e colonna singola del tipo varchar(max), utilizzando le regole di confronto del database corrente.

SINGLE_NCLOB
Leggendo data_file come UNICODE, restituisce il contenuto come set di righe a riga singola e colonna singola del tipo nvarchar(max), utilizzando le regole di confronto del database corrente.

Osservazioni

È possibile utilizzare OPENROWSET per accedere ai dati remoti dalle origini dati OLE DB solo quando l'opzione del Registro di sistema DisallowAdhocAccess è impostata esplicitamente su 0 per il provider specificato e l'opzione di configurazione avanzata Ad Hoc Distributed Queries è attivata. Quando queste opzioni non vengono impostate, il comportamento predefinito non consente l'accesso ad hoc.

Quando si accede alle origini dati OLE DB remote, l'identità dell'account di accesso delle connessioni trusted non viene delegata automaticamente dal server in cui il client è connesso al server su cui viene eseguita la query. È necessario configurare la delega dell'autenticazione. Per ulteriori informazioni, vedere Configurazione di server collegati per la delega.

Se il provider OLE DB supporta più cataloghi e schemi nell'origine dati specificata, è necessario specificare i nomi di catalogo e di schema. I valori per catalog e schema possono essere omessi quando non sono supportati dal provider OLE DB. Se il provider supporta solo nomi di schema, è necessario specificare un nome composto da due parti nel formato schema**.object . Se il provider supporta solo nomi di catalogo, è necessario specificare un nome composto da tre parti nel formato catalog.schema.**object. È necessario specificare nomi composti da tre parti per le query pass-through che utilizzano il provider OLE DB per SQL Native Client. Per ulteriori informazioni, vedere Convenzioni della sintassi Transact-SQL (Transact-SQL).

La funzione OPENROWSET non accetta variabili come argomenti.

Utilizzo di OPENROWSET con l'opzione BULK

I miglioramenti di Transact-SQL seguenti supportano la funzione OPENROWSET(BULK…):

Una clausola FROM utilizzata con SELECT può chiamare OPENROWSET(BULK…) anziché un nome di tabella. In questo modo, sono disponibili tutte le funzionalità dell'istruzione SELECT.
OPENROWSET con l'opzione BULK richiede un nome di correlazione, noto anche come alias o variabile di intervallo, nella clausola FROM. È possibile specificare alias di colonne. Se non è specificato un elenco di alias di colonne, il file di formato deve contenere nomi di colonne. Se si specificano gli alias di colonna, i nomi di colonna nel file di formato vengono sostituiti, ad esempio:
FROM OPENROWSET(BULK...) AS table_alias
FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
Un'istruzione SELECT…FROM OPENROWSET(BULK...) consente di eseguire query direttamente sui dati in un file, senza importare i dati in una tabella. Le istruzioni SELECT…FROM OPENROWSET(BULK…) consentono inoltre di elencare alias di colonna di massa utilizzando un file di formato per specificare nomi di colonna e tipi di dati.
Un'istruzione INSERT...SELECT * FROM OPENROWSET(BULK...) consente di eseguire l'importazione di massa di dati da un file di dati in una tabella di SQL Server. Per ulteriori informazioni, vedere Importazione di dati per operazioni di massa utilizzando BULK INSERT o OPENROWSET(BULK...) .
Quando l'opzione OPENROWSET BULK viene utilizzata con un'istruzione INSERT, la clausola BULK supporta gli hint di tabella. Oltre agli hint di tabella normali, ad esempio TABLOCK, la clausola BULK può accettare gli hint di tabella specializzati seguenti: IGNORE_CONSTRAINTS (ignora solo i vincoli CHECK e FOREIGN KEY), IGNORE_TRIGGERS, KEEPDEFAULTS e KEEPIDENTITY. Per ulteriori informazioni, vedere table_hint (Transact-SQL).

Per ulteriori informazioni sull'utilizzo di istruzioni INSERT...SELECT * FROM OPENROWSET(BULK...), vedere Importazione ed esportazione di dati dati per operazioni di massa. Per informazioni su quando le operazioni di inserimento di righe eseguite durante l'importazione di massa vengono registrate nel log delle transazioni, vedere Prerequisiti per la registrazione minima nell'importazione di massa.

[!NOTA] Quando si utilizza OPENROWSET, è essenziale comprendere il modo in cui la rappresentazione viene gestita da SQL Server 2005. Per informazioni e considerazioni sulla protezione, vedere Importazione di dati per operazioni di massa utilizzando BULK INSERT o OPENROWSET(BULK...).

Esportazione o importazione di massa di documenti SQLXML

Per l'esportazione o l'importazione di massa di dati SQLXML, utilizzare uno dei tipi di dati seguenti nel file di formato.

Tipo di dati	Effetto
SQLCHAR o SQLVARYCHAR	I dati vengono inviati nella tabella codici del client o nella tabella codici implicita delle regole di confronto.
SQLNCHAR o SQLNVARCHAR	I dati vengono inviati come Unicode.
SQLBINARY o SQLVARYBIN	I dati vengono inviati senza conversione.

Autorizzazioni

Le autorizzazioni OPENROWSET sono determinate dalle autorizzazioni del nome utente che viene passato al provider OLE DB. Per utilizzare l'opzione BULK è necessario disporre dell'autorizzazione ADMINISTER BULK OPERATIONS.

Esempi

A. Utilizzo di OPENROWSET con SELECT e il provider OLE DB di SQL Native Client

Nell'esempio seguente viene utilizzato il provider OLE DB di SQL Native Client (SQLNCLI) per accedere alla tabella HumanResources.Department del database AdventureWorks nel server remoto Seattle1. Viene utilizzata un'istruzione SELECT per definire il set di righe restituito. La stringa del provider contiene le parole chiave Server e Trusted_Connection. Queste parole chiave sono riconosciute dal provider OLE DB di SQL Native Client.

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
     'SELECT GroupName, Name, DepartmentID
      FROM AdventureWorks.HumanResources.Department
      ORDER BY GroupName, Name') AS a;

B. Utilizzo del provider Microsoft OLE DB per Jet

Nell'esempio seguente viene ottenuto l'accesso alla tabella Customers del database Northwind di Microsoft Access tramite il provider Microsoft OLE DB per Jet.

[!NOTA] Nell'esempio si suppone che sia installato Access. Per eseguire questo esempio, è necessario installare il database Northwind. Per informazioni sulle procedure di installazione del database Northwind vedere Download dei database di esempio Northwind e pubs.

SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
      'admin';'',Customers)
GO

C. Utilizzo di OPENROWSET e un'altra tabella in un INNER JOIN

Nell'esempio seguente vengono selezionati tutti i dati dalla tabella Customers dall'istanza locale del database SQL Server Northwind e dalla tabella Orders dal database Northwind Access archiviato nello stesso computer.

[!NOTA] Nell'esempio si presuppone che sia installato Access. Per eseguire questo esempio, è necessario installare il database Northwind. Per informazioni sulle procedure di installazione del database Northwind, vedere Download dei database di esempio Northwind e pubs.

USE Northwind  
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c 
   INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'', Orders)    
   AS o 
   ON c.CustomerID = o.CustomerID 
GO

D. Utilizzo di OPENROWSET per eseguire un inserimento di massa dei dati del file in una colonna varbinary(max).

Nell'esempio seguente viene creata una tabella di piccole dimensioni a titolo dimostrativo e vengono quindi inseriti i dati di file da un file denominato Text1.txt archiviato nella directory principale C: in una colonna varbinary(max).

USE AdventureWorks
GO
CREATE TABLE myTable(FileName nvarchar(60), 
  FileType nvarchar(60), Document varbinary(max))
GO

INSERT INTO myTable(FileName, FileType, Document) 
   SELECT 'Text1.txt' AS FileName, 
      '.txt' AS FileType, 
      * FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document
GO

E. Utilizzo del provider OPENROWSET BULK con un file di formato per recuperare le righe da un file di testo

Nell'esempio seguente viene utilizzato un file di formato per recuperare le righe da un file di testo delimitato da tabulazione, values.txt contenente i dati seguenti:

1     Data Item 1
2     Data Item 2
3     Data Item 3

Il file di formato, values.fmt, descrive le colonne in values.txt:

9.0
2
1  SQLCHAR  0  10 "\t"        1  ID                SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 "\r\n"      2  Description        SQL_Latin1_General_Cp437_BIN

Questa è la query che recupera tali dati:

SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt', 
   FORMATFILE = 'c:\test\values.fmt') AS a;

Esempi aggiuntivi

Per ulteriori esempi relativi all'utilizzo di INSERT...SELECT * FROM OPENROWSET(BULK...), vedere gli argomenti seguenti:

Vedere anche

Cronologia modifiche

Versione	Cronologia
12 dicembre 2006	Nuovo contenuto: Aggiunta dell'informazione che l'hint di tabella IGNORE_CONSTRAINTS consente di ignorare sia i vincoli CHECK che i vincoli FOREIGN KEY.
17 luglio 2006	Nuovo contenuto: Aggiunta della frase in cui viene indicato che è consigliabile utilizzare server collegati anziché la funzione OPENROWSET per ottenere riferimenti più frequenti alle origini dati OLE DB. Applicazione delle modifiche seguenti nella sezione "Osservazione": Aggiunta del paragrafo relativo alla necessità di configurare la delega dell'autenticazione quando si accede a origini dati OLE DB tramite un hop doppio. Aggiunta della frase in cui viene indicato che è necessario specificare nomi composti da tre parti per query pass-through che utilizzano il provider OLE DB per SQL Native Client. Aggiunta di ulteriori informazioni sull'utilizzo dell'opzione BULK.

12 dicembre 2006

Nuovo contenuto:

Aggiunta dell'informazione che l'hint di tabella IGNORE_CONSTRAINTS consente di ignorare sia i vincoli CHECK che i vincoli FOREIGN KEY.

17 luglio 2006