Skip to main content

Notifications

Microsoft Dynamics GP forum
Suggested answer

Querying Great Plains using SQL Studio

Posted on by 5

Does anyone have experience pulling Great Plains data using SQL? Any helpful resources or documentation on how to get started would be much appreciated.

Categories:
  • Suggested answer
    Beat Bucher  GP Geek  GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,019 Moderator on at
    RE: Querying Great Plains using SQL Studio

    Aside from the generic views already provided by Microsoft, you can use the online resources on AzureCurve to identify the GP tables by their physical name and try to understand what their purpose is (and get fields names too).

    gptables.azurecurve.co.uk/

    The site has also tons of other useful resources BTW..

  • Suggested answer
    Joseph Markovich Profile Picture
    Joseph Markovich 3,863 on at
    RE: Querying Great Plains using SQL Studio

    I'll second Beat's recommendation of using Victoria Yudin's table guides on her blog. They are the BEST.

    Something else you can do in SQL Server Management Studio is connect to your GP company database, expand Views, and then look for all the views that have a nice name to them:

    Accounts
    AccountTransactions
    PayablesTransactions
    PurchaseOrders

    And on and on. These are the views created by Microsoft for SmartList, but you can also use them to do other reporting too. They have easy-to-read column names and easier-to-read data (example: instead of a number for a document type, the actual words will show instead) so you know what you're looking at. These are a good start too.

    I hope this helps you out.

    Joe

  • Suggested answer
    Beat Bucher  GP Geek  GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,019 Moderator on at
    RE: Querying Great Plains using SQL Studio

    If you want to start somewhere (aside of the suggested SDK & GPPT), you should have a look at Victoria Yudin's blog site, which holds a wealth of SQL code and GP table references.

    https://victoriayudin.com/

    Then there is also the GPUG community (paid membership) which you can browse for free during a 30 days trial membership.. Tons of useful infos and SQL scripts too. http://www.gpug.com

  • Suggested answer
    David Musgrave MVP GPUG All Star Legend Moderator Profile Picture
    David Musgrave MVP ... 13,831 Moderator on at
    RE: Querying Great Plains using SQL Studio

    As Richard mentioned, the SDK can help you understand the data model. It has E-R diagrams.

    Also there is a resource descriptions window in GP which provides data about tables, etc.

    Finally, I would look at GP Power Tools (http://WinthropDC.com/GPPT)  which has a very powerful Resource Information and Resource Finder window which can identify exactly where data is stored in the databases from the window fields.

    Regards

    David

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Querying Great Plains using SQL Studio

    I would start by installing the GP SDK which is located in  your GP install media. This will get you started.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Dynamics 365 Community Update – Sep 16th

Welcome to the next edition of the Community Platform Update. This is a weekly…

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,522 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,441 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Product updates

Dynamics 365 release plans