I have a stored procedure which output a column. It runs without problem.
我有一个输出列的存储过程。它运行没有问题。
exec dbo.sp_SelectAlignmentSubset
@AlnID
,@ParentTaxID
,@SeqTypeID
,@LocationID
,@SubsetSize
,@SimilarityThreshold
,@SimilarityTable
,@AnchorSeqID
But when I insert the procedure output into a temp table,
但是当我将过程输出插入临时表时,
create table #resultSeqIDs
(
SeqID int not null
)
Insert into #resultSeqIDs
exec dbo.sp_SelectAlignmentSubset
@AlnID
,@ParentTaxID
,@SeqTypeID
,@LocationID
,@SubsetSize
,@SimilarityThreshold
,@SimilarityTable
,@AnchorSeqID
error pop out "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."
错误弹出“当前事务无法提交,无法支持写入日志文件的操作。回滚事务。”
Thanks in advance.
提前致谢。
The query of the procedure is here:
该过程的查询在这里:
Alter Proc dbo.sp_SelectAlignmentSubset
@AlnID int,
@ParentTaxID int,
@SeqTypeID int,
@LocationID int,
@SubsetSize int,
@SimilarityThreshold float,
@SimilarityTable nvarchar(255),
@AnchorSeqID int = null
As
declare @sql nvarchar(4000), @param nvarchar(2000), @subsetActualSize int, @lastSeqID int, @meanSimilarity float
--- Cleaning
begin try
drop table #Subset
end try
begin catch
end catch
begin try
drop table #tmpAllSeqs
end try
begin catch
end catch
begin try
drop table #AllSeqs
end try
begin catch
end catch
--- Create temp tables
create table #Subset
(
SeqID int not null
)
create table #AllSeqs
(
SeqID int not null
)
create table #tmpAllSeqs
(
SeqID int not null,
Similarity float not null
)
--- Prepare anchor
if @AnchorSeqID is not null
begin
Insert into #Subset values (@AnchorSeqID)
set @lastSeqID = @AnchorSeqID
end
--- Get all SeqIDs in the alignment under the parent taxID
Insert into #AllSeqs
Select SeqID
from dbo.fn_SelectAlignedSequences(@AlnID, @ParentTaxID, @SeqTypeID, @LocationID)
--- Put SeqIDs into #Subset
select @subsetActualSize = count(*) from #Subset
while @subsetActualSize < @SubsetSize
begin
-- select 'Subset size:'+cast(@subsetActualSize as nvarchar(10))
truncate table #tmpAllSeqs
set @sql =
' Insert into #tmpAllSeqs
select st.SeqID2 as SeqID
,st.Similarity
from ' + @SimilarityTable + ' st
where st.SeqID1 = @lastSeqID
and st.SeqID2 in ( select SeqID from #AllSeqs )
and st.Similarity <= @SimilarityThreshold
'
set @param = '@lastSeqID int, @SimilarityThreshold float'
exec sp_executesql @sql, @param, @lastSeqID, @SimilarityThreshold
--- Mark the SeqID will be selected from #AllSeqs and
--- added to #Subset in this round
---
--- Here I select the SeqID with minimum difference from
--- mean of similarity as the SeqID
select @meanSimilarity = AVG(Similarity)
from #tmpAllSeqs
select top 1 @lastSeqID = SeqID
from #tmpAllSeqs
order by abs(Similarity - @meanSimilarity) asc
Insert into #Subset values (@lastSeqID)
--- Update #AllSeqs (all candidate SeqIDs)
truncate table #AllSeqs
Insert into #AllSeqs
select SeqID
from #tmpAllSeqs
--- Increment size of #Subset
select @subsetActualSize = count(*) from #Subset
end
select SeqID from #Subset
drop table #Subset
drop table #AllSeqs
drop table #tmpAllSeqs
Go
2 个解决方案
#1
2
You are doing the following statements at the end of your stored procedure:
您在存储过程结束时执行以下语句:
select SeqID from #Subset
drop table #Subset
Try changing that to
尝试将其更改为
Insert Into #resultSeqIDs select SeqID from #Subset
drop table #Subset
Then remove the INSERT
command from where you execute the statement.
然后从执行语句的位置删除INSERT命令。
Since your #resultSeqIDs Temp table will still be in scope, it will hold all the values you need.
由于#resultSeqIDs Temp表仍在范围内,因此它将包含您需要的所有值。
If you are on SQL Server 2008 and above, you can return a table variables - a much better alternative to #temp tables.
如果您使用的是SQL Server 2008及更高版本,则可以返回表变量 - 这是#temp表的一个更好的替代方法。
#2
0
If the procedure runs okay, but inserting a record into a temporary table does not, then I'd check the roles of the user running the record insertion. The error message states read-only access.
如果过程运行正常,但是没有将记录插入临时表,那么我将检查运行记录插入的用户的角色。错误消息指出只读访问权限。
#1
2
You are doing the following statements at the end of your stored procedure:
您在存储过程结束时执行以下语句:
select SeqID from #Subset
drop table #Subset
Try changing that to
尝试将其更改为
Insert Into #resultSeqIDs select SeqID from #Subset
drop table #Subset
Then remove the INSERT
command from where you execute the statement.
然后从执行语句的位置删除INSERT命令。
Since your #resultSeqIDs Temp table will still be in scope, it will hold all the values you need.
由于#resultSeqIDs Temp表仍在范围内,因此它将包含您需要的所有值。
If you are on SQL Server 2008 and above, you can return a table variables - a much better alternative to #temp tables.
如果您使用的是SQL Server 2008及更高版本,则可以返回表变量 - 这是#temp表的一个更好的替代方法。
#2
0
If the procedure runs okay, but inserting a record into a temporary table does not, then I'd check the roles of the user running the record insertion. The error message states read-only access.
如果过程运行正常,但是没有将记录插入临时表,那么我将检查运行记录插入的用户的角色。错误消息指出只读访问权限。