SQL 远程访问 和 文件访问示例

Remote Access

select * from opendatasource('sqloledb','server=192.168.1.1;uid=sa;pwd=123456;database=TT').TT.dbo.test1

select top 10 * from opendatasource('sqloledb','Data Source=MSOutputDB3;Initial Catalog=TimeSeries;;Integrated Security=SSPI;').TimeSeries.dbo.NetAssets
-----------------------------------------------------------------------------------

File access sample

schema.ini Sample
[US_Other_Funds_20090416.txt]
ColNameHeader = True
Format=TabDelimited
MaxScanRows=0

select SecId+',' from OpenRowset('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=D:\Data;Format=TabDelimited;',
'select * from [US_Other_Funds_20090416.txt]')

select SecId,Year
from opendatasource('sqloledb','Data Source=MSOutputDB3;Initial Catalog=TimeSeries;;Integrated Security=SSPI;').TimeSeries.dbo.NetAssets
where SecId  in
(select SecId from OpenRowset('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\Data;Format=TabDelimited;',
'select * from [US_Other_Funds_20090416.txt]'))

----------------------------------------------------------------------------------
Another sample

select   *   from    
  OPENROWSET('MICROSOFT.JET.OLEDB.4.0'  
  ,'Text;HDR=Yes;DATABASE=D:\Data\FullIdList'
  ,'select * from [b.txt]')
 
  [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\TEXT]