April 30, 2010 at 4:48 am
Can some one give me some help on the below? I have set up database auditing and wish to run the ALTER SERVER AUDIT command to reycling the auditfile I'm using, the problem I'm having is passing a variable to it.
This is the code I'm trying to run and it errors at the alter server audit comment.
DECLARE @AuditID int
DECLARE @MaxAuditID int
SET @AuditID = (Select MIN(audit_id) FROM sys.server_audits audit)
SET @MaxAuditid = (Select MAX(audit_id) FROM sys.server_audits audit)
WHILE @AuditID <> @maxauditid +1
IF @AuditID IN (SELECT AUDIT_ID FROM sys.server_audits)
BEGIN
DECLARE @AuditName nvarchar(50)
SET @AuditName = (SELECT Name FROM sys.server_audits WHERE audit_id = @AuditID)
Select @AuditName
ALTER SERVER AUDIT @AuditName WITH (STATE = OFF)
ALTER SERVER AUDIT @AuditName WITH (STATE = ON)
SET @AuditID = @AuditID + 1
END
ELSE
SET @AuditID = @AuditID + 1
May 1, 2010 at 7:46 pm
Like table names in a SELECT statement, the audit specification name probably can't be a variable.
Try doing this with dynamic SQL instead.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 4, 2010 at 2:07 am
thanks for the reply, ended up getting result I wanted using dynamic sql.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply