Condividi tramite

OPENROWSET (Transact-SQL)

  • Articolo

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 Server collegati (Motore di database). È 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 bulk 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 ]
   [ , ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ]

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 per l'inizializzazione di quest'ultimo. 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 consente di specificare il contesto di sicurezza 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 accesso 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. In provider_string sono incluse 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 Server Native Client, vedere Proprietà di inizializzazione e di autorizzazione.

  • 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 al provider ed eseguita da questo. L'istanza locale di SQL Server non elabora 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 tabulari 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 di query supporti l'oggetto OLE DB Command e le relative interfacce obbligatorie. Per ulteriori informazioni, vedere Guida di riferimento di SQL Server Native Client (OLE DB).

  • BULK
    Utilizza il provider BULK per set di righe per OPENROWSET per leggere i dati da un file. In SQL Server, 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 illustrato 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 bulk, vedere Preparazione dei dati per l'importazione o l'esportazione bulk (SQL Server).

  • '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 supporta due tipi di file di formato, ovvero XML e non XML.

    Un file di formato è necessario per definire i tipi di colonna nel set di risultati. L'unica eccezione si verifica 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 bulk dei dati (SQL Server).

  • < 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 di dati. 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.

    [!NOTA]

    È consigliabile specificare un nome di regole di confronto per ogni colonna in un file di formato.

    Valore CODEPAGE

    Descrizione

    ACP

    Converte le colonne con tipo di dati char, varchar o text dalla tabella codici ANSI/Microsoft Windows (ISO 1252) a quella di SQL Server.

    OEM (predefinito)

    Converte le colonne con tipo di dati char, varchar o text dalla tabella codici OEM di 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.

    Nota importanteImportante

    SQL Server non supporta la tabella codici 65001 (codifica UTF-8).

  • ERRORFILE ='file_name'
    Specifica il file utilizzato per raccogliere le righe che contengono errori di formattazione e 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 fornisce 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. Questo valore indica la prima riga nel file di dati specificato. I numeri di riga sono determinati dal conteggio dei caratteri di terminazione. FIRSTROW è in base 1.

  • LASTROW =last_row
    Specifica il numero dell'ultima riga da caricare. Il valore predefinito è 0. Questo valore 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 alla conversione dei tipi di dati 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. Se, tuttavia, 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.

  • ORDER ( { column [ ASC | DESC ] } [ ,... n ] [ UNIQUE ] )
    Hint facoltativo che specifica il modo in cui vengono ordinati i dati nel file di dati. Per impostazione predefinita, per l'operazione bulk si presume che il file di dati non sia ordinato. Se l'ordine specificato può essere sfruttato da Query Optimizer per generare un piano di query più efficiente, le prestazioni possono migliorare. Di seguito vengono riportati alcuni esempi di situazioni in cui può essere utile specificare l'ordinamento:

    • Inserimento di righe in una tabella con un indice cluster, in cui i dati del set di righe sono ordinati in base alla chiave dell'indice cluster.

    • Unione del set di righe con un'altra tabella, in cui le colonne di ordinamento e di join corrispondono.

    • Aggregazione dei dati del set di righe tramite le colonne dell'ordinamento.

    • Utilizzo del set di righe come tabella di origine nella clausola FROM di una query, in cui le colonne di ordinamento e di join corrispondono.

    UNIQUE specifica che il file di dati non ha voci duplicate.

    Se le righe effettive del file di dati non sono ordinate in base all'ordine specificato o se l'hint UNIQUE viene specificato e sono presenti chiavi duplicate, viene restituito un errore.

    Gli alias di colonna sono richiesti quando si utilizza ORDER. L'elenco di alias di colonna deve fare riferimento alla tabella derivata a cui è possibile accedere tramite la clausola BULK. I nomi di colonna specificati nella clausola ORDER si riferiscono a questo elenco di alias di colonna. Non è possibile specificare colonne con tipi di dati per valori di grandi dimensioni (varchar(max), nvarchar(max), varbinary(max) e xml) e tipi di dati LOB (text, ntext e image).

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

    Nota importanteImportante

    Per l'importazione di dati XML è consigliabile utilizzare solo l'opzione SINGLE_BLOB anziché SINGLE_CLOB e SINGLE_NCLOB, in quanto solo SINGLE_BLOB 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 di 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 di 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 è abilitata. 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.

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 di SQL Server Native Client. Per ulteriori informazioni, vedere Convenzioni della sintassi Transact-SQL (Transact-SQL).

La funzione OPENROWSET non accetta variabili come argomenti.

Qualsiasi chiamata a OPENDATASOURCE, OPENQUERY o OPENROWSET nella clausola FROM viene valutata separatamente e indipendentemente da qualsiasi altra chiamata a queste funzioni utilizzate come destinazione dell'aggiornamento, anche se alle due chiamate vengono forniti argomenti identici. In particolare, le condizioni di filtro o join applicate al risultato di una di tali chiamate non hanno effetto sui risultati dell'altra.

Utilizzo di OPENROWSET con l'opzione BULK

I miglioramenti seguenti apportati a Transact-SQL offrono il supporto per 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 colonna, il file di formato deve contenere nomi di colonna. 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 bulk utilizzando un file di formato per specificare nomi di colonna e tipi di dati.

  • L'utilizzo di OPENROWSET(BULK...) come tabella di origine in un'istruzione INSERT o MERGE consente di eseguire l'importazione bulk di dati da un file di dati in una tabella di SQL Server. Per ulteriori informazioni, vedere Importazione di dati per operazioni bulk utilizzando BULK INSERT o OPENROWSET(BULK...) (SQL Server) .

  • 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 Hint di tabella (Transact-SQL).

Per ulteriori informazioni sull'utilizzo di istruzioni INSERT...SELECT * FROM OPENROWSET(BULK...), vedere Informazioni sull'importazione ed esportazione bulk di dati (SQL Server). Per informazioni sui casi in cui le operazioni di inserimento di righe eseguite durante l'importazione bulk vengono registrate nel log delle transazioni, vedere Prerequisiti per la registrazione minima nell'importazione bulk.

[!NOTA]

Quando si utilizza OPENROWSET, è essenziale comprendere il modo in cui la rappresentazione viene gestita da SQL Server. Per informazioni relative alle considerazioni sulla sicurezza, vedere Importazione di dati per operazioni bulk utilizzando BULK INSERT o OPENROWSET(BULK...) (SQL Server).

Importazione bulk di dati SQLCHAR, SQLNCHAR o SQLBINARY

OPENROWSET(BULK...) presuppone che, se non è specificata, la lunghezza massima dei dati SQLCHAR, SQLNCHAR o SQLBINARY non supera 8000 byte. Se i dati importati sono in un campo dati di tipo LOB contenente un oggetto varchar(max), nvarchar(max) o varbinary(max) che supera 8000 byte, è necessario utilizzare un file in formato XML che definisca la lunghezza massima per il campo dati. Per specificare la lunghezza massima, modificare il file di formato dichiarando l'attributo MAX_LENGTH.

[!NOTA]

Un file di formato generato automaticamente non specifica la lunghezza o la lunghezza massima per un campo di tipo LOB. Tuttavia, è possibile modificare un file di formato e specificare la lunghezza o la lunghezza massima manualmente.

Esportazione o importazione bulk di documenti SQLXML

Per l'esportazione o l'importazione bulk 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 utilizzata dalle 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 Server Native Client

Nell'esempio seguente viene utilizzato il provider OLE DB di SQL Server Native Client per accedere alla tabella HumanResources.Department del database AdventureWorks2012 nel server remoto Seattle1. (L'utilizzo di SQLNCLI e SQL Server reindirizza alla versione più recente del provider OLE DB per SQL Server Native Client.) 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 Server Native Client.

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
     'SELECT GroupName, Name, DepartmentID
      FROM AdventureWorks2012.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 presuppone che Access sia installato. Per eseguire l'esempio, è necessario installare il database Northwind.

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 di un'altra tabella in un INNER JOIN

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

[!NOTA]

Nell'esempio si presuppone che Access sia installato. Per eseguire l'esempio, è necessario installare il database Northwind.

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 bulk 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 un file denominato Text1.txt archiviato nella directory radice C: in una colonna varbinary(max).

USE AdventureWorks2012;
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 tabulazioni, 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

Di seguito è illustrata 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

Riferimento

DELETE (Transact-SQL)

FROM (Transact-SQL)

INSERT (Transact-SQL)

OPENDATASOURCE (Transact-SQL)

OPENQUERY (Transact-SQL)

Funzioni per i set di righe (Transact-SQL)

SELECT (Transact-SQL)

sp_addlinkedserver (Transact-SQL)

sp_serveroption (Transact-SQL)

UPDATE (Transact-SQL)

WHERE (Transact-SQL)

Concetti

Informazioni sull'importazione ed esportazione bulk di dati (SQL Server)