Loop through temp table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Loop through temp table

Author

Topic

thePhoolish
Starting Member

22 Posts

Posted - 2002-08-12 : 12:21:29

I'm fairly new to T-SQL..so go easy please.

Basically I have a table with a list of server IP's

I'm trying to create a Stored Proc that will loop through each server in this table, ping it, and dump the results into a recordset that I can access through ASP.

I've had an attempt at this, some of the code is below. I have a SP called SP_GetServers that already grabs all the rows from the server table.

I have a rough outline of what to do:
Grab all server rows into temp table,
Loop through temp table and use ip column to use against ping command,
add ping result (yes/no) to new column in temp table,
return temp table to ASP page.

I am going about this the right way?

------------------------

CREATE PROC SP_ServerStats
AS
DECLARE @strCmd VARCHAR(60)

CREATE TABLE #Servers

INSERT #Servers
EXEC SP_GetServers

SELECT *
FROM #Servers

SELECT @strCmd = 'ping ' + @strServerIP
EXEC Master..xp_cmdShell @strCmd
GO

DROP TABLE #Servers

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-12 : 13:06:47

declare @IP varchar(30)
declare @MaxIP varchar(20)

select @IP = '', @MaxIP = max(IP) from #tbl

while @IP < @MaxIP
begin
select @IP = min(IP) from #tbl where IP > @IP
SELECT @strCmd = 'ping ' + @IP
exec ...
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

thePhoolish
Starting Member

22 Posts

Posted - 2002-08-13 : 04:49:10

Thanks, but it's still got problems.

I get the msg below when parsing:

Server: Msg 156, Level 15, State 1, Procedure SP_ServerStats, Line 10
Incorrect syntax near the keyword 'INSERT'.

The table that sources the SP_GetServers has three columns, serverId, serverIP and serverName.

I don't understand where you get the values for @IP and @MaxIP in the While clause.

thanks for your help.

CREATE PROCEDURE SP_ServerStats
AS
DECLARE @strCmd VARCHAR(60)
DECLARE @IP VARCHAR(30)
DECLARE @MaxIP VARCHAR(20)

CREATE TABLE #Servers

INSERT #Servers
EXEC SP_GetServers

SELECT *
FROM #Servers

while @IP < @MaxIP
begin
select @IP = min(serverIP) from #Servers where serverIP > @IP
SELECT @strCmd = 'ping ' + @IP
exec Master..xp_cmdShell @strCmd
end

DROP TABLE #Servers

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-13 : 06:41:43

CREATE PROCEDURE SP_ServerStats
AS

CREATE TABLE #Servers
(
ServerID int ,
ServerIP varchar(100) ,
ServerName varchar(100)
)

INSERT #Servers
EXEC SP_GetServers

declare @ServerIP varchar(100)
declare @ServerName varchar(100), @maxSeverName varchar(100)
SELECT @ServerName = '', @maxSeverName = max(ServerName) from #Servers

while @ServerName < @maxSeverName
begin
select @ServerName = min(ServerName) from #Servers where ServerName > @ServerName
select @ServerIP = ServerIP from #Servers where ServerName = @ServerName
SELECT @strCmd = 'ping ' + @ServerIP
exec Master..xp_cmdShell @strCmd
end

DROP TABLE #Servers

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

thePhoolish
Starting Member

22 Posts

Posted - 2002-08-13 : 07:10:53

works a treat.. :D

thanks

thePhoolish
Starting Member

22 Posts

Posted - 2002-08-19 : 10:43:49

Just 1 more thing... ;)

How would I insert the result from the ping into a 'ping' column in the #servers table? ...so I can return servername, IP and ping state in 1 nice formated RS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources