Tuesday, August 30, 2016

Thursday, August 25, 2016

SQL - Crosstab Queries


Use this SP for Dynamic Columns

You can use this technique for fixed columns


SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
(SELECT SalesPerson, Product, SalesAmount
FROM ProductSales ) ps
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
    • OR
    • Run the database maintenance and reload all of the FA objects

Tuesday, August 23, 2016

Copy a List of all files in a windows directory


  • 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

Monday, August 22, 2016

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.

Thursday, August 18, 2016

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


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

Wednesday, August 17, 2016

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
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
    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 
      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