-- 连接EXCEL的方法
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\student.xls',
NULL,
'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
select * from openquery(ExcelSource,'Select * from [table$]');
select * from openquery(ExcelSource, 'select * from [table$]')
首先在SQL2005 所在计算机上安装ORACLE 客户端;并做好配置:
例如:
C:\Oracle\ora90\network\ADMIN\tnsnames.ora
内容修改该为:
myData=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.76.33.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = cy3q)
)
)
在SQL2005中 执行下列代码:
EXEC sp_addlinkedserver
@server = 'OracleDataSource',
@srvproduct = 'Oracle',
@provider = 'MSDAORA.1',
@datasrc = 'myData'
exec sp_addlinkedsrvlogin 'OracleDataSource',
'false',
'sa',
'hello', -- oracle 用户名称
'helloworld' -- oracle用户密码
-- 执行下列测试代码:
select * from openquery(OracleDataSource', 'select jh,rq,scsj from hello.dba01 ')