Issue retriving data from mssql 2005

[Mikael]

Different queries accessing a storedprocedure sometimes hang until they are
timedout.

Calling the stored procedure mannually (Management Studio)(from the client
computer that also executes the program) returns 1090 rows in one sec.

EXEC spa08v1GetTransactions NULL ,'20091006','20091006'

Executing the stored procedure from a simple app, just counting the rows
gives me a timeout at 956 rows. This runs nicely against my test enviroment.

SqlCommand cmd = new SqlCommand();
cmd.Connection = new
SqlConnection("Server=MyServer;Database=MyDatabase;Trusted_Connection=True;");
cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.CommandText = "[dbo].[spa08v1GetTransactions]";
SqlParameter p = cmd.Parameters.Add("port_key",
System.Data.SqlDbType.Int);
p.Value = DBNull.Value;

p = cmd.Parameters.Add("from_date",
System.Data.SqlDbType.SmallDateTime);
p.Value = DateTime.Parse("2009-10-06");

p = cmd.Parameters.Add("to_date",
System.Data.SqlDbType.SmallDateTime);
p.Value = DateTime.Parse("2009-10-06");

cmd.Connection.Open();
SqlDataReader dr = cmd.ExecuteReader();

int i = 0;

while(dr.Read())
{
//Console.WriteLine(dr[3]);
i++;
}
cmd.Connection.Close();

Console.WriteLine(i);
Console.Read();



Running on test and production:
Microsoft SQL Server 2005 - 9.00.3080.00 (X64) Sep 6 2009 09:15:46
Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on
Windows NT 5.2 (Build 3790: Service Pack 2)

App. running 2008 - 3.5


I had the same issue last monday and it helped dropping and recreating the
stored procedure, but it dosent help me today.

I also tried the DBCC FREEPROCCACHE without any luck.

Any idears (Besides restarting the production server

 

[Family Tree Mike]

Different queries accessing a storedprocedure sometimes hang until they are
timedout.

Calling the stored procedure mannually (Management Studio)(from the client
computer that also executes the program) returns 1090 rows in one sec.

EXEC spa08v1GetTransactions NULL ,'20091006','20091006'

Executing the stored procedure from a simple app, just counting the rows
gives me a timeout at 956 rows. This runs nicely against my test enviroment.

SqlCommand cmd = new SqlCommand();
cmd.Connection = new
SqlConnection("Server=MyServer;Database=MyDatabase;Trusted_Connection=True;");
cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.CommandText = "[dbo].[spa08v1GetTransactions]";
SqlParameter p = cmd.Parameters.Add("port_key",
System.Data.SqlDbType.Int);
p.Value = DBNull.Value;

p = cmd.Parameters.Add("from_date",
System.Data.SqlDbType.SmallDateTime);
p.Value = DateTime.Parse("2009-10-06");

p = cmd.Parameters.Add("to_date",
System.Data.SqlDbType.SmallDateTime);
p.Value = DateTime.Parse("2009-10-06");

cmd.Connection.Open();
SqlDataReader dr = cmd.ExecuteReader();

int i = 0;

while(dr.Read())
{
//Console.WriteLine(dr[3]);
i++;
}
cmd.Connection.Close();

Console.WriteLine(i);
Console.Read();



Running on test and production:
Microsoft SQL Server 2005 - 9.00.3080.00 (X64) Sep 6 2009 09:15:46
Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on
Windows NT 5.2 (Build 3790: Service Pack 2)

App. running 2008 - 3.5


I had the same issue last monday and it helped dropping and recreating the
stored procedure, but it dosent help me today.

I also tried the DBCC FREEPROCCACHE without any luck.

Any idears (Besides restarting the production server

One thing that jumps out at me is you do not ever dispose of the SQL
objects. That shouldn't cause any issue as described, necessarily.

When "the same thing" works in one code, but not another, always check
permissions of the user.