0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • How long does it take to run in SSMS. An ADO.net connection will time out (default is 30 seconds i think, but it can be adjusted) whereas queries in management studio will just run until they complete. – Ben Robinson Dec 19 '14 at 14:41
  • Hi, it is not a timeout (adjustable one, I mean) matter, I've tried till 150, 180 even 210 but no changes. In SSMS it takes around 50s to complete. – Mirco Castellani Dec 20 '14 at 07:29

0 Answers0