Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Some SQL Server network properties

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

Print | posted on Thursday, June 19, 2008 3:57 PM | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# re: Some SQL Server network properties

only if xp_cmdshell is enabled...
6/26/2008 9:09 AM | maja
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET