Browse by Tags

All Tags » SQL Server Programming   (RSS)
10 December 2008
Identifying Duplicate Transactions
By David Musgrave http://blogs.msdn.com/developingfordynamicsgp/archive/2008/12/05/identifying-duplicate-transactions.aspx Because Microsoft Dynamics GP is designed to have separate tables for WORK, OPEN and HISTORY transactions, there are times where Read More...
0 Comments  
10 December 2008
Useful SQL Scripts Series
Guys, David started his new piece, he’s working on SQL Scripts package that's needed to complement GP environment, checkout his blog below: http://blogs.msdn.com/developingfordynamicsgp/archive/2008/12/01/useful-sql-scripts-series.aspx?CommentPosted=true#commentmessage Read More...
0 Comments  
10 December 2008
Search all columns of all tables for a given search string and replace it with another string
Guys, By: Narayana Vyas Kondreddi My colleague and best friend Areej Neshewat just sent me the following stored procedure for knowledge sharing. This procedure replaces any kind of strings in the database with other. if exists (select * from dbo.sysobjects Read More...
0 Comments  
05 December 2008
How To Delete GP Company
I got allot of questions on how to delete company from GP, script below is the answer: CAUTION: Do not delete the company from Enterprise Manager first (step 2). If this is done, you will need to run SQL scripts to delete database tables individually. Read More...
0 Comments  
11 November 2008
Filling Missing Transactions Numbers in GP
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 Read More...
0 Comments  
11 November 2008
Vendor Statement For GP
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 Read More...
0 Comments  
11 November 2008
Drop and recreate database Logins
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 UsersCurr Fetch Read More...
0 Comments  
11 November 2008
Item Transactions Analysis
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, Read More...
0 Comments  
11 November 2008
List of inventory items that have not sold between two dates
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 Read More...
0 Comments  
11 October 2008
Copy Microsoft Dynamics GP Setup
To copy Microsoft Dynamics GP setup from company to another company DTS the following tables via SQL Finance GL00100 Chart of Accounts Note Do not copy the GL00101 GL00102 Account Category Master GL00103 Fixed Allocation Accounts GL00104 Variable Allocation Read More...
0 Comments  
06 October 2008
Why does Microsoft Dynamics GP encrypt passwords?
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 Read More...
0 Comments  
30 June 2008
Convert Numbers To Arabic Words (Tafqeet)
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 begin if @TheNo <= 0 return 'zero' declare @TheNoAfterReplicate Read More...
0 Comments  
30 June 2008
How to search all columns of all tables in a database for a keyword?
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 Read More...
0 Comments