Please, help to recreate the following stored proc to return all Services running on my local Instance of SQL, and be able to execute in producation (SQL 2K8 R2).
Evidently, when I try to execute it return error, which I finding difficult to fix. for example.
INSERT INTO #services (fld), returns the following error :
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
CREATE PROCEDURE sp_services
CREATE TABLE #services (ident SMALLINT identity, fld NVARCHAR(200))
--get output of command in temporary table
INSERT INTO #services (fld)
EXEC master..xp_cmdshell 'sc query state= all' --remove 'state= all' for displaying only running services
--keep only needed records (service code, service displayed name, state
DELETE FROM #services
WHERE ISNULL(fld, '') NOT LIKE 'SERVICE_NAME%'
AND ISNULL(fld, '') NOT LIKE 'DISPLAY_NAME%'
AND ISNULL(fld, '') NOT LIKE '%STATE%'
SET fld = LTRIM(RIGHT(fld, LEN(fld) - CHARINDEX(':', fld, 1)))
--display services, names and state
SELECT tbl1.fld AS srv_code, tbl2.fld AS srv_name, tbl3.fld AS state
FROM #services tbl1, #services tbl2, #services tbl3
WHERE tbl2.ident = tbl1.ident+1
AND tbl3.ident = tbl1.ident + 3
DROP TABLE #services