Query Timeout in ADO.net but it works in SQL Server Mgmt Studio

529 Views Asked by At

This is my configuration:

  • Windows 7 Pro 64b SP1
  • Microsoft SQL Server 2014 -
  • Microsoft SQL Server Management Studio 12.0.2000.8
  • Microsoft .NET Framewrok 4.5.1 SDK
  • Microsoft Visual Studio 2013 (v12)

I’m experiencing a problem when I try to run the following query:

declare @date datetime
set @date='9/12/2014 15:58:06’

    SELECT  TOP 1000 Articolo1.id as 'Articolo1.id' , Articolo1.nome as 'Articolo1.nome' 
    FROM Articolo as Articolo1 WITH (NOLOCK)  
    WHERE ((Articolo1.dataUltimaModifica  >=  @date)) 
     AND NOT EXISTS (
         SELECT [TabPagExt].[TabPag_Id] AS [TabPag_Id]
         FROM ( SELECT TOP 1000 Articolo1_PAG.[Id] AS [TabPag_Id]  
                FROM Articolo as Articolo1_PAG WITH (NOLOCK)
                WHERE ((Articolo1_PAG.dataUltimaModifica  >=  @date)) 
                ORDER BY  Articolo1_PAG.dataUltimaModifica ASC 
               )  AS [TabPagExt] WHERE Articolo1.[Id] = [TabPagExt].[TabPag_Id]
     ) 
    ORDER BY  Articolo1.dataUltimaModifica ASC

If I launch it in SSMS (SQL Server Management Studio) it runs with no problem but when I try it in ADO.net it throws an error:

System.Data.SqlClient.SqlException: Timeout.

Both are launched against a production server in the same machine and connection.

Here is the source code:

private void button1_Click(object sender, EventArgs e)
{
            SqlConnection conn = new SqlConnection(@"Server=mypc\sqlpass2014;Database=myDB;User Id=id;Password=pass;");
            SqlCommand command = new SqlCommand();
            command.CommandTimeout = 150;
            command.Connection = conn;
            conn.Open();
            //command.CommandText = "SET ARITHABORT ON ";
            //command.ExecuteNonQuery();
            command.CommandText = "SELECT TOP 1000 Articolo1.id as 'Articolo1.id' , Articolo1.nome as 'Articolo1.nome' FROM Articolo as Articolo1 WITH (NOLOCK)  WHERE ((Articolo1.dataUltimaModifica  >=  @Articolo1_dataUltimaModificaxxxxx0)) AND NOT EXISTS (SELECT [TabPagExt].[TabPag_Id] AS [TabPag_Id] FROM ( SELECT TOP 1000 Articolo1_PAG.[Id] AS [TabPag_Id]  FROM Articolo as Articolo1_PAG WITH (NOLOCK) WHERE ((Articolo1_PAG.dataUltimaModifica  >=  @Articolo1_dataUltimaModificaxxxxx0)) ORDER BY  Articolo1_PAG.dataUltimaModifica ASC )  AS [TabPagExt] WHERE Articolo1.[Id] = [TabPagExt].[TabPag_Id])  ORDER BY  Articolo1.dataUltimaModifica ASC  ";
            command.Parameters.Add(new SqlParameter("@Articolo1_dataUltimaModificaxxxxx0", new DateTime(2014, 12, 9, 15, 58, 06)));
            try
            {
                SqlDataReader reader = command.ExecuteReader();

                int rows = 0;
                try
                {
                    while (reader.Read())
                        rows++;
                }
                finally
                {
                    reader.Close();
                }
                MessageBox.Show("Rows read: " + rows.ToString());
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.ToString());
            }
            finally
            {
                conn.Close();
            }
}

I’ve tried all the solutions (I think so) given in similar cases already seen here in StackOverflow but none of them worked for me.

Any clue about the source of the problem and possible solution/workaround?

Thank you all in advance for your help,

Mirco

0

There are 0 best solutions below