You’re offline. This is a read only version of the page.
115
I'm using GP 2010 in a classroom and need to clean old transactions out of the DB - any easy way to do this?
*This post is locked for comments
Wanted to thank everyone - good ideas. Just so everyone knows we did try doing a backup after setting up everything they way we wanted it prior to the semester starting with the idea we would then restore at the end of the semester. Even though we tested the process prior to doing it, the restore failed and that is why we're looking at getting a clean sandbox.
Hi all,
The next note number for Record notes (not window notes) is stored in the SY01500 table of the DYNAMICS database in the NOTEINDX column. Yep, the system dtabase, not the company database. As noted earlier, if you back up and restore one company over the other, the SY01500 table does not change so the note number is not advanced. Note indexes get very cross-linked when that kind of thing happens.
Instead of the NoteFix.sql script, I would advise getting a copy of the free tool from Microsoft which is the Note Fix utility. It's a dexterity tool with a complete user interface. It's very clever too. The tool allows several options for setting, filling in, and finding cross-linked notes. You can even select which record should keep a note when it seeming belongs to two different records. Be very sure to get the correct version of the utility. There is a separate copy of the tool for each version.
To my knowledge the INTERID column will not mess up the note indexes, but it will cause other problems. The system thinks you're trying to post intercomany transactions:)
I hope this helps.
Leslie
Hi all,
I am probably thinking too simplistically here (or completely missing the point), but let me give it a shot. This sounds like the question many people have about ‘starting over’ or copying all of the settings and master files from an existing or test company to a new company.
A.) If you use the PSTL tool (GP2010) to copy a company, you can select what you want to copy. The choices are:
Overall options:
ü Data
ü Report Options
Specific options:
ü All modules
ü Company
ü Financial
ü Inventory
ü Fixed Assets
ü Multicurrency
ü Payroll
ü Payables
ü Receivables
ü Sales Order Processing
ü Purchase Order Processing
ü Advanced Financial Analysis
ü Extended Pricing
ü Invoicing
ü Human Resources
ü Manufacturing
ü Bill of Materials
ü Project Accounting
B.) Prior to GP 2010, you have to download the Copy Company Automated Solution.
C.) You could use the Rapid tools (32 bit only) to export the configuration and master files from the old company to Excel templates that you can then use to import into the fresh company.
D.) To avoid the user/company SQL security problem, deny access to the database you are working with before you back up the company. When you do a forced restore of the company, the users will not come over. You can hook them back up later. Be sure to export your security files so that you can import them. Use the Support Debugging tool to make the security import/export seamless. Use the macro language and mail merge if you have a quantity of users. I also use mail merge to create giant SQL statements.
E.) If you are using Fabrikam, follow my colleague’s recommendation of reloading the company data.
F.) Above all, when you have created the clean ‘start over’ company, make a complete backup of your environment so that you can reuse it next year :}
Does any of this resemble what you are trying to accomplish?
Kind regards,
Leslie
You are going to have problems with user ID's because if you restore a database the users associated with that database will be the ones to come back after the restore. So now you will have SQL users who are no longer associated with the company database. When you say transactions are we talking AR, SOP, AP or POP. I have never done this but I suppose you could use the capture logins script, restore the database and then your users will come back to the database. At this point in time I think it is best to do a fresh install of GP. Drop the DYNAMICS and company databases, go into SQL and drop all the users and start over. In the future I would get all your users defined and then do a backup before you start entering any transactions. This way when I new semester starts you simply roll in the backup. Now what you could also do is use generic user ID's like LESSON USER1 and LESSON USER2. This way the restore will be all you need to do. Otherwise, you will always need to clean up the user list per database.
I'd use Rubal's method. You'll have a clean database with no historical transactions mucking things up. I've done it many times for clients who want a clean sandbox to play in.
I'm assuming that you are using the Fabricam Company. If that is the case you can just reload the company by launching Great Plain Utilities and then using the drop down to reload the test company. You would have to be logged in as sa. Or if this is a company that you keep going in using for training, if you use the clear data but loose more then you expected, Load what you needed to have in there, then do a backup prior to teaching, then you can always restore this data to the company with this backup. (Go to Microsoft Dynamics GP>>Maintenance>>Backup or Restore and use help for more information). You would need to be sa again to use this feature.
We've had issues loading or at least copying the lesson company. The last take was we created a back up and thought we could restore it with IDs and no student transactions but the restore didn't work. So now I'm looking at cleaning out last semester's transactions
Do you really want to clean out transactions or simply reload the lesson company? You can reload using GP Utilities.
Hi,
You can use Clear Data window (Microsoft Dynamics GP menu >> Maintenance >> Clear Data) to clear the data. For which module you want to clear the data? Clear data does not ensure integrity of transaction. Therefore, you will need to make sure that you clear data from all relevant tables. You may need to run the check links and reconcile for updating the tables depended on these tables. Make sure you have good backup before clearing the data.
Rubal,
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,134
Super User 2024 Season 2
#2
Martin Dráb
229,928
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (