At one seemingly inoccuous step in my CLR stored procedure, execution stops
and the query times-out.
I've tried debugging the stored proc by stepping into it from within VS.
When I do, I get to the code in question, but then simply get this message:
WARNING: Debugger was accessing T-SQL variables while managed code was not
suspended.
Waiting until the access is done to continue T-SQL execution.
Continueing T-SQL execution.
And these messages appear to repeat indefinitely. I'm running SQL Server
locally on my machine, but this also happens on out development SQL Server
server.
The place in the code it appears to happen is when returning back results
from a lower-level CLR stored proc called within the higher-level CLR stored
proc -- when piping the result set, I suppose.
I've set MAXDOP to 1. No help there.
Anyone know what's going on?
Hello Quimbly,
> The place in the code it appears to happen is when returning back
> results from a lower-level CLR stored proc called within the
> higher-level CLR stored proc -- when piping the result set, I
> suppose.
Can you be a bit more specific about what you're doing?
Is your CLR proc calling another CLR proc without in parameter passed as
ref out out? How are you executing the other stored procedure.
Note: a better newsgroup for this would be Micorosoft.Public.SqlServer.CLR,
I've copied this there.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||> Can you be a bit more specific about what you're doing?
The top-level CLR proc calls a regular static method in the SQL CLR project.
This static method makes a call to another SqlProcedure (static method
tagged with [Microsoft.SqlServer.Server.SqlProcedure] attribute).
The static method is calling the lower-level CLR proc as follows:
string sqlString = "LC_SP_ScheduleEvents_SelectForDate";
SqlCommand command = new SqlCommand(sqlString, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@.P_LuminaireID", luminaireID);
command.Parameters.AddWithValue("@.P_TargetDate", usageDate);
command.Parameters.AddWithValue("@.P_TargetDateStar tInUTC", startTimeUTC);
command.Parameters.AddWithValue("@.P_TargetDateEndI nUTC", endTimeUTC);
SqlDataReader reader = command.ExecuteReader();
//...
Inside the LC_SP_ScheduleEvents_SelectForDate proc, it times out when it's
piping it's results back:
I.e.:
...
for (int i = 0; i < dr.Length; i++)
{
// send one event
eventRow = new SqlDataRecord(eventRowMetaData);
eventRow.SetSqlBoolean(0, bool.Parse(dr[i]["IsLampOn"].ToString()));
eventRow.SetSqlDateTime(1,
DateTime.Parse(dr[i]["ActualEventTime"].ToString()));
eventRow.SetInt32(2, int.Parse(dr[i]["DimmingLevel"].ToString()));
SqlContext.Pipe.SendResultsRow(eventRow);
}
}
SqlContext.Pipe.SendResultsEnd();
|||Hello Quimbly,
I know this seems lame, but I suspect you have a reference type issue here.
Try assigning IsLampOn, ActualEventTime and DimmingLevel to local value types,
then sets those into the DataRecord.
Its low hanging fruit to solve first.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
