2

Can anyone help me to work out below T-SQL code. I am facing issue while adding the second value. The first value gives the state of SQL Service and second value gives the SQL last restart time. I am not a core developer so can anyone help me .

if object_id ('tempdb..#temp1') is not null
drop table tempdb..#temp1

create table #temp1 
([Current Service State] nvarchar(200),
[SQL_Server_last_RestartOn]nvarchar(200))

insert into  #temp1
EXEC master.sys.xp_servicecontrol N'querystate',N'MSSQLSERVER',
(select min(login_time) from master..sysprocesses) as SQL_Server_last_RestartOn 

1 Answer 1

1

Give this a try

IF object_id('tempdb..#temp1') IS NOT NULL
    DROP TABLE #temp1

CREATE TABLE #temp1 (
    [Current Service State] NVARCHAR(200)
    ,[SQL_Server_last_RestartOn] NVARCHAR(200)
    )

INSERT INTO #temp1 ([Current Service State])
EXEC master.sys.xp_servicecontrol N'querystate'
    ,N'MSSQLSERVER'

UPDATE #temp1
SET SQL_Server_last_RestartOn = (
        SELECT min(login_time)
        FROM master..sysprocesses
        )

SELECT *
FROM #temp1
5
  • ,I am getting this error : Invalid column name Current Service State Oct 3, 2016 at 14:35
  • 1
    What version of Sql Server are you using? I just copied my answer into a blank query windows running Sql 2008 and it ran fine without any errors. Oct 3, 2016 at 14:38
  • I am using sql server 2014 Oct 3, 2016 at 14:38
  • 1
    I just ran my answer on 2014 and it ran fine without any errors. Update you question with the 'exact' query you're trying now. Oct 3, 2016 at 14:40
  • I just re ran it it was successful,Thanks a lot @Scott Hodgin Oct 3, 2016 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.