Vytvoření LINKED SERVER různých typů na MSSQL

[jan.havranek]

/*
Následující texty se dají přenést do konzole SQL.
Jedná se o vzorové příkazy SQL s komentáři,
použitelné pro registraci různých typů LINKED SERVERů na MSSQL.
*/

--OBECNĚ: Pro debug linked serverů - bude pak v hlášce chyby vypisovat detailnější info:
DBCC TRACEON(7300, 3604)



--==LINKED SERVER typu MSSQL SERVER

--registraci může provést jen sysadmin
IF IS_SRVROLEMEMBER('sysadmin')=1 BEGIN

--pokud existuje linked server s názvem SHARK, smaž ho (i s loginy)
IF EXISTS(SELECT * FROM master.dbo.sysservers WHERE srvname='SHARK') EXEC dbo.sp_dropserver 'SHARK', 'droplogins'

--zaregistruj linked server s názvem SHARK
EXEC dbo.sp_addlinkedserver 'SHARK', '', 'SQLOLEDB', 'SHARK', NULL, NULL, 'HeliosDB'

--nastav vlastnost Collation compatibile - bude to rychlejší
EXEC dbo.sp_serveroption 'SHARK', 'collation compatible', 'true'

IF @@MICROSOFTVERSION>=0x08000000 BEGIN
--zruš vlastnost 'use remote collation' (jen na MSSQL2000 a vyšším) jinak padá na chybu SQL:446
EXEC dbo.sp_serveroption 'SHARK', 'use remote collation', 'false'
END

--práva pro všechny
EXEC dbo.sp_addlinkedsrvlogin 'SHARK', FALSE, NULL, 'Uzivatel', 'heslo'

--co vidím?
--EXEC dbo.sp_tables_ex 'SHARK'
--EXEC dbo.sp_tables 'SHARK'
END
--------------------------------------------
--bez registrace (ale s omezenými možnostmi)
SELECT * FROM OPENROWSET('MSDASQL', 'Driver=SQL Server;Server=tcp:SHARK;UID=sa;PWD=******;APP=HeIQ','SELECT @@SERVERNAME')



--==LINKED SERVER typu EXCEL

--registraci může provést jen sysadmin
IF IS_SRVROLEMEMBER('sysadmin')=1 BEGIN

--pokud existuje linked server s názvem Excel, smaž ho (i s loginy)
IF EXISTS(SELECT * FROM master.dbo.sysservers WHERE srvname='Excel') EXEC dbo.sp_dropserver 'Excel', 'droplogins'

--zaregistruj linked server s názvem Excel
EXEC dbo.sp_addlinkedserver 'Excel', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', N'C:\Test.xls', NULL, 'Excel 5.0;IMEX=1'

--práva pro všechny
EXEC dbo.sp_addlinkedsrvlogin 'Excel', FALSE, NULL, Admin, NULL

--co vidím?
--EXEC dbo.sp_tables_ex 'Excel'
END
--------------------------------------------
--bez registrace (ale s omezenými možnostmi)
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=\"C:\Test.xls\";User ID=Admin;Password=;Extended properties=\"Excel 5.0;IMEX=1\"')...[List1$]

/*
!!! zajímavý je parametr IMEX=1 (lze totéž i v registru ImportMixedTypes=Text)
HOWTO: Use ADO with Excel Data from Visual Basic or VBA - sekce A Caution about Mixed Data Types:
http://support.microsoft.com/?id=257819
*/



--==LINKED SERVER typu ACCESS

--registraci může provést jen sysadmin
IF IS_SRVROLEMEMBER('sysadmin')=1 BEGIN

--pokud existuje linked server s názvem Access, smaž ho (i s loginy)
IF EXISTS(SELECT * FROM master.dbo.sysservers WHERE srvname='Access') EXEC dbo.sp_dropserver 'Access', 'droplogins'

--zaregistruj linked server s názvem Access
EXEC dbo.sp_addlinkedserver 'Access', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', N'C:\Test.MDB'

--práva pro všechny
EXEC dbo.sp_addlinkedsrvlogin 'Access', FALSE, NULL, Admin, NULL

--co vidím?
--EXEC dbo.sp_tables_ex 'Access'
END
--------------------------------------------
--bez registrace (ale s omezenými možnostmi)
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=\"C:\Test.MDB\";User ID=Admin;Password=')...[Customers]



--==LINKED SERVER typu TXT

--registraci může provést jen sysadmin
IF IS_SRVROLEMEMBER('sysadmin')=1 BEGIN

--pokud existuje linked server s názvem Txt, smaž ho (i s loginy)
IF EXISTS(SELECT * FROM master.dbo.sysservers WHERE srvname='Txt')EXEC dbo.sp_dropserver 'Txt','droplogins'

--zaregistruj linked server s názvem Txt
EXEC dbo.sp_addlinkedserver 'Txt', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', N'C:\Txt', NULL, 'Text'

--práva pro všechny
EXEC dbo.sp_addlinkedsrvlogin 'Txt', FALSE, NULL, Admin, NULL

--co vidím?
--EXEC dbo.sp_tables_ex 'Txt'
END
--------------------------------------------
--bez registrace (ale s omezenými možnostmi)
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\;Extended properties=Text')...[TEST#TXT]
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\;Extended Properties=\"Text;HDR=No;FMT=Delimited;CharacterSet=Unicode\"')...[TEST#TXT]

/*
v OPENDATASOURCE() i ve SCHEMA.INI funguje: CharacterSet=1250
*/



--==LINKED SERVER typu DBASE

--registraci může provést jen sysadmin
IF IS_SRVROLEMEMBER('sysadmin')=1 BEGIN

--pokud existuje linked server s názvem DBF, smaž ho (i s loginy)
IF EXISTS(SELECT * FROM master.dbo.sysservers WHERE srvname='DBF')EXEC dbo.sp_dropserver 'DBF','droplogins'

--zaregistruj linked server s názvem DBF
EXEC dbo.sp_addlinkedserver 'DBF', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', N'C:\', NULL, 'dBase 5.0'

--práva pro všechny
EXEC dbo.sp_addlinkedsrvlogin 'DBF', FALSE, NULL, Admin, NULL

--co vidím?
--EXEC dbo.sp_tables_ex 'DBF'
END
--------------------------------------------
--bez registrace (ale s omezenými možnostmi)
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\Incoming;Extended properties=dBase 5.0')...[streda]

/*
http://www.dbfmanager.com/dbfstructure.php
Table Header Record Structure
Byte offset Description
0 File type:
0x02 FoxBASE
0x03 FoxBASE+/Dbase III plus, no memo
0x30 Visual FoxPro
0x31 Visual FoxPro, autoincrement enabled
0x43 dBASE IV SQL table files, no memo
0x63 dBASE IV SQL system files, no memo
0x83 FoxBASE+/dBASE III PLUS, with memo (memo=.DBT)
0x8B dBASE IV with memo
0xCB dBASE IV SQL table files, with memo
0xF5 FoxPro 2.x (or earlier) with memo
0xFB FoxBASE
*/



--==LINKED SERVER typu FOXBASE

--registraci může provést jen sysadmin
IF IS_SRVROLEMEMBER('sysadmin')=1 BEGIN

--pokud existuje linked server s názvem FOX, smaž ho (i s loginy)
IF EXISTS(SELECT * FROM master.dbo.sysservers WHERE srvname='FOX')EXEC dbo.sp_dropserver 'FOX','droplogins'

--zaregistruj linked server s názvem FOX
EXEC dbo.sp_addlinkedserver 'FOX', '', 'MSDASQL', NULL, NULL, 'Driver={Microsoft Visual FoxPro Driver};SourceDB=C:\SIS;SourceType=DBF'

--práva pro všechny
EXEC dbo.sp_addlinkedsrvlogin 'FOX', FALSE, NULL, NULL, NULL

--co vidím?
--EXEC dbo.sp_tables_ex 'FOX'
--EXEC dbo.sp_columns_ex 'FOX', 'psc'
--SELECT * FROM OPENQUERY(FOX, 'SELECT * FROM psc') --SELECT * FROM FOX...psc --tohle nefunguje END
--------------------------------------------
--bez registrace (ale s omezenými možnostmi)
SELECT * FROM OPENROWSET('MSDASQL','Driver=Microsoft Visual FoxPro Driver;SourceDB=C:\SIS;SourceType=DBF;Collate=Machine','SELECT * FROM PSC')

/*
HOWTO: Do SQL Server 7.0 Distributed Queries With FoxPro .dbf Files
http://support.microsoft.com/default.aspx?scid=kb;[LN];207595

How To Add a FoxPro Database to SQL Server as a Linked Server:
http://support.microsoft.com/?id=199131

existuje též (netestoval jsem): Microsoft OLE DB Provider for Visual FoxPro 8.0
http://www.microsoft.com/downloads/deta ... 5869CB3BC9
*/



--==LINKED SERVER typu ORACLE

--registraci může provést jen sysadmin
IF IS_SRVROLEMEMBER('sysadmin')=1 BEGIN

--pokud existuje linked server s názvem Oracle, smaž ho (i s loginy)
IF EXISTS(SELECT * FROM master.dbo.sysservers WHERE srvname='Oracle') EXEC dbo.sp_dropserver 'Oracle', 'droplogins'

--zaregistruj linked server s názvem Oracle
EXEC dbo.sp_addlinkedserver 'Oracle', '', 'MSDAORA', 'MS'

--práva pro všechny
EXEC dbo.sp_addlinkedsrvlogin 'Oracle', FALSE, NULL, 'internal', 'oracle'

--co vidím?
--EXEC dbo.sp_tables_ex 'Oracle'
END
--------------------------------------------
--bez registrace (ale s omezenými možnostmi)
SELECT * FROM OPENDATASOURCE('MSDAORA', 'Data Source=MS;User ID=internal;Password=oracle')...USER_TABLES

/*
viz též: HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in SQL Server
http://support.microsoft.com/?id=280106
*/



--==LINKED SERVER typu INTERBASE/FIREBIRD

/*
Jediná, která se poadřilo rozchodit (a který je i částečně specializovaný pro linked server MSSQL)
InterBase OLE DB Provider http://www.lcpi.lipetsk.ru/prog/eng/sta ... ov_ib.html
Cena:
1 Licence : 150 USD
Licence neomezaná pro šíření dále, 600 USD / 1rok.
support : OK, řešení problému/dotazu ihned.
email : dima@lcpi.lipetsk.ru

otevřít textový soubor %WinDir%\system32\drivers\etc\SERVICES a přidat na konec řádku ukončenou Enterem:
gds_db 3050/tcp
*/

--registraci může provést jen sysadmin
IF IS_SRVROLEMEMBER('sysadmin')=1 BEGIN

--pokud existuje linked server s názvem INTERBASE, smaž ho (i s loginy)
IF EXISTS(SELECT * FROM master.dbo.sysservers WHERE srvname='INTERBASE') EXEC dbo.sp_dropserver 'INTERBASE', 'droplogins'

--zaregistruj linked server s názvem INTERBASE
EXEC dbo.sp_addlinkedserver
@server='INTERBASE', @provider='LCPI.IBProvider.2', @srvproduct='LCPI',
@provstr='data source=slavanew:D:\MM\SpeedSoft\SpeedSoft\BAZA\SPEEDSOFT.GDB;user=sysdba;password=masterkey;ctype=win1251;free_threading=true;truncate_char=false;auto_commit=True;auto_commit_level=65536;'

--práva pro všechny
EXEC dbo.sp_addlinkedsrvlogin 'INTERBASE', 'false', NULL, 'sysdba', 'masterkey'

--co vidím?
--EXEC dbo.sp_tables_ex 'INTERBASE'
END

SELECT * FROM OPENQUERY(INTERBASE, 'SELECT * FROM ZLECENIA')
--------------------------------------------
--bez registrace (ale s omezenými možnostmi)
SELECT * FROM OPENDATASOURCE('LCPI.IBProvider.2','data source=slavanew:D:\MM\SpeedSoft\SpeedSoft\BAZA\SPEEDSOFT.GDB;user=sysdba;password=masterkey;ctype=win1251;free_threading=true;truncate_char=false;auto_commit=True;auto_commit_level=65536;')...ZLECENIA
--^^NEFUNGUJE^^



--==LINKED SERVER typu MySQL

--stáhnout a nainstalovat ODBC driver z http://www.mysql.com/products/myodbc/index.html
--funkčnost ověřena na MySQL ODBC 3.51 driveru

--registraci může provést jen sysadmin
IF IS_SRVROLEMEMBER('sysadmin')=1 BEGIN

--pokud existuje linked server s názvem MySQL, smaž ho (i s loginy)
IF EXISTS(SELECT * FROM master.dbo.sysservers WHERE srvname='MySQL')EXEC dbo.sp_dropserver 'MySQL','droplogins'

--zaregistruj linked server s názvem MySQL
EXEC dbo.sp_addlinkedserver 'MySQL', '', 'MSDASQL', NULL, NULL, 'Driver={MySQL ODBC 3.51 Driver};Server=localhost;Option=16834;Database=he_test'

--práva pro všechny
EXEC dbo.sp_addlinkedsrvlogin 'MySQL', FALSE, NULL, 'root', NULL

--co vidím ?
--EXEC dbo.sp_tables_ex 'MySQL'
END

SELECT * FROM OPENQUERY(MySQL, 'SELECT * FROM d_helios') UPDATE OPENQUERY(MySQL, 'SELECT id,helios FROM d_helios WHERE id=18') SET helios=1

--SELECT * FROM MySQL...d_helios
--^^four-part name nefunguje^^ - ověřeno na MySQL ODBC 3.51 driveru
--------------------------------------------
--bez registrace (ale s omezenými možnostmi)
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={MySQL ODBC 3.51 Driver};Server=localhost;Option=16834;Database=he_test;Uid=root;Pwd=;', 'SELECT * FROM d_helios')

/*
MySQL český manuál
http://mm.gene.cz/
*/



--==LINKED SERVER typu Sybase

/*
How to set up a linked server to a Sybase database server and troubleshoot problems that may occur:
http://support.microsoft.com/?id=280102

Configuring a Microsoft SQL Server Linked Server with Sybase ASE (TDS) OLEDB Provider Using NT Authentication:
http://www.sybase.com/detail/1,6904,1029118,00.html
*/

--!neotestováno!


--==LINKED SERVER typu LDAP

/*
Mapping Between IADsUser Properties and Active Directory Attributes:
http://msdn.microsoft.com/library/en-us ... dspath.asp

Platform SDK: Active Directory - User Object User Interface Mapping:
http://msdn.microsoft.com/library/en-us ... apping.asp

Platform SDK: Active Directory Schema - Attributes:
http://msdn.microsoft.com/library/en-us ... ibutes.asp
*/

SELECT * FROM OPENROWSET('ADSDSOObject', '', 'SELECT distinguishedName,name,objectGuid,sAMAccountName,scriptPath FROM ''LDAP://lcs.cz/DC=lcs,DC=cz'' WHERE objectClass=''user''')

/*
Linking SQL Server to Heterogeneous Systems - DB2, Sybase, Oracle:
http://www.databasejournal.com/features ... hp/1756161

Connection Strings - MSSQL, Access, Oracle, MySQL, Interbase, IBM DB2, Sybase, Informix, Excel, DBF, FoxPro, Visual FoxPro, Pervasive:
http://www.connectionstrings.com
*/

[michal.jarkovsky]

Pomocí uvedených LINKED SERVERŮ se mi nepodařilo načíst DBFka pro Visual FoxPro 9.0 (např. z nový REDky).

Tady je postup jak se mi to nakonec podařilo:
1) Ze stránek Microsoftu stáhnout Microsoft OLE DB Provider for Visual FoxPro 9.0 (vfpoledb.exe)

http://www.microsoft.com/downloads/details.aspx?familyid=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&displaylang=en

2) a pak už funguje

Kód: Vybrat vše

SELECT * FROM OPENROWSET('vfpoledb', 'c:\dbf\';'';'','S_ROZPAD')


kde S_ROZPAD je jméno souboru a c:\dbf\ je cesta k souboru

[jan.havranek]

Následující praktickou ukázku funkčního propojení MSSQL a Firebird pomocí ODBC
poskytl p.Viktor Majer ze společnosti Aerotech Czech s.r.o..

Předpoklady:
1/ Instalace Firebird client na server, kde běží MSSQL.
2/ Instalace Firebird ODBC driver na server, kde běží MSSQL.

Postup:
1/ Instalace Firebird clienta – vybrat minimální instalaci.

1.png (51.95 KiB) Zobrazeno 250154 krát

2/ Instalace správné verze ODBC driveru.
3/ Nastavení ODBC driveru – spustit správce zdrojů ODBC (spustit -> ODBC), zvolit systémová DSN a vybrat správný ovladač.

2.png (36.77 KiB) Zobrazeno 250154 krát

4/ Správné nastavení ODBC driveru a jeho otestování
- Nutno nastavit DSN, cestu k databázi, cestu k fbclient.dll, jméno a heslo k DB.

3.png (48.37 KiB) Zobrazeno 250154 krát

5/ Na MSSQL serveru přidat linkovaný server.
- Nutno vyplnit název linkovaného serveru,
- Provider (Microsoft OLE DB Provider for ODBC Drivers)
- Data source (název systémového DSN)
- Provider string: (DRIVER=Firebird/InterBase(r)driver;UID=SYSDBA;PWD=mypasswd;DBNAME=c:\directory\demo.fdb)
- Location (c:\directory\demo.fdb)

6/ V nastavení zabezpečení definovat kdo bude mít právo přístupu (nutno přidat uživatele, kteří budou mít přístup)

4.png (75.98 KiB) Zobrazeno 250154 krát

7/ Výběr dat pak pomocí openquery

Kód: Vybrat vše

SELECT
Convert(datetime,cas) as datum, jmeno, prijmeni, kod
FROM OPENQUERY(dochazka, 'SELECT * FROM rudalosti')


Odkazy:
http://www.devlist.com/ConnectionStringsPage.aspx
http://www.firebirdsql.org/en/odbc-driver/
http://www.firebirdsql.org/en/firebird-2-5-2-upd1/