当前位置: 代码迷 >> Sql Server >> 找不到可安装的 ISAM。该如何处理
  详细解决方案

找不到可安装的 ISAM。该如何处理

热度:65   发布时间:2016-04-27 11:27:08.0
找不到可安装的 ISAM。
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
本人系菜鸟一名,使用某前辈写的这个存储过程想实现导出表中数据到EXCEL 但是执行的时候总是报错
错误源: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: