[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

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

0
Favorable
Asked:
Favorable
5 Solutions
 
radcaesarCommented:
INSERT INTO #services (fld) values (..........)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try:
CREATE PROCEDURE sp_services
AS

CREATE TABLE #services (fld NVARCHAR(200))

--get output of command in temporary table
INSERT INTO #services 
EXEC master..xp_cmdshell 'sc query state= all' --remove 'state= all' for displaying only running services

ALTER TABLE #services ADD ident SMALLINT identity

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

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
a3, the ident column is used later in joining / filtering on rows of the #services table.  If having ident in the definition is the issue, then just giving you a heads up that the SELECT portion needs to be rewritten also.
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
:)
ALTER TABLE #services ADD ident SMALLINT identity

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
:) I am blind.  Thanks for pointing that out.  That is a great workaround.  
0
 
FavorableAuthor Commented:
Thanks all
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now