OPENROWSET (Transact-SQL)

Aktualisiert: 12. Dezember 2006

Enthält alle für einen Zugriff auf Remotedaten von einer OLE DB-Datenquelle notwendigen Verbindungsinformationen. Diese Methode ist eine Alternative zum Zugriff auf Tabellen eines Verbindungsservers. Sie ist eine einmalig verwendete Ad-hoc-Methode zum Verbinden und Zugreifen auf Remotedaten mithilfe von OLE DB. Für häufigere Verweise auf OLE DB-Datenquellen verwenden Sie stattdessen Verbindungsserver. Weitere Informationen finden Sie unter Verbindungsserver. Auf die OPENROWSET-Funktion kann in der FROM-Klausel einer Abfrage verwiesen werden, als sei sie ein Tabellenname. Auf die OPENROWSET-Funktion kann auch als Zieltabelle einer INSERT-, UPDATE- oder DELETE-Anweisung verwiesen werden, je nach den Funktionen des OLE DB-Anbieters. Obwohl die Abfrage möglicherweise mehrere Resultsets zurückgibt, gibt OPENROWSET nur das erste Resultset zurück.

OPENROWSET unterstützt auch Massenvorgänge über einen integrierten BULK-Anbieter, mit dem Daten aus einer Datei gelesen und als Rowset zurückgegeben werden können.

Transact-SQL-Syntaxkonventionen

Syntax

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 ] 

Argumente

• 'provider_name'
  Eine Zeichenfolge für den angezeigten Namen (oder die PROGID) des OLE DB-Anbieters, wie er in der Registrierung angegeben wurde. provider_name hat keinen Standardwert.

• 'datasource'
  Eine Zeichenfolgenkonstante, die einer bestimmten OLE DB-Datenquelle entspricht. datasource ist die DBPROP_INIT_DATASOURCE-Eigenschaft, die an die IDBProperties-Schnittstelle des Anbieters übergeben wird, um den Anbieter zu initialisieren. Normalerweise enthält diese Zeichenfolge den Namen der Datenbankdatei, den Namen des Datenbankservers oder einen Namen, mit dem der Anbieter die Datenbank(en) suchen kann.

• 'user_id'
  Eine Zeichenfolgenkonstante für den Benutzernamen, der an den angegebenen OLE DB-Anbieter übergeben wird. user_id gibt den Sicherheitskontext für die Verbindung an und wird als DBPROP_AUTH_USERID-Eigenschaft übergeben, um den Anbieter zu initialisieren. user_id darf kein Microsoft Windows-Anmeldename sein.

• 'password'
  Eine Zeichenfolgenkonstante für das Benutzerkennwort, das an den OLE DB-Anbieter übergeben wird. password wird beim Initialisieren des Anbieters als DBPROP_AUTH_PASSWORD-Eigenschaft übergeben. password darf kein Microsoft Windows-Kennwort sein.

• 'provider_string'
  Eine anbieterspezifische Verbindungszeichenfolge, die als DBPROP_INIT_PROVIDERSTRING-Eigenschaft übergeben wird, um den OLE DB-Anbieter zu initialisieren. provider_string kapselt normalerweise alle zum Initialisieren des Anbieters benötigten Verbindungsinformationen. Eine Liste der Schlüsselwörter, die vom OLE DB-Anbieter von SQL Native Client erkannt werden, finden Sie unter Initialization and Authorization Properties.

• catalog
  Der Name des Katalogs oder der Datenbank, zu dem bzw. der das angegebene Objekt gehört.

• schema
  Der Name des Schemas oder des Besitzers für das angegebene Objekt.

• object
  Der Objektname, der das zu verwendende Objekt eindeutig identifiziert.

• 'query'
  Eine Zeichenfolgenkonstante, die zum Anbieter geschickt und von ihm ausgeführt wird. Die lokale Instanz von SQL Server verarbeitet nicht diese Abfrage, sondern die vom Anbieter zurückgegebenen Abfrageergebnisse (eine Pass-Through-Abfrage). Pass-Through-Abfragen eignen sich bei Anbietern, die ihre Tabellendaten nicht über Tabellennamen verfügbar machen, sondern nur über eine Befehlssprache. Pass-Through-Abfragen werden auf dem Remoteserver unterstützt, wenn der Abfrageanbieter das Command-Objekt von OLE DB und die dafür notwendigen Schnittstellen unterstützt. Weitere Informationen finden Sie unter SQL Native Client (OLE DB) Reference.

• BULK
  Verwendet den BULK-Rowsetanbieter für OPENROWSET, um Daten aus einer Datei zu lesen. In SQL Server 2005 kann OPENROWSET aus einer Datendatei lesen, ohne die Daten in eine Zieltabelle zu laden. Auf diese Weise können Sie OPENROWSET mit einer einfachen SELECT-Anweisung verwenden.

  Die Argumente der Option BULK ermöglichen eine erhebliche Kontrolle darüber, wo das Lesen von Daten begonnen und beendet werden soll, wie mit Fehlern umgegangen wird und wie Daten interpretiert werden sollen. Beispielsweise können Sie angeben, dass die Datendatei als einzeiliges, einspaltiges Rowset vom Datentyp varbinary, varchar oder nvarchar gelesen wird. Das Standardverhalten ist in den folgenden Argumentbeschreibungen erläutert.

  Informationen zum Verwenden der Option BULK finden Sie unter "Hinweise" weiter unten in diesem Thema. Informationen zu den für die Option BULK erforderlichen Berechtigungen finden Sie im Abschnitt "Berechtigungen" weiter unten in diesem Thema.

  Hinweis:
  Wenn OPENROWSET (BULK ...) zum Importieren von Daten mit dem vollständigen Wiederherstellungsmodell verwendet wird, wird die Protokollierung nicht optimiert.

  Informationen zum Vorbereiten von Daten für den Massenimport finden Sie unter Vorbereiten von Daten für den Massenexport oder -import.

• 'data_file'
  Der vollständige Pfad der Datendatei, deren Daten in die Zieltabelle kopiert werden sollen.

• FORMATFILE ='format_file_path'
  Gibt den vollständigen Pfad einer Formatdatei an. SQL Server 2005 unterstützt zwei Formatdateitypen: XML- und Nicht-XML-Formatdateien.

  Eine Formatdatei ist erforderlich, um Spaltentypen im Resultset zu definieren. Die einzige Ausnahme hierzu ist, dass SINGLE_CLOB, SINGLE_BLOB oder SINGLE_NCLOB angegeben ist. In diesem Fall ist die Formatdatei nicht erforderlich.

  Weitere Informationen zu Formatdateien finden Sie unter Verwenden einer Formatdatei für den Massenimport von Daten.

• < bulk_options >
  Gibt mindestens ein Argument für die Option BULK an.

• CODEPAGE = { **'**ACP '| **'**OEM '| **'**RAW '| 'code_page' }
  Gibt die Codepage für die in der Datendatei enthaltenen Daten an. CODEPAGE ist nur dann von Bedeutung, wenn die Daten Spalten vom Typ char, varchar oder text mit mehr als 127 oder weniger als 32 Zeichenwerten enthalten.

  CODEPAGE-Wert	Beschreibung
  ACP	Konvertiert Spalten vom Datentyp char, varchar oder text von der ANSI/Microsoft Windows-Codepage (ISO 1252) in die SQL Server-Codepage.
  OEM (Standard)	Konvertiert Spalten vom Datentyp char, varchar oder text von der OEM-Codepage des Systems in die SQL Server-Codepage.
  RAW	Es erfolgt keine Konvertierung in eine andere Codepage. Dies ist die schnellste Option.
  code_page	Gibt die Quellcodepage an, nach der die Zeichendaten in der Datendatei codiert werden, beispielsweise 850. Die Codepage ist erforderlich, damit SQL Server 2005-Datenbankmodul die Eingabedaten ordnungsgemäß interpretiert.

• ERRORFILE ='file_name'
  Gibt die Datei an, die zum Sammeln der Zeilen verwendet wird, die Formatierungsfehler enthalten und nicht in ein OLE DB-Rowset konvertiert werden können. Diese Zeilen werden aus der Datendatei unverändert in diese Fehlerdatei kopiert.

  Die Fehlerdatei wird zu Beginn der Ausführung des Befehls erstellt. Falls die Datei bereits vorhanden ist, wird ein Fehler ausgelöst. Darüber hinaus wird eine Kontrolldatei mit der Erweiterung .ERROR.txt erstellt. Diese Datei enthält einen Verweis auf jede Zeile in der Fehlerdatei und stellt eine Fehlerdiagnose bereit. Sobald die Fehler korrigiert wurden, können die Daten geladen werden.

• FIRSTROW **=**first_row
  Gibt die Nummer der ersten zu ladenden Zeile an. Der Standardwert ist 1 und steht für die erste Zeile in der angegebenen Datendatei. Die Zeilennummern werden durch Zählen der Zeilenabschlusszeichen bestimmt.

• LASTROW **=**last_row
  Gibt die Nummer der letzten zu ladenden Zeile an. Der Standardwert ist 0 und steht für die letzte Zeile in der angegebenen Datendatei.

• MAXERRORS **=**maximum_errors
  Gibt an, nach wie vielen Syntaxfehlern oder nicht übereinstimmenden Zeilen gemäß der Definition im Dateiformat OPENROWSET eine Ausnahme auslöst. Bis zum Erreichen von MAXERRORS ignoriert OPENROWSET fehlerhafte Zeilen und lädt diese nicht, wobei jede fehlerhafte Zeile als ein Fehler gezählt wird.

  Der Standardwert für maximum_errors ist 10.

  Hinweis:
  MAX_ERRORS kann für CHECK-Einschränkungen oder zum Konvertieren der Datentypen money und bigint nicht verwendet werden.

• ROWS_PER_BATCH **=**rows_per_batch
  Gibt die ungefähre Anzahl von Datenzeilen in der Datendatei an. Der Wert sollte von der gleichen Größenordnung sein wie die tatsächliche Zeilenanzahl.

  OPENROWSET importiert eine Datendatei immer als einzelnen Batch. Wenn Sie jedoch für rows_per_batch einen Wert > 0 angeben, verwendet der Abfrageprozessor den Wert von rows_per_batch als Hinweis für die Zuordnung der Ressourcen im Abfrageplan.

  Standardmäßig ist ROWS_PER_BATCH unbekannt. ROWS_PER_BATCH = 0 hat den gleichen Effekt, als würden Sie ROWS_PER_BATCH nicht angeben.

• SINGLE_BLOB
  Gibt den Inhalt von data_file als einzeiliges, einspaltiges Rowset vom Typ varbinary(max) zurück.

  Wichtig:
  Es wird empfohlen, XML-Daten anstelle von mit SINGLE_CLOB und SINGLE_NCLOB ausschließlich mithilfe der SINGLE_BLOB-Option zu importieren, da nur SINGLE_BLOB alle Windows-Codierungskonvertierungen unterstützt.

• SINGLE_CLOB
  Wenn data_file als ASCII gelesen wird, wird der Inhalt als einzeiliges, einspaltiges Rowset vom Typ varchar(max) zurückgegeben, wobei die Sortierung der aktuellen Datenbank verwendet wird.

• SINGLE_NCLOB
  Wenn data_file als UNICODE gelesen wird, wird der Inhalt als einzeiliges, einspaltiges Rowset vom Typ nvarchar(max) zurückgegeben, wobei die Sortierung der aktuellen Datenbank verwendet wird.

Hinweise

Mit OPENROWSET kann nur auf Remotedaten von OLE DB-Datenquellen zugegriffen werden, wenn für den angegebenen Anbieter die Registrierungsoption DisallowAdhocAccess explizit auf 0 festgelegt wird und wenn die erweiterte Konfigurationsoption Ad Hoc Distributed Queries aktiviert ist. Wenn diese Optionen nicht festgelegt sind, ermöglicht das Standardverhalten keinen Ad-hoc-Zugriff.

Beim Zugriff auf OLE DB-Remotedatenquellen wird die Anmelde-ID vertrauenswürdiger Verbindungen nicht automatisch von dem Server delegiert, auf dem der Client mit dem Server verbunden ist, der abgefragt wird. Die Authentifizierungsdelegierung muss konfiguriert sein. Weitere Informationen finden Sie unter Konfigurieren von Verbindungsservern für die Delegierung.

Der Katalog- und Schemaname sind erforderlich, falls der OLE DB-Anbieter mehrere Kataloge und Schemas in der angegebenen Datenquelle unterstützt. Die Angabe von Werten für catalog und schema kann entfallen, falls diese vom OLE DB-Anbieter nicht unterstützt werden. Falls der Anbieter nur Schemanamen unterstützt, muss ein zweiteiliger Name im Format schema**.object angegeben werden. Falls der Anbieter nur Katalognamen unterstützt, muss ein dreiteiliger Name im Format catalog.schema.**object angegeben werden. Dreiteilige Namen müssen für Pass-Through-Abfragen angegeben werden, für die der OLE DB-Anbieter von SQL Native Client verwendet wird. Weitere Informationen finden Sie unter Transact-SQL-Syntaxkonventionen (Transact-SQL).

Für die Argumente von OPENROWSET können keine Variablen verwendet werden.

Verwenden von OPENROWSET mit der Option BULK

Die folgenden Transact-SQL-Erweiterungen unterstützen die OPENROWSET(BULK…)-Funktion:

• Mithilfe einer FROM-Klausel, die mit SELECT verwendet wird, kann OPENROWSET(BULK…) anstelle eines Tabellennamens mit voller SELECT-Funktionalität aufgerufen werden.
  OPENROWSET mit der Option BULK erfordert in der FROM-Klausel einen abhängigen Namen (wird auch als Bereichsvariable oder Alias bezeichnet). Spaltenaliase können angegeben werden. Wenn keine Spaltenaliasliste angegeben wird, sind für die Formatdatei Spaltennamen notwendig. Durch das Angeben von Spaltenaliasen werden die Spaltennamen in der Formatdatei wie folgt außer Kraft gesetzt:
  FROM OPENROWSET(BULK...) AS table_alias
FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
• Mithilfe einer SELECT…FROM OPENROWSET(BULK...)-Anweisung können Daten in einer Datei direkt abgefragt werden, ohne dass die Daten in eine Tabelle importiert werden. Mithilfe von SELECT…FROM OPENROWSET(BULK…)-Anweisungen können auch Bulkspaltenaliase aufgeführt werden, indem eine Formatdatei verwendet wird, um Spaltennamen und auch Datentypen anzugeben.
• Mithilfe einer INSERT...SELECT * FROM OPENROWSET(BULK...)-Anweisung wird ein Massenimport von Daten aus einer Datendatei in eine SQL Server-Tabelle ausgeführt. Weitere Informationen finden Sie unter Importieren von Massendaten mithilfe von BULK INSERT oder OPENROWSET(BULK...).
• Wenn die Option OPENROWSET BULK mit einer INSERT-Anweisung verwendet wird, unterstützt die BULK-Klausel Tabellenhinweise. Zusätzlich zu den regulären Tabellenhinweisen, z. B. TABLOCK, sind für die BULK-Klausel die folgenden speziellen Tabellenhinweise möglich: IGNORE_CONSTRAINTS (ignoriert nur die CHECK- und FOREIGN KEY-Einschränkungen), IGNORE_TRIGGERS, KEEPDEFAULTS und KEEPIDENTITY. Weitere Informationen finden Sie unter Tabellenhinweis (Transact-SQL).

Informationen zum Verwenden von INSERT...SELECT * FROM OPENROWSET(BULK...)-Anweisungen finden Sie unter Importieren und Exportieren von Massendaten. Informationen dazu, wann durch den Massenimport ausgeführte Zeileneinfügevorgänge im Transaktionsprotokoll protokolliert werden, finden Sie unter Voraussetzungen für die minimale Protokollierung beim Massenimport.

Hinweis:
Für die Verwendung von OPENROWSET ist es wichtig, nachvollziehen zu können, wie in SQL Server 2005 mit Identitätswechseln umgegangen wird. Informationen zu Sicherheitsaspekten finden Sie unter Importieren von Massendaten mithilfe von BULK INSERT oder OPENROWSET(BULK...).

Massenexportieren und -importieren von SQLXML-Dokumenten

Verwenden Sie in der Formatdatei einen der folgenden Datentypen für den Massenexport oder -import von SQLXML-Daten.

Berechtigungen

Die Berechtigungen für OPENROWSET werden anhand der Berechtigungen des an den OLE DB-Anbieter übergebenen Benutzernamens bestimmt. Für die Verwendung der Option BULK ist die ADMINISTER BULK OPERATIONS-Berechtigung erforderlich.

Beispiele

A. Verwenden von OPENROWSET mit SELECT und dem OLE DB-Anbieter von SQL Native Client

Im folgenden Beispiel wird der OLE DB-Anbieter von SQL Native Client (SQLNCLI) für den Zugriff auf die HumanResources.Department-Tabelle in der AdventureWorks-Datenbank auf dem Remoteserver Seattle1 verwendet. Mithilfe einer SELECT-Anweisung wird das zurückgegebene Rowset definiert. Die Anbieterzeichenfolge enthält die Schlüsselwörter Server und Trusted_Connection. Diese Schlüsselwörter werden vom OLE DB-Anbieter von SQL Native Client erkannt.

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. Verwenden von Microsoft OLE DB-Anbieter für Jet

Im folgenden Beispiel wird mithilfe von Microsoft OLE DB-Anbieter für Jet auf die Customers-Tabelle in der Northwind-Datenbank von Microsoft Access zugegriffen.

Hinweis:
In diesem Beispiel wird vorausgesetzt, dass Access installiert ist. Um dieses Beispiel auszuführen, müssen Sie die Northwind-Datenbank installieren. Informationen zum Installieren der Northwind-Datenbank finden Sie unter Downloaden der Beispieldatenbanken Northwind und pubs.

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

C. Verwenden von OPENROWSET und einer weiteren Tabelle in einem INNER JOIN

Im folgenden Beispiel werden alle Daten aus der Customers-Tabelle in der Northwind-Datenbank der lokalen Instanz von SQL Server sowie alle Daten der Orders-Tabelle in der Northwind-Datenbank von Microsoft Access auf dem gleichen Computer ausgewählt.

Hinweis:
In diesem Beispiel wird vorausgesetzt, dass Access installiert ist. Um dieses Beispiel auszuführen, müssen Sie die Northwind-Datenbank installieren. Informationen zum Installieren der Northwind-Datenbank finden Sie unter Downloaden der Beispieldatenbanken Northwind und 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. Verwenden von OPENROWSET zum Masseneinfügen von Dateidaten in eine Spalte vom Typ varbinary(max)

Das folgende Beispiel erstellt eine kleine Tabelle für Demonstrationszwecke und fügt Dateidaten aus der Datei Text1.txt, die im Stammverzeichnis C: gespeichert ist, in eine Spalte vom Datentyp varbinary(max) ein.

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. Verwenden des OPENROWSET BULK-Anbieters mit einer Formatdatei zum Abrufen von Zeilen aus einer Textdatei

Im folgenden Beispiel werden mithilfe einer Formatdatei Zeilen aus der durch Tabstopps getrennten Textdatei values.txt abgerufen, die die folgenden Daten enthält:

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

Die Formatdatei values.fmt beschreibt die Spalten 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

In der folgenden Abfrage werden diese Daten abgerufen:

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

Zusätzliche Beispiele

Zusätzliche Beispiele für die Verwendung von INSERT...SELECT * FROM OPENROWSET(BULK...) finden Sie in den folgenden Themen:

• Beispiele für den Massenimport und -export von XML-Dokumenten
• Beibehalten von Identitätswerten beim Massenimport von Daten
• Beibehalten von NULL-Werten oder Verwenden von Standardwerten während des Massenimports
• Verwenden einer Formatdatei für den Massenimport von Daten
• Verwenden des Zeichenformats zum Importieren und Exportieren von Daten
• Überspringen einer Tabellenspalte mithilfe einer Formatdatei
• Auslassen eines Datenfeldes mithilfe einer Formatdatei
• Verwenden einer Formatdatei zum Zuordnen von Tabellenspalten für Datendateifelder

Siehe auch

Verweis

DELETE (Transact-SQL)
FROM (Transact-SQL)
INSERT (Transact-SQL)
OPENDATASOURCE (Transact-SQL)
OPENQUERY (Transact-SQL)
Rowsetfunktionen (Transact-SQL)
SELECT (Transact-SQL)
sp_addlinkedserver (Transact-SQL)
sp_serveroption (Transact-SQL)
UPDATE (Transact-SQL)
WHERE (Transact-SQL)

Andere Ressourcen

Verteilte Abfragen
Importieren und Exportieren von Massendaten
Benutzerdefinierte Funktionen (Datenbankmodul)

Hilfe und Informationen

Informationsquellen für SQL Server 2005

Änderungsverlauf