Hello everybody,
I've encountered a strange thing using a CLR Stored procedure:
The procedure throws an exception with no message inside... value = {" "}
Basically the procedure has a string as argument which contains a SQL statement that changes according to the users selections...
The results of the query are saved into a dataset for further processing.
Those resultsets can sometimes be very big (ex: 15000 records...). (For the record the procedure works fine for smaller datasets ex 6000 records and running the same query on the application server returns the expected resultset )
By Debugging the procedure I could determine that the failing point was when the dataset is filled...
Anyone having any idea?
The only information I have from this exception is the stacktrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.CloseInternal(Boolean closeReader)
at System.Data.SqlClient.SqlDataReader.Close()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at DataAccess.runScalar(String strSQL, Boolean isStoredProcedure) in c:\Inetpub\wwwroot\Statistix\App_Code\DataAccess.cs:line 114
Thanks in advance
Alaindlk
Hello,
Still nobody having any idea of what is happening here?
Could it be possible that the SQL server is running out of memory or even having a connection timeout?
this is the procedure I've written that causes this behaviour:
[Microsoft.SqlServer.Server.SqlProcedure]
public static int Stats_Print_Data(string SqlQuery)
{
// Put your code here
int jobId = -1;
using (DataSet dsResults = new DataSet())
{
using (SqlConnection mConn = new SqlConnection("context connection = true"))
{
SqlCommand mCmd = new SqlCommand(SqlQuery, mConn);
mCmd.CommandTimeout = 100;
SqlDataAdapter mDa = new SqlDataAdapter(mCmd);
mDa.Fill(dsResults, "Print_Data");
if (dsResults.Tables[0].Rows.Count > 0)
{
ArrayList ColumnNames = new ArrayList();
for (int i = 8; i < dsResults.Tables[0].Columns.Count; i++)
{
DataColumn dc = dsResults.Tables[0].Columns
;
ColumnNames.Add(dc.ColumnName);
}
StringBuilder InsertCommand = new StringBuilder();StringBuilder Values = new StringBuilder();
try
{
mConn.Open();
mCmd.CommandText = "Select max(report_id) from Statistix_Report";
jobId = System.Convert.ToInt16(mCmd.ExecuteScalar());
jobId++;
foreach (DataRow dr in dsResults.Tables[0].Rows)
{
if (InsertCommand.Length > 0) InsertCommand.Remove(0, InsertCommand.Length);
if (Values.Length > 0) Values.Remove(0, Values.Length);
mCmd.CommandText = "Select count(*) from print_data where AG_LIB = '" + dr[2].ToString().Replace("'", "''") +
"' and AN_LIB = '" + dr[3].ToString().Replace("'", "''") + "' and PRD_LIB = '" + dr[4].ToString().Replace("'", "''") +
"' and TM_LIB = '" + dr[5].ToString().Replace("'", "''") + "' and RM_LIB = '" + dr
.ToString().Replace("'", "''") +
"' and SU_LIB = '" + dr[7].ToString().Replace("'", "''") + "'";
if (System.Convert.ToInt16(mCmd.ExecuteScalar()) > 0)
{
InsertCommand.Append("Update PRINT_DATA set ");
for (int i = 0; i < ColumnNames.Count; i++)
{
InsertCommand.Append(ColumnNames + "_" + int.Parse(dr["Month"].ToString()) + " = ");
if (dr[i + 8] != DBNull.Value)
{
InsertCommand.Append(dr[i + 8].ToString().Replace(",", "."));
}
else
{
InsertCommand.Append("0");
}
if (i != ColumnNames.Count - 1)
{
InsertCommand.Append(",");
}
}
InsertCommand.Append(" where AG_LIB = '" + dr[2].ToString().Replace("'", "''"));
InsertCommand.Append("' and AN_LIB = '" + dr[3].ToString().Replace("'", "''") + "' and PRD_LIB = '" + dr[4].ToString().Replace("'", "''"));
InsertCommand.Append("' and TM_LIB = '" + dr[5].ToString().Replace("'", "''") + "' and RM_LIB = '" + dr.ToString().Replace("'", "''"));
InsertCommand.Append("' and SU_LIB = '" + dr[7].ToString().Replace("'", "''") + "' and JOB_ID = " + jobId);
mCmd.CommandText = InsertCommand.ToString();
mCmd.ExecuteNonQuery();
}
else
{
Values = new StringBuilder();
InsertCommand = new StringBuilder();
InsertCommand.Append("insert into print_data(AG_LIB,AN_LIB,PRD_LIB,TM_LIB,RM_LIB,SU_LIB,JOB_ID,");
Values.Append("'" + dr[2].ToString().Replace("'", "''") + "','" + dr[3].ToString().Replace("'", "''") + "','");
Values.Append(dr[4].ToString().Replace("'", "''") + "','" + dr[5].ToString().Replace("'", "''") + "','");
Values.Append(dr.ToString().Replace("'", "''") + "','" + dr[7].ToString().Replace("'", "''") + "'," + jobId + ",");
for (int i = 0; i < ColumnNames.Count; i++)
{
InsertCommand.Append(ColumnNames + "_" + int.Parse(dr["Month"].ToString()));
if (dr[i + 8] != DBNull.Value)
{
Values.Append(dr[i + 8].ToString().Replace(",", "."));
}
else Values.Append("0");
if (i != ColumnNames.Count - 1)
{
InsertCommand.Append(",");
Values.Append(",");
}
}
InsertCommand.Append(") values (");
InsertCommand.Append(Values.ToString() + ")");
mCmd.CommandText = InsertCommand.ToString();
mCmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw new Exception(InsertCommand.ToString(), ex);}
finally
{
mCmd.Dispose();
mConn.Close();
}
}}
}
return jobId;
}
Any help would be greatly appreciated...
How can i debug something like this?
Thanks in advance
|||Are you sure that it is the error message thrown by SqlClient is empty and not the exception you throw here:
catch (Exception ex)
{
throw new Exception(InsertCommand.ToString(), ex);
}
? What happens if you remove this catch block and let the unhandled exception get propogated back to sql server - is it still empty?
|||Hello Steven,
Thanks for your reply...
The exception I've given above is effectively thrown by the stored procedure...
Further investigation learned me that it was indeed a timeout of the stored procedure, so I deed improve the indexing on the tables which now solves partly my problem...
However The problem still remains as the stored procedure, after fetching the required data, saves this data into a table for reporting. The problem is that each 12 rows in the dataset has to be transformed as one record with 12 columns (Table sucks but I have to work with this solution imposed to me 
) This means I first need to know if the record already exists, if not then create an insert statement else create an update statement.
Now it seems that executing this causes a timeout for the procedure.
Is there any way to increase this timeout period?
Hello everybody,
I think I've finally found the reason why this timeout occurs: Thats because of the fact that the stored procedure returns a value...
In the worst case, my procedure takes about 10 minutes to complete.... letting the Application server waiting that long is "unacceptable". I think that SQL server automatically throws a timeout exception whenever a procedure/ function takes to long to complete.
I've solved this by changing the procedure signature from Static int SP_Name to static void SP_name(); This way the procedure doesn't have to return a value and isn't any more interrupted by a timeout thrown by SQL server...
Can anyone confirm me this behaviour of SQL server? (By the way this solution works fine for me now...)
Thanks in advance
No comments:
Post a Comment