楼主是这样做的: @sqlstr varchar(8000), --要导出的查询名 @path nvarchar(1000), --文件存放目录 @fname nvarchar(250)='temp.dbf',--文件名,默认为temp @over bit=0 --是否覆盖已经存在的文件,如果不覆盖,则直接追加 as declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000),@fdlist1 varchar(8000)--参数检测 if isnull(@fname,'')='' set @fname='temp.dbf'--检查文件是否已经存在 if right(@path,1)<>'\' set @path=@path+'\' create table #tb(a bit,b bit,c bit) set @sql=@path+@fname insert into #tb exec master..xp_fileexist @sql if exists(select 1 from #tb where a=1) if @over=1 begin set @sql='del '+@sql exec master..xp_cmdshell @sql,no_output end else set @over=0 else set @over=1--数据库创建语句 set @sql=@path+@fname set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="dBASE 5.0;' +'HDR=NO;DATABASE='+@path+'"'--创建表的SQL declare @tbname sysname set @tbname='##tmp_'+convert(varchar(38),newid()) set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a' exec(@sql)--连接数据库 exec @err=sp_oacreate 'adodb.connection',@obj out if @err<>0 goto lberrexec @err=sp_oamethod @obj,'open',null,@constr if @err<>0 goto lberr--创建表的SQL select @sql='',@fdlist='',@fdlist1='' select @fdlist=@fdlist+','+(case b.name when 'datetime' then 'replace(convert(nvarchar(10),'+a.name+',23),''-'','''') as ' +a.name else a.name end), @fdlist1=@fdlist1+',['+a.name+']', @sql=@sql+',['+a.name+'] ' +case when b.name in('char','nchar','varchar','nvarchar') then 'text('+cast(case when a.length>250 then 250 else a.length end as varchar)+')' when b.name in('tynyint','int','bigint','tinyint') then 'text(4)' when b.name in('smalldatetime','datetime') then 'text(10)' when b.name in('money','smallmoney') then 'text(8)' when b.name in('image') then 'General' when b.name in('decimal','float','numeric','int') then 'text(10)' when b.name in('text') then 'memo' else b.name end FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype where b.name not in('uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp') and a.id=(select id from tempdb..sysobjects where name=@tbname) select @sql='create table ['+@fname +']('+substring(@sql,2,8000)+')' ,@fdlist=substring(@fdlist,2,8000)set @fdlist1=substring(@fdlist1,2,8000)if @over=1 begin exec @err=sp_oamethod @obj,'execute',@out out,@sql if @err<>0 goto lberr endexec @err=sp_oadestroy @objset @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''dBase 5.0;DATABASE=' +@path+''',''select * from ['+@fname+']'')'--导入数据 exec('insert into '+@sql+'('+@fdlist1+') select '+@fdlist+' from ['+@tbname+']')set @sql='drop table ['+@tbname+']' exec(@sql)return 1lberr: return 0GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
@sqlstr varchar(8000), --要导出的查询名
@path nvarchar(1000), --文件存放目录
@fname nvarchar(250)='temp.dbf',--文件名,默认为temp
@over bit=0 --是否覆盖已经存在的文件,如果不覆盖,则直接追加
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000),@fdlist1 varchar(8000)--参数检测
if isnull(@fname,'')='' set @fname='temp.dbf'--检查文件是否已经存在
if right(@path,1)<>'\' set @path=@path+'\'
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
if exists(select 1 from #tb where a=1)
if @over=1
begin
set @sql='del '+@sql
exec master..xp_cmdshell @sql,no_output
end
else
set @over=0
else
set @over=1--数据库创建语句
set @sql=@path+@fname
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="dBASE 5.0;'
+'HDR=NO;DATABASE='+@path+'"'--创建表的SQL
declare @tbname sysname
set @tbname='##tmp_'+convert(varchar(38),newid())
set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'
exec(@sql)--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberrexec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr--创建表的SQL
select @sql='',@fdlist='',@fdlist1=''
select @fdlist=@fdlist+','+(case b.name when 'datetime' then 'replace(convert(nvarchar(10),'+a.name+',23),''-'','''') as ' +a.name else a.name end),
@fdlist1=@fdlist1+',['+a.name+']',
@sql=@sql+',['+a.name+'] '
+case when b.name in('char','nchar','varchar','nvarchar') then
'text('+cast(case when a.length>250 then 250 else a.length end as varchar)+')'
when b.name in('tynyint','int','bigint','tinyint') then 'text(4)'
when b.name in('smalldatetime','datetime') then 'text(10)'
when b.name in('money','smallmoney') then 'text(8)'
when b.name in('image') then 'General'
when b.name in('decimal','float','numeric','int') then 'text(10)'
when b.name in('text') then 'memo'
else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in('uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
and a.id=(select id from tempdb..sysobjects where name=@tbname)
select @sql='create table ['+@fname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000)set @fdlist1=substring(@fdlist1,2,8000)if @over=1
begin
exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberr
endexec @err=sp_oadestroy @objset @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''dBase 5.0;DATABASE='
+@path+''',''select * from ['+@fname+']'')'--导入数据
exec('insert into '+@sql+'('+@fdlist1+') select '+@fdlist+' from ['+@tbname+']')set @sql='drop table ['+@tbname+']'
exec(@sql)return 1lberr:
return 0GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
为什么导出dbf格式呢?应该可以直接导成excel之类的吧!
.dbf应该是VisulFoxPro的数据库文件吧.