APPLICATION'S LOG

Yesterday a had an idea to create an application log . This implies both the source code and database monitoring . If recording any user action throw the application interface is not a hard thing to do , i had no idea how to create the tool that records everything "that moves" in the database . This tool already exists (profiler and database_log ) i don't know(for the moment ) how to link the user action in the application and the log created by sql server so , i thought i can write some scripts in order to record every action that occurs in the database. I started digging the help hopping that i will find the same objects that the profiler uses , and I’ve found them. There are in fact a set of system function and procedures and using them i thought "this should do the trick". But the problem occurs with the first procedure that I’ve tried to do some testing with. It is called "sp_trace_create" and you can find it in the master database at extended system procedures. The first param that this procedure uses is the trace_id (there are other params but they are not important in this case). When i tried to execute the procedure with my trace_id i get the Error : "Formal parameter '@traceid' was defined as OUTPUT but the actual parameter not declared OUTPUT." This is a bug in the procedure source code , and is very easy to solve this ...all i had to do was to add at the parameter declaration the "output" string (declare @traceid integer output) . The Sql Server lets you to modify the system stored procedures but this is an extended system procedure and in this case access is denided . There are two types of extended system procedures in the master database , one compiled as .dll files on the disk and the other one is [server internal] . On the .dll files you can start an reverse engineering process to obtain the code in order to modify and compile it again .This is not an easy thing to do and is not recommended but it is not impossible . Bad luck occurs or shall i say "tzeapa" , because this procedure is a server internal type and i need the source code of sql server 2000 in order to solve this . I will try to lookup for a patch on the net for this problem .