Consultor Eletrônico

Status: Verified

SYMPTOM(s):

Records are missing when loading large tables into a Microsoft SQL Server database


Record count being reported from the SQL Server via query analyzer shows less records

Error 2624 occationally comes up and goes away during the load

<file-name> in use by <user> on <tty>. Wait or choose CANCEL to stop. (2624)

No error returned from Microsoft SQL Server to the DataServer

As of now all evidence suggests this is a timing issue that only occurs if bulk inserts are performed in a very tight loop with significant CPU, memory and other resources available to the one bulk load process.

FACT(s) (Environment):

Slower systems, server instances under heavier loads, a bulk load task that is bogged down with other resource requirements appear less likely to see the problem.
Can occur when data being loaded with Data Administration tool
Can occur when data being loaded via IMPORT STATEMENT
Can occur when doing bulk inserts into SQL Server
Large amounts of records being loaded
Data Dictionary reports correct number of records were loaded.
Only one user connected to the SQL server database
Database is migrated using DataServer migration tool
MS SQL DataServer

CAUSE:

This is a Microsoft SQL Server issue. When DataServer's insert trigger is fired, some transactions are not committed even though there is only a single user in the database.
In addition, Microsoft SQL server is not returning any error conditions to DataServer to indicate an error occurred, therefore DataServer code is not aware the transactions were not committed

FIX:

Set Progress Native Lock Wait to -1 (infinite) by doing the following:
For MS SQL server DataServer versions later than 10.1B02, 10.1C FCS and 10.2A use the following connection parameter to set the switch for the data load session
-Dsrv PRGRS_NATIVE_LOCKWAIT,-1
Note: 10.1B0330, 10.1B0331 and 10.1B0332 hot fix levels do not require this as Hot fixes already have the switch set to -1
For OpenEdge customers prior to 10.0B and Progress customers prior to 9.1E only need to run "2_Set_Infinite_Timeout.p"
For customers earlier than these versions but are equal to or later than Progress 91E or OE 10.0B, follow instructions below.
1. Connect to their MSS schema holder
2. Run "1_disable_firehose.p" (this sets bits in schema holder to disable firehose)
3. Disconnect from their MSS schema holder ("1_disable_firehose.p" prompts you to disconnect. This step is necessary as the change in the schema holder is not read unless you reconnect to it.)
4. Reconnect to their MSS schema holder
5. Run "2_Set_Infinite_Timeout.p"
6. Start data load
7. Reenable firehose cursors by running 3_enable_firehost.p.
8. Disconnect from their MSS schema holder ("3_enable_firehose.p" prompts you to disconnect)
9. Reconnect to the MSS schema holder and resume normal operations


1_disable_firehose.p
=========================
DEFINE VAR pos AS INT.
DEFINE VAR comma AS CHAR.
DEFINE VAR orig_switches AS CHAR.
DEFINE VAR repl_switches AS CHAR.
FOR EACH _db WHERE _db-type = "MSS":
pos = INDEX(_db._db-misc2[4], ",36", 1).
IF pos > 0 THEN DO:
orig_switches = _db._db-misc2[4].
comma = SUBSTRING(_db._db-misc2[4], pos, 1).
IF comma = "," THEN
repl_switches = REPLACE(_db._db-misc2[4], "36,", "").
ELSE
repl_switches = REPLACE(_db._db-misc2[4], "36", "").
_db._db-misc2[4] = repl_switches.
DISPLAY
orig_switches LABEL "Original Switches" FORMAT "x(50)" SKIP
_db._db-misc2[4] LABEL "Changed to" FORMAT "x(50)" WITH SIDE-LABELS
WITH FRAME X.
MESSAGE "Disconnect and reconnect schema. Then run SEND-SQL-STATEMENT stored procedure to set server timout"
VIEW-AS ALERT-BOX.
END.
ELSE DO:
DISPLAY
"No changes required" SKIP WITH FRAME Y.
MESSAGE "Run SEND-SQL-STATEMENT stored procedure to set server timout"
VIEW-AS ALERT-BOX.
END.
END.
=====================
2_set_infinite_timeout.p
RUN STORED-PROC send-sql-statement NO-ERROR ("SET LOCK_TIMEOUT -1").
============================
3_enable_firehose.p
DEFINE VAR orig_switches AS CHAR.
DEFINE VAR x AS INT.
FOR EACH _db WHERE _db-type = "MSS":
IF INDEX(_db._db-misc2[4], "36", 1) = 0 THEN DO:
orig_switches = _db._db-misc2[4].
/* ends in comma */
IF R-INDEX(_db._db-misc2[4], ",") = LENGTH(_db._db-misc2[4]) THEN
ASSIGN _db._db-misc2[4] = _db._db-misc2[4] + "36,".
ELSE
ASSIGN _db._db-misc2[4] = _db._db-misc2[4] + ",36,".
DISPLAY
orig_switches LABEL "Original Switches" FORMAT "x(50)" SKIP
_db._db-misc2[4] LABEL "Changed to" FORMAT "x(50)" WITH SIDE-LABELS
WITH FRAME X.
END.
ELSE
DISPLAY "No changes required" SKIP WITH FRAME Y.
END.



FIX:

Upgrade to the latest 10.1C04 patch, the lastest 10.2A01 patch, 10.2A02 or 10.2B.