Page 3 of 1612345678910...Last »

Quick Tip: Debugging Visual Studio Tools – Developing For Dynamics GP

Awesome tip up there on Developing For Dynamics GP blog on how to debug VS Tools For Dynamics GP.

I am late in blogging about this post. But NOW is the time, as this post just saved my 1/2 day. Seriously. Not kidding. I was stuck with one SQL query execution. There were records to be retrieved, but my code didn’t. After 5-10mins of frustrated troubleshooting, I remembered this post. I should have done that immediately then, but I didn’t.

I setup my VS Tools project to launch GP as instructed on this post and within 2 minutes I could find the issue.

This one deserves to be there on Microsoft Dynamics GP Cookbook (technical).

VAIDY

Filed under: Customizations, Debugging, GP Administration, SQL, SQL Profiler, Support, Tools, Troubleshooting, Visual Studio Tools, VS Tools

More here:
Quick Tip: Debugging Visual Studio Tools – Developing For Dynamics GP

July 30, 2013 · Vaidyanathan Mohan · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 144

Developing with the Microsoft Dynamics GP 2013 Named System Database

David Meego - Click for blog homepageMicrosoft Dynamics GP 2013 introduced another long sought after feature….
The Named System Database.

Instead of the System database being hardcoded to DYNAMICS, a different name can be selected when installing GP.

So what does that mean? Well, it means that more than one Dynamics GP system can reside on a single instance of SQL Server. Before when a second Dynamics GP system was needed, you would have to install a new instance of SQL Server. Now you can have multiple System Databases on a single server without needing additional instances of SQL Server.

For hosting partners, it means that they can host multiple clients off a single SQL Server instance. As a side benefit for consultants supporting multiple versions and multiple customers, it means no longer having to have lots of SQL Server instances on their machines

As a developer, how can I make sure my code uses the correct System Database for my instance?

From a Dexterity perspective it means updating your code to use a call to the function GetSystemDatabaseName() rather than using the constant SQL_SYSTEM_DBNAME to obtain the name of the System Database.

If you have code shared between previous versions of Dynamics, you can create your own function which has conditionally compiled code based on the value of a “version” constant. See the code below for an example:

Code MBS_GetSystemDatabaseName()

function returns string sSystemDBName;
optional in boolean fConnected = true;

#if MBS_PROD_MAJ >= 12 then
    sSystemDBName = GetSystemDatabaseName(fConnected);
#else
    sSystemDBName = SQL_SYSTEM_DBNAME;
#end if

 

But how can you write SQL code which can run against a company database, but reference data in the System Database?

You can’t just prefix the table names with “DYNAMICS..” anymore as that might not be the correct name for the System Database.

I discussed this issue with Patrick Roth who pointed out that the GP 2013 company databases now have a new sySystemDB (SY00100) table with a single record in it that provides the Company ID and the name of the System Database. So if you need to know the Company ID of the current company database or the name of the System Database, just get the first record in the table (ie. from Dexterity: get first table sySystemDB;). 

From SQL, try the query below to see the table.

select top 1 * from SY00100

So armed with this knowledge I created some code to build a query as a string to return some data from a table in the System Database. Then I used the exec() SQL function to call it. This works, but how can I return the value from the pass through SQL code to my script? The exec() function cannot do this, it has no mechanism to pass data back to the calling script.

I then asked my friend and SQL guru, Robert Cavill, “How you can execute pass through SQL code AND return data from the code?”. He told me about the sp_executesql stored procedure which allows for parameters to be defined and passed to and from the pass through SQL code.

The final piece to the puzzle was identifying whether to use the hardcoded “DYNAMICS” (for pre GP 2013) or to use the data in the SY00100 table for the System Database name. This was fairly easy to solve as the SY00100 table will only exist in a company database from GP 2013 or later.

The code below should be executed in the context of a company database. It will identify the associated System Database and then look at the Company Master table and return whether the Use Security Checkbox in the Company Setup window is selected:

SQL Code Example

– Define Variables
declare @UseSecurity INT
declare @SQLCode NVARCHAR(4000)
declare @SYSDBNAME CHAR(80)

– Identify System Database Name
if exists (select * from dbo.sysobjects where id = object_id(N’dbo.SY00100′) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
 select top 1 @SYSDBNAME = DBNAME from SY00100
else
 set @SYSDBNAME = ‘DYNAMICS’

– Define and Execute pass through SQL Code
set @SQLCode = N’select @pUseSecurity = USESCRTY from ‘ + rtrim(@SYSDBNAME) + ‘..SY01500 (nolock)
     where INTERID = DB_NAME()’
exec sp_executesql @SQLCode, N’@pUseSecurity int output’, @UseSecurity output

– Display result from Variable
select @UseSecurity AS UseSecurity

/* Copyright © Microsoft Corporation.  All Rights Reserved.                     */
/* This code released under the terms of the                                    */
/* Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.) */

 

 

I hope you find these techniques useful. I know I did. In my next post you will see why….

David

More:
Developing with the Microsoft Dynamics GP 2013 Named System Database

July 26, 2013 · David Musgrave · No Comments
Tags: , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 215

SQL view for sales quantities by item by site by month in Dynamics GP

As many variations for item quantity summaries as I think I have posted on this blog, there is always another one to be had. A request from a reader brings us a monthly version of my Sales Quantities by Item by Year. The view below shows the total item quantity sold by site by month for a hard-coded year. I am making a number of assumptions (listed in the view comments in green), and there is also an overall yearly total column at the end. You can easily change the year as needed on line 64.

Some additional resources:

create view view_Sales_Qty_by_Item_Site_Month
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
--view_Sales_Qty_by_Item_Site_Month
--Created Jun 14, 2013 by Victoria Yudin - Flexible Solutions Inc
--For updates see http://victoriayudin.com/gp-reports/
--Returns total sales quantities fulfilled (SOP invoices less
--  returns) for each item by month for hardcoded year
--Calendar months and Document Dates are used
--Only posted invoices and returns are included
--Quantity is calculated by multiplying by QTYBSUOM column in
--  case other UofM's are used on transations
--Voided transations are excluded
--Item Description is taken from Inventory Item Maintenance
--  for all inventory items
--  and from SOP line items for non-inventory items
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
d.ITEMNMBR Item_Number, d.Item_Description,
d.LOCNCODE Site_ID,
sum(case when month(d.DOCDATE) = 1
    then d.Qty else 0 end) as Jan_Qty,
sum(case when month(d.DOCDATE) = 2
    then d.Qty else 0 end) as Feb_Qty,
sum(case when month(d.DOCDATE) = 3
    then d.Qty else 0 end) as Mar_Qty,
sum(case when month(d.DOCDATE) = 4
    then d.Qty else 0 end) as Apr_Qty,
sum(case when month(d.DOCDATE) = 5
    then d.Qty else 0 end) as May_Qty,
sum(case when month(d.DOCDATE) = 6
    then d.Qty else 0 end) as Jun_Qty,
sum(case when month(d.DOCDATE) = 7
    then d.Qty else 0 end) as Jul_Qty,
sum(case when month(d.DOCDATE) = 8
    then d.Qty else 0 end) as Aug_Qty,
sum(case when month(d.DOCDATE) = 9
    then d.Qty else 0 end) as Sep_Qty,
sum(case when month(d.DOCDATE) = 10
    then d.Qty else 0 end) as Oct_Qty,
sum(case when month(d.DOCDATE) = 11
    then d.Qty else 0 end) as Nov_Qty,
sum(case when month(d.DOCDATE) = 12
    then d.Qty else 0 end) as Dec_Qty,
sum(d.Qty) Total_Qty

from
(select sh.DOCDATE, sd.ITEMNMBR, sd.LOCNCODE,
 coalesce(I.ITEMDESC, sd.ITEMDESC) Item_Description,
 case sd.SOPTYPE
     when 3 then sd.QTYFULFI*QTYBSUOM
     when 4 then sd.QUANTITY*QTYBSUOM*-1
     end Qty
 from SOP30200 sh
 inner join SOP30300 sd
     on sd.SOPNUMBE = sh.SOPNUMBE
     and sd.SOPTYPE = sh.SOPTYPE
 left outer join IV00101 I
     on I.ITEMNMBR = sd.ITEMNMBR
 where sh.VOIDSTTS = 0
     and sh.SOPTYPE in (3,4)
	 and year(sh.DOCDATE) = 2013 --change year as needed
     and sd.ITEMNMBR not like 'XXXXXXXXXXXXXXX%') d

group by d.ITEMNMBR, d.Item_Description, d.LOCNCODE

go
grant select on view_Sales_Qty_by_Item_Site_Month to DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

Filed under: Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code Tagged: featured, GP Reports code, Sales Order Processing, SQL code

See more here:
SQL view for sales quantities by item by site by month in Dynamics GP

June 14, 2013 · Victoria Yudin · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 272

SQL view for SOP sales by customer by month

Someone asked me for code to get monthly SOP sales and I could have sworn I already had that. But I searched and didn’t come up with anything, so here is a new view to show SOP sales (the total of SOP invoices less returns) by month. As usual, I am making a few assumptions (listed in the view comments), and am hard coding one year at a time (2013 in this example). You can easily change the year as needed on line 59.

create view view_Sales_by_Customer_by_Month
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Sales_by_Customer_by_Month
-- Created June 7, 2013
--    by Victoria Yudin - Flexible Solutions, Inc.
-- For updates see http://victoriayudin.com/gp-reports/
-- Returns total sales (invoices - returns) for each customer
--    by month (for the specified year)
-- Amount used is the invoice total (including freight,
--    taxes, discounts, etc.)
-- Only posted invoices and returns are included
-- Shows functional currency
-- Voided transactions are excluded
-- Document Date is used (not GL Posting Date)
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
d.CUSTNMBR Customer_ID,
d.CUSTNAME Customer_Name,
sum(case when month(d.DOCDATE) = 1
  then d.SALES else 0 end) Jan_Sales,
sum(case when month(d.DOCDATE) = 2
  then d.SALES else 0 end) Feb_Sales,
sum(case when month(d.DOCDATE) = 3
  then d.SALES else 0 end) Mar_Sales,
sum(case when month(d.DOCDATE) = 4
  then d.SALES else 0 end) Apr_Sales,
sum(case when month(d.DOCDATE) = 5
  then d.SALES else 0 end) May_Sales,
sum(case when month(d.DOCDATE) = 6
  then d.SALES else 0 end) Jun_Sales,
sum(case when month(d.DOCDATE) = 7
  then d.SALES else 0 end) Jul_Sales,
sum(case when month(d.DOCDATE) = 8
  then d.SALES else 0 end) Aug_Sales,
sum(case when month(d.DOCDATE) = 9
  then d.SALES else 0 end) Sep_Sales,
sum(case when month(d.DOCDATE) = 10
  then d.SALES else 0 end) Oct_Sales,
sum(case when month(d.DOCDATE) = 11
  then d.SALES else 0 end) Nov_Sales,
sum(case when month(d.DOCDATE) = 12
  then d.SALES else 0 end) Dec_Sales,
sum(d.SALES) Yearly_Total

from
(select s.DOCDATE, s.CUSTNMBR, c.CUSTNAME,
 case s.SOPTYPE
   when 3 then s.DOCAMNT
   when 4 then s.DOCAMNT*-1
   end SALES
 from SOP30200 s
 left outer join RM00101 c
   on s.CUSTNMBR = c.CUSTNMBR
 where s.VOIDSTTS = 0
   and s.SOPTYPE in (3,4)
   and year(s.DOCDATE) = 2013 --change year as needed
 ) d

group by d.CUSTNMBR, d.CUSTNAME

-- add permissions for DYNGRP
go
grant select on view_Sales_by_Customer_by_Month to DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

Filed under: Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code Tagged: featured, GP Reports code, GP SQL view, Sales Order Processing

Read More:
SQL view for SOP sales by customer by month

June 7, 2013 · Victoria Yudin · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 161

Assign sequential numbers in SQL Server

Here is a cool little SQL Server tip from the April GP Reports Viewer newsletter. Say you have a list of rows and you want to assign sequential numbers to them, so that you always know which one is first, second, etc. Or so that you can display line numbers. This is often a need for Dynamics GP Sales Order Processing (SOP) transactions because they use line item sequence numbers that are very spread out to allow for inserting rows in-between them. (More detail on this from Dynamics GP MVP Mariano Gomez.)

Here is an example of an SOP transaction in GP:SOP Transaction example

Using the following SQL code you can see this data in SQL:

select SOPTYPE, SOPNUMBE, LNITMSEQ, ITEMNMBR
from SOP10200 where SOPNUMBE = 'ORDST2227'

The results look like this:

SQL Results - original

The LNITMSEQ column is what determines the order of the lines. If you wanted to know which line is 1st, 2nd, 3rd, that would be a little difficult. The code below will add a sequential line number to this data using the line item sequence to determine the order of the lines:

select
row_number() over (partition by SOPNUMBE, SOPTYPE
 order by LNITMSEQ) LineNumber,
SOPTYPE, SOPNUMBE, LNITMSEQ, ITEMNMBR
from SOP10200
where SOPNUMBE = 'ORDST2227'

Now your results will include a line number column:
SQL Final Results

For more tips like this sign up for the GP Reports Viewer newsletters. You can see past newsletters on the GP Reports Viewer website.

Filed under: Dynamics GP, GP Reports code, GP Reports Viewer, SOP SQL code, SQL Server Tagged: Dynamics GP, featured, GP Reports code, GP Reports Viewer, Sales Order Processing, SQL code

See the original article here:
Assign sequential numbers in SQL Server

May 7, 2013 · Victoria Yudin · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 204

Support Debugging Tool Build 17 hotfix 2 released

David Meego - Click for blog homepageFollowing on from the release of the Support Debugging Tool Build 17 hotfix, I am releasing a hotfix 2 build.

It turns out that some of the changes in the way the Support Debugging Tool adds buttons to the Standard Toolbar were still not perfect. We had a few reports that when the SDT dictionary was installed, newly created users would be missing their menus. The solution was to remove the SDT from the system and recreate the user, or to use SQL to drop the contents of the menu and toolbar tables, which will force them to be recreated.

This hotfix changes the method that the buttons are added, which means that they will not keep coming back if a user removes them from their toolbar, and as a happy side effect also fixed the issue above. It also includes the minor changes and fixes made since the last build was released.

If you have a previous build (including any build 17 release) installed, please just install this update over the top of the existing code, no need to uninstall first. 

 

Below is a summary of the changes made for releases 10.00.0017, 11.00.0017 and 12.00.0017, dated as Last Modified: 09-Apr-2013:

Fixes

  • Fixed Support Debugging Tool and ScreenShot Toolbar icons cannot be removed.
  • Fixed Support Debugging Tool and ScreenShot Quick Links cannot be removed.
  • Fixed issue where newly created users were missing menus and/or toolbars.
  • Fixed Composite Field Data not displaying for 3rd party Composite fields in Resource Explorer windows.
  • Fixed SQL Profile Traces from multiple instances of Dynamics GP on the same SQL Server showing up as Stranded Traces.
  • Fixed error message when searching for tables with spaces in their technical name on Resource Information window.

 

Enhancements

  • Updated Security Information Window to allow access to be controlled independently of the Microsoft Dynamics GP Security windows.
  • Added Database Type field for Tables to Resource Information window. This allows easy identification of ctree vs SQL based temporary tables.

 

Downloads

Support Debugging Tool for Microsoft Dynamics GP 10.0 Secure Link

Support Debugging Tool for Microsoft Dynamics GP 2010 (v11.0) Secure Link

Support Debugging Tool for Microsoft Dynamics GP 2013 (v12.0) Secure Link

NOTE: The tool is posted on PartnerSource Secure Link and so can be downloaded by partners. If you are a customer, you will need to obtain the tool through your partner. Any feedback or questions about the tool will be handled via the Microsoft Dynamics GP Community Forum.

 

Please check out the Support Debugging Tool Portal (http://aka.ms/SDT) page, your one-stop-shop for all things relating to the Support Debugging Tool.

Please post your feedback on what you think of this build and what you would like to see in the future.

Thanks to Robert Cavill for his assistance with this build, especially testing the toolbar and Quick Links behaviour. Thanks also to Kelly Youells for getting the files on PartnerSource updated.

David

Continued here:
Support Debugging Tool Build 17 hotfix 2 released

April 12, 2013 · David Musgrave · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 144

Support Debugging Tool Build 17 hotfix released

David Meego - Click for blog homepageJust before I went on leave over Christmas and New Year, I announced the release of the Support Debugging Tool Build 17.

Well, as much as I try to ensure that there are not any bugs in the code, I managed to introduce one.

So this hotfix build fixes the bug which can cause the two Support Debugging Tool buttons to appear multiple times on the Standard Toolbar.

I also fixed an issue with the SQL Profile Trace setup where it can generate SQL errors if the system has users in the SY_Users_MSTR (SY01400) table which don’t exist as SQL Logins. This situation can happen when databases are restored to a different SQL Server.

If you have a previous build (including the original build 17) installed, please just install this update over the top of the existing code, no need to uninstall first. 

 

Below is a summary of the changes made for releases 10.00.0017, 11.00.0017 and 12.00.0017, dated as Last Modified: 25-Jan-2013:

Fixes

  • Fixed repeated creation of Screenshot and Support Debugging Tool Toolbar buttons on the Standard Toolbar.
  • Fixed SQL Profile Trace setup errors when user exists in SY01400 table but does not have a SQL login.

 

Downloads

Support Debugging Tool for Microsoft Dynamics GP 10.0 Secure Link

Support Debugging Tool for Microsoft Dynamics GP 2010 (v11.0) Secure Link

Support Debugging Tool for Microsoft Dynamics GP 2013 (v12.0) Secure Link

NOTE: The tool is posted on PartnerSource Secure Link and so can be downloaded by partners. If you are a customer, you will need to obtain the tool through your partner. Any feedback or questions about the tool will be handled via the Microsoft Dynamics GP Community Forum.

 

Please check out the Support Debugging Tool Portal (http://aka.ms/SDT) page, your one-stop-shop for all things relating to the Support Debugging Tool.

Please post your feedback on what you think of this build and what you would like to see in the future.

Thanks to Robert Cavill for his assistance with this build. Thanks also to Kelly Youells for getting the files on PartnerSource updated.

David

See the original article here:
Support Debugging Tool Build 17 hotfix released

January 31, 2013 · David Musgrave · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 213