Copying PD Data to PY

[ice_cube210]

Hi List,

I am using SQL Server 2000 as my database , I needed to copy my current production data to my CRP data. Whats the best way to do this. JDE has some report R98403 to Copy Business Data from Data Source to another. I tried this once but it dint really give me any results. has any one used this

In SQL server I can restore the last backup of my PD data to overwrite my PY data...but it keeps the owners of the objects as those of PD. ie. my tables owners will be PRODDTA and PRODCTL instead of CRPDTA and CRPCTL.

Will this cause any problems....my users security records use the JDE user..so i guess..they shud still be able to access the data fine..?

How can i change this in SQL....any shortcuts..or will i have to change it for each table one at a time

Any suggestions..thanks in advance

 

[WRaunecker]

Hello Ice_cube,
it won't work with the "wrong" owners.
I always do a Backup - Restore - Change Owner - procedure.

First find UID:
select 'prodctl', uid from sysusers where name = 'PRODCTL'
select 'proddta', uid from sysusers where name = 'PRODDTA'
select 'crpctl', uid from sysusers where name = 'CRPCTL'
select 'crpdta', uid from sysusers where name = 'CRPDTA'

Then do the update:
sp_configure 'allow updates',1
reconfigure with override
go
Update Sysobjects set uid =<uidcrpdta> where uid = <uidproddta>
Update Sysobjects set uid = <uidcrpctl> where uid = <uidprodctl>
go

sp_configure 'allow updates', 0
reconfigure with override
go

That's all

Regards

 

[jdesmm]

I had a script that originally updated sysobjects as well. I ran into problems with that down the line and had to contact Microsoft. Here is an excerpt from the issue I had:

PROBLEM DESCRIPTION:
===========================

You have a database in which you cannot revoke permissions from the public role. The tables are not owned by DBO and the permissions were granted by a user that no longer has sufficient permissions to grant anything.

STEPS TAKEN:
============

We determined that the direct update to sysobjects that is part of your "refresh" from production to test has caused some problems. The supported way to change object ownership is with the system stored procedure sp_changeobjectowner. This stored procedure has some checks in it as well as a delete from syspermissions for at least some object ownership changes.

RESOLUTION:
===========
Use sp_changeobjectowner to change permissions on objects.

I followed Microsoft's advice & have had no further problems. Just an FYI.

 

[jpalkhivala]

Hi,
You can use R98403 to copy business data between environments. Copy version XJDE0021 "Refresh Prototype Data from Production". Look at processing option #6 - 0 is for proof mode, 1 will write to the source database - 0 is default so you will want to change it. Run the version locally.
Hope this helps,
Jim

 

[shearerj]

There are more tables involved than SYSOBJECTS.

If you had any security over the tables in the database, then you need to modify the syspermssissions to reflect the correct 'grantor'.

etc....

 

[dschlieder]

I use a VB script that lists all of the tables in the production database by owner, counts the records, orders by the most # of records, then truncates the table in PY and does a insert into select * from statement if there are records to insert.

I have about 12 inserts running at one time. I have almost 40 gig now and it takes just over 1 1/2 hours the last time we did a refresh.

Of course the server, cpu, memory and hard drives you have would all make a difference.

 

[shearerj]

Along those some lines, you can execute the following:

sp_msforeachtable "truncate table jde_crp.?"
GO
sp_msforeachtable "insert into jde_crp.? select * from jde_production.?"
GO

It does not have the advantage of dschlieder multi-threaded method, but is simpler.