Quick Tip: Unusual behaviour when working with SQL Server from Dexterity

Today, I came across an issue where a feature in Microsoft Dynamics GP would work when the workstation had its regional settings set to United States, but would fail to produce any data when the workstation had its regional setting set to Australia, New Zealand, United Kingdom, etc.

We have seen related issues where code would appear to work for the first 12 days of a month, but would generate errors on the 13^th day of the month when using non United States regional settings.

We have also seen issues where errors get generated when there is a single quote character in data, or records beginning with Z failing to be included when processing or printing reports.

We have even seen issues where errors are generated when regional settings for time were altered so that the suffixes for 12 hour time were changed from AM and PM to A.M. and P.M.

All of these problems are caused when Dexterity code talks directly to SQL Server and best practices are not followed.

Note: All table access that is handled by Dexterity itself will correctly handle the issues that can cause the above mentioned problems.

However, the leverage the functionality of SQL to optimize performance, there are times where developers write Dexterity code that bypasses the Dexterity table handling and talks directly with the SQL Server.

There are many benefits that can gained when using SQL Where clauses with the Dexterity range table where command, when executing pass through SQL commands, or calling stored procedures. But there are some best practice methods which will avoid all of the problems mentioned above.

Here is a quick summary:

Strings

• Always make sure that strings are passed through to SQL using the SQL_FormatStrings() function which will double up single quote characters and also surround the string with starting and ending single quotes. This will avoid the issues caused by a single quote in the data terminating a string early.
   
• If passing a range by specifying a minimum and maximum value and the maximum is not defined (so we want all records to the end), make sure you use the system 9600 method (see KB 910129) to define the maximum value for the current SQL collation. This will stop records starting with Z from being excluded on DOCI sort orders.
   
• If writing any SQL code that building statements to be executed with the exec command and there are string values from the data being hardcoded into the code, but sure to use set @variable = replace(@variable, '''', '''''') to double up any quote characters to prevent early termination of a string.

Dates 

• Always make sure that dates are passed through to SQL using the sqlDate() function to convert the date value to a string and not the str() function. This will avoid all the date based regional settings issues by making the conversion to a string independent of the control panel settings.
   
• You can then use the SQL_FormatStrings() function add the starting and ending quotes.

Times

• Always make sure that times are passed through to SQL by creating a sqlTime() function (see KB 929786) to convert the time value to a 24 hour string and not the str() function. This will avoid all the issues caused by non standard suffixes in 12 hour time.
   
• You can then use the SQL_FormatStrings() function add the starting and ending quotes.

Numbers

• Using the str() function for numbers works fine. Remember that currency data type fields will be converted to strings with five decimal places.

You might have seen all or some of this before, but just in case I wanted to highlight it again as I am still seeing code that does not follow these best practices and so fails under certain circumstances that the developer probably did not test for. 

More Information

The following articles on this blog discuss related issues:

• Quick Tip: Passing Date and Time Variables to SQL Server
   
• Quick Tip: Passing Variables to SQL Server
   
• Language settings for Windows client must match SQL Server Language

The following Knowledge Base articles also discuss these issues:

• Some records in Microsoft Dynamics GP that start with the letter "Z" are not displayed in SQL Server (KB 926651)
   
• How to write "Passthrough" SQL statements and "Range Where" clauses in Microsoft Great Plains Dexterity (KB 910129)
     
• How to pass dates and times to SQL Server from Dexterity in Microsoft Dynamics GP (KB 929786)
   
• How to use a "Range Where" clause that is based on more than one table in Dexterity in Microsoft Dynamics GP (KB 922056)

Hope this helps you write robust code for all regions and settings

David