SQL query help: error in Inserting into temp table from stored procedure

Question

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

Answer 1

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.

Since your #resultSeqIDs Temp table will still be in scope, it will hold all the values you need.

If you are on SQL Server 2008 and above, you can return a table variables - a much better alternative to #temp tables.

Answer 2

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.