再给你提供一种方法,看看对你有帮助吗,是本地access,到远程sqlserver数据库的一种处理方式,使用存储过程。@accPath,是本地Access数据库的完整路径。 CREATE PROCEDURE TestProc @AccPath varchar(500) AS Declare @SqlStr nvarchar(500) Declare @AccValue varchar(500) set @AccValue='Data Source="'+ @AccPath + '";User ID=Admin;Password=''' if exists(select * from sysobjects where name='测试表' and xtype='U') Drop Table [测试表] set @SqlStr = 'SELECT * INTO 测试表 FROM OPENDATASOURCE (''Microsoft.ACE.OLEDB.12.0'',''' + @AccValue + ')...测试表' exec sp_executesql @SqlStr GO
var
n: variant;
oconnection: variant;
ostep,otask,ocustomtask,mopackage: variant;
begin
mopackage := createoleobject(dts.package2);
oconnection := mopackage.connections.new(sqloledb);
ostep := mopackage.steps.new;
otask := mopackage.tasks.new(dtsexecutepackagetask);
ocustomtask := otask.customtask;
mopackage.failonerror := true;
ocustomtask.packagepassword := user;
ocustomtask.filename := strdts_name;
ocustomtask.name := execpkgtask;
ostep.taskname := ocustomtask.name;
ostep.name := execpkgstep;
ostep.executeinmainthread := true;
mopackage.steps.add(ostep);
mopackage.tasks.add(otask);
try
mopackage.execute;
except
ocustomtask := n;
otask := n;
ostep := n;
mopackage.uninitialize;
execdts := false;
exit;
end;
execdts := true;
ocustomtask := n;
otask := n;
ostep := n;
mopackage.uninitialize;
end;调用的时候,在按钮里调用上面的函数:
if execdts('c:\test.dts') then
showmessage('成功') else showmessage('失败');\
希望对你有帮助。procedure TForm1.Button1Click(Sender: TObject);
var
aSql: String;
begin
with self.ADOQuery1 do
begin
Asql := 'exec master.dbo.xp_cmdshell ''dtsrun /S /E /N"dtsTest" /A"DestAddress":8="%s" '
+ '/A"DestUser":8="%s" /A"DestPwd":8="%s" '
+ '/A"SourceAddress":8="%s" /A"SourceUser":8="%s" /A"SourcePwd":8="%s"''';
aSql := Format(aSql, [
'192.168.1.100',
'sa',
'123',
'192.168.1.200',
'sa',
'456'
]); close;
sql.Clear;
sql.Add(asql);
Execsql;
close;
end;
end;
另外你可以把组织好的sql语句,放到查询分析器里面执行,看看具体报了什么错误。
CREATE PROCEDURE TestProc @AccPath varchar(500) AS
Declare @SqlStr nvarchar(500)
Declare @AccValue varchar(500)
set @AccValue='Data Source="'+ @AccPath + '";User ID=Admin;Password='''
if exists(select * from sysobjects where name='测试表' and xtype='U')
Drop Table [测试表]
set @SqlStr = 'SELECT * INTO 测试表 FROM OPENDATASOURCE (''Microsoft.ACE.OLEDB.12.0'',''' + @AccValue + ')...测试表'
exec sp_executesql @SqlStr
GO
就是说,你要先把本地的access数据库上传到服务器上,然后执行。
我处理的时候,是一条龙的。就是说对于客户来说,就是本地到远程。中间的过程我都处理了。