Getting Timeout on stored procedure call

Question

I have a stored procedure which works fine when executed manually but when it is called on the code it return a Null Reference Exception because of a TimeOut. Here's the code:

private DataTable GetProcedure(DateTime dtStart, DateTime dtEnd) {
        DataTable dt = new DataTable();
        SqlDataReader reader;

        string cs = //Connection String, it works everywhere, no need to put the code.
        SqlConnection sqlConnection = new SqlConnection(cs);
        string cmdText = "ProcedureName";
        SqlCommand cmd = new SqlCommand(cmdText, sqlConnection);
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter pFechaInicio = new SqlParameter("@dtStart", dtStart);
        SqlParameter pFechaFin = new SqlParameter("@dtEnd", dtEnd);
        cmd.Parameters.Add(pFechaInicio);
        cmd.Parameters.Add(pFechaFin);

        sqlConnection.Open();

        reader = cmd.ExecuteReader(); //NULL REFERENCE EXCEPTION HERE

        dt.Load(reader);

        sqlConnection.Close();

        return dt;
}

I made a manual call to the stored procedure with the exact same parameters and it worked fine (The stored procedure returns a 3 row / 2 column result with no NULLs whatsoever.

Even more, this error started happening 1 month ago but the program has been running for 6 months flawlesly.

EDIT: Here's some info on the SP:

SP header:

ALTER PROCEDURE  [dbo].[SPName]
        @dtStart datetime, @dtEnd datetime
AS
BEGIN
    SET NOCOUNT ON;  

select column1 as Name1, column2 as Name2
/* doesn't matter */

END

The specific result I'm getting when I execute the SP manually is:

column1     column2
1.0000000   105.426890
2.0000000   96.316330
3.0000000   82.849690

And here's the whole error code:

en System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   en System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   en System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   en System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   en System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   en System.Data.SqlClient.SqlDataReader.get_MetaData()
   en System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   en System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   en System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   en System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   en System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   en System.Data.SqlClient.SqlCommand.ExecuteReader()
   en BACK.OFERTAS.COBERTURAS.PRECIOS._01._00.StartJob.GetProcedure(DateTime dtStart, DateTime dtEnd) en C:\Users\Dell\Documents\BACK.OFERTAS.COBERTURAS.PRECIOS.01.00\StartJob.cs:línea 278
   en BACK.OFERTAS.COBERTURAS.PRECIOS._01._00.StartJob.Start() en C:\Users\Dell\Documents\BACK.OFERTAS.COBERTURAS.PRECIOS.01.00\StartJob.cs:línea 176

Re-Reading the whole stackTrace it seems like a Timeout? How is that even possible, there SP is executed in less than 1 sec.

It will make no sense... The stored procedure returns a select with 2 decimal columns. In this specific case it returns 3 rows with correct values. It returns a single DataTable as well, there's just one select. — Miquel Coll, Oct 13 '15 at 10:22
Please show the full text of the exception, as well as the stacktrace. — Lasse V. Karlsen, Oct 13 '15 at 10:23
I think the issue is with parameter passed as datetime in your stored proc, that's why i wanted to see your sp. — Paresh J, Oct 13 '15 at 10:23
Possible duplicate of [What is a NullReferenceException and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — , Oct 13 '15 at 10:39
@MiquelColl Got the solution?? You can use varchar in your code as well as in sp and then CAST as DATETIME in your sp. I think it should help. — Paresh J, Oct 13 '15 at 10:41
I set the Timeout higher and it worked but I'm still wondering how is that possible.. This SP runs in less than 1 sec. in the server but it is running for almost 50 secs on the job... — Miquel Coll, Oct 13 '15 at 10:43
@Andreas it's a TimeOut really. And as I read the Null appears because it's trying to close the reader when this error turns out. — Miquel Coll, Oct 13 '15 at 10:43
stacktrace shows `System.Data.SqlClient.SqlConnection.OnError`. Check your connection first. — qxg, Oct 13 '15 at 10:49
The connection is OK. Everything's fine. The problem is a strange TimeOut since the SP is fast but not in the job (~50 secs) — Miquel Coll, Oct 13 '15 at 10:52
If your `"/* doesn't matter */` is a where clause see the canonical [Slow in the Application, Fast in SSMS?](http://www.sommarskog.se/query-plan-mysteries.html) — Alex K., Oct 13 '15 at 11:03
Thank you Alex, you're absoletuly right. I'll try to understand and carefully read that article. — Miquel Coll, Oct 13 '15 at 12:29

score 0 · Accepted Answer · answered Oct 19 '15 at 11:21

0

I've solved it changing the TimeOut time of the procedure (which was of 30 secs by default).

This happens because it isn't the same to execute a SP directly on the server than in the job.

answered Oct 19 '15 at 11:21

Miquel Coll

729
12
43

Getting Timeout on stored procedure call

1 Answers1