PD Business Data Copy to PY

Hello all,

We are running E1 8.0 on Windows with SQL Server 2000. Basic JDE installation. We restored our JDE_CRP database (business data and control tables) using a backup of our JDE_PRODUCTION database. We used the SQL Server restore tools. All went well.

Using scripts, I changed the table owners using the following script:
EXEC SP_CHANGEOBJECTOWNER 'JDE_CRP.PRODDTA.F554211U','CRPDTA'

and granted permissions to the public role of which jde is a member:
GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [JDE_CRP].[CRPDTA].[F554211U] TO [public]

I also changed the logical file name on the database to JDE_CRP_data as it was before.

I can log in to PY and view data without issue. However, when I try to update data I get this error in jde.log: "[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'PRODDTA.F0101'. - SQLSTATE: S0002".

jdedebug.log has no reference to an error or to PROD. All data sources show it hitting CRP. Is there something else that needs to be modified?

One interesting note. I can update data in the F4201 table. That is the only table that seems to work.

I appreciate the help,

Jer

Are your CRP and PROD databases on different servers?

Ken,

No. They are on the same server.

I am wondering if I need to take the CRP database offline and back online at some point in the process.

lso, interesting test: I get the same error in Access using the Business Data CRP ODBC and crpdta login. Somehow, the database sees these tables as owned by PRODDTA, even though Enterprise Manager shows them as CRPDTA.

Thanks,

Jer

Detaching and reattaching the database may help. Did you lock the database (single user mode) before you did the restore?

You may try and run the catalog procedure; it will refresh the tables JDE is using when accessing the database.

[ QUOTE ]


Using scripts, I changed the table owners using the following script:
EXEC SP_CHANGEOBJECTOWNER 'JDE_CRP.PRODDTA.F554211U','CRPDTA'



[/ QUOTE ]

Did you change table owners of all tables or just F554211U? Or are you just using that as an example?

[ QUOTE ]


and granted permissions to the public role of which jde is a member:
GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [JDE_CRP].[CRPDTA].[F554211U] TO [public]



[/ QUOTE ]

Same question above- all tables or just F554211U?

All,

Thanks for the responses. I should have done a better job explaining my situation. I have CNC experience, but have been strictly on the business side of JDE for the past 7 years. The CNC/DBA here quit at the end of the year, so I am working on my rusty knowledge and some brief knowledge transfer from him.

Ken,

No, I did not put the database in single user mode. Our shop is very small and we have no testing/developing/use of PY outside of IT currently. I did validate that we had no processes/user connections to JDE_CRP before starting.
My understanding was that the restore job in EM would also fail to start if there were connections.

Do you (or anyone) know for sure that I can take JDE_CRP offline without affecting the JDE_PRODUCTION database which is on the same server/instance? I would like to do that, but haven't found definitive proof that it will only affect CRP. I have talked to our former DBA who is not confident about it, but he never tried it.

Adrian,

I have not heard of a catalog procedure. How do I do that on Windows/SQL Server installation?

Jeff,

The scripts in the thread are just examples. I changed all of the "CTL" and "DTA" tables and views in the database.

Thanks all,

Jer

Jer,

I do this all of the time and have it all scheduled out through stored procedures.

Check out the attachment - I have posted this in the past but may have made mods since then.

All of these stored procs are in JDE_PRODUCTION.

There is the first basic stored procedure that restores the last full production backup to the CRP database - you of course will need to modify it for your installation - the drives and paths to your CRP data base, etc.

It is set to only run from the production data bases.

The subsequent procs run from the restored databases - they take care of changing the owner, setting any extra grants, fixing up any SQL triggers and any custom views.

You would of course need to evaluate the need for any of these and possibly create new ones for your particular situation.

And some will need to be run multiple times - custom views for example - you may need run that proc a few times to get all of the links to all of the objects resolved - such as a view that references a JDE table and a view that references a JDE table, etc.

But the basic restore and owner change should be a good start.

At one time I was running this every day for a big project we were doing.

Our production database is 133 gig and the restore takes around 2 hours - of course your results will vary depending on the size of the data base and the speed of your system.

Dave Schlieder

Dave,

Thanks for the script. Sadly, we are going to have to put this project on hold as the users have decided to implement a new business process and need PY to test (and me to help design and build). We have successfully put an old PY backup in place using the same process, so our issue must be with changing the owners.

Thanks all for your help. We are considering testing this in our DEV environment, so I may post back if I find something definite we missed.

This post is a little old but....

I liked your scriped. I took out the Change owner script and used it on a backup I restored as a Dev Environment. Worked great.

Question, I haven't run the SID cleanup or the trigger or view update.

For views, we don't have any at the SQL level so no need to bother. But for the orphan SID cleanup, what are you tring to fix? I am familiar with SIDs but I wouldnt' say I fully understand them. I know they can cause problems if the user accounts dont' match the SID id for them. Is that what your 'autofix'ing? For the Triggers, what's that part for?

Hey I appreiate you putting out your script, it takes confidence to post for everyone to see and it was very useful, I just wanted to better understand the SID and Trigger section before I used it.

I just wanted to post back the issue we had. Unknown to me, RFSmart, a 3rd party application for the warehouse requires non-JDE triggers on some JDE tables. These triggers were restored in PY as part of the PD backup. However, they contain table owner names of PRODDTA which was causing my problems. I found them by running a SQL Profiler. Once we removed the triggers in PY, problem solved.

Dave's script may handle this issue in the trigger section. I, too, am impressed with the script but haven't had an opportunity to use it.

Jer