Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Export Change Log via ...
Microsoft Dynamics NAV (Archived)

Export Change Log via SQL Query by Date Range

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

I'm running NAV 2009 r2 on SQL Server Standard version 2008r2

 I hoping to find a method, by which I can automate an SQL Query in Management Studio, to export a date range in Change Log.  For example, every Sunday morning, export back to the previous Sunday morning.  I would like for it to export to a separate file.  I don't care if it is CSV, TXT, or really any standard file format.  I don't care if it is something that would automatically run every day, week.  I think monthly would be too large.  Just dump it to a UNC path and save it.  Afterwards, I could go back and manually delete those entries from NAV using the delete report.  Optimally, I'd like for both of these to be automated, but the export is what seems to take me the longest, as I have been just copy/pasting into excel.

Right now my Change Log has about a million lines, and that is for about 2 months.  At this point, I am looking to keep the same level of logging.  I am not an SQL expert.  I can usually edit a query fairly well.

Any suggestions would be appreciated.

*This post is locked for comments

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Export Change Log via SQL Query by Date Range

    Glad to see that you got a solution, but I'd suggest you review what you're logging and why it's generating so many entries.  If you're going to delete them anyway, why log them?  My company is on a 180 user system and we're generating less entries than yours on a month to month basis.  We log customer, vendor, setup tables, etc.  It sounds like you may be logging some transactional stuff which probably isn't necessary.

    Have fun,

    Greg

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Export Change Log via SQL Query by Date Range

    Thank you, all.

    I have implemented your suggestions as my solution.

    In SQL, I have the data being exported via the query above as a job in SQL Management Studio.  The CSV file is saved to a secure network storage directory.

    I have a report in NAV that allows me to delete by any criteria, but I'll be doing it by date.  I have to run it manually, but it only takes a few seconds.  SQL will send me an alert after each run by email, and this will remind me to delete the older data. I'll look into automating it, but I'd prefer not to have another NAS running for this.

    Much thanks, gentlemen!

    Matthew

  • Verified answer
    TharangaC Profile Picture
    TharangaC 23,116 on at
    RE: Export Change Log via SQL Query by Date Range

    In simple word : If you delete it via NAV then NAV will do the cleanup job for you but If you use SQL then you will have to do the cleanup job for NAV.

  • Suresh Kulla Profile Picture
    Suresh Kulla 43,745 on at
    RE: Export Change Log via SQL Query by Date Range

    Glad to be of help.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Export Change Log via SQL Query by Date Range

    Perfect explanation, Suresh.  Thank you for taking the time to clarify that for me.

  • Verified answer
    Suresh Kulla Profile Picture
    Suresh Kulla 43,745 on at
    RE: Export Change Log via SQL Query by Date Range

    Matthew,

    If you delete the data or insert or modify using SQL query it does not execute any business logic which is written in Navision and which is why it will cause inconsistency and that is the reason it is not recommended to do any updates outside NAV.

    For example:

    If you delete an item from Navision, it will also delete the data from alteast 10 or 15 tables with that item related data but if you do a query and delete an item from SQL it will delete the data only from that table.

    I hope this explains, let me know if you have any further questions.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Export Change Log via SQL Query by Date Range

    I very much appreciate the offering of suggestions to protect my data.  Thank you!  I'm curious if the worry is really warranted.

    Whereas:

    • my databases are backed daily, and replicated offsite nightly,
    • I would be deleting historical data which, in this same script, or a separate one run just before, I just made another copy of this data
    • the data is only a log of changes that have been made by users in NAV (EX. Jane in sales made a change to the shipping date for order number 123987 on March 5th)

    Could you help me understand the real risk of a query deleting this kind of data based on date?  The date format will never change.  So I am not sure I get why this approach is not as safe as doing it via NAS or some other programmatic method.

    Please know that I am not doubting your information; I'm trying to understand.  I was hoping to have a simple method, using the tools I have, and not adding any complexity to my NAV environment.

    Thank you!

    Matthew

  • Suggested answer
    TharangaC Profile Picture
    TharangaC 23,116 on at
    RE: Export Change Log via SQL Query by Date Range

    As suresh mentioned it is not recommended to delete data in the NAV database. If you required to delete the data then you should do it using C/AL programming since if there is a logical error NAV will point it for you.

    If you do it using SQL, then NAV will not be able to help you and will have issues in the long run.

  • Suggested answer
    Suresh Kulla Profile Picture
    Suresh Kulla 43,745 on at
    RE: Export Change Log via SQL Query by Date Range

    I think if you have a process in SQL then the same query should delete the data but i highly recommend to write a process in NAV to delete the data instead of deleting it from SQL.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Export Change Log via SQL Query by Date Range

    It took a bit before I could get back to the project (wearing too many hats these days).

    I had to tweak it for the server name.  It gave me: "Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [53]."

    When I omitted the @@servername, I got my csv file!

    Here's the code I am using:

    declare @outputDatCommand varchar(1000)
 
select @outputDatCommand = 'bcp " select * from [MyDataBase].[dbo].[MyTable] where [Date and Time] >= DATEADD(day,-7, GETDATE())" queryout D:\BU\ChngeLog'++'-'+convert(char(8), GETDATE(), 112)+'.csv -c -t, -T -S'

EXEC master..xp_cmdshell @outputDatCommand


    Now I just need to get the query to delete the data from the table after it exports it... hopefully in the same query.

    Any suggestions to that end would be appreciated

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans