Okay here is the scenario:
I have a managed stored procedure. The procedure takes some parameters and
generates a SQL statement and calls ExecuteAndSend on the SqlPipe class.
When calling the stored procedure and the underlying table has ~100K records
I get the following error:
(100 row(s) affected)
Msg 6535, Level 16, State 70, Line 1
..NET Framework execution was aborted. Another query caused the AppDomain
Commerce.dbo[runtime].2 to be unloaded or an unhandled .NET exception
happened.
Msg 6535, Level 16, State 70, Line 1
..NET Framework execution was aborted. Another query caused the AppDomain
Commerce.dbo[runtime].2 to be unloaded or an unhandled .NET exception
happened.
..NET Framework execution was aborted. Another query caused the AppDomain
Commerce.dbo[runtime].2 to be unloaded or an unhandled .NET exception
happened.
Msg 6535, Level 16, State 49, Procedure ctlg_Search, Line 0
..NET Framework execution was aborted. Another query caused the AppDomain
Commerce.dbo[runtime].2 to be unloaded or an unhandled .NET exception
happened.
System.Threading.ThreadAbortException: Thread was being aborted.
System.Threading.ThreadAbortException:
at
System.Data.SqlServer.Internal.UMGS+PrepExecDel9b955f82-323d-4cd4-aba9-51bfaa922c4a(IntPtr
, Int64 , IntPtr , Int32* , SqlAccessApiReturnCode* )
at System.Data.SqlServer.Internal.UMGS.PrepExecDel.Invoke(IntPtr
ipgchContext, Int64 tranId, IntPtr rsid, Int32* fHasFirstRow,
SqlAccessApiReturnCode* eRc)
at System.Data.SqlServer.Internal.ClrLevelContext.PrepExec(EventTranslator
eventTranslator, Int64 tranId, UrtNativeRequest* pRequest, Int32*
pfRowValid)
at System.Data.SqlServer.Internal.EventTranslator.PrepExec(Int64 tranId,
UrtNativeRequest* pRequest)
at System.Data.SqlServer.Internal.RequestExecutor.Execute(SmiConnection
connection, Int64 transactionId, CommandBehavior behavior, SmiExecuteType
executeType)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean
sendToPipe)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteToPipe(SmiContext pipeContext)
at Microsoft.SqlServer.Server.SqlPipe.ExecuteAndSend(SqlCommand command)
at StoredProcedures.ctlg_Search(SqlString catalogName, SqlString properties,
SqlString orderByProperties, SqlString whereClause, SqlInt32 pageNumber,
SqlInt32 itemsPerPage)
Script to create table:
CREATE TABLE [dbo].[Test_Catalog](
[oid] [int] NOT NULL,
[name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[base_price] [money] NOT NULL,
CONSTRAINT [PK_Test_Catalog] PRIMARY KEY CLUSTERED
(
[oid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Script to populate table:
TRUNCATE TABLE Test_Catalog
DECLARE @row_count AS INT
DECLARE @i AS INT
DECLARE @name AS NVARCHAR(20);
DECLARE @desc AS NVARCHAR(20);
DECLARE @price AS MONEY
SET @row_count = 62000
SET @i = 1
SET NOCOUNT ON
BEGIN TRAN
SET @desc = 'Description';
WHILE @i < @row_count
BEGIN
SET @name = N'Name ' + CAST(@i AS NVARCHAR)
SET @price = RAND() * 10000
INSERT
INTO Test_Catalog VALUES(@i, @name, @desc, @price);
SET @i = @i + 1
END
COMMIT TRAN
Here is the C# code for the managed stored procedure:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ctlg_Search(SqlString catalogName,
SqlString properties,
SqlString orderByProperties,
SqlString whereClause,
SqlInt32 pageNumber,
SqlInt32 itemsPerPage)
{
StringBuilder sqlBuilder = new StringBuilder();
if(properties.IsNull || properties.ToString().Trim() == "")
properties = "*";
sqlBuilder.Append("WITH OrderedCatalog AS (");
long startRecord = (long)pageNumber * (long)itemsPerPage;
long endRecord = startRecord + (long)itemsPerPage - 1;
sqlBuilder.Append("SELECT ");
sqlBuilder.Append("ROW_NUMBER() OVER (ORDER BY ");
sqlBuilder.Append(orderByProperties);
sqlBuilder.Append(") AS RowNumber,");
sqlBuilder.Append(properties);
sqlBuilder.Append(" FROM ");
sqlBuilder.Append(catalogName);
sqlBuilder.Append("_Catalog");
sqlBuilder.Append(")");
sqlBuilder.Append("SELECT * FROM OrderedCatalog ");
sqlBuilder.Append(" WHERE RowNumber BETWEEN ");
sqlBuilder.Append(startRecord);
sqlBuilder.Append(" AND ");
sqlBuilder.Append(endRecord);
SqlCommand command = new SqlCommand(sqlBuilder.ToString());
SqlContext.Pipe.Send(sqlBuilder.ToString());
SqlContext.Pipe.ExecuteAndSend(command);
}
};
Script to run the procedure
EXEC ctlg_Search N'Test', N'*', 'base_price DESC', null, 1, 100
Machine Specs
Windows 2003 R2 32 Bit w/ All Updates
SQL Server 2005 Developer Edition
1 - 3.2 GHz Intel Processor
2 GB Memory
|