Tuesday, April 25, 2017

eOne Flexicoder - When some segments are set to "Do Nothing" blanks are used for the segments instead of the default account segments


  • This is happening because the defaults are being taken from the item or customer master record.
  • That specific item or customer card does not have a default account setup on it.
  • Review the accounts, and set defaults.
Posted by at No comments:

Monday, April 24, 2017

SQL - Determining the number of active connections on your SQL server (may be causing SQL to close connections if overloaded)

https://blogs.msdn.microsoft.com/developingfordynamicsgp/2014/04/22/more-on-sql-server-connection-issues-with-microsoft-dynamics-gp/




SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame

Posted by at No comments:

Wednesday, April 19, 2017

Dynamics GP - Unable to save to the batch activity table. You will not be able to use this batch.

Delete SY00800
Posted by at No comments:

Wednesday, April 12, 2017

Dynamics GP - Automatically Print separate remittance for check with more than 12 lines in remittance

This cannot be done, you must tick "Separate Remittance" when printing your checks.

Hi Ayana

In cases where there are too many lines to print on the remittance,when printing checks you need to
  • tick “Separate Remittance”
  • Print your checks as normal
  • When the screen comes up to post checks, click “process”
  • A new windows will open to prompt for a separate remittance. Select Remittance form, and print document, then click Process
  • This will prompt you to print the remittance separately.


To control the number of records displayed on the check stub

https://community.dynamics.com/gp/f/32/t/181070

Go to LAyout>Tools>Section Options

Make sure that in Report Writer, your "Remittance Header" has a "Records Per Report Body" set to 12, and that the "No Break At Record Count" checkbox is unmarked.
This is the default setup for check formats.


Posted by at No comments:

Tuesday, April 11, 2017

Dynamics GP - Stuck SOP documents - This order has already been posted

https://community.dynamics.com/gp/b/gplearn/archive/2009/12/14/sop-34-this-document-has-been-posted-34-error-38-resolution

----------------------------------------------------------------------
--Check which tables have data
----------------------------------------------------------------------
select * from sop10100 where sopnumbe = '11746'
select * from sop30200 where sopnumbe = '11746'
select * from sop30300 where sopnumbe = '11746'
------------------------------------------------------------------------
--If the transaction is not in the GL, it did not post
------------------------------------------------------------------------
select * from gl10000 where DTAControlNum = '11746'
select * from gl20000 where orctrnum = '11746'
select * from gl30000 where ORCTRNUM = '11746'
------------------------------------------------------------------------

Sometimes, when an sop transaction gets stuck or corrupted, you may not be able to reopen it.

Ensure that you update the CUSTNMBR, BACHNUMB and BCHSOURC fields to allow the transaction to be visible in the SOP lookup menu.


update sop10100 set custnmbr = 'AARON001',BACHNUMB = 'RECOVER', bchsourc = 'Sales Entry' where sopnumbe = 'ORD001'
Posted by at No comments:

Monday, April 10, 2017

Dynamics NAV - Write to text file in CAL

https://dynamicsuser.net/nav/f/developers/8867/best-way-tro-write-to-a-file?pi2079=1



FileMyHTML.TEXTMODE:=TRUE;
FileMyHTML.CREATE('C:\TEST\main.txt');
FileMyHTML.WRITE('<html>');
FileMyHTML.WRITE('');
FileMyHTML.WRITE('<head>');
FileMyHTML.WRITE('<title>My Page</title>');
FileMyHTML.WRITE('</head>');
FileMyHTML.WRITE('');
FileMyHTML.WRITE('<P>Hello world!</p>');
FileMyHTML.WRITE('');
FileMyHTML.WRITE('</html>');
FileMyHTML.CLOSE();

Variables
Name DataType Subtype Length
FileMyHTML File
Posted by at No comments:

Wednesday, April 5, 2017

SQL How to decrypt encrypted stored procedures

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e7056ca8-94cd-4d36-a676-04c64bf96330/decrypt-the-encrypted-store-procedure-through-the-tsql-programming-in-sql-server-2005?forum=transactsql




CREATE PROCEDURE dbo.ShowDecrypted(@ProcName SYSNAME = NULL)
AS
--Jon Gurgul 27/09/2010
--Adapted idea/code from shoeboy/joseph gama
SET NOCOUNT ON
IF EXISTS 
(
SELECT * FROM sys.dm_exec_connections ec JOIN sys.endpoints e 
on (ec.[endpoint_id]=e.[endpoint_id]) 
WHERE e.[name]='Dedicated Admin Connection' 
AND ec.[session_id] = @@SPID
)
 BEGIN

 DECLARE @i BIGINT,@a NVARCHAR(MAX),@b NVARCHAR(MAX),@d NVARCHAR(MAX),@c NVARCHAR(MAX)
 SET @a=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = OBJECT_ID(@ProcName) and [valclass] = 1 and [subobjid] = 1)
 SET @b='ALTER PROCEDURE '+ @ProcName +' WITH ENCRYPTION AS '+REPLICATE('-', 8000)

  BEGIN TRANSACTION
   EXECUTE (@b)
   SET @c=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = OBJECT_ID(@ProcName) and [valclass] = 1 and [subobjid] = 1) 
  ROLLBACK TRANSACTION

 SET @d = REPLICATE(N'A', (DATALENGTH(@a) /2 ))
 SET @i=1
 WHILE @i<=(DATALENGTH(@a)/2)
 BEGIN
 SET @d = STUFF(@d, @i, 1,NCHAR(UNICODE(SUBSTRING(@a, @i, 1)) ^(UNICODE(SUBSTRING('CREATE PROCEDURE '+ @ProcName +' WITH ENCRYPTION AS ' + REPLICATE('-', 8000), @i, 1)) ^UNICODE(SUBSTRING(@c, @i, 1)))))
 SET @i=@i+1
 END

 SELECT @d [StoredProcedure]

 END
 ELSE
 BEGIN
  PRINT 'Use a DAC Connection'
 END

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Posted by at No comments:

Dynamics GP - Stock Count Variance View

/****** Object:  View [dbo].[BI_INV_StockCountVariance]    Script Date: 17/08/2017 11:40:30 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*This view can be used to review and print completed stock counts before they have been posted.
-------------------------------------------------------------------*/
CREATE VIEW [dbo].[BI_INV_StockCountVariance]
AS
SELECT        dbo.IV10300.STCKCNTID, dbo.IV10300.STCKCNTDSCRPTN, dbo.IV10300.STCKCNTSTTS, dbo.IV10300.DOCDATE, dbo.IV00101.ITMCLSCD AS ItemClassCode, dbo.IV40400.ITMCLSDC AS ItemClassDesc,
                         dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.IV00101.CURRCOST, dbo.IV10301.LOCNCODE, dbo.IV10301.BINNMBR, dbo.IV10301.COUNTDATE, dbo.IV10301.DECPLQTY, dbo.IV10301.ITMTRKOP,
                         dbo.IV10301.IVIVINDX, dbo.IV10301.IVVARIDX, CASE WHEN VERIFIED = 1 THEN 'Yes' ELSE 'No' END AS Verified, dbo.IV40201.BASEUOFM, dbo.GL00100.ACTDESCR AS IVActDesc,
                         dbo.GL00105.ACTNUMST AS IVActNum, GL00100_1.ACTDESCR AS VarActDesc, GL00105_1.ACTNUMST AS VarActNum, dbo.IV10301.CAPTUREDQTY AS QtyOnHand,
                         dbo.IV10301.CAPTUREDQTY * dbo.IV00101.CURRCOST AS AmtOnHand, dbo.IV10301.COUNTEDQTY AS QtyCounted, dbo.IV10301.COUNTEDQTY * dbo.IV00101.CURRCOST AS AmtCounted,
                         dbo.IV10301.COUNTEDQTY - dbo.IV10301.CAPTUREDQTY AS VarQty, (dbo.IV10301.COUNTEDQTY - dbo.IV10301.CAPTUREDQTY) * dbo.IV00101.CURRCOST AS VarAmt
FROM            dbo.IV10300 INNER JOIN
                         dbo.IV10301 ON dbo.IV10300.STCKCNTID = dbo.IV10301.STCKCNTID LEFT OUTER JOIN
                         dbo.IV40400 INNER JOIN
                         dbo.IV40201 INNER JOIN
                         dbo.IV00101 ON dbo.IV40201.UOMSCHDL = dbo.IV00101.UOMSCHDL ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.IV10301.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
                         dbo.GL00105 AS GL00105_1 INNER JOIN
                         dbo.GL00100 AS GL00100_1 ON GL00105_1.ACTINDX = GL00100_1.ACTINDX ON dbo.IV10301.IVVARIDX = GL00100_1.ACTINDX LEFT OUTER JOIN
                         dbo.GL00105 INNER JOIN
                         dbo.GL00100 ON dbo.GL00105.ACTINDX = dbo.GL00100.ACTINDX ON dbo.IV10301.IVIVINDX = dbo.GL00100.ACTINDX

GO


Posted by at No comments:

Monday, April 3, 2017

SQL and EXCEL - How to get Sheet names from an Excel Workbook using SQL, then use those to cycle through each sheet. Import data from multiple excel sheets.


CREATE TABLE [dbo].[STFNAMES](
[TABLE_CAT] [nchar](10) NULL,
[TABLE_SCHEM] [nchar](10) NULL,
[TABLE_NAME] [varchar](50) NULL,
[Table_Type] [varchar](50) NULL,
[Remarks] [varchar](50) NULL
) ON [PRIMARY]

GO


exec sp_dropserver 'ExcelSource','droplogins'
exec sp_addlinkedserver 'ExcelSource','', 'Microsoft.ACE.OLEDB.12.0','E:\myfile.xlsx',NULL, 'Excel 12.0'
exec sp_addlinkedsrvlogin 'ExcelSource','false'
delete STFNAMES
insert into STFNAMES EXEC sp_tables_ex 'ExcelSource'
drop table STFNAMES2
select ROW_NUMBER() OVER(ORDER BY TableNm ASC) AS Rownum, TableNm into STFNAMES2 from (
select distinct left(right(table_name,len(Table_Name)-1),charindex('$',Table_Name)-1) as TableNm from stfnames where Table_Name not like '%Name%' and Table_Name not like '%Cases%') as Tbl
select * from STFNAMES2


Posted by at 1 comment:
Subscribe to: Posts (Atom)