Monday, December 30, 2013

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'

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





Thursday, December 19, 2013

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

Sunday, December 15, 2013

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' 

Saturday, December 14, 2013

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. 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
  9. This will most likely cause a fair amount of data loss, but the database will be accessible

Tuesday, December 10, 2013

Dynamics GP - Error message when you try to post a batch that has a Posting Interrupted status in the Batch Recovery window in Microsoft Dynamics GP: "Batch Failed to Complete Posting"

This was a strange error message we got when trying to recover a batch that was attempting to access the serial/lot tables while doing the batch recovery.

Nothing worked, the batch would keep getting put back into recovery.

We eventually discovered a trick that worked to allow us to recover the batches.


  1. Log into one machine as sa, run an inventory reconcile on everything
  2. Log onto another machine as another user and run batchrecovery WHILE the reconcile is running on the first machine
  3. For some reason, it stops trying to access the lot table, and just recovers the batch correctly.

Associated errors:

Transaction not completed at initiating scripts exit, implicitly rolled back.

Unhandled database exception:
A remove operation on table 'IV_Lot_MSTR'  failed because the record couldn't be locked.

Other scripts to use

--Make "posted" transactions available
select PSTGSTUS,BACHNUMB ,* from sop10100 where pstgstus > 0
UPDATE SOP10100 SET PSTGSTUS = 0 where PSTGSTUS =12
UPDATE SOP10100 SET PSTGSTUS = 0 where PSTGSTUS =14


select * from sy00500 where BCHSTTUS > 0  and BCHSOURC = 'sales entry'
update sy00500 set BCHSTTUS = 0, MKDTOPST = 0 where BCHSTTUS > 0  and BCHSOURC = 'sales entry'

update iv10001 set unitcost =12.75 where itemnmbr = 'cdrom'

Sunday, December 8, 2013

SQL 2012 - Long running processes and blocked processes

A process that normally runs in less than 10 seconds has been running for more than an hour. You examine the application log and discover that the process is using session ID 60.
You need to find out whether the process is being blocked. Which Transact-SQL statement should you use?

SELECT * FROM sys.dm_exec_sessions WHERE sessionid = 60

Additional scripts you can use to check up on blocking
http://msdn.microsoft.com/en-us/library/ms176013.aspx

Thursday, December 5, 2013

Dynamics GP 2013 - Business Analyser - Unable to render report. View Log file for more details

Other Related Errors:
Could not connect to configuration report


The Business Analyzer is not installed in the same location as dynamics gp.
Reinstall the business analyzer so it is in the same Program files folder, or copy the entire Business Analyzer folder into the same location as the GP2013 so the two folders are next to one another

Tuesday, December 3, 2013

Dynamics GP - Users cannot access the reports in Business Analyzer

Business Analyzer uses the SSRS report security for whatever it is displaying.

Give the user access to the ssrs reports.

Monday, December 2, 2013

Dynamics GP - Autobatch - Script to automatically create daily batches, and move transactions into them to work with Salespad

/****** Object:  StoredProcedure [dbo].[BI_AutoDayBatch_byUser]    Script Date: 01/11/2017 12:19:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[BI_AutoDayBatch_byUser] @xBatch varchar(50),@xTag varchar(30)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Create new batches if they do not already exist
Insert into SY00500 (GLPOSTDT,BCHSOURC,BACHNUMB,SERIES,MKDTOPST,NUMOFTRX,BACHFREQ,BCHCOMNT,USERID,BCHTOTAL,ORIGIN)
               SELECT     B.DOCDATE, B.Expr1, B.BNum, B.Expr2, B.Expr3, B.Expr4, B.Expr5, B.Expr6, B.Expr7, B.Expr8, B.Expr9
FROM         (SELECT DISTINCT
                                              DOCDATE, 'Sales Entry' AS Expr1,left(CONVERT(char(10), DOCDATE, 126) + @xtag,15)  AS BNum, 3 AS Expr2, 0 AS Expr3, 0 AS Expr4, 1 AS Expr5, 'Move-' + BACHNUMB + ' ' + @xTag AS Expr6,
                                              'DYNSA' AS Expr7, 0 AS Expr8, 1 AS Expr9
                       FROM          SOP10100
                       WHERE      (BACHNUMB = @xBatch)) AS B LEFT OUTER JOIN
                      SY00500 ON B.BNum = SY00500.BACHNUMB
WHERE     (SY00500.BACHNUMB IS NULL)

             
-- Update all transactions in batch
Update sop10100
               Set BACHNUMB = left(CONVERT(char(10), docdate,126)+@xTag,15)
               where BACHNUMB  = @xBatch and USER2ENT = @xTag



END




GO
/****** Object:  StoredProcedure [dbo].[BI_AutoMonthBatch]    Script Date: 01/11/2017 12:19:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Creates batches every month in the format BATCHNAME_YYMM
-- =============================================
CREATE PROCEDURE [dbo].[BI_AutoMonthBatch] @xBatch varchar(50),@xTag varchar(30)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Create new batches if they do not already exist
Insert into SY00500 (GLPOSTDT,BCHSOURC,BACHNUMB,SERIES,MKDTOPST,NUMOFTRX,BACHFREQ,BCHCOMNT,USERID,BCHTOTAL,ORIGIN)
               SELECT     B.DOCDATE, B.Expr1, B.BNum, B.Expr2, B.Expr3, B.Expr4, B.Expr5, B.Expr6, B.Expr7, B.Expr8, B.Expr9
FROM         (SELECT DISTINCT
                                              DOCDATE, 'Sales Entry' AS Expr1,left('CREDIT_DEC_' + right(year(docdate),2) + right('00'+month(docdate),2),15)  AS BNum, 3 AS Expr2, 0 AS Expr3, 0 AS Expr4, 1 AS Expr5, 'Move-' + BACHNUMB + ' ' + @xTag AS Expr6,
                                              'DYNSA' AS Expr7, 0 AS Expr8, 1 AS Expr9
                       FROM          SOP10100
                       WHERE      (BACHNUMB = @xBatch)) AS B LEFT OUTER JOIN
                      SY00500 ON B.BNum = SY00500.BACHNUMB
WHERE     (SY00500.BACHNUMB IS NULL)

             
-- Update all transactions in batch

Update sop10100
               Set BACHNUMB = left('CREDIT_DEC_' + right(year(docdate),2) + right('00'+month(docdate),2),15)
               where BACHNUMB  = @xBatch and USER2ENT = @xTag



END




GO
/****** Object:  StoredProcedure [dbo].[BI_BuildBatches]    Script Date: 01/11/2017 12:19:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Schedule this SP to run periodically to generate batches
-- =============================================
CREATE PROCEDURE [dbo].[BI_BuildBatches]

AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Get list of current batches and users

 --Cycle through each record and generate batch, assign all records, then reconcile batch
 DECLARE @SOPCursor CURSOR;
DECLARE @MyField varchar(30);
BEGIN
    SET @SOPCursor = CURSOR FOR
  select distinct USER2ENT from sop10100
  where SOPTYPE = 3 and BACHNUMB = 'INVOICED'


    OPEN @SOPCursor
    FETCH NEXT FROM @SOPCursor
    INTO @MyField

    WHILE @@FETCH_STATUS = 0
    BEGIN
 --begin algorithm

 IF @MyField > '0' BEGIN
 exec BI_AutoDayBatch_byUser 'INVOICED', @MyField
 exec BI_SOP_Batch_Reconcile 'Sales Entry',0

 END
 --end algorithm
      FETCH NEXT FROM @SOPCursor
      INTO @MyField
    END;

    CLOSE @SOPCursor ;
    DEALLOCATE @SOPCursor;
END;


END


GO
/****** Object:  StoredProcedure [dbo].[BI_BuildCDBatches]    Script Date: 01/11/2017 12:19:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Schedule this SP to run periodically to generate batches
-- =============================================
CREATE PROCEDURE [dbo].[BI_BuildCDBatches]

AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Get list of current batches and users

 --Cycle through each record and generate batch, assign all records, then reconcile batch
 DECLARE @SOPCursor CURSOR;
DECLARE @MyField varchar(30) ;
BEGIN
    SET @SOPCursor = CURSOR FOR
  select distinct USER2ENT from sop10100
  where SOPTYPE = 3 and BACHNUMB = 'CREDIT DECLINED'


    OPEN @SOPCursor
    FETCH NEXT FROM @SOPCursor
    INTO @MyField

    WHILE @@FETCH_STATUS = 0
    BEGIN
 --begin algorithm

 IF @MyField > '0' BEGIN
  exec BI_AutoMonthBatch 'CREDIT DECLINED',@MyField
 exec BI_SOP_Batch_Reconcile 'Sales Entry',0

 END
 --end algorithm
      FETCH NEXT FROM @SOPCursor
      INTO @MyField
    END;

    CLOSE @SOPCursor ;
    DEALLOCATE @SOPCursor;
END;


END


GO
/****** Object:  StoredProcedure [dbo].[BI_SOP_Batch_Reconcile]    Script Date: 01/11/2017 12:19:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

-- create the store procedure
CREATE PROCEDURE [dbo].[BI_SOP_Batch_Reconcile]

@BCHSOURC varchar(15),
@REMOVE  int    --0 = Don't Delete Empty Batches, 1 = DO Delete Empty Batches

AS

/*
********************************************** STORED PROCEDURE HEADER  ********************************************************

Comments:     This stored procedure will reconcile SOP batches; add missing batch headers, recalculate batch totals, and
    remove any empty batches.

REVISION HISTORY:

EXEC [dbo].[BI_SOP_Batch_Reconcile] 'Sales Entry', 1

*******************************************END OF STORED PROCEDURE HEADER  ********************************************************
*/

set nocount on

/* Update the totals for the batch only for sales batches */
IF (@BCHSOURC <> 'Sales Entry')
 RETURN (0)

--DECLARE THE VARIABLES
DECLARE @BACHNUMB varchar(15),
  @I_sCompanyID smallint,
  @O_mNoteIndex numeric(19,5),
  @O_iErrorState int,
  @I_vCHEKBKID char(15),
  @CURNCYID char(15)

--Get the CompanyID
select @I_sCompanyID = CMPANYID
from DYNAMICS..SY01500
where INTERID = DB_Name()

--Get the Checkbook ID
select @I_vCHEKBKID = CHEKBKID
from SOP40100

--Get the Functional Currency
select @CURNCYID  = FUNLCURR
from MC40000

--DECLARE THE CURSOR
DECLARE curReconcileSOPBatches cursor fast_forward for
 -- Reconcile existing batches
 select BACHNUMB
 from SY00500
 where BCHSOURC = @BCHSOURC

 union all

 --Insert any missing SOP Batches
 select distinct BACHNUMB
 from SOP10100
 where BACHNUMB  not in (select BACHNUMB from SY00500 where BCHSOURC = @BCHSOURC)
   and BACHNUMB <> ''

--OPEN THE CURSOR
OPEN curReconcileSOPBatches

FETCH next from curReconcileSOPBatches
 into @BACHNUMB
WHILE (@@FETCH_STATUS = 0)
BEGIN

 print(@BACHNUMB)

 IF NOT EXISTS
  (
   select BACHNUMB
   from SY00500
   where BACHNUMB  = @BACHNUMB
     and BCHSOURC = @BCHSOURC
  )
 BEGIN
  --Get and increment the next note index
  exec DYNAMICS.[dbo].[smGetNextNoteIndex] @I_sCompanyID, 1, @O_mNoteIndex output, @O_iErrorState output

  INSERT into SY00500
   (
   GLPOSTDT,BCHSOURC,BACHNUMB,SERIES,BACHFREQ,USERID,CHEKBKID,MODIFDT,CREATDDT,NOTEINDX,CURNCYID,ORIGIN
   )
  select
   CONVERT(varchar(10), GetDate(), 101), -- GLPOSTDT
   @BCHSOURC, -- BCHSOURC
   @BACHNUMB, -- BACHNUMB
   3, -- SERIES
   1, -- BACHFREQ
   'sa', -- USERID
   @I_vCHEKBKID, -- CHEKBKID
   CONVERT(varchar(10), GetDate(), 101), -- MODIFDT
   CONVERT(varchar(10), GetDate(), 101), -- CREATDDT
   @O_mNoteIndex, -- NOTEINDX
   @CURNCYID, -- CURNCYID
   1 --ORIGIN
 END

 --EXECUTE THE LOGIC
 DECLARE @NUMOFTRX integer
 DECLARE @BCHTOTAL numeric(19,5)

 -- Get the number of transactions
 SELECT @NUMOFTRX = COUNT(s.SOPNUMBE)
 FROM SOP10100 s
 INNER JOIN SY00500 b ON
   s.BACHNUMB = b.BACHNUMB AND
   b.BCHSOURC = @BCHSOURC
 WHERE s.BACHNUMB = @BACHNUMB

 -- Get the batch amount
 SELECT @BCHTOTAL = SUM(s.DOCAMNT)
 FROM SOP10100 s
 INNER JOIN SY00500 b ON
   s.BACHNUMB = b.BACHNUMB AND
   b.BCHSOURC = @BCHSOURC
 WHERE s.BACHNUMB = @BACHNUMB

 -- Remove any nulls
 SELECT @BCHTOTAL = COALESCE(@BCHTOTAL, 0)

 /* See if the quantity is 0. */
 IF @NUMOFTRX = 0
  AND @REMOVE = 1
 BEGIN
  -- Remove the batch if it does not have any transactions
  DELETE SY00500
  WHERE BACHNUMB = @BACHNUMB AND
    BCHSOURC = @BCHSOURC
 END
 ELSE
 BEGIN
  -- Update the batch
  UPDATE SY00500
  SET  BCHTOTAL = @BCHTOTAL,
    NUMOFTRX = @NUMOFTRX
  WHERE BACHNUMB = @BACHNUMB AND
    BCHSOURC = @BCHSOURC
 END

 --GET THE NEXT RECORD
 FETCH next from curReconcileSOPBatches
  into @BACHNUMB

END

--CLOSE AND DEALLOCATE THE CURSOR
CLOSE curReconcileSOPBatches
DEALLOCATE curReconcileSOPBatches


GO

Disconnect all Dynamics GP Sessions accessing a reports.dic

run this from a command line to IMMEDIATELY disconnect all sessions to this file so you can replace it.
This is very dangerous, and should not be used in a production system unless it is absolutely necessary.
This will most likely corrupt your dictionary file, and cause users to be disconnected.

openfiles /disconnect /op "C:\GPReports\Reports.dic"