Database

4 May '12 - 17:38

Hi, `How can i read SQL Database 2008 LDF (Log) file programmatically (with T-SQL OR C# ).` Its very Important for me. Plz Help
Sign In·Permalink

4 May '12 - 23:11

The format isn't publicly available. Why do you need them? Bastard Programmer from Hell
Sign In·Permalink

4 May '12 - 23:19

I wanna to check the action placed on the database at different times and dates.(when insert occured in tblName or what palce for data in Update Statement OR ...)
Sign In·Permalink

4 May '12 - 23:31

If you want to read the log file, you'll have to contact Microsoft for a license. What you want could be easily achieved using a trace, logging it's results. To a textfile or some other database. Google for "Sql Trace" and you should find some interesting results, even from CodeProject Bastard Programmer from Hell
Sign In·Permalink

5 May '12 - 20:31

thanks for solution. but it wont give me that `when and what` for example insert/Delete/Update/Select in/from tblName. I wanna something log like this.
Sign In·Permalink

Mycroft Holmes

5 May '12 - 23:00

To do this you need to implement an AUDITING strategy, this generally involves triggers (spit) and an audit table. Do some research on database Auditing. Never underestimate the power of human stupidity RAH
Sign In·Permalink

6 May '12 - 6:08

Mycroft Holmes wrote: this generally involves triggers Cool suggestion Bastard Programmer from Hell
Sign In·Permalink

6 May '12 - 0:37

jojoba2011 wrote: but it wont give me that `when and what` for example When, what, and even who are included. Bastard Programmer from Hell
Sign In·Permalink

6 May '12 - 23:30

Really thanks for all your Solutions! but i have sqlexpress and this feature isnot active in Express 2008! What can i do then?
Sign In·Permalink

7 May '12 - 0:30

Creating a trace is also possible for Sql Express. Google Harder Bastard Programmer from Hell
Sign In·Permalink

7 May '12 - 17:37

Hi, but trace can only catch the Error not the work i wanna.
Sign In·Permalink

JohnPayton

7 May '12 - 18:10

You can't always expect a solution to your problem to be handed to you. Google is one of the best resources for finding answers to programming questions. If you don't get the answer you are looking for, restructure your Google key words until you get an answer that will work for you. Basically it's the best way to learn anyway, by doing it yourself.
Sign In·Permalink

8 May '12 - 0:17

~~You're right.~~ A trace logs all actions. I'm not sure why you keep insisting that it won't work. Bastard Programmer from Hell
Sign In·Permalink

9 May '12 - 17:52

thanks for Solution! is it posssible for you to give me small `Example` on how to use trace for all action.
Sign In·Permalink

10 May '12 - 0:07

There are quite some examples on the internet; some are based on Sql-statements, others on SMO. It's your turn to do some research Bastard Programmer from Hell
Sign In·Permalink

11 May '12 - 17:28

I know that i should do the rest of work but i am little week in Googling.I didnt find any result that can give me the way.plz give me the link or Exmaple. Plz thanks
Sign In·Permalink

Re: Read LFD(log ) file SQLserver 2008 [modified]

12 May '12 - 0:45

Two options; Using code[^], and using SQL[^]. Bastard Programmer from Hell
Sign In·Permalink

12 May '12 - 22:31

Thanks for reply!
I have create App. and trace everything in SQL but I cannot trace SP and ... that will call from C#.
I mean when i call SP in C# it doesnt trace.



//را یکبار اجرا نمایید SP این trace ابتدا باید برای فعال کردن

           declare @TraceId int

           -- create the trace

           exec sp_trace_create @TraceId output, 0, N'C:\Temp\OurDemoTrace'

           -- set event and columns to trace

           exec sp_trace_setevent @TraceId, 12, 1, 1

           exec sp_trace_setevent @TraceId, 12, 6, 1

           exec sp_trace_setevent @TraceId, 13, 1, 1

           exec sp_trace_setevent @TraceId, 13, 6, 1

           -- start the trace

           exec sp_trace_setstatus @TraceId, 1

           -- check status for our trace

           select value from fn_trace_getinfo(@TraceId) where property in (2, 5)

           go

<pre lang="c#">sb.AppendLine("\nEventClass: " + ts.GetString(ts.GetOrdinal("EventClass")));
                            sb.AppendLine("NTUserName: " + ts.GetString(ts.GetOrdinal("NTUserName")));
                            sb.AppendLine("Command : " + ts.GetString(ts.GetOrdinal("TextData")));

modified 13 May '12 - 4:56.

Re: Read LFD(log ) file SQLserver 2008 [modified]

13 May '12 - 4:08

jojoba2011 wrote: and trace everything in SQL but I cannot trace SP and ... that will call from C#. I mean when i call SP in C# it doesnt trace. Not quite everything; stored procedures have their own trave_event, specifically SP:StmtStarting (44) and SP:StmtCompleted (45). A complete list of events can be found here[^]. Bastard Programmer from Hell
Sign In·Permalink

13 May '12 - 19:22

thanks a lot Eddy Vluggen.
Your really an export in SQL.

I have use this for SP:

 declare @TraceId int
            -- create the trace
            exec sp_trace_create @TraceId output, 0, N'C:\Temp\SQLLog'
            -- set event and columns to trace
            exec sp_trace_setevent @TraceId, 12, 1, 1
            exec sp_trace_setevent @TraceId, 12, 6, 1
            exec sp_trace_setevent @TraceId, 13, 1, 1
            exec sp_trace_setevent @TraceId, 13, 6, 1
            exec sp_trace_setevent @TraceId, 12, 13, 1
             exec sp_trace_setevent @TraceId, 12, 10, 1
            exec sp_trace_setevent @TraceId, 12, 13, 1
            exec sp_trace_setevent @TraceId, 12, 14, 1
            exec sp_trace_setevent @TraceId, 12, 15, 1
             exec sp_trace_setevent @TraceId, 13, 10, 1
            exec sp_trace_setevent @TraceId, 13, 13, 1
            exec sp_trace_setevent @TraceId, 13, 14, 1
            exec sp_trace_setevent @TraceId, 13, 15, 1
            exec sp_trace_setevent @TraceId, 12, 35, 1
            exec sp_trace_setevent @TraceId, 13, 35, 1
            exec sp_trace_setevent @TraceId, 44, 10, 1
            exec sp_trace_setevent @TraceId, 44, 13, 1
            exec sp_trace_setevent @TraceId, 44, 14, 1
            exec sp_trace_setevent @TraceId, 44, 15, 1
             exec sp_trace_setevent @TraceId, 44, 1, 1
            exec sp_trace_setevent @TraceId, 44, 6, 1
              exec sp_trace_setevent @TraceId, 44, 35, 1
            exec sp_trace_setevent @TraceId, 45, 35, 1
           exec sp_trace_setevent @TraceId, 45, 10, 1
            exec sp_trace_setevent @TraceId, 45, 13, 1
            exec sp_trace_setevent @TraceId, 45, 14, 1
            exec sp_trace_setevent @TraceId, 45, 15, 1
             exec sp_trace_setevent @TraceId, 45, 1, 1
            exec sp_trace_setevent @TraceId, 45, 6, 1
            
            -- start the trace
            exec sp_trace_setstatus @TraceId, 1
            -- check status for our trace
            select value from fn_trace_getinfo(@TraceId) where property in (2, 5)
            go

and in C#

sb = new StringBuilder();
                            sb.AppendLine("\nEventClass: " + ts.GetString(ts.GetOrdinal("EventClass")));
                            sb.AppendLine("NTUserName: " + ts.GetString(ts.GetOrdinal("NTUserName")));
                            sb.AppendLine("Command : " + ts.GetString(ts.GetOrdinal("TextData")));

I have done about 90 percent:

but when i run any SP in SQL Management it returns exactly the SP witch have exec in LOG like bellow :

INSERT INTO tblGroups
                      (GroupName, GroupFatherId)
VALUES     ('Friends',1)

DECLARE	@return_value int
 
EXEC	@return_value = [dbo].[InsertGroups]
		@GroupName = N'ali joon',
		@GroupFatherId = 1
 
SELECT	'Return Value' = @return_value

But when i run SP in C# it only return the SP Code.like bellow:

INSERT INTO tblGroups
                      (GroupName, GroupFatherId)
VALUES     (@GroupName,@GroupFatherId)

But i wanna to have this also :

INSERT INTO tblGroups
                      (GroupName, GroupFatherId)
VALUES     ('Friends',1)

DECLARE	@return_value int
 
EXEC	@return_value = [dbo].[InsertGroups]
		@GroupName = N'ali joon',
		@GroupFatherId = 1
 
SELECT	'Return Value' = @return_value

modified 14 May '12 - 3:51.

15 May '12 - 5:11

No ans for my Question!
Sign In·Permalink

16 May '12 - 2:06

jojoba2011 wrote: No ans for my Question You do realize that we're volunteers here? I know that Sql-profiler can see the parameters, I assume that they're also there for a trace. I don't have an answer ready for this question. Bastard Programmer from Hell
Sign In·Permalink

JohnPayton

22 May '12 - 19:18

Here have a 5 for patience and being... really an export in SQL
Sign In·Permalink