Help with a Query

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_Informat
ion
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_Informati
on (SystemName,
AuditDateTime)
SELECT SystemName = @.@.SERVERNAME,
AuditDateTime = @.datetime
INSERT System_Monitor_Information_SQL_Informati
on (SQLStartDate)
SELECT crdate FROM master.dbo.sysdatabases
WHERE name = 'tempdb'
INSERT System_Monitor_Information_SQL_Informati
on (ProductVersion) EXEC
xp_msver 'ProductVersion'
INSERT System_Monitor_Information_SQL_Informati
on (MSSQLServer) EXEC
master..xp_servicecontrol 'QUERYSTATE', 'MSSQLServer'
INSERT System_Monitor_Information_SQL_Informati
on (SQLServerAgent) EXEC
master..xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'
SELECT * FROM #System_Monitor_Information_SQL_Informat
ion
DROP TABLE #System_Monitor_Information_SQL_Informat
ionMatthew,
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_Informat
ion 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_Informat
ion SET
ProductVersion = (
SELECT s FROM #ProductVersion
)
DROP TABLE #ProductVersion
Steve Kass
Drew University
Matthew wrote:

>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_Informat
ion
> 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_Informati
on (SystemName,
>AuditDateTime)
>SELECT SystemName = @.@.SERVERNAME,
> AuditDateTime = @.datetime
>INSERT System_Monitor_Information_SQL_Informati
on (SQLStartDate)
>SELECT crdate FROM master.dbo.sysdatabases
>WHERE name = 'tempdb'
>INSERT System_Monitor_Information_SQL_Informati
on (ProductVersion) EXEC
>xp_msver 'ProductVersion'
>INSERT System_Monitor_Information_SQL_Informati
on (MSSQLServer) EXEC
>master..xp_servicecontrol 'QUERYSTATE', 'MSSQLServer'
>INSERT System_Monitor_Information_SQL_Informati
on (SQLServerAgent) EXEC
>master..xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'
>SELECT * FROM #System_Monitor_Information_SQL_Informat
ion
>DROP TABLE #System_Monitor_Information_SQL_Informat
ion
>
>|||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_Informati
on
( SystemName, AuditDateTime, SQLStartDate, ProductVersion )
SELECT @.@.SERVERNAME, CURRENT_TIMESTAMP, crdate,
SERVERPROPERTY( 'ProductVersion' )
FROM master.dbo.sysdatabases
WHERE name = 'tempdb' ;
Anith