Show SQL-Server-Log in a view

JonnyD
JonnyD Member Posts: 18
edited 2007-09-11 in SQL General
Hello everybody!

I have to implement a table in NAV 4.0 SP3 that shows the current SQL-Server-2005-Log. As far as I know it's possible to implement it as a Table with the property "Linked Objekt = yes" and give the table the same name of the SQL-View that "keeps" the data.

So this is a question for all you T-SQL-experts: Can anybody give me the T-SQL-source code of a view that shows me the content of the sql-server-log which can be shown in the "SQL Server Management Studio" under
SQL-Server --> Management --> SQL-Server Logs?

Unfortunately I'm absolutely not familiar with the syntax of T-SQL and all those things that are possible with T-SQL so I hope one of you can help me.

Thank you in advance!

Jonny

Comments

  • stryk
    stryk Member Posts: 645
    Hmmm ... I'm not if this could be done at all ... the "SQL Server Log" is actually a text-file, not a table, usually saved in ...\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\
    So, IMO, why don't you directly import it into a real NAV table?
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • ara3n
    ara3n Member Posts: 9,258
    Hello you can use the following code to return the sql log

    EXEC master.dbo.xp_readerrorlog



    In order to run this from navision you have use ADO and connect to the sql server. run the comand which will return the record set. Store it in temporary table in navision and the display it on the form. Put your code on open form trigger so that it runs the sql statement and your form gets the latest info.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • colin_leversuch-roberts
    colin_leversuch-roberts Member Posts: 113
    I'm just interested, but why would you want to do this?
  • JonnyD
    JonnyD Member Posts: 18
    We want to certify one of our products and one of the criteria is that every loggin that was not successful must be logged within the application. So it's necessary to somehow get all the stuff of the SQL server into NAV.
  • Waldo
    Waldo Member Posts: 3,412
    So you want this as an easy way to log the failures of your application. I don't think anyone would like to search SQL logs to look for certain functionality failures ... :-k

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • colin_leversuch-roberts
    colin_leversuch-roberts Member Posts: 113
    I'm not sure there's anything which will read the logs in this way, I'll perhaps have a look - the T-SQL that reads the log reads all the log, there's no ability to be selective. I'd have thought such an audit log should be independent of the application - if you store the info in the application you technically run the risk of thr data being modified.
    An interesting issue - you may need a third party tool to read the log, Baretail springs to mind, prior to putting into sql server.

Categories