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

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
  • ,I am getting this error : Invalid column name Current Service State – SQLBoy Oct 3 '16 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. – Scott Hodgin Oct 3 '16 at 14:38
  • I am using sql server 2014 – SQLBoy Oct 3 '16 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. – Scott Hodgin Oct 3 '16 at 14:40
  • I just re ran it it was successful,Thanks a lot @Scott Hodgin – SQLBoy Oct 3 '16 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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