SQLSERVER2000使用TSQL将数据导入ACCESS并压缩生成rar

查询分析器操作ACCESS数据表数据

(1)查询:select top 10 * from OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘C:\Documents and Settings\Administrator\桌面\update.mdb’; ‘admin’;
”, product) 。

(2)删除ACCESS中一度是的多少(在ASP后台实现,也可用TSQL实现)

<%

  filename = “zongbu”

  Dim SourceFile,TargetFile,TargetFileName
  SourceFile = “single\” & filename & “_update.mdb”
  TargetFileName = filename & “_update” & “_” & username
  TargetFile = “single\” & TargetFileName & “.mdb”

  Set conn2 = Server.CreateObject(“ADODB.Connection”)
  conn2.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=”&Server.MapPath(TargetFile)
  set rs2=server.createobject(“adodb.recordset”)
  ’清空貨色號
  sql2=”delete from product”
  rs2.open sql2,conn2,1,1
  set rs2=nothing
  conn2.close:set conn2=nothing

%>

(3)新增:

insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’, ‘C:\Documents and
Settings\Administrator\桌面\update.mdb’; ‘admin’; ”,
product)(itemno,pkindid,productno,type)
values(‘1111′,’97’,’11110033′,’pcr’)。

(4)实现缩小的贮存过程:

CREATE procedure [dbo].[pr_getRar] @path varchar(5000),@sourcefile
varchar(500),@targetfile varchar(500) as
/*
— winrar: mdb to rar for download
— createdate: 2016/07/05
— exec pr_getRar ‘D:\WEB\SINGLE’,’cc.txt’,’cc.rar’
*/
ACCESS,begin tran
set nocount on
declare @err int
set @err = 0

declare @SQLSTR varchar(5000)
set @SQLSTR =’C:\”Program Files”\WinRAR\WinRAR.exe m -r -ep1 -o+
“‘+@path+’\’+@targetfile+'” “‘+@path+’\’+@sourcefile+'”‘ —
调用winrar工具压缩并挂已在文件
— print @SQLSTR

EXEC MASTER..XP_CMDSHELL @SQLSTR  – 最先执行
if @@error <> 0
  set @err = @err + 1

if @err = 0
begin
  commit tran
  select 1 as result
end
else
begin
  rollback tran
  select 0 as result
end

 

相关文章