Freigeben über

Language settings for Windows client must match SQL Server Language

  • Artikel

David Meego - Click for blog homepageA few weeks ago, I had a very interesting case with a Middle East customer based in the United Arab Emirates (UAE). The customer had reported that when the modified Computer Check Report was printed from one particular work station, it was blank. The same modified report printed fine on other workstations.

We managed to resolve this issue after a couple of fairly long screen sharing sessions. I thought that you might find the process we went through and the final solution interesting.

First Screen Sharing Session

So, the first thing we tried was changing the security settings to point to the original Computer Check Report, thinking that there was a problem with the modified report (even if it did work elsewhere). However, the original report had the same result a blank report was printed.

We tested this with both the Check with Stub On Top and Check with Stub On Bottom reports (original and modified) and still no data printed.

Next we modified the reports to include the names of the reports in the report header section and change security to use the modified reports. When then used Tools >> Customize >> Customization Status (can also use Dictionary Control in the Support Debugging Tool if it is installed.) to disable all third party triggers and printed the report. This time the report included the name of the report that we added, but no other contents. This confirmed that the reports are printing but with no data and that the problem is not caused by another product.

Changing the report to select the Suppress SQL Query option (which reads data from each table one record at a time rather than using a SQL Query with joined tables) did not make any difference.

We installed the Support Debugging Tool on the system so we could access the SQL Execute window. This allowed us to run SQL Queries directly on the problem workstation without needing to install the SQL Utilities. We then created a SQL Query to look at the data in the same way that the report does, with the same relationships between tables, report restrictions and restrictions from the Dexterity code calling the report.

  in[PM_CHECKFORMAT_STUBTOP] {Check w/ Stub on Top}
   run report 'Check With Stub on Top' with restriction
    'Batch Source' of file PM_Payment_WORK = l_Source and
    'Batch Number' of file PM_Payment_WORK = I_sBatchNumber and
    'Control Type' of file PM_Payment_WORK = PAYMENT_NUMBER_CONTROL_TYPE and
    'Reprinted' of file PM_Payment_WORK = IO_nReprinted
    destination rgsDestID[1]
    printer NamedPrinter;

As we were using the SDT's SQL Execute window we could use Technical Names to write the query:

select A.* from {PM_Payment_WORK alias A}
JOIN {Batch_Headers alias X} on {Batch Number alias X} = {Batch Number alias A}
and {Batch Source alias X} = {Batch Source alias A}
JOIN {CM_Checkbook_MSTR alias B} on {Checkbook ID alias B} = {Checkbook ID alias A}
JOIN {CM_Bank_MSTR alias C} on {Bank ID alias C} = {BaAnk ID alias B}
JOIN {PM_Vendor_MSTR alias D} on {Vendor ID alias D} = {Vendor ID alias A}
JOIN {MC_Currency_SETP alias E} on {Currency ID alias E} = {Currency ID alias A}
left outer JOIN {MC_PM_Transactions alias F} on {Document Type alias F} = {Document Type alias A}
and {Voucher Number alias F} = {Payment Number alias A}
JOIN {PM_Payment_Apply_WORK alias G} on {Payment Number alias G} = {Payment Number alias A}
where {Batch Number alias A} = 'TEST' and {Batch Source alias A} = 'XPM_Cchecks'
and {Control Type alias A} = 1 and {Reprinted alias A} = {Reprinted alias X}
and {Print On Stub alias G} = 1

Which converts to the query below when executed:

select A.* from PM10300 A
JOIN SY00500 X on X.BACHNUMB = A.BACHNUMB
and X.BCHSOURC = A.BCHSOURC
JOIN CM00100 B on B.CHEKBKID = A.CHEKBKID
JOIN SY04100 C on C.BANKID = B.BANKID
JOIN PM00200 D on D.VENDORID = A.VENDORID
JOIN DYNAMICS..MC40200 E on E.CURNCYID = A.CURNCYID
left outer JOIN MC020103 F on F.DOCTYPE = A.DOCTYPE
and F.VCHRNMBR = A.PMNTNMBR
JOIN PM10201 G on G.PMNTNMBR = A.PMNTNMBR
where A.BACHNUMB = 'TEST' and A.BCHSOURC = 'XPM_Cchecks'
and A.CNTRLTYP = 1 and A.REPRNTED = X.REPRNTED
and G.PrtOnStb = 1

This query returned the results we expected from the batch ID "TEST", so we knew the data was there. We did expect the data to be returned by this query as the same report worked on other machines. If it was a data issue, it would affect all workstations.

So now we looked closer at what the Report Writer itself was doing. On the failing workstation we captured the DebugRW.txt using the Dex.ini setting DebugRW=895 (everything active). For more information on DebugRW see the Reports tab of the Dex.ini Settings window in the Support Debugging Tool.

The report summary in the DebugRW.txt file showed the following, which confirmed no data could be found:

beginning of report
page header not printed this page
print report header
out of records
add bodies to present count of 0 to reach 12
print report footer
not printing page footer

Finally, we compared the DebugRW.txt files from the working machine to the non-working machine. We found the following difference reported.

  • Good:
    rw_dm_file( file = 7, keyno = 7, options = FM_FIRST, call_data = 0x00000000 );
     result = NO_ERROR
     
  • Bad:
    rw_dm_file( file = 10, keyno = 7, options = FM_FIRST, call_data = 0x00000000 );
     result = FM_EOF

File 10 is PM_Payment_WORK as shown below:

rw_dm_fileopen_security( file_name = "PM_Payment_WORK", options = 0x1004,
 more_options_p = 0x0012e324, query_obj = 0x00000000,
 file_list = 0x16621b2c ) ==> 10
 result = NO_ERROR

So we finished this session knowing that the data is in the tables, but for some reason the Report Writer is unable to see it and so prints a blank report.

Second Screen Sharing Session

During our second session, we focused on why the Report Writer was unable to see the data that we knew was in the tables. To capture the SQL query issued by the Report Writer we made sure that the Suppress SQL Query option on the Report Definition window was unselected and then printed the report on both the non-working and working machines while capturing logs with the Support Debugging Tool's Manual Logging Mode.

Looking at the DEXSQL.LOG for the two machines we were able to spot a difference. The WHERE clause on the select statement against the PM_Payment_Work (PM10300) table was different between the machines.

  • Non-working:
     
    select *
    FROM TEST.dbo.PM10300
    WHERE BCHSOURC = 'XPM_Cchecks ' AND BACHNUMB = 'TEST '
    AND DOCNUMBR >= '' AND DOCNUMBR <= 'ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ'
    ORDER BY BCHSOURC ASC, BACHNUMB ASC, DOCNUMBR ASC, DEX_ROW_ID ASC
     
  • Working:
     
    select *
    FROM TEST.dbo.PM10300
    WHERE BCHSOURC = 'XPM_Cchecks ' AND BACHNUMB = 'TEST '
    AND DOCNUMBR >= '' AND DOCNUMBR <= 'ےےےےےےےےےےےےےےےےےےےےے'
    ORDER BY BCHSOURC ASC, BACHNUMB ASC, DOCNUMBR ASC, DEX_ROW_ID ASC

You will notice that the range selected for the Document Number (DOCNUMBR) field goes from the smallest value (a blank string) to the largest value (a string filled with the "Maximum Character"). However the "Maximum Character" used on the non-working machine is ÿ (lowercase y with dots, an umlaut) and on the working machine it is ے (upside down ? mark).

If we added this part of the WHERE clause into the queries we used in the first session, we could make the query return no data (when ÿ was used) and return data (when ے was used).

What the "Maximum Character" should be is controlled by the SQL Server sort order, we want it to be the character that is last when sorted. In a system with a Binary sort order it is simply ASCII character 255. With other sort orders, the character can change and the application needs to use the appropriate character, both from the automatically generated code from Dexterity as well as any pass through SQL commands. See the More Information section below for further details.

We checked the sort order of the customer's SQL Server using the sp_helpsort command. Below is the result

  • Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 146 on Code Page 1256 for non-Unicode Data

Sort Order 146 is an Arabic order, not surprising considering this system was based in UAE.

On the SQL Server we ran some scripts to check what the SQL Server expected as the "Maximum Character":

print char(255)
print ascii('ÿ')
print ascii('ے')

Below are the results returned from the Customer's Arabic System and from my English DOCI (Document Order, Case Insensitive)System

  • Results from customer's SQL Server:
    ے
    63
    255
     
  • Results from my machine:
    ÿ
    255
    63

You will notice that the characters are reversed and that using the wrong character when end the range of data included in the WHERE clause before any alphabetic characters. The first alphabetic character is A which has an ASCII value of 65.

Dexterity has a system command that it can use to identify the "Maximum Character", so that it can be used when creating pass through SQL commands (see More Information below for details). This command uses the following SQL stored procedure to identify the "Maximum Character" and its ASCII value.

exec master..smDEX_Max_Char

So now we knew that the issue was with the "Maximum Character", but had yet to work out exactly why the character being used was different on this workstation.

The Solution

We had been told that the ODBC settings and Regional Settings for the machine were correct, but decided to double check them again to be sure. The ODBC settings checked out, all the checkbox were unmarked as recommended.

However, while checking the regional settings we noticed that when logged on as an Administrator, there was an additional Advanced Tab on the Regional and Language Options window. Under Windows XP, this tab does not show unless logged in as an Administrator.

This is where we found our difference between the workstations. under the setting Language for non-Unicode programs:

Control Panel >> Regional and Language Options >> Advanced tab (must be logged on as Administrator)  >> Language for non-Unicode programs

  • Working machine:
    Arabic (U.A.E.)
     
  • Non-working machine:
    English (United States)

For Windows 7: Control Panel >> Region and Language >> Administrative tab (must be logged on as Administrator) >> Language for non-Unicode programs

Click Change System Locale: 

So it appears that once SQL is running with an Arabic Sort Order, the windows workstations must be in a matching Language for non-unicode programs so that the "Maximum Character" used for where clauses matches what the SQL Server expects.

 

More Information

The following Knowledge Base articles discuss some issues with the "Maximum Character":

  

Hope you found this interesting and useful. 

David

Comments