存储过程在VC中调用时和在查询分析器里调用时结果不一样？

[evlon]

我写了一个数据导入的存储过程，在使用时发现用VC调用时存储过程执行到一半就退出了，而且还没有提示。结果Excel表中的数据也没有完成导入!

而在查询分析器中调用该存储过程就没有问题？

这是为什么呀？头都 大了!
附：

ALTER proc dbo.usp_importExcelAnalysisData
(
@file varchar(300)
)
as
begin

declare @maxPiCi int
,@SheetName varchar(50)
,@SortID int
,@Rows int
,@strRows varchar(30)
,@sql varchar(3000)
,@InSql varchar(500)
,@InSqlNoHdr varchar(500)
,@SelFields varchar(200)
,@RowCount int

--set @SelFields='应答试呼比,每线话务量,平均通话时长'

Select @Rows=RowsToCount from SheetInfo where SortID=@SortID
select @maxPiCi=max(nPiCi) from dbo.AnalysisData
if(@maxPiCi is NULL) set @maxPiCi=0
--print @maxPiCi

declare cur_SheetName Cursor
for
select SheetName,SortID from SheetInfo order by SortID ASC
open cur_SheetName
fetch next from cur_SheetName into @SheetName,@SortID
while @@Fetch_Status=0
begin
set @inSqlNoHdr='Select * from OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=NO;DATABASE='+@file+''','+@SheetName+'$) a Where a.F1 like ''%未入库%'''
--print @inSqlNoHdr
execute(@InSqlNoHdr)
Select @RowCount=@@rowcount
if(@rowCount=0)
begin
set @InSql=' from OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;DATABASE='+@file+''','+@SheetName+'$) a'
if(@SortID=0) set @SelFields='对端局名,应答试呼比,每线话务量,平均通话时长'
else set @SelFields='电路群名,应答试呼比,每线话务量,平均通话时长'
Select @Rows=RowsToCount from SheetInfo where SortID=@SortID
if(@Rows is NULL)
set @strRows=''
else
set @strRows='top '+cast(@Rows as varchar)+' '
set @Sql='select '+@strRows+cast ((@MaxPiCi+1) as varchar ) +' as nPiCi ,'
+cast (@SortID as varchar ) +' as SheetSortID ,IDENTITY(int,1,1) as RowID,'+@SelFields+' into #tmpData'+

@inSql+ ';insert into AnalysisData
select * from #tmpData'

--print @Sql
execute(@sql)
end

fetch next from cur_SheetName into @SheetName,@SortID
end
close cur_SheetName
deallocate cur_SheetName
end

GO

[zjcxc]

既然查询分析器中没有问题
说明问题出在你的VC调用处理上

可能的原因:

1.用户权限的问题,执行openrowset需要类似系统管理员(例如)的权限

2.excel文件目录的问题,要求你的excel文件放在sql服务器上.

3.超时的问题,你的数据处理要求较长的时间,执行时发生超时,解决的方法是设置对象的超时属性,以ado为例,设置对象的:
.commandtimeout=0 --sql处理超时,为0表示不限制
.connectimeout=0 --连接超时.

[niukl]

如果上述方法也不行的话，建议你把你的存储过程改成下面这样：
CREATE proc dbo.usp_importExcelAnalysisData
(
@file varchar(300)
)
as
begin

declare @maxPiCi int
,@SheetName varchar(50)
,@SortID int
,@Rows int
,@strRows varchar(30)
,@sql varchar(3000)
,@InSql varchar(500)
,@InSqlNoHdr varchar(500)
,@SelFields varchar(200)

--set @SelFields='应答试呼比,每线话务量,平均通话时长'

Select @Rows=RowsToCount from SheetInfo where SortID=@SortID
select @maxPiCi=max(nPiCi) from dbo.AnalysisData
if(@maxPiCi is NULL) set @maxPiCi=0
print @maxPiCi

declare cur_SheetName Cursor
for
select SheetName,SortID from SheetInfo order by SortID ASC
open cur_SheetName
fetch next from cur_SheetName into @SheetName,@SortID
while @@Fetch_Status=0
begin
set @InSql=' from OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;DATABASE='+@file+''','+@SheetName+'$) a'
set @inSqlNoHdr=' from OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=NO;DATABASE='+@file+''','+@SheetName+'$) a'
if(@SortID=0) set @SelFields='对端局名,应答试呼比,每线话务量,平均通话时长'
else set @SelFields='电路群名,应答试呼比,每线话务量,平均通话时长'
Select @Rows=RowsToCount from SheetInfo where SortID=@SortID
if(@Rows is NULL)
set @strRows=''
else
set @strRows='top '+cast(@Rows as varchar)+' '
set @Sql='select '+@strRows+cast ((@MaxPiCi+1) as varchar ) +' as nPiCi ,'
+cast (@SortID as varchar ) +' as SheetSortID ,IDENTITY(int,1,1) as RowID,'+@SelFields+' into #tmpData'+

@inSql+ ';insert into AnalysisData
select * from #tmpData'


set @Sql='if (not exists(Select * '+@inSqlNoHdr+' Where a.F1 like ''%未入库%'')) begin '+'execute('''+replace(@sql,'''','''''')+''')'+' end'

print @Sql
execute(@sql)

fetch next from cur_SheetName into @SheetName,@SortID
end
close cur_SheetName
deallocate cur_SheetName
end

GO

[evlon]

谢谢邹建建兄和niukl，问题已经解决！