November 5, 2008 at 2:11 pm
I have the task of going back to a Windows 2000 machine running SQL Server 2000 and adding auditing with server-side traces. Here is my plan of attack to make this happen:
1. Windows 2000 starts up
2. SQL Server 2000 is set to auto-start with the OS
3. There is a stored procedure which will auto-start SQL Server Agent
4. When SQL Server Agent starts it will turn on server-side tracing
The problem is that the call to set up the stored procedure to auto-start SQL Server Agent and the call to start a SQL Server Agent are both XP OS calls (using xp_servicecontrol).
Questions
1. How can I do this using W2000-speak?
2. Can I just call the server-side traces from a SQL Server 2000 auto-start stored procedure without using SQL Server Agent??
Thanks for you help!
November 5, 2008 at 2:32 pm
Hi,
You don't need to set up anything for SQL Server Agent to autostart - just set the property of the service to Autostart (in Administrative Tools/Services).
You could have a stored procedure (which you configure as a startup procedure) that calls sp_trace_setstatus (which in it's turn changes the status on the trace you have defined from Status = 0 to Status = 1).
Have you done this before so you know the steps to take to define a trace with T-SQL?
HTH!
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 5, 2008 at 2:41 pm
SQL Server Agent has to auto-start because we have to update thousands of machines where the users may have very little computer knowledge.
My second question was really a brief afterthought while writing the post. So I decided to give it a try and it worked! I can write the server-side trace as an auto-start stored procedure. We still have a bit of testing to do but it seems to be working.
Thanks for your comments!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply