SQL Server Installation Path

The installation path for SQL Server 2000 can be determined using the undocumented sp_msget_setup_paths Stored Procedure.  This procedure performs a Reg Read for the particular SQL Server instance to determine the install path from HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup for the default instance or HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance\Setup for a named instance.  Below is an example of how to use sp_msget_setup_paths to determine the installation path.

 

DECLARE @sqlpath SYSNAME
DECLARE @datapath SYSNAME

EXEC master.dbo.sp_msget_setup_paths @sqlpath OUT, @datapath OUT

SELECT @sqlpath, @datapath

Audting a Table using a Trigger

Below is an example of how to use a Trigger to audit who updated, deleted or inserted data.  The trigger also captures the old and new values when an update is performed:

CREATE TABLE dbo.tblProducts
(
ProductId INT IDENTITY(1, 1) CONSTRAINT PK_tblProducts PRIMARY KEY,
ProductName VARCHAR(20)
)
GO

CREATE TABLE dbo.tblProductsHISTORY
(
ProductId INT,
OldProductName VARCHAR(20),
NewProductName VARCHAR(20),
DateUpdated DATETIME,
UpdatedBy NVARCHAR(256),
Statement CHAR(1)
)
GO

CREATE TRIGGER dbo.tr_tblProducts ON dbo.tblProducts
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON

IF (SELECT COUNT(*) FROM inserted) > 0
BEGIN
IF (SELECT COUNT(*) FROM deleted) > 0
BEGIN
— update
INSERT INTO tblProductsHISTORY
SELECT    t1.productid, t1.productname, t2.productname, GETDATE(), SYSTEM_USER, ‘U’
FROM      deleted t1 JOIN inserted t2
ON        t1.productid = t2.productid
JOIN    tblProducts t3
ON  t1.productid = t3.productid
END
ELSE
BEGIN
— insert
INSERT INTO tblProductsHISTORY
SELECT  productid, productname, productname, GETDATE(), SYSTEM_USER, ‘I’
FROM  inserted
END
END
ELSE
BEGIN
— delete
INSERT INTO tblProductsHISTORY
SELECT  productid, productname, productname, GETDATE(), SYSTEM_USER, ‘D’
FROM  deleted
END