Help with a Query

18 May 2006 9:07 PM

Matthew

I am trying to get the following all into a single line, so i can run a
larger Query and have a nice simple table listing all the SQL servies
going on and when it was started.

-TIA-

CREATE TABLE #System_Monitor_Information_SQL_Information
    SystemName     varchar(50)
    MSSQLServer    varchar(50)
    SQLServerAgent varchar(50)
    SQLStartDate   smalldatetime
    ProductVersion varchar(50)
    AuditDateTime smalldatetime

DECLARE @datetime smalldatetime
SET @datetime = (SELECT GETDATE())

INSERT System_Monitor_Information_SQL_Information (SystemName,
AuditDateTime)
SELECT SystemName = @@SERVERNAME,
        AuditDateTime = @datetime

INSERT System_Monitor_Information_SQL_Information (SQLStartDate)
SELECT crdate FROM master.dbo.sysdatabases
WHERE name = 'tempdb'

INSERT System_Monitor_Information_SQL_Information (ProductVersion) EXEC
xp_msver 'ProductVersion'
INSERT System_Monitor_Information_SQL_Information (MSSQLServer)    EXEC
master..xp_servicecontrol 'QUERYSTATE', 'MSSQLServer'
INSERT System_Monitor_Information_SQL_Information (SQLServerAgent) EXEC
master..xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'

SELECT * FROM #System_Monitor_Information_SQL_Information
DROP TABLE #System_Monitor_Information_SQL_Information

18 May 2006 9:37 PM

Steve Kass

Matthew,

It's not clear how you want to treat multi-row results
from single-value results, but here's one snippet that
might help you. It gets the results of EXEC xp_msver
as a table, which should help. (You can get this bit
of information without xp_msver, too, using the
SERVERPROPERTY function, but I'm assuming
the question is how to get this kind of information
more conveniently.)

If you have a loopback linked server set up

UPDATE #System_Monitor_Information_SQL_Information SET
ProductVersion = (
    SELECT Character_Value
    FROM OPENQUERY(ME,'SET FMTONLY OFF; EXEC master..xp_msver
''ProductVersion''')
)

That isn't the most convenient solution, but you can also do this:

CREATE TABLE #ProductVersion (
s varchar(50)
)
INSERT INTO #ProductVersion
EXEC master..xp_msver 'ProductVersion'
UPDATE #System_Monitor_Information_SQL_Information SET
ProductVersion = (
    SELECT s FROM #ProductVersion
)
DROP TABLE #ProductVersion

Steve Kass
Drew University

Matthew wrote:

Show quote

>I am trying to get the following all into a single line, so i can run a
>larger Query and have a nice simple table listing all the SQL servies
>going on and when it was started.
>
>-TIA-
>
>CREATE TABLE #System_Monitor_Information_SQL_Information
>    SystemName     varchar(50)
>    MSSQLServer    varchar(50)
>    SQLServerAgent varchar(50)
>    SQLStartDate   smalldatetime
>    ProductVersion varchar(50)
>    AuditDateTime smalldatetime
>
>DECLARE @datetime smalldatetime
>SET @datetime = (SELECT GETDATE())
>
>INSERT System_Monitor_Information_SQL_Information (SystemName,
>AuditDateTime)
>SELECT SystemName = @@SERVERNAME,
>        AuditDateTime = @datetime
>
>INSERT System_Monitor_Information_SQL_Information (SQLStartDate)
>SELECT crdate FROM master.dbo.sysdatabases
>WHERE name = 'tempdb'
>
>INSERT System_Monitor_Information_SQL_Information (ProductVersion) EXEC
>xp_msver 'ProductVersion'
>INSERT System_Monitor_Information_SQL_Information (MSSQLServer)    EXEC
>master..xp_servicecontrol 'QUERYSTATE', 'MSSQLServer'
>INSERT System_Monitor_Information_SQL_Information (SQLServerAgent) EXEC
>master..xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'
>
>SELECT * FROM #System_Monitor_Information_SQL_Information
>DROP TABLE #System_Monitor_Information_SQL_Information
>
>
>

18 May 2006 9:49 PM

Anith Sen

To get the status of the service, though it is easier to use
xp_servicecontrol, it is not recommended due to its undocumented nature.
Otherwise, for the reminder of the script you can shorten the query as:

INSERT System_Monitor_Information_SQL_Information
( SystemName, AuditDateTime, SQLStartDate, ProductVersion )
SELECT @@SERVERNAME, CURRENT_TIMESTAMP, crdate,
SERVERPROPERTY( 'ProductVersion' )
FROM master.dbo.sysdatabases
WHERE name = 'tempdb' ;

--
Anith