?
Solved

Timeout expired. The timeout period elapses prior to completion after "cmd.ExecuteNonQuery" is run.

Posted on 2009-02-20
27
Medium Priority
?
1,044 Views
Last Modified: 2013-12-17
I am creating a C# Console application (on XP SP2) to pass in a Computer Name, see if it exists in DNS, and if it does, Ping the Computer Name, if successful, update a Column named "PingStatus" with value "Reachable" in a Table called "Nodes" on DB called DMS.
When it executes"cmd.ExecuteNonQuery") it throws this error: "Timeout expired. The timeout period elapses prior to completion. That statement has been terminated." Therefore, it never updates the "PingStatus" column with the value "Reachable".  Below is the Table design, c# code and SQL SP.
Any help would be terrific. Thank you experts!
Wallace

--- Table (Called "Nodes" on "DMS" DB) Design ---
1. 1stColumn = NodeID, PK, bigint, not null, 
2. 2ndColumn = Name, varchar(75), not nulll, 
3. 3rdColumn = PingStatus, varchar(50), not null, 
4. 4thColumn = IPAddress, char(15), null)
------- C# CONSOLE APP CODE ----------------------
namespace RmEndpiontStatus
{
    class PingEndpoints
    {
        
        static void Main()
        {
            // retrieves endpoint list from db
            SqlConnection conn = new SqlConnection(SettingsManager.ConnectionString);
            SqlDataReader rdr = null;
 
            try
            {
                conn.Open();
                Console.WriteLine("DB Connection has been opened\n");
                
                // create sql command
                SqlCommand cmd = new SqlCommand("usp_GetNodes", conn);
                SqlCommand rcdCount = new SqlCommand("SELECT Count(*) FROM Nodes", conn);
                UInt32 totalRows = Convert.ToUInt32(rcdCount.ExecuteScalar());
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
 
                // configure input parameters
                SqlParameter param = new SqlParameter();
                param = cmd.Parameters.Add("@Endpoints", System.Data.SqlDbType.VarChar, 75);
                param.Direction = System.Data.ParameterDirection.Input;
 
                //Console.WriteLine(String.Format("There are {0} records in the database.", totalRows));
                //Console.WriteLine("\n");
                //Console.WriteLine("Press any key to continue...");
                //Console.ReadLine();
 
 
                rdr = cmd.ExecuteReader();
 
                if (rdr.HasRows)
                {
                    //int currentRow = 0;
                    while (rdr.Read())
                    {
 
                        // Check and make sure Host is still in DNS
                        try
                        {
                            string hostname = rdr[0].ToString();
                            IPHostEntry IPHost = Dns.GetHostEntry(hostname);
                            foreach (IPAddress address in IPHost.AddressList)
                            {
                                if (address != null)
                                {
                                    Console.WriteLine(String.Format("Host IP Address for {0} is {1}", hostname, address));
                                    PingHosts(address);
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine("Error occurred: " + ex.Message);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error occurred: " + ex.Message);
            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }
 
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
 
        public static void PingHosts(IPAddress address)
        {
            Ping pSend = new Ping();
            PingReply pReply = pSend.Send(address);
 
            //pSend.Send(address);
 
            if (pReply.Status == IPStatus.Success)
            {
                // setting up connection object
                SqlConnection conn = new SqlConnection(SettingsManager.ConnectionString);
                conn.Open();
 
                // setting up command objects
                SqlCommand cmd = new SqlCommand("usp_PingStatus", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
 
                // creating output parameter (as objects)
                // & adding to parameter collection
                string successVal = "";
                SqlParameter param = new SqlParameter();
                param = cmd.Parameters.Add("@Status", System.Data.SqlDbType.VarChar, 50, "PingStatus");
                param.Direction = System.Data.ParameterDirection.InputOutput;
                param.Value = "Reachable";
 
                // execute the query
                cmd.ExecuteNonQuery();
 
                Console.WriteLine(param.Value);
                Console.ReadLine();
                conn.Close();
            }
        }
 
    }
}
--------------- STORED PROCEDURE Code -------------------------------------------------------
USE DMS
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE usp_PingStatus 
	-- variable(s)
	@Status varchar(50) OUTPUT 
 
AS
IF @Status is not null
BEGIN
	UPDATE nodes SET PingStatus = @Status;
END
ELSE
BEGIN
	RETURN;
END
GO

Open in new window

0
Comment
Question by:wally_davis
  • 15
  • 11
27 Comments
 
LVL 19

Expert Comment

by:daveamour
ID: 23692318
How many nodes do you have?
Also can you post the script to create your table?
0
 
LVL 8

Expert Comment

by:bedanand
ID: 23692374
Make sure you have opened the firewall port on the database server.  And also make sure you have enable the remote connection on the database server.


0
 
LVL 19

Expert Comment

by:daveamour
ID: 23692403
I assume you are running this on the pc you used to connect to the database to create the table with Enterprise Manager or Management Studio so I don't think things like permissions/security are likeley to be to blame.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:wally_davis
ID: 23692462
Yes Dave, there are no  issues with permissions/security and the likes of that.
Here's the Nodes table script (and the updated Stored Procedure):
------- NODES Table Script ---------------------------------------------------------------
USE [DMS]
GO
/****** Object:  Table [dbo].[nodes]    Script Date: 02/20/2009 07:59:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[nodes](
      [NodeID] [bigint] IDENTITY(1,1) NOT NULL,
      [Name] [varchar](75) NOT NULL,
      [PingStatus] [varchar](50) NULL,
      [IPAddress] [char](15) NULL,
 CONSTRAINT [PK_nodes] PRIMARY KEY CLUSTERED
(
      [NodeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_nodes] UNIQUE NONCLUSTERED
(
      [Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
-------------- UPDATED STORED PROCEDURE --------------------------------
USE DMS
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE usp_PingStatus
      -- variable(s)
      @Status varchar(50) OUTPUT,
      @Endpoint varchar(75)

AS
IF @Status is not null
--BEGIN
--      -- added to prevent extra result sets from
--      -- interfering with SELECT statements.
--      SET NOCOUNT ON;
--      INSERT INTO nodes (PingStatus) Values (@Status);
--END
--ELSE
BEGIN
      UPDATE nodes SET PingStatus = @Status
      WHERE name = @Endpoint
END
ELSE
BEGIN
      RETURN;
END
GO
0
 

Author Comment

by:wally_davis
ID: 23692518
FYI, UPDATED PORTION OF THE C# Code that may possibly be giving me the problem.
A method call is made to this Method and the IP Address and hostname get passed in here.

public static void PingHosts(IPAddress address, string hostname)
        {
            Ping pSend = new Ping();
            PingReply pReply = pSend.Send(address);

            //pSend.Send(address);

            if (pReply.Status == IPStatus.Success)
            {
                // setting up connection object
                SqlConnection conn = new SqlConnection(SettingsManager.ConnectionString);
                conn.Open();

                // setting up command objects
                SqlCommand cmd = new SqlCommand("usp_PingStatus", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

// creating output parameter (as objects)
                // & adding to parameter collection
                SqlParameter param = new SqlParameter();
                SqlParameter param2 = new SqlParameter();
                param = cmd.Parameters.Add("@Status", System.Data.SqlDbType.VarChar, 50, "PingStatus");
                param2 = cmd.Parameters.Add("@Endpoint", System.Data.SqlDbType.VarChar, 75, "Name");
                param.Direction = System.Data.ParameterDirection.Output;
                param.Value = "Reachable";
                param2.Value = hostname;
                                               
                // execute the query
                cmd.ExecuteNonQuery();  1. Once this executes, the two values above should be passed in and the "Reachable" value added to that specific computers "PingStatus" column but is  not.

                Console.WriteLine(param.Value); <-- Interestingly enough, it doesn't show the Value "Reachable" here.
0
 

Author Comment

by:wally_davis
ID: 23692564
Another question.
Do these values (param.Value = "Reachable"; param2.Value = hostname; )  need to somehow assigned to a variable and then passed into the cmd.ExecuteNonQuery??
0
 
LVL 19

Expert Comment

by:daveamour
ID: 23692568
Can you show me all of the PingHosts method please?
0
 
LVL 19

Expert Comment

by:daveamour
ID: 23692581
Dunno yet - just trying to get everything setup and compiling at the mo!
0
 

Author Comment

by:wally_davis
ID: 23692614
This is the PingHosts method so far....more updates to take place once I or we can get this part figured out.

public static void PingHosts(IPAddress address, string hostname)
        {
            Ping pSend = new Ping();
            PingReply pReply = pSend.Send(address);

            //pSend.Send(address);

            if (pReply.Status == IPStatus.Success)
            {
                // setting up connection object
                SqlConnection conn = new SqlConnection(SettingsManager.ConnectionString);
                conn.Open();

                // setting up command objects
                SqlCommand cmd = new SqlCommand("usp_PingStatus", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                // creating output parameter (as objects)
                // & adding to parameter collection
                SqlParameter param = new SqlParameter();
                SqlParameter param2 = new SqlParameter();
                param = cmd.Parameters.Add("@Status", System.Data.SqlDbType.VarChar, 50, "PingStatus");
                param2 = cmd.Parameters.Add("@Endpoint", System.Data.SqlDbType.VarChar, 75, "Name");
                param.Direction = System.Data.ParameterDirection.Output;
                param.Value = "Reachable";
                param2.Value = hostname;
                                               
                // execute the query
                cmd.ExecuteNonQuery();

                Console.WriteLine(param.Value);
                Console.ReadLine();
                conn.Close();
            }
        }
0
 
LVL 19

Expert Comment

by:daveamour
ID: 23692642
Ok stupid question but it isn't waiting for you to press a key at the end of PingHosts is it?
Console.ReadLine();
 
0
 
LVL 19

Expert Comment

by:daveamour
ID: 23692672
Also you have changed the signature of PingHosts so what is the following line now changed to:
PingHosts(address);  <---- Line is in the Main method

0
 
LVL 19

Expert Comment

by:daveamour
ID: 23692683
I assume just:

PingHosts(address, hostname);
Best to check though
 
0
 

Author Comment

by:wally_davis
ID: 23692696
That's correct. That's the Method Call and  just those two values are passed in.
0
 
LVL 19

Expert Comment

by:daveamour
ID: 23692698
Also which version of SQL Server are you using?
0
 
LVL 19

Expert Comment

by:daveamour
ID: 23692706
Did you check the Console.ReadLine()   - see above
0
 

Author Comment

by:wally_davis
ID: 23692777
We're using SQL 2005 and yes, I respond to the Console.ReadLine() Method but still no update to the DB yet.
0
 
LVL 19

Expert Comment

by:daveamour
ID: 23692783
I need usp_GetNodes as well please.
0
 
LVL 19

Expert Comment

by:daveamour
ID: 23692869
Going in 30 minutes so need to get a move on!
0
 

Author Comment

by:wally_davis
ID: 23692884
Here  you go: usp_GetNodes

USE [DMS]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_GetNodes]
      -- variables
      @Endpoints varchar(75) = '%'
AS
If @Endpoints is not null
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      SELECT n.Name FROM Nodes n WHERE n.Name LIKE @Endpoints
END
ELSE
BEGIN
      RETURN;
END
0
 
LVL 19

Accepted Solution

by:
daveamour earned 2000 total points
ID: 23693002
Ok change your stored procedure as attached.
Also change the PingHosts method as attached and it should work.


CREATE PROCEDURE usp_PingStatus 
      -- variable(s)
      @Status varchar(50),
      @Endpoint varchar(75)
 
AS
IF @Status is not null
--BEGIN
--      -- added to prevent extra result sets from 
--      -- interfering with SELECT statements.
--      SET NOCOUNT ON;
--      INSERT INTO nodes (PingStatus) Values (@Status);
--END
--ELSE
BEGIN
      UPDATE nodes SET PingStatus = @Status
      WHERE name = @Endpoint
END
ELSE
BEGIN
      RETURN;
END
GO
 
        public static void PingHosts(IPAddress address, string hostname)
        {
            Ping pSend = new Ping();
            PingReply pReply = pSend.Send(address);
 
            //pSend.Send(address);
 
            if (pReply.Status == IPStatus.Success)
            {
                // setting up connection object
                SqlConnection conn = new SqlConnection(connectionString);
                conn.Open();
 
                // setting up command objects
                SqlCommand cmd = new SqlCommand("usp_PingStatus", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
 
                // creating output parameter (as objects)
                // & adding to parameter collection
                SqlParameter param = new SqlParameter();
                SqlParameter param2 = new SqlParameter();
                param = cmd.Parameters.Add("@Status", System.Data.SqlDbType.VarChar, 50, "PingStatus");
                param2 = cmd.Parameters.Add("@Endpoint", System.Data.SqlDbType.VarChar, 75, "Name");
                param.Direction = System.Data.ParameterDirection.Input;
                param2.Direction = System.Data.ParameterDirection.Input;
                param.Value = "Reachable";
                param2.Value = hostname;
 
                // execute the query
                cmd.ExecuteNonQuery();
 
                Console.WriteLine(param.Value);
                Console.ReadLine();
                conn.Close();
            }
        }

Open in new window

0
 

Author Comment

by:wally_davis
ID: 23693155
YOU....ARE....AWESOME Dave. lol. That did the trick!!
I thought OUTPUT in my SP was a way of saying that my Console app would OUTPUT
the two values in my code but I can assume that's what the INPUT Does?
Could you please explain the difference between how data moves when using
OUTPUT vs. INPUT?
0
 

Author Comment

by:wally_davis
ID: 23693164
I'll award your points as soon as you answer the question. I just want to make sure I understand the concepts between OUPUT and INPUT. Thank you so much for your help!!
0
 
LVL 19

Expert Comment

by:daveamour
ID: 23693180
In your stored procedure you define parameters as output if they are going to be populated within the stored procedure and then passed back to the calling code.
A bit like how you used ExecuteScalar to count the number of rows but you can output many stored procedure values.
See the following:
http://www.programmingado.net/a-145/Retrieving-SQL-Server-stored-procedure-output-parameters.aspx
 
0
 
LVL 19

Expert Comment

by:daveamour
ID: 23693189
PS Glad to help, was a nice bit of code otherwise.
0
 

Author Comment

by:wally_davis
ID: 23693264
Thank you for the compliment and thank you for your professionalism.
0
 

Author Closing Comment

by:wally_davis
ID: 31549233
Once again Dave, thank you for stepping me through the problem and providing resolution. Its an enjoyable experience to receive assistance (and the solution) from professionals like yourselve. I'd award you 1000 points if I could. :)
0
 
LVL 19

Expert Comment

by:daveamour
ID: 23693938
Your welcome and thanks for the points
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month15 days, 7 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question