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(100) 
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

--This script will create a daily batch  if it does not exist, and move all transactions into it that were done on that day

--------------------------------------------------------------------------------------------
/****** Object:  StoredProcedure [dbo].[BI_AutoDayBatch_byUser]    Script Date: 21/06/2017 03:51:25 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(10)
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_BuildBatches]    Script Date: 21/06/2017 03:54:00 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
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 = 'INVOICE'


    OPEN @SOPCursor
    FETCH NEXT FROM @SOPCursor
    INTO @MyField

    WHILE @@FETCH_STATUS = 0
    BEGIN
--begin algorithm

IF @MyField > '0' BEGIN
exec BI_AutoDayBatch_byUser 'INVOICE', @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: 21/06/2017 03:54:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

-- create the store procedure
ALTER 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





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"

Friday, November 29, 2013

Dynamics GP - Ar Reconcile Error

Problem:
While running an AR reconcile, this error was coming up

A get/change first operation on table 'RM_Distribution_History' (45)

[Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near 'S'.


[Microsoft][SQL Server Native Client 10.0][SQL Server]Unclosed quotation mark after the character string ') ORDER BY RMDTYPAL ASC ,DOCNUMBR ASC ,SEQNUMBR ASC '.

Cause:
This was due to a customer ID set as AL'S and the ' was causing the code to error out

Solution:
-- Script shows all records with an id containing the ' character
declare @ts as varchar (50)

set @ts = '%' + '''' + '%'
print @ts

select * from rm30301 where custnmbr like @ts


Dynamics GP - Report Writer - Calculated field to get a specific Vendor Address ID Info

User-Defined System Function -RW_PMADDRINFO

More info and Function list here
http://blogs.msdn.com/b/developingfordynamicsgp/archive/2008/09/01/using-the-built-in-report-writer-functions.aspx

Dynamics GP - Users login and instead of initializing, GP hangs at a blank white screen

We experienced this issue due to a single faulty SOP transaction that could not post properly.
A single user was trying to do a transaction post on a specific invoice, and caused the entire database to lock up.
GP would get stuck attempting to post this bad invoice.
No other users could log in.

The only solution was to restore to a previous backup file, and run a check links and reconcile to fix the transaction.
The transaction itself was then deleted and re-entered manually.
This resolved the issue.

You can also try deleting any stuck transaction-level postings by running this script.

delete from sy00500 where  bchsourc like '*%'

It also corrupted our Reports dictionary,and that had to be restored from a backup.

Wednesday, November 27, 2013

SSRS 2008 - Action - Go to URL in a new window. Pass parameters. Handle Empty spaces in parameter values.

If you choose go to report, you cannot open the report in a new window.

You must select Go to URL and click the Fx button to enter an expression.
You will use SQL to write javascript that will generate the correct link when clicked on from the SSRS report

Points to note

  • Click on the SSRS report you want, and get the URL in the address bar. The only part you need is the actual report name after the first ? to the end of the report name
  • That will go in the space for "MyReportName"
  • Use Reportserver, not Reports
This code is entered into the Fx box.


="javascript:void(window.open('http://gpsql/Reportserver?MyReportName&FiltDate="+ Fields!DATERECD.Value+"&FiltRct="+replace(Fields!MOPRCTNM.Value," ","+") +"'))"

When clicked, it should open a new window and go to this link

http://gpsql/ReportServer/Pages/ReportViewer.aspx?MyReportName&FiltDate=11/1/2013&FiltRct=MR02554++++++++++++++

Tuesday, November 26, 2013

Dynamics GP 2013 Analysis Cubes - analysis cubes dynamics gp sql server connection failed. please verify the dynamics gp sql server information

This error happens because your windows account does not have access to read the DYNAMICS database on the SQL server you are trying to read from.


On the GP SQL server, add your windows account to the dyngrp, and give it access to the dynamics and all company databases.

Friday, November 8, 2013

Wednesday, October 30, 2013

Integration Manager 2013 - Unimplemented cast from Btype 'BROWSETYPE_UNKNOWN'

This is a bug with Integration manager 2013.
You transactions integrated just fine.
If you ran it multiple times, you will need to void the additional transactions.

Increase the number of errors allowed ,and run your integration once.

Dynamics GP Report Writer Tricks

All calculated string fields can show up to 80 characters. However, report writer truncates what is displayed based on the size of the text box, and the font size being used. To get the full 80 characters,drop the font size to 6, and expand the entire field all the way out.

This will allow it to export correctly for your word form template.

Dynamics GP - Default Smartlist Security

There is no default smartlist security.
All users have access to all smartlists by default.

To limit access, create a task and a role.
In the role, select Smartlist, smartlist objects, smartlist objects, and select the specific smartlists to grant access.

Assign this role to a user.

This will override the default "all access"

Tuesday, October 29, 2013

Dynamics GP - Report Writer Description fields not showing all characters on reports.

Notes:
Report Writer WILL wrap text if you stretch the field downward, but the default field only shows 50 characters.
Report Writer WILL NOT wrap text if there are no spaces in the data, even if you stretch the field downward.

Issue:
GP Item Description can take 100 characters.
Report Writer Fields are limited to 50 Characters (this is 80 in newer version of gp)
Report Writer Calculated Fields are limited to 40 Characters  (this is 80 in newer versions of gp)

This applies to a number of different fields.

Solution 1: Report Writer functions
Create Multiple calculated fields to chop the description into 40,80,100, and pull all three fields onto the report.
This will cause hard cuts, and may cut a word in the middle

Use the RW_Substring function under user-defined functions

  • The format is 
  • Functions>User-defined>System
  • RW_Substring add
  • Item Description add
  • start position constant add
  • substring length constant add
Do not remove any characters, do not add any other fields or characters
So for example, if i wanted to chop the item description start at character 81, and i wanted my string to be 20 characters long, it would look like
RW_Substring Item Description 81 20
in the report writer editor

For the Check Formats, you can split the PM10300.STRGA255 to replace the STRGA255 and STRGB255 fields on the report. (does not work, cuts cents badly)

Create two calculated string fields with formulas that will look like

  • Function Script(RW_Substring PM_Payment_Work.STRGA255 1 60)
  • Function Script(RW_Substring PM_Payment_Work.STRGA255 61 60)

Or use the Purchasing>RW_Splitwordamt function

  • Create 3 calculated fields
  • Function Script(RW_Splitwordamt PM_Payment_Work.STRGA255 PM_Payment_Work.STRGB255 75 1)
  • Function Script(RW_Splitwordamt PM_Payment_Work.STRGA255 PM_Payment_Work.STRGB255 75 2)
  • Function Script(RW_Splitwordamt PM_Payment_Work.STRGA255 PM_Payment_Work.STRGB255 75 3)
Or use the RW_ConvertToWordsAndNumbersParse (use this method)
https://blogs.msdn.microsoft.com/developingfordynamicsgp/2010/05/23/announcing-report-writer-function-rw_converttowordsandnumbersparse/


  • FUNCTION_SCRIPT(RW_ConvertToWordsAndNumbersParse  cyPrintTotal   (c) Currency ID  0   80   1)
  • FUNCTION_SCRIPT(RW_ConvertToWordsAndNumbersParse  cyPrintTotal   (c) Currency ID  0   80   2)
  • FUNCTION_SCRIPT(RW_ConvertToWordsAndNumbersParse  cyPrintTotal   (c) Currency ID  0   80   3)


Related links:
http://support.microsoft.com/kb/950780


Solution 2: Report Writer with VBA
Add VB code to connect directly to table, get the full description, then cut the fields  up based on spaces between the words.

I would just pass this to a label field instead to prevent the need to cut based on spaces in the words, but there is also a limit on the label field.

Related links:
http://blogs.msdn.com/b/developingfordynamicsgp/archive/2009/05/25/showing-the-full-item-description-on-invoices-example.aspx


ADO Sample
-----------------------------------------------------
Option Explicit
Dim objRec
 Dim objConn
 Dim cmdString
'Dim cn As New ADODB.Connection



Private Sub Report_BeforeBody(SuppressBand As Boolean)
End Sub
Private Sub report_start()
 Set objRec = CreateObject("ADODB.Recordset")
 Set objConn = CreateObject("ADODB.Connection")
 objConn.ConnectionString = "Provider=SQLOLEDB;Data Source=DYNGP;Initial Catalog=TWO;User Id=sa;Password="

 'cn = UserInfoGet.CreateADOConnection
 ' cn.CursorLocation = 3
 '  cn.DefaultDatabase = UserInfoGet.IntercompanyID
 ' cn.Open
 
 objConn.Open
End Sub
Private Sub Report_BeforeAH(ByVal Level As Integer, SuppressBand As Boolean)

 cmdString = "select Source_Document_Number from pop10110 where PONumber = '" + RTrim(PONumber) + "' and itemnmbr = '" + RTrim(ItemNumber) + "'"
 Set objRec = objConn.Execute(cmdString)
 If objRec.EOF = True Then
 xReqNo = ""
 Else
 xReqNo.Value = objRec!Source_Document_Number
 End If
End Sub

Private Sub report_end()
 objConn.Close
End Sub

Ensure that you add all fields you need to VB
Ensure you run GP as administrator or the VB will not save
Call this in the body to execute for each record
ManufacturingOrderReceiptEn.CurrencyM105 is an empty calculated field
You must put the calculated field in the BODY or the appropriate section where the VB code is running

Sunday, October 27, 2013

SQL - Scan all tables and fields for a text string

Original Post
http://stackoverflow.com/questions/591853/search-for-a-string-in-an-all-the-tables-rows-and-columns-of-a-sqll-server-db


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

DECLARE
    @search_string  VARCHAR(100),
    @table_name     SYSNAME,
    @table_id       INT,
    @column_name    SYSNAME,
    @sql_string     VARCHAR(2000)

SET @search_string = 'pal'

DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239)

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @sql_string = 'IF EXISTS (SELECT * FROM [' + @table_name + '] WHERE [' + @column_name + '] LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''
--print @sql_string
        EXECUTE(@sql_string)

        FETCH NEXT FROM columns_cur INTO @column_name
    END

    CLOSE columns_cur

    DEALLOCATE columns_cur

    FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur

DEALLOCATE tables_cur

Saturday, October 26, 2013

Dynamics GP - Install smartconnect 2013

Pre-Requisites
You may need to install the following on the client depending on the types of integrations you intend to run


Confirm the Smartconnect User Password if you have already setup smartconnect previously.

Install smartconnect 2013
Run smartconnect, setup gp connector
Run gp, include code, update resource cache
Run smartconnect, run maintenance


Dynamics GP - How to upgrade a forms / Reports dictionary using Dynamics GP Utilities

Copy Old GP Folder
Install New GP
Copy Old dictionary to New GP Data folder
Run GP Utilities
Select Upgrade forms/Reports dictionary
Select OLD Dynamics.set
Select Reports.dic you copied into NEW GP
Process

Tuesday, October 22, 2013

SSRS - How to convert Numbers to Words in SQL for use with SSRS

This can be achieved by creating a lookup table, and a function to convert numbers to words.

Original Post
http://stackoverflow.com/questions/1673265/how-to-write-number-to-word-function-in-sql-server

--Create conversion table
CREATE TABLE [dbo].[BI_Sequence]
    (
      seq INTEGER NOT NULL UNIQUE,
      word [varchar](25) NOT NULL
    )

INSERT INTO [BI_Sequence] SELECT 0, ''
INSERT INTO [BI_Sequence] SELECT 1, 'One'
INSERT INTO [BI_Sequence] SELECT 2, 'Two'
INSERT INTO [BI_Sequence] SELECT 3, 'Three'
INSERT INTO [BI_Sequence] SELECT 4, 'Four'
INSERT INTO [BI_Sequence] SELECT 5, 'Five'
INSERT INTO [BI_Sequence] SELECT 6, 'Six'
INSERT INTO [BI_Sequence] SELECT 7, 'Seven'
INSERT INTO [BI_Sequence] SELECT 8, 'Eight'
INSERT INTO [BI_Sequence] SELECT 9, 'Nine'

INSERT INTO [BI_Sequence] SELECT 10, 'Ten'
INSERT INTO [BI_Sequence] SELECT 11, 'Eleven'
INSERT INTO [BI_Sequence] SELECT 12, 'Twelve'
INSERT INTO [BI_Sequence] SELECT 13, 'Thirteen'
INSERT INTO [BI_Sequence] SELECT 14, 'Fourteen'
INSERT INTO [BI_Sequence] SELECT 15, 'Fifteen'
INSERT INTO [BI_Sequence] SELECT 16, 'Sixteen'
INSERT INTO [BI_Sequence] SELECT 17, 'Seventeen'
INSERT INTO [BI_Sequence] SELECT 18, 'Eighteen'
INSERT INTO [BI_Sequence] SELECT 19, 'Nineteen'

INSERT INTO [BI_Sequence] SELECT 20, 'Twenty'
INSERT INTO [BI_Sequence] SELECT 30, 'Thirty'
INSERT INTO [BI_Sequence] SELECT 40, 'Forty'
INSERT INTO [BI_Sequence] SELECT 50, 'Fifty'
INSERT INTO [BI_Sequence] SELECT 60, 'Sixty'
INSERT INTO [BI_Sequence] SELECT 70, 'Seventy'
INSERT INTO [BI_Sequence] SELECT 80, 'Eighty'
INSERT INTO [BI_Sequence] SELECT 90, 'Ninety'

--Create Function
create FUNCTION dbo.BI_NumToWords (
                @num AS INTEGER
)       RETURNS VARCHAR(50)
AS
BEGIN

DECLARE @words AS VARCHAR(50)

IF      @num =     0 SELECT @words = 'Zero'
ELSE IF @num <    20 SELECT @words = word FROM BI_Sequence WHERE seq = @num
ELSE IF @num <   100 (SELECT @words = TTens.word + ' ' + TUnits.word
                      FROM BI_Sequence AS TUnits
                     CROSS JOIN BI_Sequence AS TTens
                     WHERE TUnits.seq = (@num % 100) % 10
                       AND TTens.seq = (@num % 100) - (@num % 100) % 10
                    )
ELSE IF @num =   100 (SELECT @words = THundreds.word + ' Hundred'
                      FROM BI_Sequence AS THundreds
                     WHERE THundreds.seq = (@num / 100)
                    )
ELSE IF @num <  1000 (
    SELECT @words = THundreds.word + ' Hundred and '
                   + TTens.word + ' ' + TUnits.word
                      FROM BI_Sequence AS TUnits
                     CROSS JOIN BI_Sequence AS TTens
                     CROSS JOIN BI_Sequence AS THundreds
                     WHERE TUnits.seq = (@num % 100) % 10
                       AND TTens.seq = (@num % 100) - (@num % 100) % 10
                       AND THundreds.seq = (@num / 100)
                    )
ELSE IF @num =  1000 (SELECT @words = TThousand.word + ' Thousand'
                      FROM BI_Sequence AS TThousand
                     WHERE TThousand.seq = (@num / 1000)
                    )
ELSE IF @num < 10000 (
    SELECT @words = TThousand.word + ' Thousand '
                   + THundreds.word + ' Hundred and '
                   + TTens.word + ' ' + TUnits.word
                      FROM BI_Sequence AS TUnits
                     CROSS JOIN BI_Sequence AS TTens
                     CROSS JOIN BI_Sequence AS THundreds
                     CROSS JOIN BI_Sequence AS TThousand
                     WHERE TUnits.seq = (@num % 100) % 10
                       AND TTens.seq = (@num % 100) - (@num % 100) % 10
                       AND THundreds.seq = (@num / 100) - (@num / 1000) * 10
                       AND TThousand.seq = (@num / 1000)
                    )
ELSE SELECT @words = STR(@num)

RETURN @words

END

--Call the function by using
--Select dbo.BI_NumtoWords(9)
--Where 9 is any number

Friday, October 18, 2013

SSRS - Export a PDF as Landscape instead of Portait

Other Related Issues:
How to set report properties
How to change default printing layout

Solution:

  • In the Report Builder, Right click on the grey area outside the report
  • click on Report Properties
To fix it in Internet Explorer if printing directly
  • Click the Gear
  • Click Print
  • Click Page Setup

Sunday, October 13, 2013

Dynamics GP - Field Service Contract Line Mail Merge Macro

ActivateWindow dictionary 'FieldService'  form 'SVC_Contract_Maintenance' window 'SVC_Contract_Maintenance'
  ClickHit field 'Contract Record Type' item 2  # 'Contract'
  MoveTo field 'Contract Number'
  TypeTo field 'Contract Number' , '«ContNo»'
  MoveTo field 'Customer Number'
  MoveTo field '(L) Contract Lines'
  ClickHit field '(L) Contract Lines'
NewActiveWin dictionary 'FieldService'  form 'SVC_Contract_Maintenance' window 'SVC_Contract_Line_Entry'
  ScrollByLine down scrollwin 'SVC_Contract_Line_Entry_Scroll'
  ScrollByLine down scrollwin 'SVC_Contract_Line_Entry_Scroll'
  ScrollByLine down scrollwin 'SVC_Contract_Line_Entry_Scroll'
  ScrollByLine down scrollwin 'SVC_Contract_Line_Entry_Scroll'
  ScrollByLine down scrollwin 'SVC_Contract_Line_Entry_Scroll'
# Key 1: '0', '', '0.00000'
  MoveTo line «Line» scrollwin 'SVC_Contract_Line_Entry_Scroll' field 'Item Number'
# Key 1: '0', '', '0.00000'
  TypeTo line «Line» scrollwin 'SVC_Contract_Line_Entry_Scroll' field 'Item Number' , '«ServiceItemNumber»'
# Key 1: '0', '', '0.00000'
  MoveTo line «Line» scrollwin 'SVC_Contract_Line_Entry_Scroll' field '(L) Serial Number'
ActivateWindow dictionary 'FieldService'  form 'SVC_Contract_Maintenance' window 'SVC_Contract_Line_Entry'
# Key 1: '0', '', '0.00000'
  TypeTo line «Line» scrollwin 'SVC_Contract_Line_Entry_Scroll' field '(L) Serial Number' , '«EquipmentNumber»'
# Key 1: '0', '', '0.00000'
  MoveTo line «Line» scrollwin 'SVC_Contract_Line_Entry_Scroll' field 'U Of M'
# Key 1: '0', '', '0.00000'
  MoveTo line «Line» scrollwin 'SVC_Contract_Line_Entry_Scroll' field QTY
# Key 1: '0', '', '0.00000'
  TypeTo line «Line» scrollwin 'SVC_Contract_Line_Entry_Scroll' field QTY , '1'
# Key 1: '0', '', '0.00000'
  MoveTo line «Line» scrollwin 'SVC_Contract_Line_Entry_Scroll' field '(L) SVC Monthly Price'
ActivateWindow dictionary 'FieldService'  form 'SVC_Contract_Maintenance' window 'SVC_Contract_Line_Entry'
# Key 1: '0', '', '0.00000'
  TypeTo line «Line» scrollwin 'SVC_Contract_Line_Entry_Scroll' field '(L) SVC Monthly Price' , '«RlCost»'
# Key 1: '0', '', '0.00000'
  MoveTo line «Line» scrollwin 'SVC_Contract_Line_Entry_Scroll' field 'Item Description'
# Key 1: '0', '', '0.00000'
  MoveTo line «Line» scrollwin 'SVC_Contract_Line_Entry_Scroll' field 'Config Reference'
# Key 1: '0', '', '0.00000'
  MoveTo line «Line» scrollwin 'SVC_Contract_Line_Entry_Scroll' field 'Start Date'
# Key 1: '0', '', '0.00000'
  MoveTo line «Line» scrollwin 'SVC_Contract_Line_Entry_Scroll' field 'End Date'
# Key 1: '0', '', '0.00000'
  MoveTo line «Line» scrollwin 'SVC_Contract_Line_Entry_Scroll' field 'Bill Start'
# Key 1: '0', '', '0.00000'
  MoveTo line «Line» scrollwin 'SVC_Contract_Line_Entry_Scroll' field 'Bill End'
# Key 1: '0', '', '0.00000'
  MoveTo line «Line» scrollwin 'SVC_Contract_Line_Entry_Scroll' field '(L) Total'
  MoveTo field 'OK Button'
  ClickHit field 'OK Button'
NewActiveWin dictionary 'FieldService'  form 'SVC_Contract_Maintenance' window 'SVC_Contract_Maintenance'
  MoveTo field 'Save Button'
  ClickHit field 'Save Button'

Thursday, October 10, 2013

Dynamics GP Manufacturing - Wrong quantities are calculated in the Planned Qty to Backflush

Problem:
When doing a receipt for an MO, the backflush quantities do not calculate correctly for inventory items.

This happens whenever you recieve additional receipts against a completed MO

Tuesday, October 8, 2013

Dynamics GP - Cash Receipts - Another user is editing this document

RM00401 has the transaction status record.

Delete this record to free up the document.

delete from RM00401 where docnumbr = 'mydocnum'

Dynamics GP - How to fix average cost

Problem:
Average cost is all wrong because of data entry errors.


Solution:

If you want to reset the costing for all of your inventory, the simplest method is to remove all of your stock, adjust the iv10200 table to set the qtyonhnd to 0 for the last record, then adjust everything back in at the proper cost.
http://support.microsoft.com/kb/2706556

If you kno wthe exact problem, or the exact cost layer that's causing the bad average calculation, just modify the cost layer by going to

Tools>Utilities>Inventory>Adjust Costs

This will adjust the specific cost layer, and post the differences to the GL.
If the price still does not look correct, run an inventory reconcile on the item to have it recalculate the average cost.

Monday, October 7, 2013

Dynamics GP - this document number already exists you must provide a new document number

If you have "Delete documents" unticked for a specific sales order type, GP will automatically create a record for each SOP transaction in the SOP30200 and SOP30300 tables from the moment you enter any transaction for that document type, even if you don't post it.

To resolve this, tick the "Delete documents" for the sales order type, and delete the records in the sop30200 and sop30300 tables that should not be there.

Dynamics GP eOne SmartConnect - Could not log on to SQL server

If you get any errors pertaining to logging on or connecting to SQL, chances are the default Smartconnect user is not setup correctly.


  • Install dot net 4.5
  • Turn off all firewalls
  • Log in to SQL server
  • Delete the Smartconnect user (Or confirm the password by logging into SQL with the smartconnect account)
  • Reinstall Smartconnect
  • Log into GP
  • Include Code
  • GP>Smartconnect>Build cache
  • Launch Smartconnect

Wednesday, October 2, 2013

Dynamics GP - In-Transit Transfer Tables and Views

https://www.gptablereference.com/2010/Group/Field%20Service


SVC00700 - In-Transit Transfer Header Work
SVC00701 - In-Transit Transfer Line Work

IV30300 - Item transaction detail
SVC30700 - In-Transit Transfer History Hdr
SVC30701 - In-Transit Transfer History Dtl



/****** Object:  View [dbo].[BI_ITTRHist]    Script Date: 03/29/2017 11:16:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_ITTRHist]
AS
SELECT     dbo.SVC30700.ORDDOCID, dbo.SVC30700.RFRNCDOC, dbo.SVC30700.LOCNCODE, dbo.SVC30700.TRNSFLOC, dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC,
                      dbo.SVC30701.UOFM, dbo.SVC30701.TRNSFQTY, dbo.SVC30701.QTYFULFI, dbo.SVC30701.QTYSHPPD, dbo.SVC30701.QTY_To_Receive, dbo.SVC30701.QTYRECVD,
                      dbo.SVC30701.LNITMSEQ, dbo.SVC30701.STATUS, dbo.SVC30701.QTYBSUOM, dbo.SVC30701.TRNSFLOC AS Expr1, dbo.SVC30701.TRNSTLOC,
                      dbo.SVC30701.Reason_Code, dbo.SVC30701.ITLOCN
FROM         dbo.SVC30700 INNER JOIN
                      dbo.SVC30701 ON dbo.SVC30700.ORDDOCID = dbo.SVC30701.ORDDOCID INNER JOIN
                      dbo.IV00101 ON dbo.SVC30701.ITEMNMBR = dbo.IV00101.ITEMNMBR

GO


Tuesday, October 1, 2013

Dynamics GP - Unable to open customizations dictionary

Problem:
This is happening because someone has Dynamics GP open on their machine.

Solution:
Make sure no one is actually using the system, or doing work before kicking them out, or just killing GP.
Any one of the following will work

  1. Ask all users to log out and CLOSE GP
  2. Disable the network connection to kill all connections to the file, then re-enable it
  3. If you're on a terminal server, open the task manager, show processes from all users and kill all instances of dynamics*32.exe

Dynamics GP - Smartlist Builder - You do not have security privileges to view all of the tables used in this SmartList

Problem:
Users get the error
"You do not have security privileges to view all of the tables used in this SmartList"
Even though you have set the SQL table security, and the Smartlist security correctly


Solution:
You will need to add permissions to the View SmartLists with SQL Tables operation. Here are the settings under Security Tasks:

Product: SmartList Builder
Type: SmartList Builder Permissions
Series: SmartList Builder Permissions or SmartList Builder (depends on the Service Pack)
Operations: View SmartLists with SQL Tables

Original Post
http://victoriayudin.com/2008/10/08/granting-access-to-a-new-smartlist-in-gp-10/

Monday, September 30, 2013

Dynamics GP - How to Import tax details on PO's

Integration Manager cannot do it.

You can update the PO tables in the background, then create a macro to cycle through all the PO's and recalculate the tax on each PO.

POP10100 - PO Hdr
POP10110 - PO Dtl

Sunday, September 29, 2013

Dynamics GP - Integration Manager - - PO Integration - An item with the same key has already been added

A valid exchange rate for the date period of the PO has not been setup.

Check your currency setups and ensure that the currency is set to search for rates from a previous date to avoid having to set rates for each specific date.

Friday, September 27, 2013

Dynamics GP - How to update Item Decimal Places, Currency Decimal Places and Standard Price Lists without wiping out all the prices

This script will update ALL items and ALL UofM schedules. Use a WHERE if you need to update specific ranges to different values.
Do not use this on a live environment with transactions being processed
This is meant for setup only in a new environment or test environment.
Run check links after to ensure the item is setup correctly.



update IV40201 set UMDPQTYS = 3 --Sets all UofM dp's to 2
UPDATE IV00101 SET DECPLQTY=3 --Sets all Inventory DP's to 2
update IV00108 set TOQTY = 999999999999.99, FROMQTY = 0.01 --Sets all pricing ranges to the correct DP's

update sop10200 set decplqty = 3 --fix open sop line items
update POP10110 set decplqty = 3 --fix open pop line items
update iv10001 set decplqty=3 --fix open inventory trx line items

Rebuild any Inventory BOMs
Rebuild any MFG BOMS
Update iv10402 to fix the UofM if it has changed, and the QTYTO to set the correct FROMQTY and TOQTY
Update iv00106 to fix the UofM if it has changed


To change item currency decimals, refer to this article
http://dynamicsgpblogster.blogspot.com/2011/02/from-newsgroups-changing-item-currency.html

For multicurrency transactions, the decplcur values are as follows
Number = decimal places
7 = 0
8 = 1
9 = 2
10 = 3
11 = 4
12 = 5

The total scripts i used to update the currencies dp's to 5dp for all currencies for this item were as follows.
These scripts are repeated for each table identified from

SELECT * FROM sysobjects o, syscolumns c
WHERE o.id = c.id AND o.type = 'U' AND c.name = 'DECPLCUR'
ORDER BY o.name


that has an itemnmbr and a decplcur field
--This is for the functional currency
--select 'IV00101', * from IV00101 where itemnmbr = 'Item001' and decplcur <6
update IV00101 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6

--This is for the multicurrency
--select 'IV00101', * from IV00101 where itemnmbr = 'Item001' and decplcur >6
update IV00101 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6


Therefore a complete example of all the scripts i used for this one item are

update IV00101 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IV00105 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IV10001 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IV30300 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IV30701 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IV50300 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IVC10101 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IVC30102 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update POP10110 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update POP10310 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update POP30110 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update POP30310 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update PT10000 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update SEE30303 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update SOP10200 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update SOP30300 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IV00101 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IV00105 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IV10001 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IV30300 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IV30701 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IV50300 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IVC10101 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IVC30102 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update POP10110 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update POP10310 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update POP30110 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update POP30310 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update PT10000 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update SEE30303 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update SOP10200 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update SOP30300 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6

Dynamics GP - Set the default selling UofM of all items to the base UofM for that item

update IV00101 set selnguom = baseuofm
--SELECT     a.BASEUOFM, IV00101.SELNGUOM
FROM         IV00101 INNER JOIN
                          (SELECT     IV00101_1.ITEMNMBR, IV00101_1.ITEMDESC, IV40201_1.BASEUOFM, IV00101_1.DECPLQTY
                            FROM          IV00101 AS IV00101_1 INNER JOIN
                                                   IV40201 AS IV40201_1 ON IV00101_1.UOMSCHDL = IV40201_1.UOMSCHDL) AS a ON IV00101.ITEMNMBR = a.ITEMNMBR
WHERE     (IV00101.SELNGUOM = '')



Tuesday, September 17, 2013

SSRS - You do not have sufficient priveleges to access this report

Other Errors:
There are a myriad of error messages pertaining to security access that you may encounter when attempting to access the SSRS portal using Internet Explorer.

Solution:

Make sure you login as the Domain Admin first so that you can give admin access to any other users

  1. Add the ssrs website to your trusted intranet zone
    1. Find your SSRS site address by using the Reporting Services configuration Manager or going to GP>Tools>Setup>System>Reporting Tools Setup
    2. Browse to your SSRS site, get the error
    3. Internet Options>Security>Local Intranet>Sites>Add the current site
  2. Right click Internet explorer and choose Run as Administrator
    1. If it brings up another menu with a small internet explorer icon, just right click on that icon then run as administrator
  3. Open your SSRS site
    1. Click on Site settings in the top right hand corner
    2. Security>New Role Assignment
    3. Type in windows username that should be an administrator in SSRS, tick System Administrator, click ok
    4. Repeat this for as many administrators as you need
  4. Now you should be able to login with a normal windows account (you will have to repeat steps 1 and 2 to set the IE settings for this windows account)
    1. You should be able to login and access the reports normally.
    2. If this still does not work, right click and run IE as administrator.