SQL 2008 R2 - Log Shipping Error - The log shipping secondary database TWO has restore threshold of 45 minutes and is out of sync

Problem:
This occurs when the logs are unable to be restored to the secondary database, and the secondary database falls out of sync or misses a log.

Solution:
Reconfigure log shipping completely.

Dynamics GP - How to reset all Average Costed Inventory Costs at a specific Historical Date

Adjust all stock out at that date to ensure your HITB reads 0

--Run this to identify all of the cost rows that need to be updated, change the date accordingly.
--copy the UpdOnHnd column, and run the scripts to update each row.

SELECT iv10200.qtyonhnd, 
       iv10200.itemnmbr, 
       iv10200.trxloctn, 
       iv10200.daterecd, 
       iv10200.rctseqnm, 
       iv10200.dex_row_id, 
       iv10200.unitcost, 
       'update iv10200 set qtyonhnd = 0 where dex_row_id = ' 
       + Cast(iv10200.dex_row_id AS VARCHAR(50)) AS UpdOnHnd, 
       'delete from see30303 where ITEMNMBR = ' 
       + '''' 
       + Rtrim(Cast(iv10200.itemnmbr AS VARCHAR(50))) 
       + ''''                                    AS DelHITB 
FROM   iv10200 
       INNER JOIN (SELECT itemnmbr, 
                          Max(expr1) AS MaxSeq 
                   FROM   (SELECT itemnmbr, 
                                  Max(rctseqnm) AS Expr1, 
                                  daterecd 
                           FROM   iv10200 AS IV10200_1 
                           GROUP  BY itemnmbr, 
                                     daterecd 
                          --                          HAVING ( daterecd <= CONVERT(DATETIME,  
                          --                                               '2014-06-30 00:00:00'  
                          --                                                , 102) ) 
                          ) AS atdate 
                   GROUP  BY itemnmbr) AS MaxLine 
               ON iv10200.itemnmbr = MaxLine.itemnmbr 
                  AND iv10200.rctseqnm = MaxLine.maxseq 
--WHERE  ( iv10200.qtyonhnd <> 0 )  



--If you HITB is showing 0 quantity, but +ve and -ve values, and you do not need the history
--Adjust the stock to 0
--Delete all HITB records for the item (Copy the DelHITB column)
delete from see30303 where ITEMNMBR = 'I0001'

Dynamics GP - Manufacturing - MO Status does not change to partially received or completed

This occurs when Post to GL for Inventory Transactions is not ticked.

Manufacturing Receipts do not consume raw materials from WIP if Post to GL for Inventory Transactions is not ticked,
Since the WIP is not consumed, the MO never detects a receipt, and the MO status never changes,

HOWEVER, the raw material stock and finished goods stock quantities are adjusted correctly.


Solution:
Tick Post to GL on the Inventory Transaction Posting setup.


Related Issues:
Receipt window shows values in qty to consume AND qty to backflush - This occurs because the initial partial receipt is not detected by manufacturing, and issued stock gets stuck in WIP.

Dynamics GP - SQL View - Item Class, Inventory Class Accounts

/****** Object:  View [dbo].[BI_InvClass]    Script Date: 09/20/2014 08:12:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_InvClass]
AS
SELECT     dbo.IV40400.ITMCLSCD, dbo.IV40400.ITMCLSDC, GL00105_1.ACTNUMST AS Inv, GL00105_10.ACTNUMST AS InvOffset, GL00105_2.ACTNUMST AS InvCOGS,
                      GL00105_3.ACTNUMST AS InvSls, GL00105_4.ACTNUMST AS InvMarkdown, GL00105_5.ACTNUMST AS SlsReturns, GL00105_6.ACTNUMST AS InvInUse,
                      GL00105_7.ACTNUMST AS InvInSvc, GL00105_8.ACTNUMST AS Damaged, GL00105_9.ACTNUMST AS InvVariance, GL00105_15.ACTNUMST AS DropShip,
                      GL00105_13.ACTNUMST AS PurchPriceVar, GL00105_14.ACTNUMST AS UnrPurchPriceVar, GL00105_12.ACTNUMST AS InvReturns,
                      GL00105_11.ACTNUMST AS AssemblyVar, dbo.GL00105.ACTNUMST AS StdCostReval
FROM         dbo.GL00105 AS GL00105_6 RIGHT OUTER JOIN
                      dbo.GL00105 AS GL00105_8 RIGHT OUTER JOIN
                      dbo.GL00105 RIGHT OUTER JOIN
                      dbo.IV40400 ON dbo.GL00105.ACTINDX = dbo.IV40400.IVSCRVIX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_11 ON dbo.IV40400.ASMVRIDX = GL00105_11.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_12 ON dbo.IV40400.IVRETIDX = GL00105_12.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_14 ON dbo.IV40400.UPPVIDX = GL00105_14.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_13 ON dbo.IV40400.PURPVIDX = GL00105_13.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_15 ON dbo.IV40400.DPSHPIDX = GL00105_15.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_9 ON dbo.IV40400.IVVARIDX = GL00105_9.ACTINDX ON GL00105_8.ACTINDX = dbo.IV40400.IVDMGIDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_7 ON dbo.IV40400.IVINSVIX = GL00105_7.ACTINDX ON GL00105_6.ACTINDX = dbo.IV40400.IVINUSIX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_5 ON dbo.IV40400.IVSLRNIX = GL00105_5.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_4 ON dbo.IV40400.IVSLDSIX = GL00105_4.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_3 ON dbo.IV40400.IVSLSIDX = GL00105_3.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_2 ON dbo.IV40400.IVCOGSIX = GL00105_2.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_10 ON dbo.IV40400.IVIVOFIX = GL00105_10.ACTINDX LEFT OUTER JOIN
                      dbo.GL00105 AS GL00105_1 ON dbo.IV40400.IVIVINDX = GL00105_1.ACTINDX

GO

Dynamics GP - SQL View - Connect GL to Inventory Notes

/****** Object:  View [dbo].[BI_GL_INV_Notes]    Script Date: 09/22/2014 13:04:18 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BI_GL_INV_Notes]'))
DROP VIEW [dbo].[BI_GL_INV_Notes]
GO

/****** Object:  View [dbo].[BI_GL_INV_Notes]    Script Date: 09/22/2014 13:04:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_GL_INV_Notes]
AS
SELECT     GL.JRNENTRY, dbo.SY03900.NOTEINDX AS InvNoteIndx, dbo.SY03900.TXTFIELD AS InvNote, IV.TRXSORCE, IV.IVDOCTYP, IV.DOCNUMBR, IV.DOCDATE,
                      IV.BCHSOURC, IV.BACHNUMB, IV.NOTEINDX, IV.GLPOSTDT, IV.SRCRFRNCNMBR, IV.SOURCEINDICATOR, IV.DEX_ROW_TS, IV.DEX_ROW_ID
FROM         (SELECT     TRXSORCE, IVDOCTYP, DOCNUMBR, DOCDATE, BCHSOURC, BACHNUMB, NOTEINDX, GLPOSTDT, SRCRFRNCNMBR, SOURCEINDICATOR,
                                              DEX_ROW_TS, DEX_ROW_ID
                       FROM          dbo.IV30200) AS IV INNER JOIN
                          (SELECT     JRNENTRY, NOTEINDX, ORCTRNUM
                            FROM          dbo.GL20000
                            UNION
                            SELECT     JRNENTRY, NOTEINDX, ORCTRNUM
                            FROM         dbo.GL30000) AS GL ON IV.DOCNUMBR = GL.ORCTRNUM LEFT OUTER JOIN
                      dbo.SY03900 ON IV.NOTEINDX = dbo.SY03900.NOTEINDX

GO

Dynamics GP - Limit item list to Items with BOMS only - Manufacturer, Manufacturing Item Number and Manufacturing Item Description

The inventory smartlist has the fields Manufacturer, Manufacturing Item Number and Manufacturing Item Description which are all blank. To populate these fields, you have to update the Manufacturing Item Number Maintenance screen, and mark a line as primary.

These additional fields are located in the Item Purchasing Options window if you click on the yellow gear next to the item number.

Data is stored in the IV00115

If you want to populate this table with current internal BOM listing, you can use this script. It will populate any manufacturing data for any items with boms that have no populated manufacturing data.


INSERT INTO IV00115
                      (ITEMNMBR, MANUFACTURER, MNFCTRITMNMBR, ItemDesc, PRIMARYITEM)
SELECT     BM010415.ITEMNMBR, 'Mycompany' AS comp, BM010415.ITEMNMBR AS Expr1, 'Manufactured' as Mf, 1 as p
FROM         BM010415 LEFT OUTER JOIN
                      IV00115 AS IV00115_1 ON BM010415.ITEMNMBR = IV00115_1.ITEMNMBR
WHERE     (IV00115_1.DEX_ROW_ID IS NULL)

Now create a smartlist that filters on the Mfg. Item Description = "Manufactured"
You can Now use this smartlist as a filter on the item lookup screen
You can set it as a default if you wish

Dynamics GP - Script to set all item engineering items to "Made" once a BOM exists for the item - "This is not a made item"

--Sets Items to "Make or Buy" and fulfil method "Make to Stock"
----------------------------------------------------------
update IVR10015 set EFFECTIVEDATE_I = '2017-01-01', ITEMSTATUS_I = 1, MAKEBUYCODE_I = 2, ITEMFULFILLMETHOD = 1 WHERE ITEMNMBR IN (SELECT ITEMNMBR FROM BM010415)

Dynamics GP - Company Setup and Posting Setup Tables - Set all reports to print to screen

SY03300 - Payment Terms master
SY04100 - Bank Master

SY02200 - Posting Print Setup Table

• ASECTMNT - Ask for destination
• --Suppress destination prompt
• update sy02200 set ASECTMNT = 0 where prtoscnt = 1
• PRNTJRNL - Print
• PRTOPRNT - Print to Printer
• --Do not print to printer
• update sy02200 set PRTOPRNT = 0 where prtoscnt = 1
• PRTOSCNT - Print to Screen
• --Set all reports to print to screen
• update sy02200 set prtoscnt = 1 where prtoscnt = 0
• PRTOFLNT - Print to File

SY02300 - Posting Setup Tables

• PTGENLED - Post to GL
• AUTPSTGL - Post Through GL
• ALTRXPST - Allow TRx Posting
• INCMCINF - Include Multicurrency
• UPSTDTFR - Posting date from: 0 - batch, 1 - trx

SSRS - How to hide the "ERROR: subreport could not be shown"

Problem:
ERROR: subreport could not be shown
shows up wherever the subreport doesn't get valid parameters

By default, SSRS will not render an empty report.

The error you are getting is due to the fact that the parameter you are passing to the subreport is an invalid parameter because you have setup Parameters to be taken from a query, thus limiting the list of valid parameters.

If you change the available values of the subreport parameter to "none" then the subreport will accept all inputs, and simply render blank instead of throwing the error.

Solution:
Change the "Available Values" option of your subreport parameter(s) to "none"

One Drive - How to reset One Drive

Original Article
http://its.uiowa.edu/support/article/102675

1. Go to the OneDrive cloud in the tray (bottom right hand corner - see above)
2. Right click and say Stop Syncing folder (it will give you a warning about permanently stopping this).  That is ok.
3. Go to c:\users\%username%\onedrive @ yourname
4. Rename the folder to onedrive@ yourname_old.  This will preserve the old data.
5. Shut down any Microsoft products (Outlook, Word, Excel, etc.)
6. Start Task Manager (Ctrl+Alt+Del)
7. End the following processes:  groove.exe, msosync.exe
8. Go to c:\users\%username%\AppData\Local\Microsoft\Office\SPW - delete everything in here.  Close the window.
9. Go to c:\users\%username%\AppData\Local\Microsoft\Office\15.0\OfficeFileCache - delete everything in here.  Close the window.
10. Restart your workstation. 
11. If the sync process does not start automatically, open a browser and go to onedrive.uiowa.edu.
12. Click on Sync and let it create a new sync folder
13. If there are files missing, you will have to go to your _old folder and copy the files to your new sync area.

eOne Smartconnect - an item with the same key has already been added

I got this error because a rounding account wasn't set to the correct account currency.

eOne Smartconnect - node tapmtransactioninsert not found in pmtransaction type

This error occurs if you have an incorrect key field set on the map and it cannot group the information correctly.
Check your key field and set it to a unique value.

Dynamics GP 2013 - Smartlist export rounds large numbers to thousands

http://www.boyerassoc.com/blog/dynamics-gp-2013-smartlist-export-error

Dynamics GP - Currency Tables

dynamics.dbo.MC00100 - Exchange Rate Master
dynamics.dbo.MC40200 - Currency Master