xp_sqlinventory (version 6.5)

Captures SQL Server configuration and inventory information asynchronously on the SQL Server computer and stores it in the specified database and table.

Syntax

xp_sqlinventory {'DbName', 'TableName', Interval | stop}

where

DbName

Specifies the database in which to store inventory information. The DbName specified must already exist and must have enough free space to hold the retrieved inventory data. The DbName can be a maximum of 30 characters and must have a varchar datatype.

TableName

Is the name of the table in which to store the inventory information. The TableName specified will be created if it does not already exist and will store configuration information only for servers that are in the same Windows NT domain as the SQL Server running xp_sqlinventory. The TableName parameter can be a maximum of 30 characters and must be a varchar datatype.

Interval

Specifies the time (in seconds) to wait between checks for inventory messages. The Interval parameter must have an int datatype and can be a value between 1 (once per second) and 3600 (once per hour).

stop

Specifies to stop all in-progress inventory actions.

Remarks

This procedure captures SQL Server configuration and inventory information for all SQL Servers on the domain and stores the information in the specified DbName and TableName. Any SQL Server running the LocalSystem account will not be included.

Caution By default SQL Server is installed as a service in the Windows NT LocalSystem account, and the SQL Server registration and configuration information cannot be sent over the network. For this extended stored procedure to work, you must configure SQL Server to run in a valid domain user account through the Startup dialog box in Control Panel, Services.

Inventory errors are written to the Windows NT Event Log.

Note This procedure does not support named parameters.

The following inventory information is captured for each inventoried SQL Server.

Example

This example checks every two seconds for SQL Server inventory messages. The information is loaded into the db_info table in the sql_info database. After time has passed, the capturing of configure and inventory information stops.

USE pubs
go
DECLARE @db varchar(30)
DECLARE @table varchar(30)
DECLARE @interval int
SELECT @db = 'sql_info', @table = 'db_info', @interval = 2
EXECUTE master..xp_sqlinventory @db, @table, @interval
...
...
EXECUTE master..xp_sqlinventory stop