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: 229

Troubleshooting the Microsoft Dynamics GP 2013 Web Client – Part 3

Part 3 – Resolving Microsoft Dynamics GP 2013 Web Client Functional Issues

Last week, Part 2 of this series dealt with resolving Web Client implementation issues. The key to remember is any exception condition prior to the Microsoft Dynamics GP login window is treated as an implementation issue and as noted back in Part 2, there are two major processes – steps, if you will – in compiling information that will lead to resolving Web Client exceptions.

Just like with the implementation issues, you must compile information related to all application exceptions.
Let’s see what type of information can be compiled:

  • If the problem involves product functionality, it’s always a good idea to record the steps to reproduce the problem.
  • It’s also possible that the issues are UI related, i.e., a window does not display correctly (though these issues are less frequent, but certainly possible).
  • Printing and data export issues. In this case, you will want to determine whether the Silverlight application is being trusted.
  • Silverlight exceptions, i.e., those displayed in a window with a stop sign
  • Third party application customization vs. standard Microsoft Dynamics GP functionality. In a multi-dictionary setting, this will play a role whether using Web Client or the Rich Client (traditional Dexterity application).
  • Enable all available Web Client logs (session management and multi-tenant logs)
  • Capture all user-machine related information with MSINFO32.EXE
  • Can you reproduce the problem in the standard Microsoft Dynamics GP rich client application
  • Can the problem be reproduced by more than one user
  • Can the problem be reproduced in more than one company

The following are sample application exceptions:

Silverlight exception

App stuck on “Initializing” upon login

As you can tell, some of the information that must be compiled is standard. What must be taken into account here is, the Session Service is a real GP client running in the background supporting the Web Client session.

With that said, the following are some key success factors for the resolution of Web Client functional issues:

  • First, all issues must be treated jointly as Microsoft Dynamics GP issues and not something isolated to the Web Client.
  • You must keep in mind that Web Client will display the same exception messages displayed by the standard Microsoft Dynamics GP rich client.
  • Breakdown the problem and make sure it’s focused on the Web Client.

Finally, summarizing:

  • The Web Client functionality “resembles” that of the Microsoft Dynamics GP rich client – we use the word “resemble” here, because we know certain things like navigation are slightly different in Web Client.
  • The rich client is installed on the Session Host server(s), therefore files like DYNAMICS.SET, DEX.INI, and forms and reports dictionaries are still in the mix
  • All exceptions experienced by the rich client are submitted to the Web Client, whether handled or unhandled.
  • All Dexterity third party applications are supported in the Web Client.
  • The Web Client uses a proprietary messaging system.

In part 4 of this series, I will look into some tools available for troubleshooting both implementation and functional issues.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

View original post here:
Troubleshooting the Microsoft Dynamics GP 2013 Web Client – Part 3

July 22, 2013 · Mariano Gomez · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 194

Troubleshooting the Microsoft Dynamics GP 2013 Web Client – Part 2

Part 2 – Resolving Microsoft Dynamics GP 2013 Web Client Implementation Issues



Yesterday, in Part 1 of this series, I addressed the Microsoft Dynamics GP Support Team’s posture when addressing Web Client support cases, by cataloging each reported issue as either implementation- or functional-related. In this opportunity, I will look at the major processes involved in driving your implementation-related support case to a rapid and successful closure.

In the process of troubleshooting your Web Client implementation issue, there are two categories of data that need to be compiled to close a support case: installation information and application related exceptions.

Compiling the installation information typically involves gathering the following information:

  • Type of installation (Intranet/Extranet/both)
  • Information about certificates
  • Firewall and Ports information
  • Number of servers supporting the installation (Web Servers/Session Hosts/SQL Servers)
  • Information about load balancers (if exist)
  • Domain security groups and user accounts
  • Run the Web Client Diagnostic Tool available as an option when you open a support case.
  • Provide the XML output file created by the Web Client Implementation Tool, if available

Compiling the Web Client application related exceptions involves gathering the following information:

  • Dynamics application log events
  • Session Service logs (if enabled)
  • Session Central logs (if enabled)
  • Internet Information Services (IIS) logs
  • Web page exception messages
  • Tenant exception information (if using Multi-tenant Services)
  • Fiddler and Microsoft Network Monitor (Netmon) traces

Web Client implementation exceptions can usually be identified by an “Unexpected Error” message in the browser as shown below:

Web Client application exception

Traditionally a support case would be started with a screenshot of an issue. However, as it relates to Web Client, Microsoft Dynamics GP Support cannot determine what an exception really means other than the fact that you encountered an exception. As such, Web Client implementation cases will usually require running the Web Client Diagnostic Tool in addition to providing Event Viewer information related to the Correlation ID displayed in the browser.

Event Viewer: Dynamics application event log

In a future installment, I will be covering all the troubleshooting tools available at your disposal, including the Web Client Diagnostic Tool, Web Client Implementation ToolFiddler, and Netmon among others.

In Part 3 of this series, I will address functional exceptions and resolution of functional issues.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

See more here:
Troubleshooting the Microsoft Dynamics GP 2013 Web Client – Part 2

July 19, 2013 · Mariano Gomez · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 200

You Cannot Print An Unauthorized Purchase Order

As the title says, “you cannot print an unauthorized purchase order”. This is an error message which I encountered the other day on my demo system when I was trying to print a PO. Now, I’m largely not surprised to … Continue reading

See the original article here:
You Cannot Print An Unauthorized Purchase Order

July 12, 2013 · Ian Grieve · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 436

Dynamics GP 2013 MVPs

Got the great news earlier this week that I was re-awarded as a Microsoft MVP for 2013. MSDynamicsWorld has a coverage of the story –

Here are the current Microsoft Dynamics GP MVPs – extremely proud to be in such great company! 

View post:
Dynamics GP 2013 MVPs

July 5, 2013 · Jivtesh Singh · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 152

Microsoft Dexterity Training Roadshow – Next Stop: Boston

(C) Microsoft Corporation

The Microsoft Dexterity Training Roadshow rolls around the United States with its next and final stop in Boston, Massachusetts from July 15 – July 19, 2013. Our training will be hosted in the Regus Business Center, from 9:00 AM to 5:00 PM.



Regus Business Center

60 State Street, Suite 700

Boston, MA 02109

We still have some seats left, so if you want to learn some of the development techniques used by Microsoft Dynamics GP developers this is your chance. For registration information, click here.

Stay tuned as we will announce our second Roadshow coming up.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Excerpt from:
Microsoft Dexterity Training Roadshow – Next Stop: Boston

June 21, 2013 · Mariano Gomez · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 130

Why does SmartList not return all of the expected data for large queries?

David Meego - Click for blog homepageHere is another support issue that you might come across.

I just closed a case where a SmartList was returning less than the expected number of records. If a SQL Statement was executed via the SQL Server Management Studio, it returned more records than were shown in the SmartList.

The system was comprised of a SQL Server machine and Terminal Server machine. If the SmartList was executed directly on the SQL Server, all the data was returned as expected. If the SmartList was executed on the Terminal Server machine, the returned data was missing records.

Turning on DEXSQL.LOG logging (via the Support Debugging Tool or Dex.ini settings) allowed us to see that a connection error was occurring.  We were also able to locate the Select statement issued by SmartList to gather the data together.

If you look near the top of the DEXSQL.LOG (assuming it was started just before displaying the SmartList), you will find a statement that starts with the following:

SELECT 1 AS ‘1’,

Taking this statement and running it from SQL Server Management Studio on the SQL Server machine confirmed how many records should be displayed.

Below is the error found near the end of the DEXSQL.LOG (assuming it was stopped as soon as SmartList finished processing) showing the connection was closed:

/*  Date: 06/20/2013  Time: 1:43:08
stmt(21859448):*/
CALL TWO.dbo.zDP_SOP10106SS_1 ( 3, ‘INV00001′ )
/*
/*  Date: 06/20/2013  Time: 1:43:08
SQLSTATE:(08S01) Native Err:(10054) stmt(21931584):*/
MicrosoftSQL Server Native Client 10.0TCP Provider: An existing connection was forcibly closed by the remote host.
*/
/*
/*  Date: 06/20/2013  Time: 1:43:08
SQLSTATE:(S1010) Native Err:(0) stmt(21931584):*/
MicrosoftODBC Driver Manager Function sequence error*/
/*
/*  Date: 06/20/2013  Time: 1:43:08
SQLSTATE:(S1010) Native Err:(0) stmt(21931584):*/
MicrosoftODBC Driver Manager Function sequence error*/

 

With some help from the SQL Server support team, we were able to resolve this issue by disabling the TCP Chimney and SynAttackProtect networking features on the two servers involved.

  

The following article explains the issues with the TCP Chimney primarily for a Windows Server 2003 machine:

“General Network error,” “Communication link failure,” or “A transport-level error” message when an application connects to SQL Server (KB 942861)

The following registry changes will disable the TCP Chimney feature on a Windows Server 2003 machine:

  1. Launch regedit.exe
  2. Edit DWORD EnableTCPChimney under HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpipParameters, set data value to 0
  3. Edit DWORD EnableRSS under HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpipParameters, set data value to 0
  4. Edit DWORD EnableTCPA under HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpipParameters, set data value to 0
  5. Changes take effect after a reboot. 

 

The following article explains how to disable the TCP Chimney for a Windows Server 2008 machine.

Information about the TCP Chimney Offload, Receive Side Scaling, and Network Direct Memory Access features in Windows Server 2008 (KB 951037)

The following command line command will disable the TCP Chimney feature on a Windows Server 2008 or later machine:

netsh int tcp set global chimney=disabled

 

Troubleshooting: Connection Forcibly Closed (TechNet Article)

The following registry changes will disable SynAttackProtect feature:

  1. Launch regedit.exe
  2. Add DWORD value named SynAttackProtect under registry key HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpipParameters, set value to 0
  3. Changes take effect after a reboot

 

After the TCP Chimney and SynAttackProtect features were disabled on both the Server and Client machines, the problem with the connection being forcibly closed went away.

Hope this information is useful.

David

See more here:
Why does SmartList not return all of the expected data for large queries?

June 21, 2013 · David Musgrave · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 219