"Ashkan Daie" <adaie@nospam.com> wrote in message news:uv%23VoS2LGHA.3860@TK2MSFTNGP12.phx.gbl...
> 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
>

"Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in
message news:OBsX8Y9LGHA.668@TK2MSFTNGP11.phx.gbl...
>I used you script and upped the number of rows to 162000. I didn't that any
>errors when executing the CLR proc. I doesn't seem like it, but check out
>http://support.microsoft.com/kb/911310/en-us anyhow.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
>
> "Ashkan Daie" <adaie@nospam.com> wrote in message
> news:uv%23VoS2LGHA.3860@TK2MSFTNGP12.phx.gbl...
>> 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
>>
>

"Ashkan Daie" <adaie@nospam.com> wrote in message
news:eK0ZlMAMGHA.1124@TK2MSFTNGP10.phx.gbl...
>I saw that KB article earlier and it does not look like the symptoms are
>the same as mine. Maybe someone at MS can confirm this. I ran the same
>procedure on two other computers and it worked fine.
>
> I recently installed Team Foundation Server RC on the computer that has
> the problem and that has an update to .Net 2.0 (KB913393). I uninstalled
> the patch and it is still producing the same results. The only other
> difference between my systems is that the one with the fault is a
> Hyper-Threaded processor.
>
> Can someone @ Microsoft confirm this to be a problem?
>
> Thanks
>
>
> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote
> in message news:OBsX8Y9LGHA.668@TK2MSFTNGP11.phx.gbl...
>>I used you script and upped the number of rows to 162000. I didn't that
>>any errors when executing the CLR proc. I doesn't seem like it, but check
>>out http://support.microsoft.com/kb/911310/en-us anyhow.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>>
>> "Ashkan Daie" <adaie@nospam.com> wrote in message
>> news:uv%23VoS2LGHA.3860@TK2MSFTNGP12.phx.gbl...
>>> 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
>>>
>>
>
>

"Steven Hemingray [MSFT]" <steve***@online.microsoft.com> wrote in message
news:OeVv%23%23EMGHA.3708@TK2MSFTNGP09.phx.gbl...
> The fix KB910414 referenced in that KB should fix your problem.  It only
> occurs when a parallel plan is used for that type of query, which is why
> you
> weren't seeing it on the non-hyperthreaded machines.
>
> http://support.microsoft.com/kb/910414/
>
> Steven
>
> "Ashkan Daie" <adaie@nospam.com> wrote in message
> news:eK0ZlMAMGHA.1124@TK2MSFTNGP10.phx.gbl...
>>I saw that KB article earlier and it does not look like the symptoms are
>>the same as mine. Maybe someone at MS can confirm this. I ran the same
>>procedure on two other computers and it worked fine.
>>
>> I recently installed Team Foundation Server RC on the computer that has
>> the problem and that has an update to .Net 2.0 (KB913393). I uninstalled
>> the patch and it is still producing the same results. The only other
>> difference between my systems is that the one with the fault is a
>> Hyper-Threaded processor.
>>
>> Can someone @ Microsoft confirm this to be a problem?
>>
>> Thanks
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote
>> in message news:OBsX8Y9LGHA.668@TK2MSFTNGP11.phx.gbl...
>>>I used you script and upped the number of rows to 162000. I didn't that
>>>any errors when executing the CLR proc. I doesn't seem like it, but check
>>>out http://support.microsoft.com/kb/911310/en-us anyhow.
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://www.solidqualitylearning.com/
>>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>>
>>>
>>> "Ashkan Daie" <adaie@nospam.com> wrote in message
>>> news:uv%23VoS2LGHA.3860@TK2MSFTNGP12.phx.gbl...
>>>> 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
>>>>
>>>
>>
>>
>
>
>

"Ashkan Daie" <adaie@nospam.com> wrote in message
news:eK0ZlMAMGHA.1124@TK2MSFTNGP10.phx.gbl...
>I saw that KB article earlier and it does not look like the symptoms are
>the same as mine. Maybe someone at MS can confirm this. I ran the same
>procedure on two other computers and it worked fine.
>
> I recently installed Team Foundation Server RC on the computer that has
> the problem and that has an update to .Net 2.0 (KB913393). I uninstalled
> the patch and it is still producing the same results. The only other
> difference between my systems is that the one with the fault is a
> Hyper-Threaded processor.
>
> Can someone @ Microsoft confirm this to be a problem?
>
> Thanks
>
>
> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote
> in message news:OBsX8Y9LGHA.668@TK2MSFTNGP11.phx.gbl...
>>I used you script and upped the number of rows to 162000. I didn't that
>>any errors when executing the CLR proc. I doesn't seem like it, but check
>>out http://support.microsoft.com/kb/911310/en-us anyhow.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>>
>> "Ashkan Daie" <adaie@nospam.com> wrote in message
>> news:uv%23VoS2LGHA.3860@TK2MSFTNGP12.phx.gbl...
>>> 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
>>>
>>
>
>

"Ashkan Daie" <adaie@nospam.com> wrote in message
news:eK0ZlMAMGHA.1124@TK2MSFTNGP10.phx.gbl...
>I saw that KB article earlier and it does not look like the symptoms are
>the same as mine. Maybe someone at MS can confirm this. I ran the same
>procedure on two other computers and it worked fine.
>
> I recently installed Team Foundation Server RC on the computer that has
> the problem and that has an update to .Net 2.0 (KB913393). I uninstalled
> the patch and it is still producing the same results. The only other
> difference between my systems is that the one with the fault is a
> Hyper-Threaded processor.
>
> Can someone @ Microsoft confirm this to be a problem?
>
> Thanks
>
>
> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote
> in message news:OBsX8Y9LGHA.668@TK2MSFTNGP11.phx.gbl...
>>I used you script and upped the number of rows to 162000. I didn't that
>>any errors when executing the CLR proc. I doesn't seem like it, but check
>>out http://support.microsoft.com/kb/911310/en-us anyhow.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>>
>> "Ashkan Daie" <adaie@nospam.com> wrote in message
>> news:uv%23VoS2LGHA.3860@TK2MSFTNGP12.phx.gbl...
>>> 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
>>>
>>
>
>

"Ashkan Daie" <adaie@nospam.com> wrote in message
news:uv%23VoS2LGHA.3860@TK2MSFTNGP12.phx.gbl...
> 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
>

"Wells Caughey" <wellscaug***@hotmail.com> wrote in message
news:u3XFqm8NGHA.2828@TK2MSFTNGP12.phx.gbl...
>I have run across a similar problem in the SQLCLR.  For some reason the
> SQLCLR crashes reporting:
>
> .NET Framework execution was aborted. Another query caused the AppDomain
> JunkDb.dbo[runtime].13 to be unloaded or an unhandled .NET exception
> happened.
>
> The weird thing is that this code runs just fine outside of a debug
> session,
> or even inside a debug session so long as there is only one break point;
> but
> once a second break point is added the SQLCLR crashes.
>
> I have seen the hotfix KB910414, but I don't think this addresses my
> problem.  First of all, my computer is not hyperthreaded; its just a
> standard notebook computer.  Secondly, I have set the maximum number or
> worker threads to 1, and thirdly, this problem literally showed up over
> night.  I was debugging the same code on the same machine yesterday just
> fine, and when I try today the SQLCLR crashes!
>
> Thanks,
> Wells
>
>
> "Ashkan Daie" <adaie@nospam.com> wrote in message
> news:uv%23VoS2LGHA.3860@TK2MSFTNGP12.phx.gbl...
>> 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
>>
>
>