各位好，下面的代码可以生成一个dbf文件。代码中想生成一个test20111122文件，但在有的电脑上生成dbf文件的文件名只有8位，结果为test2011文件。实在不知道什么原因。。
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
