|
|
Browse by Tags
All Tags » SQL Server Programming
-
If the end user created any kind of transactions in GP that holds let’s say the ID 9 and another one that holds 10, then deleted 9; the next number will be 11 and 9 will be ignored.
For the clients that do not accept these gaps, and since almost all of GP transaction tables have setups for loading next ID number, I have created the ...
-
Looking for a complete Vendor Statement that displays Posted and unposted transactions? Check the view below:----------POP UNPOSTED----------------------------SELECT'RECIEVING UNPOSTED' AS TRXSOURCE,DBO.POP10300.RECEIPTDATE AS DOCDATE,DBO.POP10300.VNDDOCNM AS ...
-
Script below loop database users then drop and recreate them one by one:DECLARE @UserName Varchar(500)DECLARE UsersCurr Cursor For Select Name FROM sysusers WHERE (islogin = 1) AND (name <> 'dbo') AND (name <> 'guest')Open UsersCurrFetch Next From UsersCurr Into @UserNameWHILE @@FETCH_STATUS = 0BEGINEXEC sp_revokedbaccess @UserNameEXEC ...
-
Interested in analyzing your items transaction? Check view below:SELECT dbo.SOP10100.DOCDATE,dbo.SOP10200.LOCNCODE as TRXLOCTN,CASE WHEN dbo.SOP10200.SOPTYPE = 4 THEN 'Return Unposted' When dbo.SOP10200.SOPTYPE = 3 Then 'Sales Unposted' end AS TrxType,dbo.SOP10200.SOPNUMBE as DOCNUMBR,dbo.SOP10200.ITEMNMBR,dbo.SOP10200.ITEMDESC,ISNULL((CASE WHEN ...
-
View below list the inventory items that have not sold between two dates:
SELECT IV00101.* FROM
dbo.IV00101 INNER JOIN
dbo.IV00102 ON dbo.IV00101.ITEMNMBR = dbo.IV00102.ITEMNMBR
WHERE
(dbo.IV00101.ITEMNMBR NOT IN
(SELECT
dbo.SOP30300.ITEMNMBR
FROM dbo.SOP30300
INNER JOIN dbo.SOP30200
ON dbo.SOP30300.SOPNUMBE = ...
-
To copy Microsoft Dynamics GP setup from company to another company DTS the following tables via SQLFinanceGL00100 Chart of Accounts Note Do not copy the GL00101 GL00102 Account Category Master GL00103 Fixed Allocation Accounts GL00104 Variable Allocation Accounts GL00105 Account Index Master CM00100 Checkbook Master CM40100 Cash ...
-
Why does Microsoft Dynamics GP encrypt passwords?
Check post below:
http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/02/why-does-microsoft-dynamics-gp-encrypt-passwords.aspx
-
Function to Convert Numbers To Arabic Words (Tafqeet):
You Can Call This Function By:
Select Tafkeet(9875)
Code Below:
CREATE FUNCTION dbo.Tafkeet (@TheNo numeric(18,3))returns varchar(1000) as beginif @TheNo <= 0 return 'zero'declare @TheNoAfterReplicate varchar(15)set @TheNoAfterReplicate = ...
-
How to search all columns of all tables in a database for a keyword?
BY http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
This procedure accepts a search string as input parameter, goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user created tables. System tables are excluded), owned by all ...
|
|
|