After restarting SQL Server, only ‘sa’ user can log into Microsoft Dynamics GP

David Meego - Click for blog homepageLast week, I resolved a rather bizarre case for a customer.

The Situation

Each time the customer’s SQL Server was restarted for whatever reason, only the ‘sa’ user was able to log into Microsoft Dynamics GP.  Once the ‘sa’ user had logged into Microsoft Dynamics GP at least once, then all the other “normal” users could log in and everything was fine until the next time the SQL Server was restarted.

I asked for screenshots, the DEXSQL.LOG and the SQL Profile Trace so we could see what was happening.

From the user interface, they received the following error:

Your attempt to log into the server failed because of an unknown error. Attempt to log in again.

 Your attempt to log into the server failed because of an unknown error. Attempt to log in again. 

  

The Cause

I did not need the SQL Profile Trace and SQL Logs and the DEXSQL.LOG had enough information to understand what was going wrong. Below is the excerpt from the log:

/*  Date: 09/05/2014  Time: 12:33:58
stmt(195184136):*/
set nocount on
insert into tempdb..DEX_SESSION values (@@spid)
select @@identity
/*
/*  Date: 09/05/2014  Time: 12:33:58
SQLSTATE:(S0002) Native Err:(208) stmt(195184136):*/
MicrosoftSQL Server Native Client 10.0SQL ServerInvalid object name ‘tempdb..DEX_SESSION’.*/
/*
/*  Date: 09/05/2014  Time: 12:33:58
SQLSTATE:(00000) Native Err:(208) stmt(195184136):*/
*/

The error is that the system could not find the tempdb..DEX_SESSION table. If we looked at the SQL Server after the restart would probably find that the tempdb..DEX_LOCK table was also missing.

The DEX_SESSION and DEX_LOCK tables are used by Dexterity’s Optimistic Currency Control (OCC) system which allows the passive locking approach to table updates which would allow two users to update the same record in a table as long as they don’t try and change the same field.

The fact that the tables are missing is causing the unknown error and preventing users from logging in.

Logging on as the ‘sa’ provides enough privileges to create the tables. Dexterity can create tables automatically if they are missing as long as the user has sufficient rights.

Once ‘sa’ has logged in and the tables have been created, other “normal” users can log in.

So why would they have been dropped from the database? Well, the truth is that the entire tempdb database is recreated every time SQL Server restarts.

A better question is: When the SQL Server restarted, why weren’t the tempdb..DEX_SESSION and tempdb..DEX_LOCK tables recreated?

Another questions is: What mechanism does the system have for recreating the tables when the SQL Server is restarted?

  

The Resolution

If you look on your system under the master database, you should find 2 Stored Procedures: dbo.smDEX_Build_Locks and dbo.smDEX_Max_Char. It is the dbo.smDEX_Build_Locks stored procedure which is meant to be executed on start up to create the tables in the tempdb.  If the stored procedure is missing or is not running on startup, we would have the issue described above.

To fix the issue:

  1. Locate the dex_req.sql file in the application folder under SQL/Util, for example: C:Program Files(x86)Microsoft DynamicsGP2013SQLUtildex_req.sql.
     
  2. Execute this script in SQL Server Management Studio. Notice it has the line sp_procoption ‘smDEX_Build_Locks’,’startup’,’true’ to make the script run automatically on start up.

Next time you restart the stored procedure should run and all users can log in.

 

Hope you find this helpful. 

David

Read this article:
After restarting SQL Server, only ‘sa’ user can log into Microsoft Dynamics GP

September 8, 2014 · David Musgrave · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 323

SQL view for easier General Ledger transaction searches in Dynamics GP

Just about every Dynamics GP customer I have worked with, at one time or another, has had occasion to search the General Ledger for a particular dollar amount. Usually, I use the Account Transactions SmartList for this and search for Debit Amount = XXX or Credit Amount = XXX (using the Match 1 or More search …

View the original here:
SQL view for easier General Ledger transaction searches in Dynamics GP

September 4, 2014 · Victoria Yudin · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 487

More from the Dynamics GP Community do the ALS #IceBucketChallenge

David Meego - Click for blog homepageFollowing on from my recent post: David Musgrave does the ALS #IceBucketChallenge. A number of other people in the Microsoft Dynamics GP Community have now risen to the challenge.

Below are their videos and blog articles:

 

Beat Bucher: @GP_Beat takes the ALS #IceBucketChallenge Aug-2014 (direct link)

(Please visit the site to view this video)

Blog: ALS Ice Bucket Challenge

 

Mariano Gomez does ALS #IceBucketChallenge (direct link)

(Please visit the site to view this video) 

Blog: Mariano Gomez does the ALS #IceBucketChallenge

 

Mark Polino: ALS Ice Bucket Challenge. 50 gallons in 50 seconds (direct link)

(Please visit the site to view this video)

 

Still waiting for some more of the people nominated to do the challenge. I suppose they will have to donate more the longer they take. :-)

Enjoy

David

Visit link:
More from the Dynamics GP Community do the ALS #IceBucketChallenge

September 3, 2014 · David Musgrave · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 335

VBA: DUOS data stored in the wrong database after switching companies

David Meego - Click for blog homepageA while ago I had a case where the customer was storing data in the DUOS (Dynamic User Object Store) table from a VBA (Visual Basic for Applications) customisation. Everything worked fine until the users switched companies. After switching company the VBA customisation would still be connected to the DUOS table in the previous company’s database. If the user exited Microsoft Dynamics GP and logged back in, the DUOS in the correct database was used.

The DUOS table is in fact the SY_User_Object_Store (SY90000) table in the company database and is usually used by VBA to store additional data without needing to create new tables in the SQL Server databases.

When you switch companies, the connection to the DUOS table is closed so it can be re-opened once you have logged into the new company. If for some reason the DUOS table is not closed, the DUOS table will still point to the previous company database rather than the new company database.

We were able to identify the cause of the issue as the Support Debugging Tool (builds 17 or earlier). The SDT can use the SY_User_Object_Store to store temporary data when using helper functions to create parameters to pass data between scripts. When exiting Microsoft Dynamics GP or switching companies, it executed some code to remove any temporary records left in the table. It turns out that this code ran after the core code had closed the table and so re-opened the table causing the issue.

Build 18 (released in February 2014) or later of the Support Debugging Tool resolves this issue. 

Recently, I had another case with the same issue and the same cause.  So I decided to write a specific blog post to request the Dynamics GP community make sure they are running the latest Support Debugging Tool builds.

So, if you use VBA with the DUOS and have the Support Debugging Tool installed, please ensure you download the latest version and install it on your server and all workstations. Please go to the Support Debugging Tool Portal (http://aka.ms/SDT) for links.

Note: Assuming you are using the “Recommended Configuration” with a shared Debugger setup file location, once you have installed a later build on one workstation and logged in, all other workstations will pop up a warning dialog on login until they are also updated.

Thanks for your support. 

David

See more here:
VBA: DUOS data stored in the wrong database after switching companies

September 1, 2014 · David Musgrave · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 286

Microsoft announces Developer Preview for Dynamics GP 2015

Kevin Racer - Click for blog homepageMicrosoft is pleased to announce the availability of the developer preview for Microsoft Dynamics GP 2015.  This partner only preview is available to any registered Microsoft Dynamics GP partner and can be downloaded from PartnerSource.  The preview itself is comprised of four parts that once downloaded can be extracted into a single virtual machine instance containing a fully functional installation of the Microsoft Dynamics GP Service Based Architecture (SBA).  Information on using the virtual machine can be found in the attached readme document (at bottom of article).
 
The preview is intended to promote the exploration and early adoption of the Microsoft Dynamics GP Service Based Architecture by partners and ISV developers and cannot be used for customer test or production environments.  The Service Based Architecture is a set of new component services as well as changes to the Dexterity IDE and runtime that enables the creation of REST based web service operations that leverage existing sanScript code based functionality.  The Dexterity IDE has also been significantly enhanced to interoperate with Microsoft’s .NET framework making it easier to use .NET classes and objects with in the sanScript language.  Users of the preview are encouraged to regularly check our blogs for updates and informational examples on this new architecture.

 

Part 1

File Name Dynamics_GPServiceISVDrop_Aug132014Parts.part1.rar
File Size 4193280 KB
https://mbs2.microsoft.com/fileexchange/?fileID=56a379f7-b982-49cd-aff7-ccc72beace34

  

Part 2

File Name Dynamics_GPServiceISVDrop_Aug132014Parts.part2.rar
File Size 4193280 KB
https://mbs2.microsoft.com/fileexchange/?fileID=0b147e80-0b5d-4087-ba30-3a90e2d95496

 

Part 3

File Name Dynamics_GPServiceISVDrop_Aug132014Parts.part3.rar
File Size 4193280 KB
https://mbs2.microsoft.com/fileexchange/?fileID=e26be271-a258-434f-be22-4258b4ccb796

 

Part 4

File Name Dynamics_GPServiceISVDrop_Aug132014Parts.part4.rar
File Size 1084807 KB
https://mbs2.microsoft.com/fileexchange/?fileID=3d5aa62c-48d7-4d4c-9e7e-07e47f95d2d5

 

Don’t forget to download the Readme document attached to the bottom of this article.

 

Please post your feedback as comments on the blog. 

Until next time

Kevin

See original article:
Microsoft announces Developer Preview for Dynamics GP 2015

August 29, 2014 · David Musgrave · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 396

Quick Tip: Dexterity Ranges and how to update Fields that are part of the Key

David Meego - Click for blog homepageLast week, I had an interesting case where a Dexterity Developer was having trouble getting some code to update a range of records selected in a table. The code would only update the first record in the range.

 

The Scenario

The developer was trying to move the contents of the SOP_Serial_Lot_WORK_HIST table for a line in the SOP_LINE_WORK table to the same line number in another document.

So that we know the key fields to be used in the range, the primary key for the SOP_Serial_Lot_WORK_HIST table is shown below:

 

The code below looks like it should work, the range is defined with the first three fields set to a value and the remaining fields are cleared/filled by virtue of the clear table and fill table commands. The while loop through the range is correctly written with change first and change next and looping while err() <> EOF do. Everything looks right except that when the code is run, only the first record in the SOP_Serial_Lot_WORK_HIST table is updated.

 

Original Code Excerpt

range clear table SOP_Serial_Lot_WORK_HIST;

clear table SOP_Serial_Lot_WORK_HIST;
‘SOP Type’ of table SOP_Serial_Lot_WORK_HIST = ‘SOP Type’ of table SOP_LINE_WORK;
‘SOP Number’ of table SOP_Serial_Lot_WORK_HIST = ‘SOP Number’ of table SOP_LINE_WORK;
‘Line Item Sequence’ of table SOP_Serial_Lot_WORK_HIST = ‘Line Item Sequence’ of table SOP_LINE_WORK;
range start table SOP_Serial_Lot_WORK_HIST by number 1;

fill table SOP_Serial_Lot_WORK_HIST;
‘SOP Type’ of table SOP_Serial_Lot_WORK_HIST = ‘SOP Type’ of table SOP_LINE_WORK;
‘SOP Number’ of table SOP_Serial_Lot_WORK_HIST = ‘SOP Number’ of table SOP_LINE_WORK;
‘Line Item Sequence’ of table SOP_Serial_Lot_WORK_HIST = ‘Line Item Sequence’ of table SOP_LINE_WORK;
range end table SOP_Serial_Lot_WORK_HIST by number 1;
 
change first table SOP_Serial_Lot_WORK_HIST by number 1;
while err() <> EOF do
    ‘SOP Number’ of table SOP_Serial_Lot_WORK_HIST = ‘(L) New Number';
    ‘SOP Type’ of table SOP_Serial_Lot_WORK_HIST = ‘(L) New Type';
    save table SOP_Serial_Lot_WORK_HIST;
    check error;    

    change next table SOP_Serial_Lot_WORK_HIST by number 1;
end while;
range clear table SOP_Serial_Lot_WORK_HIST;

 

 

The Problem

Have you worked out what the problem is?

Well, it all comes down to understanding exactly how the change next table command works. It does not look at the last record accessed in the physical table and then move to the next record. No, what it does is look at the current values in the key fields in the table buffer and then finds the next record in the physical table with values greater than those key fields.

Now have you worked it out?

The code changes the values of two of the key fields to values which are outside of the defined range and then saves the record. The table buffer still has those two new values in it when we request the next record. So that next record probably does not exist and even if it did it would be outside of the defined range and so returns err() = EOF. The code drops out of the while loop and only the first record has been updated.

 

The Solution

There are a couple of solutions and I will mention both, but I will give my preferred method first. This first method is easier to understand, but a little more work to code.

The important point is to have the correct values in the key fields of the table buffer before you execute the change next table command. The easiest way to achieve this is with a couple of local variable to store the original values and then restore them after completing the updates.

The following code shows this technique:

 

Updated Code Excerpt

local ‘SOP Number’ l_number;
local ‘SOP Type’ l_type;

range clear table SOP_Serial_Lot_WORK_HIST;

clear table SOP_Serial_Lot_WORK_HIST;
‘SOP Type’ of table SOP_Serial_Lot_WORK_HIST = ‘SOP Type’ of table SOP_LINE_WORK;
‘SOP Number’ of table SOP_Serial_Lot_WORK_HIST = ‘SOP Number’ of table SOP_LINE_WORK;
‘Line Item Sequence’ of table SOP_Serial_Lot_WORK_HIST = ‘Line Item Sequence’ of table SOP_LINE_WORK;
range start table SOP_Serial_Lot_WORK_HIST by number 1;

fill table SOP_Serial_Lot_WORK_HIST;
‘SOP Type’ of table SOP_Serial_Lot_WORK_HIST = ‘SOP Type’ of table SOP_LINE_WORK;
‘SOP Number’ of table SOP_Serial_Lot_WORK_HIST = ‘SOP Number’ of table SOP_LINE_WORK;
‘Line Item Sequence’ of table SOP_Serial_Lot_WORK_HIST = ‘Line Item Sequence’ of table SOP_LINE_WORK;
range end table SOP_Serial_Lot_WORK_HIST by number 1;
 
change first table SOP_Serial_Lot_WORK_HIST by number 1;
while err() <> EOF do
    Store Key Fields
    l_number = ‘SOP Number’ of table DTM_Serial_Lot_WORK_HIST;
    l_type = ‘SOP Type’ of table DTM_Serial_Lot_WORK_HIST;

    ‘SOP Number’ of table SOP_Serial_Lot_WORK_HIST = ‘(L) New Number';
    ‘SOP Type’ of table SOP_Serial_Lot_WORK_HIST = ‘(L) New Type';
    save table SOP_Serial_Lot_WORK_HIST;
    check error;    

    Restore Key Fields
    ‘SOP Number’ of table DTM_Serial_Lot_WORK_HIST = l_number;
    ‘SOP Type’ of table DTM_Serial_Lot_WORK_HIST = l_type;     
    change next table SOP_Serial_Lot_WORK_HIST by number 1;
end while;
range clear table SOP_Serial_Lot_WORK_HIST;

 

 

The second technique is to change the change next table to change first table. As each record is removed from the range as it is saved, using change first table will keep picking up the next record until the range is empty.  While this method only changes one word in the code, it is less readable. Another developer might even look at the code later and think it is in error as you have change first table where a change next table command should normally be.

 

More Information

For more information on ranges, check out the following Knowledge Base (KB) article I wrote: 

 

Hope you found this useful.

David

See the original article here:
Quick Tip: Dexterity Ranges and how to update Fields that are part of the Key

August 25, 2014 · David Musgrave · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 299

Update: Microsoft Dynamics GP 2013 R2 updates to Report Writer Functions – RW_CoAddrIDInfo broken

David Meego - Click for blog homepageFollowing on from my post: Microsoft Dynamics GP 2013 R2 updates to Report Writer Functions, I have to admit that I made a mistake.

The Problem

The update to the RW_CoAddrIDInfo() report writer function has broken the code. It works when no Address ID is supplied to return the information from the default Address information stored on the Company Master table, but fails to return any results from the Location Master table when an Address ID is provided.

I looked at the code and identified the issue and have logged the proposed fix with the development team as TFS Bug 79342. Hopefully, we can get the fix into the next GP 2013 R2 release soon.

In the meantime, I have created a couple of workaround fixes which can be used until the code is fixed by development. They are attached to bottom of this article.

 

The Fixes 

Using Support Debugging Tool

The first fix uses the Support Debugging Tool (http://aka.ms/SDT) to create a custom Report Writer function which can be called using the following syntax:

FUNCTION_SCRIPT( rw_TableHeaderString  5261  “RW_COADDRIDINFO”  “Address ID”  0  X )

where the Address ID can be specified as a string constant or from a variable and X is the data you want returned.

The Runtime Execute script and an example custom report are in the archive for you to use.

If you already have the SDT installed in the Recommended Configuration (with a shared setup file), you can deploy the fix by importing the configuration settings xml file once on any workstation and updating your reports to use the function call above.

 

Using Dexterity

The second fix is a small Dexterity chunk file which triggers a corrected version of the RW_CoAddrIDInfo() function after the original and so provides the correct results. It can be used without making changes to existing reports, however, the chunk will need to be deployed on all workstations that need the fix. The chunk file and its source code are provided in the archive for you to use.

 

Both fixes should be removed once the original code is fixed.

See I am human, and not the Alien that Mark Polino thinks I am. 

David

Original post:
Update: Microsoft Dynamics GP 2013 R2 updates to Report Writer Functions – RW_CoAddrIDInfo broken

August 22, 2014 · David Musgrave · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 328