Archive for the ‘SQL Server 2000’ Category
SQL Script for Vendor EFT Information
Hi all Today, I am posting a SQL Script for getting the EFT information of various vendors. /****************************************************************** Created July 16, 2011 by Sivakumar Venkataraman – Interdyn AKA This view returns a returns the details of the EFT Information for the various vendors setup in the company. Tables used: – PM00100 – Vendor Class [...]
July 16, 2011 · 
veeyeskay · 
2 Comments
Tags: EFT Information, SY06000, Vendor Address · Posted in: Accounts Payables, Dynamics, EFT For Payables, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005 Total Views: 179
SQL Script to Reconcile GL vs. AA Balances
Hi all Having used Analytical Accounting for quite some time now, I have come across various issues in Analytical Accounting module, which has caused serious data discrepancy between the General Ledger tables and the Analytical Accounting tables. So I had decided to write up a simple SQL script to reconcile the balances for every account [...]
May 16, 2010 · veeyeskay · 6 Comments
Tags: Account Balances, Analytical Accounting, General Ledger, Reconciliation · Posted in: Analytical Accounting, Dynamics, General Ledger, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008 Total Views: 1,772
SQL Script to delete stranded activity for a specific user
Hi all In this article I am providing a script to delete the activity table records for a specific stranded user in Dynamics GP. This will enable us not to have all the users log out of GP in order to execute these scripts. This is typically handy when we have many users signed into [...]
May 16, 2010 · veeyeskay · 5 Comments
Tags: ACTIVITY, DEX_LOCK, DEX_SESSION, DYNAMICS database, Stranded User, SY00800, SY00801, User Activity · Posted in: Dynamics, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, System Total Views: 1,440
SQL Script to provide access to Transaction Dimension Codes to Users
Hi all I had posted an article sometime back titled Macro to provide access to Transaction Dimension Codes to Users where I had provided a SQL script to create a macro code to provide access to all users to all transaction dimension codes setup in the company. I had received a few comments from user [...]
May 11, 2010 · veeyeskay · 10 Comments
Tags: Analytical Accounting, Transaction Dimension Codes, User Access · Posted in: Analytical Accounting, Dynamics, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008 Total Views: 682
SQL Script for Revaluation Process
Hi all During this week, I just thought of writing a simple SQL script to calculate the gain (or) loss for a particular currency/account combination based on a specific date that has been provided. The script has been written for the following assumptions. All accounts revalue at Period Balances Revaluation is done using an Exchange [...]
March 18, 2010 · veeyeskay · One Comment
Tags: Exchange Rate, Exchange Table, Multicurrency, Period Balances, Realized Gain, Realized Loss, Revaluation · Posted in: Dynamics, General Ledger, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008 Total Views: 706
Macro to provide access to Transaction Dimension Codes to Users
Hi all In this article, I am going to publish a SQL script which will be helpful to many using Analytical Accounting module. I have come across many clients who have multiple companies and they have hundreds of users spread across each of these companies. Assume the case when a new dimension code value has [...]
March 3, 2010 · veeyeskay · 6 Comments
Tags: Access, Analytical Accounting, Dimension Code, Dimension Code Access, Macro · Posted in: Analytical Accounting, Dynamics, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008 Total Views: 1,804
SQL View for Payables Transactions with AA Codes
Guys There is a smartlist object for AP transactions, but this smartlist does not show the analytical accounting information in it. So in this post, I have decided to post a SQL view to generate a view to show AP transaction information along with the analytical accounting information. IF EXISTS ( SELECT * FROM [...]
March 1, 2010 · veeyeskay · 11 Comments
Tags: AA Codes, Analytical Accounting, Dimension Codes, Payables Transactions, SQL View · Posted in: Accounts Payables, Analytical Accounting, Dynamics, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008 Total Views: 813
Handling Primary Key Errors in Analytical Accounting
Hi all… It has been some time, since I have posted something here… Was busy at many other assignments. However, here comes an article which will be of interest and use to many, especially those who are using Analytical Accounting module of Microsoft Dynamics GP. ‘How many times have we received the errors of primary [...]
December 23, 2009 · veeyeskay · 3 Comments
Posted in: Analytical Accounting, Dynamics, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008 Total Views: 1,266
SQL Code for GL Trial Balance – GP 10
Guys Today, I just decided to post a simple code snippet to generate a detailed GL Trial Balance with reference to the various sub ledgers, including the multi-dimensional information. IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N’[dbo].[vw_GLTrialBalancev10]‘) AND OBJECTPROPERTY(id, N’IsView’) = 1 ) DROP VIEW [dbo& Read the rest of this entry
April 26, 2009 · veeyeskay · 7 Comments
Tags: General Ledger, GL Trial Balance · Posted in: Dynamics, General Ledger, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008 Total Views: 1,895
String Parser SQL Function
Guys The post I am making now, can be one from a very old school of thought, but is a recurring problem, which the developers face, day in and day out, as they write SQL scripts. It is about parsing a value, which contains a single quote, before passing it into the SQL queries. Many [...]
April 20, 2009 · veeyeskay · No Comments
Posted in: SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008 Total Views: 338