Cursor for the following code
-
I'm trying to create a cursor to execute through the following code. Or if there a better way to do this:
Step 1: create a temp table that will be used to store the results from EXEC MASTER..xp_cmdshell 'ping computer name'
Step 2: Determine the total number of machines that will need to be pinged based on the results from (SELECT pingName, id FROM Remote_MedNet_Users)
Step 3: Run code to insert data into temp table for each computer.
Step 4:Update table Remote_MedNet_Users to determine if they are indeed online
Step 5: Return resilts back to the user from the table
SELECT * FROM Remote_MedNet_Users WHERE online= ‘y’
Step 6: run through all steps again.
Here is the code I have for the non looping part:
IF EXISTS(SELECT name
FROM TEMPDB..sysobjects
WHERE name like '#PING%' )
DROP TABLE #PING
;
CREATE TABLE #PING (
ID INT IDENTITY(1,1), OUTPUT VARCHAR(150))
INSERT INTO #PING (OUTPUT)
EXEC MASTER..xp_cmdshell 'ping insert computername according to each record found in a table (SELECT pingName, id FROM Remote_MedNet_Users)'
DECLARE
@v_OUTPUT VARCHAR(10)
SET @V_OUTPUT = (SELECT SUBSTRING(OUTPUT, 1, 5)
FROM #PING
WHERE ID = 4)
IF @v_OUTPUT = 'Reply'
BEGIN
Update Remote_MedNet_Users SET online = 'y'
WHERE id = 'The results from the first record found in (SELECT pingName, id FROM Remote_MedNet_Users) from above'
END
ELSE IF @v_OUTPUT = 'Reque'
BEGIN
Update Remote_MedNet_Users SET online = 'n'
WHERE id = 'The results from the first record found in (SELECT pingName, id FROM Remote_MedNet_Users) from above'
END
-- Output the results from the update
SELECT * FROM Remote_MedNet_Users WHERE online= ‘y’
-
Try this,
SET NOCOUNT ON
CREATE TABLE #PingResult (
OUTPUT VARCHAR(150)
)
DECLARE @Cmd nvarchar(200)
DECLARE @ComputerName sysname -- may need to change datatype
DECLARE @ComputerId int -- may need to change datatype
DECLARE @PingMsg varchar(150)
DECLARE @PktLoss int
DECLARE @ChrLoc1 int
DECLARE @ChrLoc2 int
DECLARE curCmp CURSOR FOR
SELECT pingName, id
FROM Remote_MedNet_Users
OPEN curCmp
FETCH NEXT FROM curCmp INTO @ComputerName
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM #PingResult
SET @Cmd = N'Ping ' + @ComputerName
INSERT INTO #PingResult (OUTPUT)
EXEC MASTER..xp_cmdshell @Cmd
SELECT @PingMsg = OUTPUT
FROM #PingResult
WHERE Output LIKE '% loss)%'
SELECT @ChrLoc1 = CHARINDEX('(', @PingMsg, 1) + 1
SELECT @ChrLoc2 = (PATINDEX('% loss)%', @PingMsg) - 1) - @ChrLoc1
SELECT @PktLoss = CAST(SUBSTRING(@PingMsg, @ChrLoc1, @ChrLoc2 ) as int)
IF @PktLoss = 0
BEGIN
UPDATE Remote_MedNet_Users
SET Online = 'Y'
WHERE id = @ComputerId
END
ELSE IF @PktLoss = 100
BEGIN
UPDATE Remote_MedNet_Users
SET Online = 'N'
WHERE id = @ComputerId
END
ELSE
BEGIN
UPDATE Remote_MedNet_Users
SET Online = 'C'
WHERE id = @ComputerId
END
FETCH NEXT FROM curCmp INTO @ComputerName
END
CLOSE curCmp
DEALLOCATE curCmp
DROP TABLE #PingResult
-- Output the results from the update
SELECT * FROM Remote_MedNet_Users WHERE Online = 'Y'This script checks the Packet loss line of the output. This will allow you to assign different status as shown by the 'IF @PktLoss ...' lines.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
Well I keep getting the same error. Here is the DB detail. Where you see pingName is what I am considering the computer name:
Server: Msg 16924, Level 16, State 1, Line 22
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
-
Sorry, the perils of modifying the code after it's been tested.
I changed the SQL statement for the cursor to be what you required, unfortunately I didn't alter the FETCH statement
Change the FETCH statements to
FETCH NEXT FROM curCmp INTO @ComputerName, @ComputerId
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface -
Ya after I posted I relized that there were two spots where that was the case. Thanks it works great
Also what does the C mean in the following update
"UPDATE Remote_MedNet_Users
SET Online = 'c'
WHERE id = @ComputerId"
Edited by - kbrady on 09/25/2003 8:20:47 PM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply