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.

SQL 2008 - How to recover a damaged or corrupted mdf or ldf file

For this example, we will assume the database name is "TEST" with the files test.mdf and test.ldf.

When we try to attach the mdf, we get an error message saying the file is corrupted.

1. Rename the TEST.mdf, and TEST.ldf  to TEST_OLD.mdf and TEST_OLD.ldf
2. Create TEST as a database so that it generates a blank mdf and ldf
3. Stop SQL server service
4. Delete the blank test.mdf and test.ldf, and rename the test_old.mdf and test_old.ldf to test.mdf and test.ldf to force sql to read the corrupted files
5. Start SQL server service
6. The database will show that it is in "suspect" mode
7. Run this script taken from
   http://www.codeproject.com/Articles/20333/How-to-Restore-SQL-Server-2005-Suspect-Database
8. 
   

9. EXEC sp_resetstatus 'yourDBname';
   ALTER DATABASE yourDBname SET EMERGENCY
   DBCC checkdb('yourDBname')
   ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
   DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
   ALTER DATABASE yourDBname SET MULTI_USER

10. This will most likely cause a fair amount of data loss, but the database will be accessible