|
|
|
Hall of Fame
Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 3,397,
Visits: 2,722
|
|
Just wanted to say thanks for sharing. The article was well thought out and well written. The level of detail in your object descriptions was great. I think this will help alot of folks. Again, thanks and good job :).
John Rowan
======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
|
|
|
|
|
Mr or Mrs. 500
Group: General Forum Members
Last Login: Monday, March 15, 2010 4:21 PM
Points: 537,
Visits: 204
|
|
John Rowan (6/12/2008)
Just wanted to say thanks for sharing. The article was well thought out and well written. The level of detail in your object descriptions was great. I think this will help alot of folks. Again, thanks and good job :).
thanks a ton for the nice words. I hope it does help.
Not to try to draw traffic away from SSC, but i have another site for auditing & compliance. Its just starting up, and points back here for all articles ive published on the topic.
For all those out there that want more specific help with this topic and surrounding issues, go and visit the SQL PASS Special Interest website for Auditing & Compliance.
I started this SIG last year, and am trying to get a group of people that can help each other out on this topic.
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, July 28, 2008 9:00 AM
Points: 4,
Visits: 15
|
|
Excelent article !
Almost the system we have implemented recently with the difference that instead of linked servers the system exports the changes of the "Audit" database (which is installed locally on each monitored server) to text files and these are uploaded to a server, where it's reviewed.
Anyway, that's the way a database should monitored. I think it's great you have decided to share your implementation with the rest.
Thank you, sir ! :)
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Sunday, January 31, 2010 11:10 PM
Points: 102,
Visits: 376
|
|
hi tjay excellent article !!!
i was jsut trying to browse thsi site and it give an error.
http://sigs.sqlpass.org/SIGs/AuditingCompliance/tabid/135/Default.aspx
and also i could not find the code for the jobs - Audit -Database changes / cleeint and Ausit Process files cleint.
Please can you tell how do you start the procedue in a job.
thanks for your help.
|
|
|
|
|
Mr or Mrs. 500
Group: General Forum Members
Last Login: Monday, March 15, 2010 4:21 PM
Points: 537,
Visits: 204
|
|
sharon (11/24/2008)
hi tjay excellent article !!!
i was jsut trying to browse thsi site and it give an error.
http://sigs.sqlpass.org/SIGs/AuditingCompliance/tabid/135/Default.aspx
and also i could not find the code for the jobs - Audit -Database changes / cleeint and Ausit Process files cleint.
Please can you tell how do you start the procedue in a job.
thanks for your help.
The PASS website http://www.sqlpass.org has changed recently, and the link to the SIGs has been altered as well. It is now http://www.sqlpass.org/Community/SIGs.aspx
In a job, you can select a variety of execution options, one of which is sql server. any viable sql statement can go here, and be executed. If you simply wanted to select 1, that would work, since its a valid sql statement. Even invalid ones that error can be put in a job, though im not sure of the reason one would want to do that.
I would suggest playing with it, and you can see how you want to implement the proc calls from a job yourself. Its fun to play and see the results of job executions, i think.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Sunday, January 31, 2010 11:10 PM
Points: 102,
Visits: 376
|
|
| hi tjay thanks a million for your help.
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: 2 days ago @ 12:20 AM
Points: 482,
Visits: 444
|
|
Apparently, there are missing SPs in your posted code, one of them was spGetDIRExists. Can you please post them too?
Thanks in advance.
:)
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, February 03, 2010 5:23 AM
Points: 41,
Visits: 108
|
|
Hi,
wanted to implement your solution , but I have a lot problemes.
Seem that I must to create directory and jobs , but I don't know what kind of these object I must create.
Your help for deploying is needed.
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, February 03, 2010 5:23 AM
Points: 41,
Visits: 108
|
|
Hi,
I really want to know how many jobs i must create et what SP i'll put insite.
I must remediate some sox control and I want to implement your solution.
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, February 03, 2010 5:23 AM
Points: 41,
Visits: 108
|
|
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
declare @DirectoryName varchar(255) ,
@DirExists int
set @DirectoryName = 'E:\BACKUPFILES'
exec spGetDirExists @DirectoryName, @DirExists output
print @DirExists
*/
ALTER procedure spGetDirExists
@DirectoryName varchar(250) = null,
@DirExists bit output
as
set NoCount on
declare
@result int,
@ErrorMsg varchar(500)
--try
--Create temporary table for FileExists
create table #fileexists
(
doesexist smallint,
fileindir smallint,
direxist smallint
)
-- Insert into the temporary table
--print @DirectoryName
Insert into #fileexists
exec master.dbo.xp_fileexist @DirectoryName
if @@Error <> 0
begin
set @ErrorMsg = 'An error occurred while calling xp_fileexist, to find a file'
Goto ErrorProc
end
select
@DirExists = direxist*fileindir
from #fileexists FE
drop table #fileexists
--finally
SuccessProc:
return 0 /* success */
--except
ErrorProc:
if @ErrorMsg is not null and @ErrorMsg <> ''
RAISERROR ( @ErrorMsg, 16, 1)
return 1 /* failure */
--end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|
|
|
|
