Some SQL Server network properties

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

General SQL Server Forums

Script Library

Some SQL Server network properties

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 09:58:45

[code]DECLARE @Stage TABLE
(
RowID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED,
Data VARCHAR(90),
Section INT
)

INSERT @Stage
(
Data
)
EXEC xp_cmdshell 'ipconfig /all'

DECLARE @Section INT

SET @Section = 0

UPDATE @Stage
SET @Section = Section = CASE
WHEN ASCII(LEFT(Data, 1)) > 32 THEN @Section + 1
ELSE @Section
END

SELECT MAX(CASE WHEN x.minRowID IS NULL THEN NULL ELSE s.Data END) AS Header,
MAX(CASE WHEN s.Data LIKE '%Host Name%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS HostName,
MAX(CASE WHEN s.Data LIKE '%Media State%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS MediaState,
MAX(CASE WHEN s.Data LIKE '%Description%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS [Description],
MAX(CASE WHEN s.Data LIKE '%IP Address%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS IPaddress,
MAX(CASE WHEN s.Data LIKE '%Physical Address%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS PhysicalAddress
FROM @Stage AS s
LEFT JOIN (
SELECT MIN(RowID) AS minRowID
FROM @Stage
GROUP BY Section
) AS x ON x.minRowID = s.RowID
WHERE s.Section > 0
GROUP BY s.Section[/code]

E 12°55'05.25"
N 56°04'39.16"

no_body_perfect
Starting Member

1 Post

Posted - 2008-06-27 : 18:22:44

dear Peso
i interest in your post but really there are some parts i can't understand like
after declaring the @stage what u mean by

INSERT @Stage
(
Data
)

what is data you asked to insert

Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-28 : 03:41:48

"Data" is the name of the column where I insert the result of the DOS command "ipconfig /all".

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources