SQLServer 连接 EXCEL

Database - MSSQL

Written by xlingfairy

Wednesday, 08 July 2009 22:41

INSERT INTO CSK_Store_Retailer_Change_Rate

SELECT *

FROM

OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0' , 'Data Source=d:\retailerChange.xls;Extended Properties=Excel 8.0')...[Sheet1$]

启用Ad Hoc Distributed Queries：

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

使用完成后，关闭Ad Hoc Distributed Queries：

exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=sa;Password=sa'
).DataBaseName.dbo.Table

select * from openrowset( 'SQLOLEDB ', 'IP地址'; '用户名'; '密码',数据库名.dbo.表名)

< Prev		Next >

Last Updated ( Friday, 17 July 2009 10:39 )