Link to home
Start Free TrialLog in
Avatar of Favorable
FavorableFlag for United States of America

asked on

SQL Server Script

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
AS

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%'

--remove labels
UPDATE #services
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

--EOF--

ASKER CERTIFIED SOLUTION
Avatar of radcaesar
radcaesar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Favorable

ASKER

Thanks all