Dynamics GP - Integration Manager - Consistently Crashes during integrations

• Remove all ' and ` characters from your data
• Use tab delimited source files
• Create a new GP user, and login with that to run your integrations
• Purge all logs
• Set logging to NONE

Check other solutions here

https://community.dynamics.com/gp/b/dynamicsgp/archive/2013/05/28/common-tips-and-issues-when-troubleshooting-and-testing-integrations-in-integration-manager

SQL - Crosstab Queries

https://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/

Use this SP for Dynamic Columns

OR use this technique to build the list of columns first and remove nulls
https://stackoverflow.com/questions/51278149/how-to-remove-null-values-from-an-sql-pivot
-------------------------------------------------------
Declare @Locs  varchar(max)
Declare @Locs2  varchar(max)
Declare @Pivot varchar(max)


select @Locs = AllComm from (
SELECT distinct
       (STUFF((SELECT distinct CAST(', [' + [Location Code] + ']' AS VARCHAR(MAX))
         FROM [BI-ItemQty]
         WHERE [Location Code] > ''
         FOR XML PATH ('')), 1, 2, '')) AS AllComm
FROM [BI-ItemQty]) as A

select @Locs2 = AllComm from (
SELECT distinct
       (STUFF((SELECT distinct CAST(', ISNULL([' + [Location Code] + '],0)  ['+[Location Code] + ']'  AS VARCHAR(MAX))
         FROM [BI-ItemQty]
         WHERE [Location Code] > ''
         FOR XML PATH ('')), 1, 2, '')) AS AllComm
FROM [BI-ItemQty]) as B


Set @Pivot =
N'select [Item No_]
      ,[Posting Date]
      ,[Document Type]
      ,[DocTypeDesc]
      ,[Entry Type]
      ,[EntryTypeDesc]
      ,[Receipts]
      ,[Sales]
      ,[Rtns]
      ,[Adjustments]
      ,[Assembly],'+@Locs2+' from(SELECT *
  FROM [LS13].[dbo].[BI-ItemQty]) ps
  PIVOT
   ( SUM (Quantity)
     FOR [Location Code] in ('+@Locs+')
   ) AS pvt'
print @Pivot
EXECUTE (@Pivot)

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

You can use this technique for fixed columns

https://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/

SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
FROM 
(SELECT SalesPerson, Product, SalesAmount
FROM ProductSales ) ps
PIVOT
(
SUM (SalesAmount)
FOR Product IN
( [Oranges], [Pickles])

Dynamics GP 2013 - Integration Manager - Fixed Assets - Error Number = 5348 Stored Procedure= taCreateAssetID Error Description = Unable to obtain the next Financial Index

• Issue
• Running the Integration Manager integration for Fixed Assets produces this error
• Cause
• Fixed Asset tables are not correct
• Most likely an older version of the tables is still in use from an earlier FA install
• The FAINDEX table has invalid data
• The FA00902 table has no Financial Index records to lookup
• Resolution
• Try manually entering an Asset with general and book data AND running depreciation against it (This worked for me)
• Once you get some records in your FA00902 table, run your integration
• OR
• https://support.microsoft.com/en-us/kb/897747
• Manually update the tables as described in the previous link
• Try clearing the FAINDEX table
• Replace TWO with your company database
• DELETE TWO..FAINDEX
• OR
• Run the database maintenance and reload all of the FA objects

Copy a List of all files in a windows directory

http://smallbusiness.chron.com/copy-list-files-windows-folder-excel-list-40032.html

• Navigate to the directory you want
• Shift Right click on the folder > Open command prompt from here
• In cmd prompt type
• dir /b > dirlist.txt
• This will generate a text file in the folder with a list of all the files

eOne Smartconnect - How to integrate Custom Tax Amounts into SOP Line Items

• The issue is that you want to integrate a tax amount that does not equal the regular % calculation. For example, the tax is 15%, but you want to put $10 tax regardless of the sales amount.
• You must use line sequence numbers in your source data
• Add this code as a column to get your own row numbers
• Replace Custnmbr with a field from your view
•  row_number() over (order by dbo.RM00101.CUSTNMBR) * 1000 as RowNum,
• Create Sales Transaction Setup
• Create taxes  -False
• Freight Tax Amount - Local constant 0
• Misc Tax Amount - Local Constant 0
• Freight Taxable - nontaxable
• Misc taxable - nontaxable
• Tax Schedule - populate with a tax schedule
• Tax Amount - populate with total tax amount for the sop document
• Create Sales Line Item Setup
• use Line Sequence
• Tax Amount - populate with line tax amount
• All other tax related fields can be left blank
• Create Add Tax Line Detail Setup
• Use Line Sequence
• Sales Amount - Sales amount
• Freight Tax - Local constant 0
• Misc Tax - Local Constant 0
• Freight Amount - local constant 0
• Misc Amount - local constant 0
• Tax Detail ID - populate with a tax detail id
• Sales Tax - Put your custom amount of sales tax here ($10)
• The system will integrate the sop document, and all the lines, and hte tax detail, then replace the tax on the lines with your Line Sales Tax amount, and caculate the correct tax total on the Sales Invoice total
• The key is to use a sequence number, and set all of the values for the other taxes to 0 or a value if you have them.

SQL Agent - Script out all jobs

http://www.sqlmatters.com/Articles/Scripting%20all%20Agent%20Jobs%20Using%20SQL%20Server%20Management%20Studio%20(SSMS).aspx

• In SQL Management Studio, navigate to SQL Server Agent>Jobs
• Click View>Object Explorer Details
• Select All Jobs
• Right click>Script

eOne Extender - Lookups - Use SQL View Values in an Extender Lookup

• You need to have eOne Extender ENTERPRISE for this to work if you want to put SQL triggers within the extender solution.
• If you do not have extender enterprise, you can have this script run periodically from the sql job scheduler instead.
• Create a dummy lookup that you will populate lookup values into eventually (I use the Site Setup Table)
• Create a Trigger when you open the window that has your lookup field on it that will run a SQL script
• In your script, delete the values in the EXT00900 table corresponding to your dummy lookup
• In your script Insert all the values from your custom view using the dummy lookup name as the Extender_Lookup_ID, leave the dex_row_id blank, it will fill itself in

SCRIPT Example

--Clear Lookup Table values for dummy lookup

delete from EXT00900 where Extender_Lookup_ID = 'DUMMYLOOKUP'

--Insert Values from my own view

Insert into EXT00900 (Extender_Lookup_ID, STR30 ,UD_Form_Field_Desc)

Select 'DUMMYLOOKUP', Property, [Cost Center] from COSTCENTERVIEW where [Location Code] = 'S031'

Dynamics NAV 2016 LS Retail - System Requirements

https://msdn.microsoft.com/en-us/library/dd301054(v=nav.90).aspx#WinClient

Basic Minimum Requirements - Will allow for comfortable functioning and responsiveness

• Backoffice
• At Least Win 7 SP1 Pro 32-bit or 64-bit
• 4gb RAM
• POS
• At Least Win 7 SP1 Pro 64-bit
• 4gb RAM
• MUST Have functional drivers for peripherals (scanner, pole, cash drawer, receipt printer) for current OS
• Network
• AD is not required, but is beneficial
• 
  

SQL 2014 - SSRS and SQL has high CPU usage > 30% Memory usage over 500mb

• This is happening because you have imported an old report from a previous version of SSRS that is referencing a subreport or location that does not exist
• Open each on of your imported reports and check for any invalid subreport links and update them
• Edit each one of the old reports in the 2014 SSRS Editor and save them in the new editor
• You should notice the CPU usage drop immediately once you find the offending report

you can use this query to view the CPU performance of your SQL

https://mssqlwiki.com/2010/11/30/how-to-find-sql-server-and-system-cpu-usage-history/

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

DECLARE @ts_now bigint

SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks)  FROM sys.dm_os_sys_info

SELECT top 20 record_id, EventTime, 

  CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization, 

  CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization

FROM 

(

  SELECT 

    record.value('(Record/@id)[1]', 'int') AS record_id,

    DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,

    100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,

    record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 , 

    100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,

    record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2

  FROM (

    SELECT timestamp, CONVERT (xml, record) AS record 

    FROM sys.dm_os_ring_buffers 

    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'

      AND record LIKE '%<SystemHealth>%') AS t

) AS t

ORDER BY record_id desc

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

eOne Smartconnect - Integrating Purchase Orders - Subtotals don't match, Only single lines integrate

• http://www.eonesolutions.com/blog-post/tech-tuesday-smartconnect-purchase-order-map-error/
• 
  
• Run this to make the subtotal not required
• UPDATE SmartConnect..[Field] SET Required = 0 WHERE Node = 'taPoHdr' AND TechnicalName = 'SUBTOTAL'
• 
  
• Set PO Create to Updateifexists true
• Set PO Line to Updateifexists true
• But untick the Update existing on the map itself
• Add a line id field on your data source with unique lines
• Map this to Line Number
• Subtotal does not include vat
•