Here is the code I execute after restoring the production company to the
test company.
This fixes the INTERID field on the most likely tables that may have
unposted transactions, thus allowing them to post in the TEST company.
Within the code, you have to make only 2 changes.
USE TEST <-- change to the test database
'<YOUR PRODUCTION DATABASE>' <-- change to the name of the production
database
Robert
/* ------------ start script ------------- */
begin tran
declare @thisID integer, @thisDB varchar(5)
declare @oldID integer, @oldDB varchar(5)
declare @anyerror integer
set @anyerror = 0
use TEST /* the <TEST database> */
select @thisID = CMPANYID, @thisDB = INTERID from DYNAMICS.dbo.SY01500 where
INTERID = db_name()
if @@ROWCOUNT <> 1 set @anyerror = @anyerror + 1 + @@ERROR
select @oldID = CMPANYID, @oldDB = INTERID from DYNAMICS.dbo.SY01500 where
INTERID = '<YOUR PRODUCTION DATABASE>'
if @@ROWCOUNT <> 1 set @anyerror = @anyerror + 1 + @@ERROR
UPDATE .dbo.SY00600 set CMPANYID = @thisID
set @anyerror = @anyerror + @@ERROR
UPDATE .dbo.SY01200 set Master_ID = @thisID where Master_Type=''CMP''
set @anyerror = @anyerror + @@ERROR
if not exists (select CMPANYID from FA49900 where CMPANYID = @thisID) UPDATE
.dbo.FA49900 set CMPANYID = @thisID
set @anyerror = @anyerror + @@ERROR
UPDATE .dbo.GL10001 set INTERID = @thisDB where INTERID = @oldDB /* leave
intercompany as is! */
set @anyerror = @anyerror + @@ERROR
UPDATE .dbo.PM10100 set INTERID = @thisDB where INTERID = @oldDB /* leave
intercompany as is! */
set @anyerror = @anyerror + @@ERROR
UPDATE .dbo.CM00100 set CMPANYID = @thisID where CMPANYID = @oldID
set @anyerror = @anyerror + @@ERROR
UPDATE .dbo.SY04800 set CMPANYID = @thisID where CMPANYID = @oldID
set @anyerror = @anyerror + @@ERROR
if @anyerror = 0
begin
commit tran
end
else begin
rollback
end
/* ------------ end script ------------- */
Post by JoeYou are awsome guys helping me like that. I did what you said but it is OK, I
think too, it is something to do with the link with the real database. One
guy gave me a script to run after a restore of a test company but even with
that I still have that error.
Regards, Joel :)
/******************************************************************************/
/* Description: */
/* Updates any table that contains a company ID or database name value */
/* with the appropriate values as they are stored in the
DYNAMICS.dbo.SY01500 table */
/* */
/******************************************************************************/
if not exists(select 1 from tempdb.dbo.sysobjects where name =
'##updatedTables')
create table [##updatedTables] ([tableName] char(100))
truncate table ##updatedTables
declare G_cursor CURSOR for
select
case
when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID
as char(3))
else
'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+''''
end
from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b,
INFORMATION_SCHEMA.TABLES c
where UPPER(a.COLUMN_NAME) in
('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME', 'COMPANYCODE_I')
and b.INTERID = db_name() and a.TABLE_NAME = c.TABLE_NAME and
c.TABLE_CATALOG = db_name() and c.TABLE_TYPE = 'BASE TABLE'
set nocount on
OPEN G_cursor
begin
insert ##updatedTables select
end
DEALLOCATE G_cursor
select [tableName] as 'Tables that were Updated' from ##updatedTables
Post by Mariano GomezThis is because your intercompany ID is incorrect for your transactions in
the PM10100.
-- run against your test company
SELECT INTERID FROM PM10100
You can compare this result against the value stored in the company master
table
SELECT * FROM DYNAMICS..SY01500
If the PM10100 values are incorrect you will need to update accordingly. If
the company master is incorrect (based on the physical database that it
should be pointing to) you will need to update the company master.
Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com
Post by JoeThanks Janakiram
The worst thing is the accounts I'm using for the transaction I'm creating
are not linked with AA. If I click on the ribbon the message is there are no
account linked...
Joel :)
Post by Janakiram M.P.Joel
Good to know that You got it back. Now, Coming back to your other
issue, this error message comes when you have Analytical Accounting
installed. Have a look at your Analytical Distributions and something
must be wrong with them and they are not distributing properly. This
is why it is allowing you to post though it says distributions with
errors. Go to that entry and click on the ribbon button for viewing AA
distributions and You should be knowing what's happening
Thanks
Janakiram M.P.
MCP-GP
Post by JoeWoW! I extremely appreciate your work, it was the adobe setting.
Can I push my luck more, if you don't know just tell me, you help me enough
it's alrigth. I do a backup of the real company and restore to the test
company with SQL 2005, I have a problem in the payables, it is related to the
backup restore because I don't have a problem in the real company. When we do
a payable new entry in the test, when we try to save our entry it tells us
this transaction won't post because it includes distributions with errors..
But before we save the distributions are OK. We have GP10 and Analytical
Accounting.
I have posted this but the last solution was to download the debug tool but
I'm a customer and I don't have access to the Partner source.
Regards, Joel :)
Post by Janakiram M.P.Joel
I remember we used to face this issue for one of my clients long back
when we used to print payroll related documents remotely. The issue is
with Adobe PDF. Just check your PDF settings. Just check if you have
Do not Send Post scripts is marked or not. This might be the culprit.
Whatever it is, Check once again all your PDF settings.
Thanks
Janakiram M.P.
MCP-GP- Hide quoted text -
- Show quoted text -