求助： sp_oacreate 和sp_oadestroy 让我很困惑

Question

说明一下情况：我想用动态语句的查询结果输出到EXCEL中的动态Sheet中
于是拜读了邹老大的文章，得到了很大的提示
（文章：http://topic.csdn.net/u/20080515/17/a97870aa-0a43-4f6c-b8cf-9c9ece0d9b09.html）但是，我在运行邹老大的存储过程时出现了障碍
把关键的地方整理了一下
希望大家帮忙解决一下下面是动态生成xls文件的一部分（写的比较粗糙，大家凑和着看吧哈哈）declare @hr int,@obj int,@constr varchar(4000),@out int,
@sql varchar(2000),@tb varchar(200),@path varchar(200)--创建一个adodb的连接（OLE对象）
exec @hr=sp_oacreate 'adodb.connection',@obj out
if @hr<>0 raiserror('1error',16,1)--调用adodb的方法open 
set @path='D:\test.xls'
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES;DATABASE='+@path+'"'
exec @hr=sp_oamethod @obj,'open',null,@constr
if @hr<>0 raiserror('2error',16,1)--调用adodb的方法execute 到这步时，xls文件就已经生成了
set @tb ='ta'
set @sql='create table ta (a int)'
exec @hr=sp_oamethod @obj,'execute',@out out,@sql--释放上面已创建的 OLE 对象adodb
--问题就出在这里，感觉sp_oadestroy这段代码似乎没有起作用,并没有释放OLE对象
exec @hr=sp_oadestroy @obj
if @hr<>0 raiserror('3error',16,1)
--用静态SQL实现的话，提示找不到对象ta$，请确认路径和文件名的正确性（所以只能用动态的去实现）
insert  into 
openrowset('microsoft.jet.oledb.4.0','excel 8.0;hdr=yes;database=D:\test.xls','select * from [ta$]')(a)
select * from csdn.dbo.ta--用动态SQL实现的话，提示文件‘’不能打开，正在被其它的用户打开，没有读取的权限（估计是没有释放OLE对象）
set @sql='insert  into 
openrowset(''microsoft.jet.oledb.4.0'',''excel 8.0;hdr=yes;database='+@path+''',''select * from ['+@tb+'$]'')(a)
select * from csdn.dbo.ta'
exec (@sql)请有经验的朋友帮忙看看
给点思路
不胜感激
谢谢了

Answer 1

这里的D:\test.xls指的是服务器的D盘，不是客户端的

Answer 2

to：SFD:\test.xls服务器就是我的本机啊
是在本地进行的

Answer 3

--释放上面已创建的 OLE 对象adodb
--问题就出在这里，感觉sp_oadestroy这段代码似乎没有起作用,并没有释放OLE对象
exec    @hr=sp_oadestroy @obj
if    @hr<>0 raiserror('3error',16,1)
如果没有error的话,应该就是释放了的.

Answer 4

是否可以分成两段来执行,
同一个process里面操作好像会出现问题,

Answer 5

我试了下，执行到
insert  into 
openrowset('microsoft.jet.oledb.4.0','excel 8.0;hdr=yes;database=D:\test.xls','select * from [ta$]')(a)
select * from xx.dbo.ta
时报，找不到xx.dbo.ta这个对象.

Answer 6

create table xx.dbo.ta( a int)
insert into ta select 1

Answer 7

代码没有问题，我机器能执行（改了7楼说的库名），一时也没模拟出楼主的问题
建议先删除D盘中test.xls、文件后重启机器后再执行一下,还要保证csdn.dbo.ta存在并且只有一个字段

Answer 8

还以为7楼不是楼主，xx.dbo.ta是 数据库名+用户史+表名 改成 ta 就可以，并建一表
create tableta(a int)  
insert into ta select 1

Answer 9

--调用adodb的方法execute 到这步时，xls文件就已经生成了
set    @tb ='ta'
set    @sql='create table ta (a int)'
exec    @hr=sp_oamethod @obj,'execute',@out out,@sql我觉得这个地方有问题吧。

Answer 10

单独建表 ta，一个列a int.
然后去掉：
--调用adodb的方法execute 到这步时，xls文件就已经生成了
set    @tb ='ta'
set    @sql='create table ta (a int)'
exec    @hr=sp_oamethod @obj,'execute',@out out,@sql
这段，就可以直行了。

Answer 11

我不太理解不过这个ta表指的是D：\test.xls文件中的一个sheet名  a是一个表格
我换个机器执行了一下
结果在第二步就报错了
文件‘’不能打开，正在被其它的用户打开，没有读取的权限看来和权限有很大关系
难解决啊

Answer 12

我刚才弄错了，
我又这样试了下，没有问题：declare    @hr int,@obj int,@constr varchar(4000),@out int,
    @sql varchar(2000),@tb varchar(200),@path varchar(200)--创建一个adodb的连接（OLE对象）
exec    @hr=sp_oacreate 'adodb.connection',@obj out
if    @hr<>0 raiserror('1error',16,1)--调用adodb的方法open 
set    @path='D:\test.xls'
set    @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES;DATABASE='+@path+'"'
exec    @hr=sp_oamethod @obj,'open',null,@constr
if    @hr<>0 raiserror('2error',16,1)--调用adodb的方法execute 到这步时，xls文件就已经生成了
set    @tb ='ta'
set    @sql='create table ta (a int)'
exec    @hr=sp_oamethod @obj,'execute',@out out,@sql--释放上面已创建的 OLE 对象adodb
--问题就出在这里，感觉sp_oadestroy这段代码似乎没有起作用,并没有释放OLE对象
exec    @hr=sp_oadestroy @obj
if    @hr<>0 raiserror('3error',16,1)
--用动态SQL实现的话，提示文件‘’不能打开，正在被其它的用户打开，没有读取的权限（估计是没有释放OLE对象）
set @sql='insert  into 
openrowset(''microsoft.jet.oledb.4.0'',''excel 8.0;hdr=yes;database='+@path+''',''select * from ['+@tb+'$]'')(a)
select a from dbname.dbo.tb'
exec (@sql)

Answer 13

去掉静态的我也试过可行，
但exec　@hr=sp_oadestroy @obj 没有达到目的，去了这句结果是一样的
那是因为动态的自动分隔了批，对象自动释放了。

Answer 14

完美行动，没有问题，我只是想弄清静态的为什么不行。
现在我也明白了，因为用静态语句，需要预编，此过程中excel还没有创建,是编译不过报错,所以不能用静态语句。