Developers of client applications in a 32-bit environment often use the Registry as a repository for application configuration data and defaults. The Registry is a database (but not an RDBMS) that stores configuration information centrally.
SQL Server exposes the following extended stored procedures for manipulating the Registry:
Extended Stored Procedure | Purpose |
---|---|
xp_regread | Reads a Registry value |
xp_regwrite | Writes to the Registry |
xp_regdeletekey | Deletes a key |
xp_regdeletevalue | Deletes a key's value |
xp_regenumvalues | Lists names of value entries |
xp_regaddmultistring | Adds a multistring (zero-delimited string) |
xp_regremovemultistring | Removes a multistring (zero-delimited string) |
This stored procedure enables you to read the value of the Registry key located on the specified path of the specified subtree:
xp_regread subtree, path, key, @value OUTPUT
In the following example, this extended stored procedure reads the default folder for storing SQL Server database data files:
declare @chvSQLPath varchar(8000) exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE' ,'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer' ,'DefaultData',OchvSQLPath OUTPUT select @chvSQLPath SQLPath go
This stored procedure enables you to write a new value to the Registry key located on the specified path of the specified subtree:
xp_regwrite subtree, path, key, datatype, newvalue
In the following example, this extended stored procedure adds one value to the Setup key:
exec master..xp_regwrite 'HKEY_LOCAL_MACHINE' ,'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer' ,'Test' ,'REG_SZ' ,'Test' go
Tip | You should be very careful when writing and deleting Registry keys using Transact-SQL. It is often a better idea (performance-wise) to store most of your configuration parameters in a special table in the application database. |