xp_sqlinventory (T-SQL)

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

Syntax

xp_sqlinventory {'database', 'table', {interval | STOP}}

Arguments

'database'

Is the database in which to store inventory information. The database specified must already exist and must have enough free space to hold the retrieved inventory data. database is varchar(30), with no default.

'table'

Is the name of the table in which to store the inventory information. The specified table is created if it does not already exist, and stores configuration information only for servers that are in the same Microsoft Windows NT® domain as the SQL Server running xp_sqlinventory. table is varchar(30), with no default.

interval

Is the time (in seconds) to wait between checks for inventory messages. interval is int, and can be a value between 1 (once per second) and 3600 (once per hour).

STOP

Specifies that all in-progress inventory actions are stopped.

Return Code Values

0 (success) or 1 (failure)

Result Sets

xp_sqlinventory returns this message (values may vary):

Server: Msg 2, Level 1, State 1

[Microsoft][ODBC SQL Server Driver][SQL Server]SQL Server Inventory Running. SQL Server Information Is Being Captured By '' To Database 'master' Table 'xp_sqlinv' Every 2 Seconds.

When stop is issued, xp_sqlinventory returns this message (values may vary):

Server: Msg 2, Level 1, State 1

[Microsoft][ODBC SQL Server Driver][SQL Server]SQL Server Inventory Stopped. SQL Server Information Captured By '' In Database 'master' Table 'xp_sqlinv' Interval 2 Seconds.

Remarks

Before executing xp_sqlinventory, start SQL Server.

xp_sqlinventory captures SQL Server configuration and inventory information for all SQL Servers on the domain and stores the information in the specified database and table. Any SQL Server running the LocalSystem account is not 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 xp_sqlinventory to work, you must configure SQL Server to run in a valid domain user account through the Startup dialog box located in Services on Control Panel.

Inventory errors are written to the Windows NT application log.

Note xp_sqlinventory does not support named parameters.

This inventory information is captured for each inventoried SQL Server.

Examples

This example checks every two seconds for SQL Server inventory messages. The information is loaded into the xp_sqlinv table in the master database.

USE pubs

GO

DECLARE @db varchar(30), @table varchar(30), @interval int

SET @db = 'master'

SET @table = 'xp_sqlinv'

SET @interval = 2

EXEC master..xp_sqlinventory @db, @table, @interval

-- Time passes...

EXEC master..xp_sqlinventory stop

See Also