itrain-home Kinderpatenschaften mit Plan Deutschland  
home
 Aktuelle Seite:  knowhow sql transfer adhoc index.asp 
 
Überblick
News
Training
Consulting
Know-how
SQL Server
SQL Server 2005
Administration
Transact SQL
Daten Import/Export
DTS
Ad-hoc
Verbindungsserver
Bulk Copy
Import
Export
Visual Basic
Internet
Windows
WWW Links
Impressum
Copyright
Newsletter
Verlauf
Sitemap



 

Datenimport/-export - Werkzeuge

Ad-hoc Abfragen mit OPENROWSET und OPENDATASOURCE

[Achtung. Diese Seite wird zur Zeit noch bearbeitet...]

Ad-hoc Abfragen sind ein flexibles Mittel, um schnell innerhalb von Transact-SQL auf Daten einer fremden Datenquelle zuzugreifen. Da bei diesem Aufruf die Verbindungsoptionen (insbesondere der Sicherheitskontext) dynamisch angegeben wird, sollte in jedem Fall geprüft werden, ob solche Abfragen überhaupt zugelassen werden sollten (s.dazu auch: OPENROWSET-Problem in SQL Server 7.0).

OPENROWSET

Mit Hilfe der Funktion OPENROWSET kann ein Rowset von einer OLEDB-Datenquelle geöffnet werden. Diese Funktion benötigt als Parameter den zu verwendenden OLEDB-Provider und alle weiteren notwendigen Verbindungseigenschaften incl. des Sicherheitskontexts. Als letzter Parameter wird die auszuführende SELECT-Anweisung angegeben.

Syntax:

Die Grundsyntax für OPENROWSET sieht so aus:

OPENROWSET ('name_des_OLEDB_providers' , 'verbindungszeichenfolge', 'abfrage')

Die Parameter

name_des_OLEDB_providers

Der Name des OLEDB-Providers, z.B. SQLOLEDB für Zugriffe auf SQL Server, Microsoft.Jet.OLEDB.4.0 für den Zugriff auf Access-Datenbanken, Excel-Arbeitsmappen, Textdateien, dBase-Dateien etc.)

verbindungszeichenfolge

Die Verbindungszeichenfolge für den OLEDB-Provider. Die hier anzugebende Zeichenfolge wird vom SQL Server intern interpretiert und entspricht (leider) nicht genau dem "gewohnten" Aufbau.

abfrage

Die Abfrage auf die Daten in der fernen Datenquelle. In der Regel ein SELECT auf die gewünschte Tabelle

Beispiele SQL Server/SQLOLEDB

Zugriff auf einen anderen SQL Server mit vertrauter Sicherheit

-- Select auf anderen SQL Server mit vertrauter Verbindung   
 SELECT * FROM OPENROWSET  
 ('SQLOLEDB',   
  'Server=anderer_server;database=Northwind;TRUSTED_CONNECTION=YES;',   
  'SELECT * FROM northwind.dbo.orders')   
   
 

Vorsicht Falle!

Das folgende Beispiel gibt Server und Datenbank über die OLEDB-Properties an; diese Parameter werden jedoch ignoriert

-- vorsicht: Hier werden Data Source und Initial Catalog ignoriert...  
 -- stattdessen wird eine Verbindung zum lokalen Server aufgebaut...  
 SELECT * FROM  
 OPENROWSET('SQLOLEDB',   
 'Data Source=anderer_Server;Initial Catalog=beispiel5;TRUSTED_CONNECTION=YES;',   
 'SELECT @@servername, * FROM northwind.dbo.orders') 

Update auf Daten eines anderen SQL Servers

Vorsicht! Diese Anweisung ändert Daten auf dem angegebenen Server.

UPDATE  
 OPENROWSET('SQLOLEDB',   
 'Server=anderer_server;Database=Northwind;TRUSTED_CONNECTION=YES;',   
 'SELECT * FROM northwind.dbo.orders')   
 SET orderdate = orderdate + 1  
 

Einfügen neuer Datensätze in einer "entfernten" Tabelle

Vorsicht! Diese Anweisung ändert Daten auf dem angegebenen Server.

INSERT INTO  
 OPENROWSET('SQLOLEDB',   
 'Server=anderer_server;Database=Northwind;TRUSTED_CONNECTION=YES;',   
 'SELECT * FROM northwind.dbo.region')   
 VALUES (99, 'Test')  
 

Löschen von Daten in einer Tabelle

Vorsicht! Diese Anweisung ändert Daten auf dem angegebenen Server.

DELETE FROM  
 OPENROWSET('SQLOLEDB',   
 'Server=anderer_server;Database=Northwind;TRUSTED_CONNECTION=YES;',   
 'SELECT * FROM northwind.dbo.region')   
 WHERE RegionID = 99  
 

Beispiele Access Datenbanken/JET

Zugriff auf Daten in einer Access-Datenbank mit SELECT

SELECT * FROM  
 OPENROWSET('Microsoft.Jet.OLEDB.4.0',  
 ';Database=C:\Access-Datenbanken\nordwind.mdb',  
 'SELECT * FROM artikel')

Zugriff auf Daten in einer kennwortgeschützten Access-Datenbank mit SELECT

-- Select mit Kennwortgeschützter Datenbank  
 SELECT * FROM  
 OPENROWSET('Microsoft.Jet.OLEDB.4.0',  
 ';Database=C:\Access-Datenbanken\vertraulich.mdb;pwd=geheim;',  
 'SELECT * FROM umsatz')   
 GO

Aktualisieren von Daten in einer Jet-Datenbank

Vorsicht! Diese Anweisung ändert Daten in der angegebenen Datenbank.

UPDATE  
 OPENROWSET('Microsoft.Jet.OLEDB.4.0',  
 ';Database=C:\Access-Datenbanken\nordwind.mdb',  
 'SELECT * FROM artikel')  
 SET einzelpreis = einzelpreis + 1  
 GO

Beispiele Excel-Arbeitsmappen/JET

Abfragen eines Arbeitsblatts

SELECT * FROM   
 OPENROWSET('Microsoft.JET.OLEDB.4.0',  
 'Excel 8.0;Database=C:\training\inventur.xls',  
 'SELECT * FROM [Filiale1$]')

Abfragen bestimmter Zellen in einem Arbeitsblatt

SELECT * FROM   
 OPENROWSET('Microsoft.JET.OLEDB.4.0',  
 'Excel 8.0;HDR=NO;Database=C:\training\inventur.xls',  
 'SELECT * FROM [Filiale1$A2:B4]')

Aktualisieren von Zellen in einem Arbeitsblatt

Vorsicht! Dieses Beispiel ändert Daten im angegebenen Excel-Arbeitsblatt

UPDATE  
 OPENROWSET('Microsoft.JET.OLEDB.4.0',  
 'Excel 8.0;Database=C:\training\inventur.xls',  
 'SELECT * FROM [Filiale1$]')  
 SET Bestand = 1 WHERE Bestand = 2

Hinzufügen von Daten in ein Excel-Arbeitsblatt

Vorsicht! Dieses Beispiel ändert Daten im angegebenen Excel-Arbeitsblatt

INSERT INTO  
 OPENROWSET('Microsoft.JET.OLEDB.4.0',  
 'Excel 8.0;Database=C:\training\inventur.xls',  
 'SELECT * FROM [Filiale1$]')  
 (Produkt, Bestand) VALUES ('Test', 27)  
 GO

Beispiele Textdateien/JET

Abfragen von Daten aus einer Textdatei

-- Abfragen einer Textdatei  
 SELECT * FROM   
 OPENROWSET('MICROSOFT.JET.OLEDB.4.0',  
 'Text;Database=C:\;',   
 'SELECT * FROM [orders#txt]')  
 /* Inhalt der Datei SCHEMA.INI auf C:\  
 [orders.txt]  
 Format=TABDelimited  
 ColNameHeader=false  
 MaxScanRows=20  
 */

Anfügen eines Satzes in eine Textdatei

-- Einfügen eines neuen Satzes  
 INSERT INTO  
 OPENROWSET('MICROSOFT.JET.OLEDB.4.0',  
 'Text;Database=C:\;',   
 'SELECT * FROM [textdemo#txt]')  
 VALUES ('XXX', 100)  
 -- Prüfen, ob der neue Satz angefügt wurde:  
 SELECT * FROM  
 OPENROWSET('MICROSOFT.JET.OLEDB.4.0',  
 'Text;Database=C:\;',   
 'SELECT * FROM [textdemo#txt]')  
   
 /* Zugehörige Schema.ini Datei  
 [textdemo.txt]  
 CharacterSet=ANSI  
 Format=TABDelimited  
 ColNameHeader=TRUE  
 MaxScanRows=20  
 */  
 GO

Beispiele HTML-Dateien/Jet

Lesen der Daten aus einer HTML-Tabelle mit Caption

SELECT * FROM  
 OPENROWSET('Microsoft.Jet.OLEDB.4.0',   
 'HTML Import;HDR=NO;Database=http://localhost/beispiele/tablemitcaption.htm;',   
 'SELECT * FROM Beispieltabelle')  
 


OPENDATASOURCE

OPENDATASOURCE wurde im SQL Server 2000 eingeführt. Diese Funktion erlaubt die Verwendung einer OLEDB-Datenquelle in einem vierstelligem Namen. Im Gegensatz zu OPENROWSET werden hier die "echten" OLEDB-Provider Eigenschaften verwendet.

Syntax

OPENDATASOURCE ( provider, provider_zeichenfolge )

Parameter

Beispiele SQL Server/SQLOLEDB

Lesender Zugriff auf Daten eines anderen Servers

-- Integrierte Sicherheit  
 SELECT * FROM   
 OPENDATASOURCE('SQLOLEDB',   
 'Data Source=itrainbo2000;Initial Catalog=Northwind;Integrated Security=SSPI;').Northwind.dbo.orders  
 -- SQL Login  
 SELECT * FROM   
 OPENDATASOURCE('SQLOLEDB',   
 'Data Source=itrainbo2000;Initial Catalog=Northwind;User ID=student;Password=#student#;').Northwind.dbo.orders  
 

Aufruf einer gespeicherten Prozedur mit Parameter auf einem entfernten SQL Server

EXEC   
 OPENDATASOURCE('SQLOLEDB',   
 'Data Source=itrainbo2000;Initial Catalog=Northwind;Integrated Security=SSPI;').Northwind.dbo.CustorderHist 'ALFKI'  
 

Beispiele Access/JET

SELECT auf eine Tabelle in einer Access-Datenbank

SELECT * FROM   
 OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',   
 'Data Source=C:\Access-Datenbanken\nordwind.mdb;')...artikel

SELECT auf eine kennwortgeschützte Tabelle in einer Access-Datenbank

SELECT * FROM   
 OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',   
 'Data Source=C:\Access-Datenbanken\umsatz.mdb;Jet OLEDB:Database Password=geheim')...tabzahlen  
 

Beispiele Excel-Arbeitsmappe/JET

Abfragen des gesamten Inhalts eines Arbeitsblatts

SELECT * FROM   
 OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  
 'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]

Der Zugriff auf einzelne Bereiche innerhalb des Arbeitsblatt über Zellenreferenzen ist in Kombination mit OPENDATASOURCE so nicht möglich:

-- Das geht nicht:  
 SELECT * FROM   
 OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  
 'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$A1:B3]  
 

Aktualisieren von Werten in einem Excel-Arbeitsblatt

Vorsicht! Dieses Beispiel verändert Daten in dem angegebenen Arbeitsblatt

UPDATE   
 OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  
 'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]  
 SET bestand = bestand + 1

Einfügen einer Zeile in ein Excel-Arbeitsblatt

Vorsicht! Dieses Beispiel verändert Daten in dem angegebenen Arbeitsblatt

INSERT INTO  
 OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  
 'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]  
 (bestand, produkt) VALUES (100, 'Test')  
 

Einfügen von Daten aus einer SQL Server Tabelle in eine Excel-Arbeitsmappe

INSERT INTO   
 OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  
 'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]  
 (Produkt, Bestand)  
 SELECT ProductName, 0 FROM northwind.dbo.products

Beispiele HTML-Dateien/JET

Abfrage einer HTML-Tabelle mit Caption und Spaltenüberschriften

SELECT * FROM   
 OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  
 'Extended Properties=HTML Import;Data Source=http://localhost/beispiele/tablemitcaption.htm;')...Beispieltabelle  
 

Erlaubte Operationen nach Datenquelle

ProviderSELECTINSERTUPDATEDELETE
SQLOLEDBjajajaja
Jet/Accessjajajaja
Jet/Exceljajajanein
Jet/Textjajaneinnein
Jet/Lotus Worksheetjajajanein
Jet/HTMLjaneinneinnein
Jet/dBasejajajaja
Jet/Paradoxjajajaja

Typische Fehlermeldungen in Zusammenhang mit OPENROWSET

[OLE/DB provider returned message: Deferred prepare could not be completed.]

Sie haben beim Zugriff auf die Tabelle nicht den voll-qualifierten Namen (datenbank.besitzer.tabelle) angegeben.

Der Ad-hoc-Zugriff auf den OLE DB-Provider 'Microsoft.Jet.OLEDB.4.0' wurde verweigert. Sie müssen auf diesen Provider über einen Verbindungsserver zugreifen.

Der Ad-hoc Zugriff auf den OLEDB-Provider Jet.OLEDB wurde aus Sicherheitsgründen deaktiviert. Greifen Sie entweder über einen Verbindungsserver auf die Daten zu oder aktivieren Sie Adhoc-Zugriffe für den JET-OLEDB Provider (Achtung! Dies beeinträchtigt die Sicherheit des Servers.)

So prüfen Sie, ob Ad-Hoc Abfragen zugelassen sind:

  1. Öffnen Sie im Enterprise Manager den Unterpunkt Sicherheit.
  2. Öffnen Sie das Kontextmenü des Unterpunkts "Verbindungsserver" und wählen Sie den Menüpunkt "Neuer Verbindungsserver" aus.
  3. Wählen Sie in der Auswahlliste "Providername" den Provider "Microsoft.Jet.OLEDB.4.0" aus.
  4. Klicken Sie auf die Schaltfläche "Provideroptionen".
  5. Prüfen Sie in der Liste "Provideroptionen" den Wert für die Option "Ad-hoc Zugriffe nicht zulassen".

Die folgende Anweisung können Sie verwenden, um per Transact-SQL Script zu prüfen, ob Ad-hoc Zugriffe für den Microsoft Jet.4.0.OLEDB Provider zugelassen sind:

EXEC master..xp_instance_regread
        N'HKEY_LOCAL_MACHINE',
        N'SOFTWARE\Microsoft\MSSQLServer\Providers\Microsoft.Jet.OLEDB.4.0',
        N'DisallowAdhocAccess'

Der OLE DB-Provider 'Microsoft.JET.OLEDB.4.0' meldete einen Fehler.
[OLE/DB provider returned message: Installierbares ISAM nicht gefunden.]

Prüfen Sie, ob der angegebene ISAM-Treiber auf dem Server verfügbar ist. In vielen Fällen führt auch eine fehlerhafte Angabe in der Verbindungszeichenfolge zu dieser Fehlermeldung.
Leerraum

Dokument zum Drucken anzeigen
English Pages
Link-Tipp zum Thema "Active Server Pages": www.4guysfromrolla.com/

Umfangreiche Site rund um ASP.net. Guter Startpunkt für dieses Thema