Dynamics GP - Bank Audit Trail Codes: GPS Error: 54

Solution here

http://dynamicscare.com/blog/dynamics-gp-error-when-trying-to-print-bank-trans-history-report-using-audit-trail-code/

Problem:
Single Audit Trail code is used across multiple dates

run this query to identify the problem Bank transactions, and the last posted dates
------------------------------------------------------------------------------------------------------

SELECT     AUDITTRAIL, COUNT(DISTINCT POSTEDDT) AS Expr1,Max(DISTINCT POSTEDDT) AS Expr2
FROM         CM20200
GROUP BY AUDITTRAIL
HAVING      (COUNT(DISTINCT POSTEDDT) > 1)
------------------------------------------------------------------------------------------------------


Next, update the posteddt for all audittrails that have multiple posteddts
------------------------------------------------------------------------------------------------------
update CM20200 set POSTEDDT = '2010-08-28 00:00:00.000' where AUDITTRAIL = 'CMDEP00001936' and POSTEDDT <> '2010-08-28 00:00:00.000'

------------------------------------------------------------------------------------------------------
After resolving this issue, another issue popped up immediately after due to multiple PDTUSRID values per audittrailcode in the CM20200

Other Related Errors:
An error occurred executing the SQL statements
GPS Error: 54
SQL Error: 2627 Violation of Primary Key Constraint, Cannot insert duplicate key in object
ODBC Error: 23000


Run this script to identify the duplicated lines for the audit trail codes throwing errors.
------------------------------------------------------------------------------------------------------
select * from cm20200 where cmrecnum in (select cmdnumwk from cm20100 where audittrail = 'cmdep00002318')
------------------------------------------------------------------------------------------------------

Find the PDTUSRID that should be used on all the transactins, then update all of the transactions to have the same PDTUSRID

------------------------------------------------------------------------------------------------------

update CM20200 set PTDUSRID = 'Gillian' where DEX_ROW_ID = '107647'

------------------------------------------------------------------------------------------------------

Dynamics GP - An error occurred executing SQL statements

Other associated errors :
GPS Error:54
SQL Error: 2601 (Microsoft)(ODBC SQL Server Driver)(SQL Server) Cannot insert duplicate key row in object 'dbo.AAG20000' with unique index 'AK2AAG20000'
ODBC Error:23000

Solutions:

1. I had this error occur due to bad inventory lots. Going through the transactions and removing the lots and re-adding different lots allowed this transaction to post even though it still threw all of the errors.
2. Running the script below to fix the AA values

Solution here
http://community.dynamics.com/gp/f/32/t/29394.aspx


Check for orphan AA records
------------------------------------------------------------------

select * from AAG30003 a left outer join AAG30000 b

on a.aaGLHdrID = b.aaGLHdrID

where b.aaGLHdrID is null

select * from AAG30002 a left outer join AAG30000 b

on a.aaGLHdrID = b.aaGLHdrID

where b.aaGLHdrID is null

select * from AAG30001 a left outer join AAG30000 b

on a.aaGLHdrID = b.aaGLHdrID

where b.aaGLHdrID is null

select * from AAG10003 a left outer join AAG10000 b

on a.aaGLWorkHdrID = b.aaGLWorkHdrID

where b.aaGLWorkHdrID is null

select * from AAG10002 a left outer join AAG10000 b

on a.aaGLWorkHdrID = b.aaGLWorkHdrID

where b.aaGLWorkHdrID is null

select * from AAG10001 a left outer join AAG10000 b

on a.aaGLWorkHdrID = b.aaGLWorkHdrID

where b.aaGLWorkHdrID is null

Dynamics GP - another user is printing computer checks for the checkbook id assigned to this batch

------------Find the stuck batch by running

select * from sy00500 where bchsttus > 0

--then run

--------------------------
UPDATE SY00500 SET MKDTOPST=0, BCHSTTUS=0 where BACHNUMB='???'

--- where ??? is the batch number that is stuck

Dynamics GP- SQL2008 - Fix SQL Orphaned Users by creating SQL users from Database Users

Original Code obtained from here
http://community.spiceworks.com/scripts/show/819-find-and-fix-orphaned-users-in-sql-server

Modified slightly to work for SQL 2008 R2

/*
Disclaimer : As with all my code, if I didn't source someone else's work please bring it to my attention. I make all efforts
to make notes of where pieces of code came from to give someone credit for their hard work. However, there are times
that I miss something and I have no problem fixing that. Just give me the opportunity to attribute the source by letting
me know.

Type : Ad-Hoc

Name :       n/a

Author :     Jason Crider
  Blog - http://www.jasoncrider.com/blog
  Twitter - http://twitter.com/jasoncrider
  LinkedIn - http://www.linkedin.com/pub/jason-crider/a/335/33
  Email - arsqldba at gmail dot com
 
Purpose :    Part 1 - Find Orphaned Users, Part 2 - Fix Orphaned Users

Notes : sp_change_users_login maps an existing database user to a SQL Server login. This feature will be removed in a future version of
Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that
currently use this feature. Use ALTER USER instead. (http://msdn.microsoft.com/en-us/library/ms174378%28v=SQL.105%29.aspx)

Syntax

sp_change_users_login [ @Action = ] 'action'
    [ , [ @UserNamePattern = ] 'user' ]
    [ , [ @LoginName = ] 'login' ]
    [ , [ @Password = ] 'password' ]
Arguments

[@Action =] 'action'
(http://msdn.microsoft.com/en-us/library/aa259633%28v=sql.80%29.aspx)

*/ 
--Part 1 - Find orphaned users in current Database 
Sp_change_users_login @Action='Report'; 

--Part 2 - Fix orphaned users in current Database 
DECLARE @SQL VARCHAR(200) 
DECLARE cursql CURSOR FOR 
  SELECT 'EXEC sp_change_users_login ''auto_fix'', ''' 
         + name + ''',NULL, ''' + name + '''' 
  FROM   sysusers 
  WHERE  issqluser = 1 
         AND name NOT IN ( 'guest', 'dbo', 'sys', 'INFORMATION_SCHEMA' ) 

OPEN cursql 

FETCH cursql INTO @SQL 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      EXEC ( @SQL ) 

      FETCH cursql INTO @SQL 
  END 

CLOSE cursql 

DEALLOCATE cursql 

go 

--Same as Part 1 
--You can comment this part out, but it shows you that the orphaned users were actually fixed. 
Sp_change_users_login @Action='Report' 

SQL - Attempt to fetch logical page in database failed. It belongs to allocation unit

Generally this error message means your database structure is corrupted, and there is nothing you can do.
you cannot drop the affected tables because they cannot be accessed.
The only real solution to this problem is to export all of the data you can get your hands on into a proper working database.

Moral of the story, backup properly. I don't even know why they bother to have databases that don't backup. 


Solution suggested from http://msdn.microsoft.com/en-us/library/aa337419.aspx

1. Identify the tables associated with the allocation units specified in the message by running the following query. Replaceallocation_unit_id with the allocation units specified in the error message.
   USE database_name;
   GO
   SELECT au.allocation_unit_id, OBJECT_NAME(p.object_id) AS table_name, fg.name AS filegroup_name,
   au.type_desc AS allocation_type, au.data_pages, partition_number
   FROM sys.allocation_units AS au
   JOIN sys.partitions AS p ON au.container_id = p.partition_id
   JOIN sys.filegroups AS fg ON fg.data_space_id = au.data_space_id
   WHERE au.allocation_unit_id = allocation_unit_id OR au.allocation_unit_id = allocation_unit_id
   ORDER BY au.allocation_unit_id;
   GO
2. Execute DBCC CHECKTABLE without a REPAIR clause on the table associated with the second allocation unit ID specified in the error message. (Table in the table_name field. add sys. in front of the name if it is a system table)
3. Execute DBCC CHECKDB without a REPAIR clause as soon as possible to determine the full extent of the corruption in the entire database. (dbcc checkdb ('mydb',REPAIR_ALLOW_DATA_LOSS))
4. Check the error log for other errors that often accompany a 605 error and examine the Windows Event Log for any system or hardware related issues. Fix any hardware-related problems that are contained in the logs.

SQL - Remove Restricted Mode on Databases

ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE DatabaseName SET MULTI_USER
GO

Dynamics GP - Product 309 does not support upgrading from version 9.0.300.

This is because a specific module has skipped an upgrade path.
The only solution is to either go back to the previous version and update it correctly, or delete the tables and reinstall the module for the current version. Of course you will lose all data that was in the module.

In this example, 309 is Fixed Assets

Solution from http://community.dynamics.com/gp/f/32/t/59532.aspx

1. UPDATE DYNAMICS..DB_Upgrade SET db_verOldMajor=0, db_verOldMinor=0, db_verOldBuild=0, db_status=0 WHERE PRODID=309 and db_name = 'xxx'

2. Delete DYNAMICS..DU000020 WHERE PRODID=309 and companyid = 'x'

3. Run the following scripts against the company database:

A. Drop table FA41000

B. Drop table FA00200

C. Drop table FAINST01

You can find the db_name and companyid in the SY01500 table.