- SQL code
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_exporttb] GO create proc p_exporttb @sqlstr sysname, --查询语句,如果查询语句中使用了order by ,请加上top 100 percent,注意,如果导出表/视图,用上面的存储过程 @path nvarchar(1000), --文件存放目录 @fname nvarchar(250), --文件名 @sheetname varchar(250)='' --要创建的工作表名,默认为文件名 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='temp.xls' if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#') --检查文件是否已经存在 if right(@path,1)<>'\' set @[email protected]+'\' create table #tb(a bit,b bit,c bit) set @[email protected][email protected] insert into #tb exec master..xp_fileexist @sql --数据库创建语句 set @[email protected][email protected] if exists(select 1 from #tb where a=1) set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE' +';CREATE_DB="[email protected]+'";[email protected] else set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 5.0;HDR=YES' +';[email protected]+'"' --连接数据库 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 declare @tbname sysname set @tbname='##tmp_'+convert(varchar(38),newid()) set @sql='select * into [[email protected]+'] from([email protected]+') a' exec(@sql) select @sql='',@fdlist='' select @[email protected]+','+a.name ,@[email protected]+',['+a.name+'] ' +case when b.name in('char','nchar','varchar','nvarchar') then 'text('+cast(case when a.length>255 then 255 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 tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp') and a.id=(select id from tempdb..sysobjects where [email protected]) select @sql='create table [[email protected] +']('+substring(@sql,2,8000)+')' ,@fdlist=substring(@fdlist,2,8000) exec @err=sp_oamethod @obj,'execute',@out out,@sql if @err<>0 goto lberr exec @err=sp_oadestroy @obj --导入数据 set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 5.0;HDR=YES ;[email protected][email protected]+''',[[email protected]+'$])' exec('insert into [email protected]+'([email protected]+') select [email protected]+' from [[email protected]+']') set @sql='drop table [[email protected]+']' exec(@sql) 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
错误源:Microsoft JET Database Engine
错误描述:找不到可安装的 ISAM。
我已经安装好了OFFICE 2003上网搜了好久 也没找到解决方法 请各位大虾指路 万分感谢
------解决方案--------------------
The stored proc uses ADO connection to create an excel file (if necessary), create the sheet, and then download the data.
To use the ADO object you need to do the following: