Finance | Project Operations, Human Resources, ...

Transfer of GP data to another server - Pjournals job

(0) Share

Report

Posted on by Shirley McCutcheon

163

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!

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

Derek Albaugh on at

Like (1)

Report

RE: Transfer of GP data to another server - Pjournals job

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

Was this reply helpful? Yes No
Shirley McCutcheon 163 on at

Like (1)

Report

RE: Transfer of GP data to another server - Pjournals job

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?

Was this reply helpful? Yes No
Suggested answer

Derek Albaugh on at

Like (0)

Report

RE: Transfer of GP data to another server - Pjournals job

Should still be there........looking in both my 18.4 installs, the SQL/Util/ folder has three files: dex_req, Grant and PJjob.

Was this reply helpful? Yes No
Shirley McCutcheon 163 on at

Like (0)

Report

RE: Transfer of GP data to another server - Pjournals job

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"

Was this reply helpful? Yes No
Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

RE: Transfer of GP data to another server - Pjournals job

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.

Was this reply helpful? Yes No
Suggested answer

Derek Albaugh on at

Like (0)

Report

RE: Transfer of GP data to another server - Pjournals job

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

Was this reply helpful? Yes No
Suggested answer

Beat Bucher GP Gee... 28,058 Moderator on at

Like (1)

Report

RE: Transfer of GP data to another server - Pjournals job

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.

Was this reply helpful? Yes No
Suggested answer

Lisa at AonC.com 870 Super User 2025 Season 2 on at

Like (0)

Report

RE: Transfer of GP data to another server - Pjournals job

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.

Was this reply helpful? Yes No
Joseph Markovich 3,976 on at

Like (0)

Report

RE: Transfer of GP data to another server - Pjournals job

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

Was this reply helpful? Yes No