A long time ago, Microsoft included a SQL script called Create_PJournal.sql, but that is no longer in the transfer scripts that you can download from the KnowledgeBase article and the KB article itself says that you only need to run the script to create the PJournal job if you are using MSDE2000. However, on all installs of GP, there is a job called Remove Posted PJOURNALs From All Companies (DYNAMCIS). So, if you are going to transfer GP to another server, how do you get the script to create that job? I have read in several places that the PJJOBS.sql script is located in the SQL\UTIL folder under the GP install folder. I only see Grant.sql in that folder. So, where do you get this script to create it? Thanks!
Hi Shirley-
I've always just scripted the job from the old server and then run the script generated on the new server. In the KB that was linked above, one of the steps is to script the SQL jobs from the old server, copy the files to the new server, and then run those scripts on the new server.
Joe
My "trick" for getting around all of this when I am rolling out a new server is to run GP Utilities BEFORE restoring databases. I let it create a new DYNAMICS dictionary (making sure to type in the Account Framework from the original server) as that includes all the SQL system-level objects (like the SQL jobs).
Once the database is created, I either rename it (if I'm going to want it for reviewing the current security object tables) or delete it if I don't need it.
Then I restore the databases.
Thank you Derek for this confirmation.
Sheryl,
while Derek is technically correct with the step #3 of the KB878449 to recreate the existing jobs from the old server to port to the new server, another option would be to perform a maiden GP setup on the new server first, and then restore the Databases (all of them) once the setup is completed.
Part of the maiden GP setup is to create the SQL Agent jobs that take care of the PJournal table, but also other jobs related to AD and WF in GP.
Capturing and recreating the jobs from the old server might sometimes lead to unwanted tasks being ported over, so you have to be very careful what you're selecting and scripting over. Typically you'll find there jobs that are created as part of some SSRS scheduled automation, and also SQL Maintenance plans, which you want to redo and/or port over as well.. not just the jobs, but the entire Maintenance plans.
I was looking at this yesterday and I actually misspoke.......
The SQL/Util/ directory will only have the Grant script in it, by default.
If you extract the dll files, which most of my GP environments have had done, that is when the PJournal and Dex_Req script get put into that directory as well.
Otherwise, you're correct, the PJournal script has been removed from the Util folder for some reason, in later versions of GP.
Thanks
Hi Derek,
I concur with Shirley, I came across multiple systems where the SQL\Util folder would only contain the grant.sql script... wondering if the other 2 scripts were somewhat lost along the road in some GP setups...
Would have to recreate several different versions of GP to check for the presence of all 3 files.
That is weird! I have 4 laptops (3 of which are older), that have GP 2018 installed on them. All of them have only the Grant.sql in that folder. "C:\Program Files (x86)\Microsoft Dynamics\GP\SQL\Util\Grant.sql"
Should still be there........looking in both my 18.4 installs, the SQL/Util/ folder has three files: dex_req, Grant and PJjob.
Derek, thank you so much for your reply. It would be very convenient to use the PJjob.sql script to create the job for deleting posted PJOURNALS. I looked back at the install folders of some of my older versions of GP. I discovered that in the SQL\Util folder for GP 2013 there were Dex_Req.sql, Grant.sql, and PJjobs.sql. In that folder for all versions later than GP2013, there was only the Grant.sql. Why would they have stopped putting those in that folder for newer versions?
If you're not, I highly recommend this article for moving GP from one server to another:
docs.microsoft.com/.../transfer-gp-installation-new-server
The answer to your question would be found in step #3 and #11.
That being said, for the PJournal job, in the GP directory, under SQL\Util\, you'll find the 'PJjob' script, that you can also use on the new server, to create this job, if it doesn't already exist. (You may need to replace %DBName% with the actual name of your GP System database, whether DYNAMICS or something else.
Thanks
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,522 Super User 2024 Season 2
Martin Dráb 228,441 Most Valuable Professional
nmaenpaa 101,148