SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\Finance\account.mdb";User ID=Admin;Password=')...YourTable
CREATE PROCEDURE [dbo].[TranCode] AS SET ANSI_NULLS ON SET ANSI_WARNINGS ON SELECT a.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0 ', 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders) AS aGO
你也可以在程序中调用事先做好定义的dts包
举个例子 CREATE PROCEDURE my_proc1 AS exec master..xp_cmdshell 'dtsrun /F d:\包1.dts' GO
提醒: OpenDatasource OpenRowset 相当于在服务器端执行,要用SQL Server 能找到的 Access .mdb 文件路径
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.mdb";User ID=Admin;Password=')...YourTable
AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0 ',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
AS aGO
CREATE PROCEDURE my_proc1 AS
exec master..xp_cmdshell 'dtsrun /F d:\包1.dts'
GO
OpenDatasource
OpenRowset
相当于在服务器端执行,要用SQL Server 能找到的 Access .mdb 文件路径