我想用存储过程把SQL的表导出为dbf的文件,在网络上找了很久!但还是不能实现的!之后我找到一个像可以实现了!但还是出现错误!请大家帮我看看!谢谢!
p_exporttb @tbname='表的名称',@path='d:\',@over=0if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id,N'IsProcedure')= 1)drop procedure [dbo].[p_exporttb]
GOcreate proc p_exporttb
@tbname sysname, --要导出的表名
@path nvarchar(1000), --文件存放目录
@fname nvarchar(250)='', --文件名,默认为表名
@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) if isnull(@fname,'')='' set @fname=@tbname+'.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=YES;DATABASE='+@path+'"'exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberr
exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr select @sql='',@fdlist=''
select @fdlist=@fdlist+','+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 'int'
when b.name in('smalldatetime','datetime') then 'datetime'
when b.name in('money','smallmoney') then 'money'
else b.name end
FROM syscolumns a left join systypes b on a.xtype=b.xusertype
where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
and object_id(@tbname)=id
select @sql='create table ['+@fname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000)
if @over=1
begin
exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberr
end
exec @err=sp_oadestroy @obj
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''dBase 5.0;DATABASE='+@path+''',''select * from ['+@fname+']'')'
--导入数据
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from '+@tbname)
return
lberr:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
select @sql,@constr,@fdlist
go运行后出现一下错误:
0x80004005 Microsoft JET Database Engine 找不到可安装的 ISAM。
d:\ Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="dBASE 5.0;;HDR=YES;DATABASE=d:\" NULL请问这是什么原因呢?还有如果要导出到一个已有的dbf文件里也有一句:
insert into openrowset('MSDASQL','Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\','select * from 表.DBF') select * from 表名
也出错:
服务器: 消息 7399,级别 16,状态 1,行 1
OLE DB 提供程序 'MSDASQL' 报错。
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]File 'stock.dbf' does not exist.]
请问又是怎么回事呢!谢谢!
p_exporttb @tbname='表的名称',@path='d:\',@over=0if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id,N'IsProcedure')= 1)drop procedure [dbo].[p_exporttb]
GOcreate proc p_exporttb
@tbname sysname, --要导出的表名
@path nvarchar(1000), --文件存放目录
@fname nvarchar(250)='', --文件名,默认为表名
@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) if isnull(@fname,'')='' set @fname=@tbname+'.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=YES;DATABASE='+@path+'"'exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberr
exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr select @sql='',@fdlist=''
select @fdlist=@fdlist+','+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 'int'
when b.name in('smalldatetime','datetime') then 'datetime'
when b.name in('money','smallmoney') then 'money'
else b.name end
FROM syscolumns a left join systypes b on a.xtype=b.xusertype
where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
and object_id(@tbname)=id
select @sql='create table ['+@fname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000)
if @over=1
begin
exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberr
end
exec @err=sp_oadestroy @obj
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''dBase 5.0;DATABASE='+@path+''',''select * from ['+@fname+']'')'
--导入数据
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from '+@tbname)
return
lberr:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
select @sql,@constr,@fdlist
go运行后出现一下错误:
0x80004005 Microsoft JET Database Engine 找不到可安装的 ISAM。
d:\ Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="dBASE 5.0;;HDR=YES;DATABASE=d:\" NULL请问这是什么原因呢?还有如果要导出到一个已有的dbf文件里也有一句:
insert into openrowset('MSDASQL','Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\','select * from 表.DBF') select * from 表名
也出错:
服务器: 消息 7399,级别 16,状态 1,行 1
OLE DB 提供程序 'MSDASQL' 报错。
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]File 'stock.dbf' does not exist.]
请问又是怎么回事呢!谢谢!
解决方案 »
- 请问这样select 变量 = 变量 from 如何查询
- 请问这句话有什么问题
- 想找sql2008企业版的下载
- 求简单的SQL关联查询
- 客户端打印sql server中的smallmoney类型数据,如何默认打印小数点后的: .00
- SQL 数量求和 小于 一定值
- 一表中的kindID(整型)与Kind(字符型)一一对应,我想知道select * from talbe where KindID=1 快还是select * from talbe where KindID=
- sqlserver2012安装出错怎么解决
- 简化sql 语句
- ASP.NET+SQL Server 2000的Web Application需要什么服务器?大伙提点儿意见
- sqlservr.exe - 应用程序出错 请各位帮帮忙
- 为什么我不能用SQL登录还有关于安装的问题
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料4.dbf]')
--*/
--/* dBase III文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料3.dbf]')
--*/
--/* FoxPro 数据库
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'select * from [aa.DBF]')
--*/
/**************导入DBF文件****************/
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where country != "USA" order by country')
------------
根据上面有人提供远程导入:
(转:http://topic.csdn.net/t/20031009/14/2337194.html)
dbf文件这样处理,只能在同一台电脑上.
你可以用xp_cmdshell调用复制命令,将数据复制到服务器上,再处理.
exec master..xp_cmdshell 'copy \\192.168.0.2\d\0926.DBF d:\0926.dbf'
select a.* from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=d:\',
'select * from [0926.DBF]') as a
=======================
CREATE proc SQLTODBF
@tbname sysname, --要导出的表名
@path nvarchar(1000), --文件存放目录
@fname nvarchar(250), --文件名,默认为表名
@over bit=1 --是否覆盖已经存在的文件,如果不覆盖,则直接追加
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000) --参数检测
if isnull(@fname,'')='' set @fname=@tbname+'.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+'"' --连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err <> 0 goto lberr exec @err=sp_oamethod @obj,'open',null,@constr
if @err <> 0 goto lberr --创建表的SQL
select @sql='',@fdlist=''
select @fdlist=@fdlist+','+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 'int'
when b.name in('smalldatetime','datetime') then 'datetime'
when b.name in('money','smallmoney') then 'money'
when b.name in('bit') then 'bit'
else b.name end
FROM syscolumns a left join systypes b on a.xtype=b.xusertype
where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
and object_id(@tbname)=id
select @sql='create table ['+@fname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000) if @over=1
begin
exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err <> 0 goto lberr
end exec @err=sp_oadestroy @obj set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''dBase 5.0;DATABASE='
+@path+''',''select * from ['+@fname+']'')' --导入数据
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from '+@tbname) return lberr:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
select @sql,@constr,@fdlist
GO
用该存储过程导出DBF表的时候有两个问题存在,请高手帮忙解决下~
感激不尽:
1. when b.name in('smalldatetime','datetime') then 'datetime'
此句表示SQL Server 表中的列如果是smalldatetime或datetime型,DBF文件中的字段建为datetime型.可建出的DBF表中发现,该字段为date型,即日期型,而不是日期时间型,这样丢失了数据的时间部分,请问如何解决? 2. DBF文件如果存在,@over=1也不会覆盖文件,而只是在文件中追加数据. 请各位大虾帮帮忙...小弟先行谢过...
成功运行后
在f:盘下找不到我导出的文件呢?
2、如果要导出到已有的dbf文件呢?覆盖原有记录
这样怎么实现啊?