Export Change Log via SQL Query by Date Range

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (15)

Answers (3)

Verified answer

aavio 895 on at

Like (2)

Report
please try to use the below script to export data

declare @outputDatCommand varchar(1000) select @outputDatCommand = 'bcp " select * from <DatabaseName>.[dbo].<TabelName> where [Date and Time] >= DATEADD(day,-7, GETDATE())" queryout <Path\FileName>'++'-'+convert(char(8), GETDATE(), 112)+'.csv -c -t, -T -S'+ @@servername
EXEC master..xp_cmdshell @outputDatCommand

you can run this script in SQL job to automate the extraction.

Was this reply helpful? Yes No
Miguel Llorca Gómez 7,019 User Group Leader on at

Like (0)

Report

Do you also want to delete the exported records from the table??

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you for that. I will give this a try at my earliest opportunity!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yes, after I have the data exported, I would like it to be deleted from the table.

Was this reply helpful? Yes No
keoma 32,729 on at

Like (0)

Report

maybe also interesting for you

solutioncenter.apexsql.com/reading-sql-server-change-tracking-results

msdn.microsoft.com/.../bb933994(v=sql.120).aspx

sqlmag.com/.../tracking-changes-sql-server-2012

www.sqlshack.com/reading-sql-server-transaction-log

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
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

Was this reply helpful? Yes No
Suggested answer

Suresh Kulla 50,243 Super User 2025 Season 2 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Tharanga Chandrasekara 23,118 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
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

Was this reply helpful? Yes No
Verified answer

Suresh Kulla 50,243 Super User 2025 Season 2 on at

Like (2)

Report

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.

Was this reply helpful? Yes No