日期:2014-05-18 浏览次数:20503 次
EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; go EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE; go EXEC sp_configure 'allow updates', 0; --设置为0 GO RECONFIGURE WITH OVERRIDE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO declare @path nvarchar(500), @over int, @obj int, @sql nvarchar(1000) set @path = N'D:\test\test20111122.dbf' set @over = 1 declare @flag int, @cmd1 nvarchar(500), @cmd2 nvarchar(500) declare @directorypath nvarchar(100), @constr nvarchar(1000) set @directorypath = N'D:\test\' if object_id(N'tempdb..#tb') is not null drop table #tb create table #tb(a bit,b bit,c bit) insert into #tb exec master..xp_fileexist @path if exists(select 1 from #tb where a = 1) if @over=1 begin set @path=N'del ' + @path --print @path exec master..xp_cmdshell @path,no_output end else set @over=0 else begin set @cmd1 = N'dir ' + @directorypath set @cmd2 = N'md ' + @directorypath exec @flag = xp_cmdshell @cmd1 , NO_OUTPUT if @flag != 0 exec master.dbo.xp_cmdshell @cmd2 set @over=1 end if right(@directorypath,1)<>'\' set @directorypath = @directorypath + '\' --数据库创建语句 set @constr = N'Provider = Microsoft.Jet.OLEDB.4.0; Extended Properties=''dBASE III;' + 'HDR=yes;IMEX=2;DATABASE=' + @directorypath + '''' declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int --连接数据库 exec @err=sp_OACreate 'adodb.connection', @obj out if @err<>0 begin exec sp_OAGetErrorInfo 0, @src out, @desc out print @src print @desc --print 'connection' end set @constr = replace(@constr, '&', '^&') --打开数据库 exec @err=sp_OAMethod @obj, 'open', null, @constr if @err<>0 begin exec sp_OAGetErrorInfo 0,@src out,@desc out print @src print @desc end declare @filepath varchar(50) set @filepath = 'test20111122.dbf' set @sql = 'Id decimal(5, 0), Name nvarchar(10), fenshu decimal(5, 2)' select @sql= 'create table ' + @filepath + '(' + @sql +')' if @over=1 begin exec @err=sp_OAMethod @obj, 'execute', @out out, @sql if @err<>0 begin -- rollback tran exec sp_OAGetErrorInfo 0,@src out,@desc out print @src print 'execute' print @desc end end exec @err = sp_OADestroy @obj