SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Zapytania rozproszone i zewnętrzne źródła danych

W SQL Server mamy dostępnych kilka metod, dających nam dostęp do danych zewnętrznych.
W artykule tym, przedstawiam możliwości funkcji OPENQUERY, za pomocą której możemy tworzyć zapytania „Ad Hoc” (spontanicznie) do innych zasobów.

Zaprezentuję także metody wykorzystujące obiekty serwerów powiązanych (Linked Servers). Te z kolei stosujemy, aby utworzyć „stałe” powiązanie z innymi zasobami i w wygodny sposób, regularnie sięgać do zewnętrznych źródeł.


Zapytania rozproszone (Distributed Queries)

Komunikacja w środowiskach rozproszonych odbywa się zawsze w oparciu o interfejs pośredniczący. Standardem są sterowniki ODBC (Open DataBase Connectivity), za pomocą których możemy łączyć się z innymi bazami danych. Każdy producent serwera bazodanowego, udostępnia zazwyczaj w pakiecie klienckim wszystkie wymagane komponenty. Jeśli masz potrzebę łączenia się np. z bazą MySQL czy Oracle, powinieneś zainstalować właściwe dla danego dostawcy biblioteki.

Microsoft, od lat rozwijał równolegle własną koncepcje warstwy pośredniczącej – tzw. dostawców OLE DB (Object Linking and Embedding Database). Biblioteki te, rozszerzają możliwości ODBC i dotyczą połączeń nie tylko do serwerów bazodanowych ale także dostępu do zasobów plikowych (Excel, Access czy nawet zwykłych, płaskich plików tekstowych).

Jeśli chodzi o komunikację z SQL Server, w wersjach 2005-2012, to właśnie dostęp za pośrednictwem Native Client OLE DB był sugerowaną (szybszą, o większych możliwościach) metodą dostępu do danych. Sterowniki Native Client, zawierają w sobie bibliotekę OLE DB oraz ODBC. Dla wielu z pewnością dużym zaskoczeniem, był nagły zwrot w kierunku standardu ODBC w SQL Server 2014. Native Client 11 OLE DB z SQL Server 2012 – jest ostatnim w tej architekturze sterownikiem (więcej na ten temat, możesz przeczytać nas stronach Microsoft dot. kierunku rozwoju oraz oficjalnego powrotu do standardu ODBC)

Komunikacja, pomiędzy klientami, instancją SQL Server (do wersji 11) i środowiskami rozproszonymi odbywa się za pośrednictwem OLE DB.
SQL_SERVER_DISTRIBUTED_QUERIES


Funkcja OPENROWSET

Za pomocą funkcji OPENROWSET, możemy tworzyć zapytania rozproszone (Distributed Queries), odpytujące praktycznie dowolne zbiory zewnętrzne. Jedynym ograniczeniem jest dostępność na serwerze właściwych bibliotek OLE DB lub sterowników ODBC. Funkcja OPENROWSET umożliwia nie tylko odpytywanie zasobów, ale również pozwala na wykonywanie za jej pośrednictwem poleceń typu DML (Insert, Update, Delete). Ponieważ jest to funkcja tabelaryczna, stosować możemy ją analogicznie jak zwykłą tabelę (np. łącząc ją z innymi zbiorami).

Wymagania początkowe – konfiguracja serwera

Warunkiem koniecznym na wykorzystanie tej funkcji, jest włączenie na poziomie serwera, możliwości pisania zapytania rozproszonych (Ad Hoc Distributed Queries). To zadanie wykonamy za pomocą instrukcji :

exec sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
 
exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE WITH OVERRIDE;
GO

Od tego momentu, możemy tworzyć zapytania „Ad hoc”, korzystające z OPENROWSET. W dalszej części zaprezentuję typowe przykłady zastosowań.

OPENROWSET w praktyce

Odpytywanie źródeł zewnętrznych za pomocą tej funkcji, wymaga podania kilku parametrów. Nie będę omawiać tu wszystkich możliwych opcji, zwrócę uwagę tylko na najważniejsze i najczęściej wykorzystywane w praktyce.

Typowe użycie funkcji OPENROWSET – odpytywanie innego SQL Servera :

SELECT *
FROM OPENROWSET(
         -- Provider_name
	'SQLNCLI',  				
 
         -- Connection String
	'SERVER=ServerZdalny\Instancja; TRUSTED_CONNECTION=yes;',
 
         -- Query
         'SELECT * FROM Northwind.dbo.Customers'
	) AS tabela

Pierwszym parametrem jest określenie dostawcy OLE DB (provider_name) za pośrednictwem którego wykonywane będzie połączenie.

Regułą jest stosowanie dedykowanego dostawcy dla określonego źródła. Jeśli nie jest on dostępny, możesz skorzystać z uniwersalnej biblioteki MSDASQL i połączenia via ODBC.

Listę dostawców OLE DB których masz zainstalowane na swoim serwerze, odnajdziesz nawigując w drzewie hierarchii obiektów w Management Studio :

OLE_DB_Providers

lub za pośrednictwem specjalnej procedury składowanej :

EXEC xp_enum_oledb_providers

OLE_DB_Providers2

W zależności z jakiego oprogramowania korzystasz, ta lista może się różnić.

Warto tutaj wspomnieć, że można utworzyć połączenia ze zdalnymi serwerami na różne sposoby. Już choćby z tej listy widać, że do SQL Server, można się odwołać za pomocą przynajmniej trzech dostawców – SQLOLEDB (ten jest stosowany domyślnie), SQLNCLI oraz uniwersalnego MSDASQL, wykorzystującego pośrednio połączenia ODBC. Wybór dostawcy, powinien być podyktowany konkretnymi potrzebami programistycznymi (więcej na temat różnic znajdziesz np. tutaj ).

Drugim parametrem jest connection string, czyli charakterystyczny dla danego dostawcy zbiór opcji, które określają warunki połączenia. W przypadku SQL Server, będzie to na pewno nazwa sieciowa serwera / instancji, tryb uwierzytelniania, czy wybór konkretnej bazy danych.

Trzecim parametrem jest nasza zdalna kwerenda. Tutaj od razu bardzo ważna uwaga – pamiętaj o tym, ze cały ten zbiór, zwracany przez to zdalne zapytanie, będzie transportowany do serwera z którego je wywołujesz. Może to być przyczyną problemów m.in. sieciowych, trwać długo czyli również przekładać się bezpośrednio na czas trwania zapytania, blokad etc… Zapytania rozproszone, powinny zwracać zawsze minimalny, konieczny zbiór danych. Chyba również z tego powodu, domyślnie możliwość ich wykonywania jest wyłączona.

Zapytania do bazy ORACLE

Jak to bywa, możemy zrealizować tego typu zapytanie ad hoc na kilka sposób. Poniżej parę przykładów – dla każdego coś miłego. Pierwsze trzy zakładają istnienie wpisu TNS w pliku {ORA_HOME}\Network\Admin\tnsnames.ora. W ostatnim przykładzie, jawnie podaje wszystkich parametry komunikacyjne do serwera zdalnego.

-- z providerem OLEDB Oraclowym + wpis w {ORA_HOME}\Network\Admin\tnsnames.ora
SELECT * 
FROM OPENROWSET(
	'OraOLEDB.Oracle',
	'NaszOracleSrv';'username';'paswword', 
	'SELECT ''works fine'' as Kol1 FROM Dual' )
 
-- z providerem MSowym do Oracle 
Select * 
FROM OPENROWSET(
	'MSDAORA',
	'NaszOracleSrv';'username';'paswword', 
	'SELECT ''works fine'' as Kol1 FROM Dual' )
 
-- a tu inaczej - wszystko w Connection Stringu + połączenie via ODBC z wpisem w {ORA_HOME}\Network\Admin\tnsnames.ora
SELECT * 
FROM OPENROWSET(
	'MSDASQL', 
	'DRIVER={Microsoft ODBC for Oracle}; UID=username; PWD=password; SERVER=NaszOracleSrv;', 
	'SELECT ''works fine'' as Kol1 FROM Dual' 
	) as Tabela
 
-- a tu inaczej - wszystko w Connection Stringu + połączenie via ODBC
SELECT * 
FROM OPENROWSET(
	'MSDASQL', 
	'DRIVER={Microsoft ODBC for Oracle}; UID=username; PWD=password; SERVER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.1)(PORT=1521)))(CONNECT_DATA=(SID=NaszOracleSID)));', 
	'SELECT ''works fine'' as Kol1 FROM Dual' 
	) as Tabela

Zapytania do baz MySQL i innych z użyciem MSDASQL + ODBC

Jeśli nie mamy dostępnego dedykowanego dostawcy OLE DB, możemy łączyć się ze zdalnym serwerem za pomocą MSDASQL i połączenia wykorzystującego sterowniki ODBC. Listę dostępnych driverów, zainstalowanych na maszynie, sprawdzisz w panelu administracyjnym ODBC :

ODBC_Drivers

W przypadku odpytania bazy MySQL z poziomu SQL Server, potrzebne będą sterowniki ODBC (jak widać powyżej, mam takie zainstalowane). Sama kwerenda jest bardzo podobna do poprzedniej. Zastosowałem inny sterownik ODBC (MySQL) i dlatego connection string, którego struktura jest określana przez producenta, różni się w stosunku do poprzedniej, gdy odpytywałem bazę Oracle.

  SELECT  * 
  FROM OPENROWSET(
	'MSDASQL',
       'Driver={MySQL ODBC 5.2 ANSI Driver}; 
  Server=MyServerMySQL; Database=MojaBaza; USER=Username; PASSWORD=alamakota',
	'SELECT * from tabelka limit 10' )

Za pomocą OPENROWSET, możemy odpytywać także zawartości plików. Mogą to być zarówno pliki tekstowe, strukturyzowane (CSV), dokumenty Excel czy „bazy danych” Access. Dość wygodnie, za pomocą OPENQUERY przeczytamy również całe pliki XML. Pobieranie danych z Excela oraz plików tekstowych, opisuje szczegółowo w kolejnych rozdziałach tego kursu.

Poniżej kilka prostych, ale praktycznych przykładów :

-- Czytanie zwykłego pliku txt
SELECT F1 as FileContent 
FROM OPENROWSET(
	'Microsoft.ACE.OLEDB.12.0', 
    'Text;Database=D:\data\;HDR=NO', 
    'SELECT *  FROM test.txt')
 
-- załadowanie pliku XML (można go przypisać do zmiennej
SELECT CONVERT(xml, BulkColumn ) as XmlDocs
FROM 
OPENROWSET (BULK 'D:\DaneXML\NBP\a051z140314.xml',SINGLE_BLOB) as T1

OPENQUERY_1


Serwery powiązane – Linked Servers

Do tej pory, korzystaliśmy z tzw. zapytań Ad hoc – spontanicznych kwerend, do pobrania określonych danych z zewnętrznego źródła. W środowiskach produkcyjnych, istnieją serwery, które regularnie odpytujemy. Pisanie za każdym razem, długich connection stringów, jest niewygodne.

Za pomocą obiektów serwerów powiązanych, możemy zrealizować integrację danych z różnych środowisk w wygodniejszy sposób.

Na wstępie warto jeszcze raz podkreślić, że zapytania rozproszone, niezależnie od metody (OPENROWSET, Linked Server) powinny być stosowane z rozwagą, bo mogą być przyczyną problemów wydajnościowych (sieć, czasy blokad etc.) w środowiskach transakcyjnych.

Świadomi tego :), możemy zabrać się za tworzenie obiektów serwerów powiązanych. Jeśli jesteś zwykłym użytkownikiem bazy danych, potrzebujesz do tego uprawnień serwerowych ALTER ANY LINKED SERVER.

Serwery powiązane, możesz tworzyć w T-SQL (służy do tego procedura sp_addlinkedserver) lub za pomocą wizarda w Management Studio.

Linked_server_01

Całość konfiguracji, ogranicza się zazwyczaj do określenia dostawcy OLE DB, nazw serwera, ewentualnie połączenia ODBC oraz ustawień związanych z bezpieczeństwem. W przypadku SQL Server, wystarczy podać nazwę sieciową instancji – zostanie utworzony serwer powiązany, domyślnie z uwierzytelnianiem zintegrowanym, bazujący na natywnym kliencie SQLNCLI OLE DB.

Pisanie zapytań z wykorzystaniem Linked Servers

Odwoływanie się w kwerendach rozproszonych do obiektów serwerów powiązanych, jest możliwe na dwa sposoby.
Za pomocą funkcji tabelarycznej OPENQUERY(), możemy wysyłać zapytanie do dowolnego obiektu Linked Server.

SELECT * 
FROM OPENQUERY(SerwerPowiazany,'SELECT * FROM TABELA')

W tej sytuacji, zostanie wykonane zapytanie na serwerze zdalnym w dokładnie takiej postaci, jak parameter query funkcji OPENQUERY. Czyli zostanie pobrana CAŁA zawartość odpytywanej tabeli i przesłana przez sieć do klienta. Porównaj takie dwa zapytania z filtracją rekordów :

-- źle
SELECT * 
FROM OPENQUERY(SerwerPowiazany,'SELECT * FROM TABELA')
WHERE KolID = 12345
 
-- dobrze :)
SELECT * 
FROM OPENQUERY(SerwerPowiazany,'SELECT * FROM TABELA WHERE KolID = 12345 ')

Zwrócą to samo, ale o ile lżejsze jest zapytanie drugie, z filtracją po stronie zdalnego serwera….
Funkcja OPENQUERY, pozwala również na wysyłanie poleceń typu DML.

Drugim sposobem, jest odwoływanie się w poleceniach SQL do obiektów (tabel, widoków czy procedur), po w pełni kwalifikowanej, czteroczłonowej nazwie. W ten sposób, możemy wywoływać także procedury składowane na zdalnym serwerze.

           <SERVER>.<BAZA_DANYCH>.<SCHEMAT>.<OBIEKT>

Zarówno pierwszy jak i drugi sposób, pozwala na pobieranie danych z zewnętrznych źródeł podobnie jak ze zwykłych tabel. Można łączyć je w zapytaniach, odwołując się jednocześnie do różnych serwerów (UWAGA na wydajność !!!).

SELECT c.*, o.OrderID  
FROM Server1.Northwind.dbo.Customers c 
		inner join Server2.Northwind.dbo.Orders o on c.CustomerID = o.CustomerID

Metadane związane z Linked Servers

Z obiektami Linked Servers, związanych jest kilka przydatnych widoków i procedur składowanych, które warto poznać. Listę dostępnych obiektów Linked Servers, loginów z nimi związanych, możesz obejrzeć poprzez widoki systemowe w bazie msdb (sys.servers, sys.linked_logins oraz sys.remote_logins).

Mamy także dostępnych kilka specjalnych procedur składowanych. Z bardziej praktycznych są sp_catalogs oraz sp_tables_ex, które pozwalają na podejrzenie wszystkich baz danych / tabel, dostępnych po przez dany obiekt (o ile dostawca OLE DB/ODB oferuje takie funkcjonalności). Poniżej przykład wywołania sp_catalogs do zdalnego SQL Servera o nazwie RFN.

  -- bazy danych
  sp_catalogs 'RFN'

Linked_server_02

Za pośrednictwem serwerów powiązanych, podobnie jak funkcji OPENROWSET, możemy odnosić się również do plików. Jest wiele dróg na import danych z plików. Jedno z nich to rozwiązanie oparte o Linked Servers. Poniżej przykład utworzenia obiektu „serwera”, będącego katalogiem z plikami. Do tego celu wykorzystam dostawcę Jet.OLEDB.4.0.

EXEC sp_addlinkedserver  PlikiWymiany, 
	'Jet 4.0', 
         'Microsoft.Jet.OLEDB.4.0',
         'D:\Dane\',
         NULL,
         'Text';

W katalogu D:\Dane\ – są umieszczone dwa pliki tekstowe :

LinkedServer_TXT_FILE_01

Na koniec zastosowanie procedury składowanej sp_tables_ex oraz odczytanie zawartości pierwszego z plików.

EXEC sp_tables_ex PlikiWymiany;
GO
 
SELECT * 
FROM PlikiWymiany...[test#txt];

LinkedServer_TXT_FILE_02


Podsumowanie

Opisane powyżej metody pracy w środowisku rozproszonym są z pewnością bardzo praktyczne i często stosowane. Korzystając z nich, pamiętaj o aspektach wydajnościowych. Pobieranie danych (czasem całych tabel) poprzez sieć ze zdalnych serwerów, z pewnością nie jest najlepszym pomysłem.

Import danych z Excela do bazy SQL Server za pomocą T-SQL

Czytanie plików Excel za pomocą T-SQL jest bardzo użyteczne, ale potrafi dostarczyć też problemów, szczególnie na etapie przygotowania środowiska. Sam spędziłem parę ładnych godzin na debugowaniu błędu związanego z czytaniem pliku (chodziło o dostęp do katalogów temp), dlatego postanowiłem zebrać wszystko co ważne w jednym miejscu. O ile sama składnia OPENROWSET jest dość łatwa, o tyle potencjalne problemy związane z ustawieniami środowiskowymi czy właściwościami interfejsów OLE DB mogą pożreć nam sporo czasu na ich rozwiązanie.
Znajdziesz tutaj najważniejsze informacje, które mogą się przydać do czytania plików Excel z poziomu T-SQL a także opis najczęściej pojawiających się błędów i ich rozwiązań.


Funkcja OPENROWSET

Do czytania plików Excel z poziomu T-SQL, idealnie nadaje się funkcja OPENROWSET. Szczególnie wtedy gdy chcemy szybko wrzucić zawartość jakiegoś Excela do bazy aby wykonać na informacjach w nim zawartych jakichkolwiek działań lub zależy nam po prostu aby wykonać import za pomocą skryptu T-SQL.
Jest to funkcja tabelaryczna, zwracająca w wyniku zbiór wierszy, który możemy traktować jak zwykłą tabelę. OPENROWSET podobnie jak inne obiekty tabelaryczne (widoki, zmienne tabelaryczne, tabele tymczasowe etc.) może być użyty tylko w klauzuli FROM zapytania.
Aby móc korzystać z OPERNROWSET potrzebujemy włączonej możliwości wykonywania kwerend rozproszonych. Jest to jedno z ‘advanced optinos’ naszego serwera – warunek konieczny do dalszych przykładów :

-- włączenie na serwerze obsługi kwerened rozproszonych
exec sp_configure 'show advanced options',1
reconfigure
 
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

Teraz w zależności od wersji w jakiej został zapisany plik Excela treść naszego zapytania może wyglądać następująco :

-- Przykład z excel zapisanym w formacie zgodnym z OFFICE 1997-2003 .xls 
-- stary sterownik OLEDB 4.0. Na wymarciu :) sugeruję stosowanie ACE.
 
select * FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\EXCEL1997-2003_TO_READ.xls', 
'select * from [Arkusz1$]');
 
-- ten sam plik zapisany w trybie zgodności 1997-2003 xls czyli OFFICE 2003 
-- nowy uniwersalny provider ACE OLE DB. Odwolanie do Excel 8.0
 
SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0', 
'Excel 8.0;Database=D:\EXCEL1997-2003_TO_READ.xls', 
'SELECT  * FROM [Arkusz1$] ');
 
-- plik zapisany w OFFICE 2010 xlsx nowy uniwersalny provider ACE OLE DB
-- istotne jest odwołanie do Excel 12.0;
 
SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',                      -- provider_name
'Excel 12.0;Database=D:\EXCEL2010_TO_READ.xlsx', -- provider_string
'SELECT * from [Arkusz1$]')                      -- query lub sam [Arkusz1$], wtedy bez '

Powyższe zapytania działają, jeśli środowisko w którym pracujemy jest odpowiednio skonfigurowane. W dalszej części tego artykułu, znajdziesz wszystkie najważniejsze aspekty związane z konfiguracją serwera, składnią OPENROWSET oraz najczęstszymi problemami z jakimi możesz się zetknąć przy próbie czytania z Excela.

Składnia OPENROWSET

W pokazanych przykładach, funkcja OPENROWSET przyjmuje 3 parametry :
OPENROWSET( provider_name, provider_string, query).

Parametr ‚provider_name

Tu podajemy nic innego jak nazwę (friendly name) interfejsu OLE DB. W przykładach użyłem dwóch interfejsów OLE DB za pomocą których możliwe jest uzyskanie dostępu do plików Excel. Pierwszy to „stary” JET.OLEDB.4.0 który czyta Excela tylko do wersji 2003 (xls) i którego nie polecam stosować (czasem jednak nie mamy wyboru, więc stąd ten przykład).
Drugi, sugerowany to uniwersalny Microsoft.ACE.OLEDB.12.0 dla plików zapisanych w wersjach 1997-2010. Jeśli nie jesteś pewien czy masz go zainstalowanego – sprawdź sterowniki np. w ODBC (biblioteka ACEODBC.DLL).
Reading_excel_odbc_providers
W moim przypadku mam dostępny Microsoft.ACE.OLEDB.12.0 w wersji 14.
Jeśli nie masz jej dostępnej – można go pobrać i zainstalować np. stąd : Microsoft Access Database Engine 2010 Redistributable.
UWAGA ! Jeśli zainstalowałeś ten interfejs, konieczne jest nadanie dla niego stosowanych uprawnień – opisane jest to poniżej – w paragrafie Problemy.

Parametr ‚provider_string

W provider_string, definiujemy zbiór (kolekcję) parametrów tzw. extended properties. Obowiązkowe są dwa – wersja pliku oraz ścieżka. Wersje plików Excel czytane przez Microsoft.ACE.OLEDB.12.0 :

Typ pilkuExtended Properties

Excel 97-2003 Workbook (.xls) „Excel 8.0”
Excel 2007-2010 Workbook (.xlsx) „Excel 12.0 Xml”
Excel 2007-2010 Macro-enabled workbook (.xlsm) „Excel 12.0 Macro”
Excel 2007-2010 Non-XML binary workbook (.xlsb) „Excel 12.0”

Do celów demonstracyjnych najważniejszych parametrów, użyję pliku excel (zapisany w MS Office 2010 jako xlsx). Zawartość pliku demo :
excel_file_example1
Wykonanie kwerendy czytającej tego excela z minimalną wymaganą liczbą parametrów :

SELECT * FROM OPENROWSET(
-- provider_name
'Microsoft.ACE.OLEDB.12.0', 
-- provider_string 
'Excel 12.0;Database=D:\EXCEL2010_TO_READ.xlsx', 
-- query
'SELECT  * FROM [Arkusz1$]');

Reading_excel_01
Jak widać wynik powyżej różni się od faktycznej zawartości pliku – jest to zależne od ustawień parametrów opcjonalnych .

Najważniejsze z opcjonalnych extended properties to :
HDR yes/no – (default = yes) czy ma czytać nazwy kolumn z pierwszego wiersza. Jeśli ustawimy na no – nazwy kolumn będą utworzone jako F1,F2 etc.
IMEX – ImportMixedTypes – interfejsy OLE DB, które wykorzystujemy, mają właściwość „zgadywania” typu danych w kolumnie. Właściwość IMEX odnosi się tylko do kolumn zawierających mieszane typy danych i stara się automatycznie określić typ na podstawie pierwszych N wartości (wartość N określa parametr MaxScanRows). Jeśli na tej podstawie zostanie wybrany np. typ integer, to wszystkie wartości nie pasujące do tego typu, zostaną zamienione na NULL, co zazwyczaj nie jest oczekiwanym zjawiskiem :).

W naszym przykładzie, w kolumnie KolC mamy 2 wartości tekstowe i 3 daty, to w wyniku zapytania otrzymamy kolumnę typu datetime a wszystko co nie pasuje do tego typu zostanie zamienione na NULL (tak jak miało to miejsce w przykładzie powyżej).
W zdecydowanej większości przypadków, gdy chcemy sami określać typ i czyścić dane – ustawiamy parametr IMEX=1, wtedy każda kolumna będzie miała narzucony typ danych jako text :

SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0', 
-- provider_string z IMEX
'Excel 12.0;IMEX=1;Database=D:\EXCEL2010_TO_READ.xlsx', 
'SELECT  * FROM [Arkusz1$]');

Reading_excel_02

Parametr ‚query

W tym parametrze określamy zakres danych jakie chcemy wyciągać. Odpytywany plik Excel, może posiadać kilka arkuszy – do konkretnego odwołujemy się po nazwie + $. Aby określić zakres czytanych komórek można to zrobić np. tak Arkusz1$A1:C3 – w wyniku czego otrzymamy tylko dwa wiersze (plus header). Query może przyjmować podstawowe komendy obsługiwane przez danego providera np. grupowanie danych lub być poprostu nazwą arkusza [Arkusz1$] (bez apostrofów).

SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;IMEX=1;Database=D:\EXCEL2010_TO_READ.xlsx', 
-- query jako zapytanie
'SELECT kolC, MAX(KolA) as maxi FROM [Arkusz1$A1:C3] group by KolC'

excel_file_example2
W wyniku dostaniemy :
Reading_excel_03
Warto zwrócić uwagę na fakt konwersji na właściwy typ danych w KolC (datetime), nawet przy ustawionym IMEX=1, ponieważ wartości pobierane z tej kolumny są spójne –same daty (bierzemy pod uwagę tylko dwa pierwsze wiersze z danymi A1:C3).

Problemy

Niestety jak to w życiu bywa, wszystko jest ok., ale nie działa. Akurat w przypadku czytania Excela z poziomu SQL zdarza się to nadzwyczaj często bo istnieje wiele uwarunkowań środowiskowych, które mogą nam skutecznie przeszkodzić w zabawie.
Najczęstsze przyczyny problemów z czytaniem excela za pomocą OPENROWSET:

  • Jeśli korzystamy z OLE DB ACE – konieczne przydzielenie odpowiednich uprawnień dla tego dostawcy czyli po instalacji konieczne jest wykonanie :
    USE [master]  
    GO  
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1  
    GO  
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1  
    GO
  • Plik do którego się odwołujemy musi być zamknięty aby móc go czytać z poziomu T-SQL przez OPENROWSET . Niestety SQL Server potrzebuje pliku na wyłączność. Jeśli jest już otwarty, otrzymamy komunikat :
    Msg 7399, Level 16, State 1, Line 2
    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 2
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
  • Konto na którym działa usługa SQL Server musi mieć dostęp do zasobów które chcesz czytać, wynika to z konieczności obsługi różnych metod uwierzytelniania (Windows lub SQL Authentication).
  • Problem związany z SQL Server x86 – użytkownik, który wykonuje kwerendę musi mieć dostęp do katalogów tymczasowych używanych przez SQL Server. Jest to podyktowane tym, że tworzona jest kopia tymczasowa pliku do którego się odwołujemy w OPENROWSET w katalogu temp. Potrzebny dostęp r/w do katalogów wskazywanych przez zmienne %TEMP% oraz %TMP% konta na którym działa SQL Server.
    Microsoft sugeruje w tym temacie dwa rozwiązania, które sprowadzają się do tego samego – nadanie uprawnień do aktualnie używanych katalogów TEMP dla użytkowników lub utworzenie nowego, dedykowanego katalogu tymczasowego np. c:\TEMP i ustawienie zmiennych środowiskowych dla konta na którym działa SQL Server, aby %TEMP% i %TMP% wskazywały właśnie na ten nowy zasób.
    W obu przypadkach nadajemy uprawnienia R/W dla wszystkich zainteresowanych użytkowników do tego katalogu oraz full rights dla konta SQL Server.
  • Inne spotykane problemy to kompatybilność sterowników – sprawdź czy zainstalowana wersja OLE DB jest odpowiednia dla twojego serwera x86 / x64.

Niestety obsługa błędów w OLE DB ACE została potraktowana bardzo ogólnie i w większości przypadków tu opisywanych, treść błędu będzie podobna – unknown error.