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

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

wally_davisAsked:
Who is Participating?
 
daveamourCommented:
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
 
daveamourCommented:
How many nodes do you have?
Also can you post the script to create your table?
0
 
bedanandCommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
daveamourCommented:
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
 
wally_davisAuthor Commented:
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
 
wally_davisAuthor Commented:
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
 
wally_davisAuthor Commented:
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
 
daveamourCommented:
Can you show me all of the PingHosts method please?
0
 
daveamourCommented:
Dunno yet - just trying to get everything setup and compiling at the mo!
0
 
wally_davisAuthor Commented:
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
 
daveamourCommented:
Ok stupid question but it isn't waiting for you to press a key at the end of PingHosts is it?
Console.ReadLine();
 
0
 
daveamourCommented:
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
 
daveamourCommented:
I assume just:

PingHosts(address, hostname);
Best to check though
 
0
 
wally_davisAuthor Commented:
That's correct. That's the Method Call and  just those two values are passed in.
0
 
daveamourCommented:
Also which version of SQL Server are you using?
0
 
daveamourCommented:
Did you check the Console.ReadLine()   - see above
0
 
wally_davisAuthor Commented:
We're using SQL 2005 and yes, I respond to the Console.ReadLine() Method but still no update to the DB yet.
0
 
daveamourCommented:
I need usp_GetNodes as well please.
0
 
daveamourCommented:
Going in 30 minutes so need to get a move on!
0
 
wally_davisAuthor Commented:
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
 
wally_davisAuthor Commented:
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
 
wally_davisAuthor Commented:
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
 
daveamourCommented:
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
 
daveamourCommented:
PS Glad to help, was a nice bit of code otherwise.
0
 
wally_davisAuthor Commented:
Thank you for the compliment and thank you for your professionalism.
0
 
wally_davisAuthor Commented:
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
 
daveamourCommented:
Your welcome and thanks for the points
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.