Home » SQL SCRIPTS
Top Websites
- Mark Polino's DynamicAccounting.net
Over 3800 resources listed. - Developing for Dynamics GP - By David Musgrave and the MS GP Dev Support Team
Over 700 resources listed. - Mariano Gomez at The Dynamics GP Blogster
Over 600 resources listed. - Mohammad Daoud's Dynamics GP Blog
Over 400 resources listed. - Dynamics GP Help
Over 400 resources listed. - Microsoft Dynamics Partner Community Blog
Over 400 resources listed. - Vaidy Mohan at Dynamics GP - Learn & Discuss
Over 400 resources listed. - Christina Phillips, Steve Endow & Lorren Zemke at Dynamics GP Land
Over 300 resources listed. - Frank Hamelly at GP2theMax
Over 200 resources listed. - About Dynamics, Development and Life
Over 200 resources listed. - eOne Business Solutions Blog
Over 200 resources listed. - Inside Microsoft Dynamics GP Official Blog
Over 200 resources listed. - VS Tools Forum
Over 100 resources listed.Your Resource for Visual Studio Tools for Dynamics GP - Inside Microsoft Dynamics GP Official Blog
Over 100 resources listed. - Janakiram M.P. at DynamicsBlogger
Over 100 resources listed. - Victoria Yudin's Dynamics GP Website
Over 100 resources listed.Victoria Yudin - Dynamics CPM
Over 100 resources listed. - Catherine Eibner MBS Developer Evangelist
Over 100 resources listed. - Sivakumar Venkataraman at Interesting Findings & Knowledge Sharing
Over 100 resources listed. - Rose Business Solutions Blog New
Over 100 resources listed. - Dynamics Small Business
Over 100 resources listed. - Belinda, The GP CSI
Over 100 resources listed. - Microsoft Dynamics GP UK Blog
Over 100 resources listed. - US Dynamics GP Field Team Blog
Over 100 resources listed. - Leslie Vail at Dynamics Confessor Blogspot
99 resources listed.
Useful Dynamics GP Links
Search all GP Blogs, Groups and Forums New!
GPWindow is Everything Dynamics GP. Try the Custom GP Search engine which searches high quality GP Blogs, and the Microsoft Dynamics GP Community Forums.
Parent Category: SQL SCRIPTS
This section has a collection of over 200 awesome Dynamics GP SQL scripts handed over free by masters of the software to the community. Experience the strength of the Microsoft Dynamics community in action. This section has got some really helpful scripts for the core GP modules like General Ledger, Analytical Accounting, Fixed Assets, Inventory Management, Payables and Receivables Management, SOP and POP and more… You can find many helpful SQL scripts like Posted/Unposted transactions related scripts , Chart of Accounts related scripts, Budget related scripts, Reconciliation related scripts and much more...
Categories
Links Sort by: Hits | References | Alphabetical
Identifying Duplicate Transactions
http://blogs.msdn.com/developingfordynamicsgp/archive/2008/12/05/identifying-duplicate-transactions.aspx
Recommended byhttp://blogs.msdn.com/developingfordynamicsgp/archive/2008/12/01/useful-sql-scripts-series.aspx
http://www.vaidy-dyngp.com/2008/12/davids-sql-series-identifying-duplicate.html
http://dynamicsgpblogster.blogspot.com/2008/12/sql-mania-series-at-developing-for.html
http://msdynamicsgp.blogspot.com/2008/12/identifying-duplicate-transactions.html
http://blogs.msdn.com/dynamicsgp/archive/2008/12/10/useful-sql-scripts-series.aspx
http://www.vaidy-dyngp.com/2008/12/davids-sql-series-identifying-duplicate.html
http://dynamicsgpblogster.blogspot.com/2008/12/sql-mania-series-at-developing-for.html
http://msdynamicsgp.blogspot.com/2008/12/identifying-duplicate-transactions.html
http://blogs.msdn.com/dynamicsgp/archive/2008/12/10/useful-sql-scripts-series.aspx
Useful SQL Scripts Series
http://blogs.msdn.com/developingfordynamicsgp/archive/2008/12/01/useful-sql-scripts-series.aspx
The posts in the series (so far) include:
- 01-Dec:…
Recommended byhttp://blogs.msdn.com/developingfordynamicsgp/archive/2008/12/01/spsearchonalldb-sql-stored-procedure-to-search-an-entire-database.aspx
http://victoriayudin.com/2008/12/01/useful-sql-scripts-series-from-developing-for-dynamics-gp/
http://www.vaidy-dyngp.com/2008/12/sql-series-from-david-musgrave-team.html
http://dynamicsgpblogster.blogspot.com/2008/12/sql-mania-series-at-developing-for.html
http://msdynamicsgp.blogspot.com/2008/12/calling-all-dynamics-gp-scrip-junkies.html
http://victoriayudin.com/2008/12/01/useful-sql-scripts-series-from-developing-for-dynamics-gp/
http://www.vaidy-dyngp.com/2008/12/sql-series-from-david-musgrave-team.html
http://dynamicsgpblogster.blogspot.com/2008/12/sql-mania-series-at-developing-for.html
http://msdynamicsgp.blogspot.com/2008/12/calling-all-dynamics-gp-scrip-junkies.html
spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database
http://blogs.msdn.com/developingfordynamicsgp/archive/2008/12/01/spsearchonalldb-sql-stored-procedure-to-search-an-entire-database.aspx
This stored procedure allows you to search all columns in all tables in a database for the occurrence of a particular string or value. Wild card characters such as those supported by the LIKE keyword can be used.
For example: _ to wild…
Recommended byhttp://blogs.msdn.com/developingfordynamicsgp/archive/2008/12/01/useful-sql-scripts-series.aspx
http://www.vaidy-dyngp.com/2008/12/sql-series-from-david-musgrave-team.html
http://dynamicsgpblogster.blogspot.com/2008/12/sql-mania-series-at-developing-for.html
http://blogs.msdn.com/dynamicsgp/archive/2008/12/10/useful-sql-scripts-series.aspx
http://www.vaidy-dyngp.com/2008/12/sql-series-from-david-musgrave-team.html
http://dynamicsgpblogster.blogspot.com/2008/12/sql-mania-series-at-developing-for.html
http://blogs.msdn.com/dynamicsgp/archive/2008/12/10/useful-sql-scripts-series.aspx
Using ORDER BY in a SQL Server 2005 or SQL Server 2008 View
http://dynamicsgpland.blogspot.com/2010/06/using-order-by-in-sql-server-2005-or.html
If you are a fan of SQL Server Views, you probably eventually noticed a change that occurred in SQL Server 2005: Views no longer honored the ORDER BY clause.
With SQL Server 2000, ORDER BY clauses were 'sort of' honored, as you could use the TOP 100 PERCENT clause to pacify SQL 2000 and get your results in the requested order.
But with SQL Server 2005, the TOP 100 PERCENT clause no longer worked. SQL purists would claim that SQL Views should not be ordered, and technically, I can understand that argument.
But I don't care about those technical or purist arguments. I use SQL Server as a business tool, whose goal is to deliver data to business users and business owners in any form they want--not what is technically correct according to database engine…
With SQL Server 2000, ORDER BY clauses were 'sort of' honored, as you could use the TOP 100 PERCENT clause to pacify SQL 2000 and get your results in the requested order.
But with SQL Server 2005, the TOP 100 PERCENT clause no longer worked. SQL purists would claim that SQL Views should not be ordered, and technically, I can understand that argument.
But I don't care about those technical or purist arguments. I use SQL Server as a business tool, whose goal is to deliver data to business users and business owners in any form they want--not what is technically correct according to database engine…
Posted by
Recommended byhttp://msdynamicsgp.blogspot.com/2010/06/using-order-by-in-sql-server-2005-or.html
http://www.vaidy-dyngp.com/2010/06/how-to-simulate-top-100-percent-in-sql.html
http://msdynamicsgp.blogspot.com/2010/06/using-order-by-in-sql-server-2005-or.html?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+DynamicAccounting+%28Dynamic+Accounting%29
http://www.vaidy-dyngp.com/2010/06/how-to-simulate-top-100-percent-in-sql.html
http://msdynamicsgp.blogspot.com/2010/06/using-order-by-in-sql-server-2005-or.html?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+DynamicAccounting+%28Dynamic+Accounting%29
Running SQL commands against all GP Company Databases
http://blogs.msdn.com/developingfordynamicsgp/archive/2008/11/03/running-sql-commands-against-all-gp-company-databases.aspx
Last week, I posted the article below explaining how you can use the osql.exe command to run a script against all Microsoft Dynamics GP company databases. The method described in this post is best if you have a *.sql script file to execute against each company database:
Running a SQL Script against all GP Company Databases
I have seen some discussions on the newsgroups about the sp_MSforeachdb system stored procedure and so decided to elaborate on this subject.
If you are already logged into SQL Server Query Analyzer…
How to split Dynamics GP's alphanumeric column values in SQL Server
http://dynamicsgpblogster.blogspot.com/2008/11/how-to-split-dynamics-gps-alphanumeric.html
Searching for strings in SQL Server using LIKE
http://victoriayudin.com/2009/03/02/searching-for-strings-in-sql-server-using-like/
SQL-mania series at Developing for Dynamics GP 
David Musgrave unleashes his toolbox of useful SQL scripts in his traditional Developing for Dynamics GP blog. David will address this topic in a series of articles that you must be sure to check out.
The articles will be released in the following order:
Mon, 12/01/2008: spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database
Wed, 12/03/2008: Releasing Stuck Batches and Transactions without exiting all Users
Fri, 12/05/2008: Identifying…
http://dynamicsgpblogster.blogspot.com/2008/12/sql-mania-series-at-developing-for.html
David Musgrave unleashes his toolbox of useful SQL scripts in his traditional Developing for Dynamics GP blog. David will address this topic in a series of articles that you must be sure to check out.
The articles will be released in the following order:
Mon, 12/01/2008: spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database
Wed, 12/03/2008: Releasing Stuck Batches and Transactions without exiting all Users
Fri, 12/05/2008: Identifying…
Posted by
Create your own taGetNextNumber stored procedure
http://dynamicsgpblogster.blogspot.com/2008/07/make-your-own-sptagetnextnumber-stored.html
I have heard this one very often on developers and users newsgroups alike. In past releases of eConnect (8.0 and before) it was not possible to retrieve the next document number for a few existing transactions in the system via eConnect's COM interface, case in point the Purchasing Receipt Transaction. However, the problem is not limited to eConnect. Many developers have tried to retrieve transactions next document numbers…
Posted by
SQL Query Optimization
http://dynamicsgpland.blogspot.com/2009/09/sql-query-optimization.html
If you are a Dynamics GP developer in the US, I'm sure you're looking forward to the upcoming holiday weekend so that you can sit down at your computer and relax by reading articles on the SQL Server Performance web site.
No??? So it IS only just me??? (blush)
I discovered the web site several years ago when I was working on a pretty challenging project. My client was receiving detailed billing data from a vendor and had asked me to develop a routine to validate the transactions against data in their database before importing the voucher into Dynamics GP. The challenge was that the vendor was sending over 250,000 records each week, and the single table in the client's operational system database was 2 terabytes.
So there I was, trying to do a JOIN of 250,000 records against a 2TB table. Not pretty. My initial query would just run, and run, and run, while hogging CPU and memory on the server. That's when I stumbled on the SQL Server Performance web site. I optimized my JOINs, I added more WHERE clauses, tweaked my indexes, and I learned all about "non-sargable" WHERE clauses. My eternal query eventually took less than 10…
No??? So it IS only just me??? (blush)
I discovered the web site several years ago when I was working on a pretty challenging project. My client was receiving detailed billing data from a vendor and had asked me to develop a routine to validate the transactions against data in their database before importing the voucher into Dynamics GP. The challenge was that the vendor was sending over 250,000 records each week, and the single table in the client's operational system database was 2 terabytes.
So there I was, trying to do a JOIN of 250,000 records against a 2TB table. Not pretty. My initial query would just run, and run, and run, while hogging CPU and memory on the server. That's when I stumbled on the SQL Server Performance web site. I optimized my JOINs, I added more WHERE clauses, tweaked my indexes, and I learned all about "non-sargable" WHERE clauses. My eternal query eventually took less than 10…
Posted by
SQL User Defined Functions
Have you ever written a SQL query only to come across a relatively small requirement that suddenly makes the query more complex than you want?
In the last few months, it's happened to me twice, but fortunately I was able to utilize a User Defined Function (UDF) to meet the requirement while simplifying my query.
The first project involved exporting data from GP. It had a requirement to split the GP SOP Order number into two fields--the alpha prefix in one field, and the numeric suffix in the other. Well, because there were different SOP order types involved, the alpha prefixes had different lengths, as did the numeric suffixes, and to top it off, some of the order numbers had a dash separating the prefix and suffix, while others did not. Examples:
ORD0000095
WAR-1025
SP001024
CONS-2933
So, how do you split a field value 'in half' when you don't know where one half ends and the other begins? Unfortunately, SQL 2005 still does not have native Regular Expression capabilities. There are some functions that provide some limited character searches (PATINDEX, CHARINDEX), but they don't allow you to search for a pattern or range like RegEx.
After doing some research, I found several references to using a .NET CLR assembly to make Regular Expressions available within SQL statements. While this approach was very appealing, it was more complicated than I wanted, as it would require a change to SQL Server security to allow assemblies to run, and would introduce CLR into the mix, something that is not typically used in GP environments.
Since I was responsible for assisting with deployment of the solution in a remote client environment, I needed something very something simple and self contained in standard SQL--the fewer pieces, the better. So I began to consider the possibility of using a cursor to loop through each character of the order number to determine where the prefix ended, and the suffix began. I'm very reluctant to use cursors in SQL unless absolutely necessary because they typically can't perform nearly as well as a set-based solution, but given my constraints, it seemed…
http://dynamicsgpland.blogspot.com/2008/11/sql-user-defined-functions.html
Have you ever written a SQL query only to come across a relatively small requirement that suddenly makes the query more complex than you want?
In the last few months, it's happened to me twice, but fortunately I was able to utilize a User Defined Function (UDF) to meet the requirement while simplifying my query.
The first project involved exporting data from GP. It had a requirement to split the GP SOP Order number into two fields--the alpha prefix in one field, and the numeric suffix in the other. Well, because there were different SOP order types involved, the alpha prefixes had different lengths, as did the numeric suffixes, and to top it off, some of the order numbers had a dash separating the prefix and suffix, while others did not. Examples:
ORD0000095
WAR-1025
SP001024
CONS-2933
So, how do you split a field value 'in half' when you don't know where one half ends and the other begins? Unfortunately, SQL 2005 still does not have native Regular Expression capabilities. There are some functions that provide some limited character searches (PATINDEX, CHARINDEX), but they don't allow you to search for a pattern or range like RegEx.
After doing some research, I found several references to using a .NET CLR assembly to make Regular Expressions available within SQL statements. While this approach was very appealing, it was more complicated than I wanted, as it would require a change to SQL Server security to allow assemblies to run, and would introduce CLR into the mix, something that is not typically used in GP environments.
Since I was responsible for assisting with deployment of the solution in a remote client environment, I needed something very something simple and self contained in standard SQL--the fewer pieces, the better. So I began to consider the possibility of using a cursor to loop through each character of the order number to determine where the prefix ended, and the suffix began. I'm very reluctant to use cursors in SQL unless absolutely necessary because they typically can't perform nearly as well as a set-based solution, but given my constraints, it seemed…
Posted by
Weekly Dynamic: Copy Headings and Data from SQL
http://msdynamicsgp.blogspot.com/2009/10/weekly-dynamic-copy-headings-and-data.html
Doing data validation, database compares and other queries in SQL Server is a pretty common occurrence when working with Dynamics GP, especially during implementation. Often this involves running a SQL query in Management Studio and pasting the…
Formatting Dynamics GP Phone Numbers in a SQL Query
http://dynamicsgpland.blogspot.com/2010/03/formatting-dynamics-gp-phone-numbers-in.html
I'm sure this has been addressed thousands of times over the years, but since I just had to write the script, I thought I would post it here for posterity.
By default, Dynamics GP stores phone numbers as a long string of zeroes. This format is based on the GP Format Definition assigned to phone number fields in GP.
I recently had to write a SQL query that would be used to export vendor information to a CSV…
By default, Dynamics GP stores phone numbers as a long string of zeroes. This format is based on the GP Format Definition assigned to phone number fields in GP.
I recently had to write a SQL query that would be used to export vendor information to a CSV…
Posted by
Date & Time fields in GP Builders
http://gpbuilders.wordpress.com/2010/07/26/date-time-fields-in-gp-builders/
Typically – most fields in GP are Date fields. GP doesn’t have many fields where time is…
Posted by
Useful SQL Scripts
http://mohdaoud.blogspot.com/2010/01/useful-sql-scripts.html
I found this post @ GP4Arab blog, download the scripts file here, below are the covered scripts:
1. How to display the details of an order
2. How to Reset System Password
3. Find Table Size of the Database
4. List all Employee names, with department and job title and codes
5. A quick way to duplicate or copy a GP dynamics PRICELEVEL SET
6. Global find and replace command to update inventory item descriptions
7. Batch update…
Posted by
Collection of MICROSOFT GREAT PLAINS DYNAMICS (GP) Sql Scripts, Tips and Tricks
http://greatplainsguy.blogspot.com/2010/01/collection-of-microsoft-great-plains.html
Some Great Great Plains SQL Scripts
http://greatplainsguy.blogspot.com/2008/02/some-great-great-plains-sql-scripts.html
Creating Price Level Based On Existing Price Level
http://msdynamicsgp.blogspot.com/2010/06/creating-price-level-based-on-existing.html?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+DynamicAccounting+%28Dynamic+Accounting%29
Useful Dynamics SQL scripts
http://www.rosebizinc.com/gpblog/2009/03/useful-dynamics-sql-scripts.html
Searching on SQL Server using the LIKE command
http://blogs.msdn.com/developingfordynamicsgp/archive/2009/03/05/searching-on-sql-server-using-the-like-command.aspx
Latest links
- Server Error in Application in Project Time and Expense in Business Portal for Microsoft Dynamics GP
- Another great Dynamics GP customer story
- IntellPartners GSD Team enjoys dinner with celebrities
- SSRS: GL Trial Balance Summary report returns no data - Follow Up
- Dynamics GP - Learn & Discuss: Customer Credit Summary: Average Days To Pay
- Company Integration - Management Reporter 2012 Feature Highlight - Dynamics Corporate Performance Management
- The eOne Dynamics GP and CRM Blog: Tech Tuesday: Moving Extender Fields
- Script History Table and Procedures - Microsoft Dynamics GP DBA - GP Technical Blogs - Microsoft Dynamics Community
- How to correct errors in the PM00400 keys master table in MS Dynamics GP. | MS Dynamics GP
- The Dynamics GP Blogster: SSRS: GL Trial Balance Summary report returns no data - Follow Up
GPWindow.com
Developed and presented by
Smith & Allen Consulting, Inc.,
GP specialists since 1991.