How to debug an intemittent error using Progress OpenEdge database

Question

I have a program that fails intermittently in a complex query.

The error reads:

System.Data.SqlClient.SqlException: Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "LinkedServer".

The query looks like this:

    SELECT Replace([JOB-NO],'M0','') as KeyTaskID,
                                                     dbo.SFGET_UniqueTaskID([CLIENT-CODE],Replace([JOB-NO], 'M0', ''), 0, [TRADE-CODE]) AS HMSUniqueTaskID,
                                                     [LATEST-PRIORITY] AS PriorityCode,
                                                     [KeyProperty] AS KeyProperty,
                                                     Replace([JOB-NO],'M0','')  AS KeyJob,
                                                     [JOB-TYPE] as TaskSubType,
                                                     CONVERT(varchar(6),[MAINT-OFFICER]) AS Officer,
                                                     LEFT(FORENAME + ' ' + SURNAME, 50) AS OfficerName,
                                                     [JOB-NO] + '  ' + LEFT(RTRIM(REPLACE([TEXT-LINE], ';', CHAR(13))), 480) AS Description,
                                                     dbo.SFGET_FormattedDate([TARGET-DATE],0) AS DueDateTime,
                                                     [CURRENT-STAGE-CODE] AS CurrentStageCode
                                                FROM openquery(LinkedServer, '
                                              SELECT DISTINCT
                                                     "RM-JOB"."JOB-NO",
                                                     "RM-JOB"."CLIENT-CODE",
                                                     "RM-JOB"."LATEST-PRIORITY",
                                                     "RM-JOB"."TRADE-CODE",
                                                     "RM-JOB"."JOB-TYPE",
                                                     "RM-JOB"."TARGET-DATE",
                                                     "RM-JOB"."MAINT-OFFICER",
                                                     "RM-JOB"."TEXT-LINE",
                                                     "RM-JOB"."CURRENT-STAGE-CODE",
                                                     "RM-JOB"."PLACE-REF",
                                                     "IH_OFFICER".FORENAME,
                                                     "IH_OFFICER".SURNAME
                                                FROM "PUB"."RM-JOB"
                                                LEFT OUTER JOIN "PUB"."IH_OFFICER"
                                                  ON ("IH_OFFICER"."OFFICER-CODE" = "RM-JOB"."MAINT-OFFICER") 
                                               WHERE "RM-JOB"."JOB-TYPE" = ''GASS''
                                                 AND "RM-JOB"."JOB-STATUS" = 06
                                                 AND "RM-JOB"."CONTRACTOR" = ''NWH001'' ') as ibsTasks
                                               INNER JOIN [SVSExtract].[dbo].Property prop
                                                  ON ibsTasks.[PLACE-REF] = prop.UserCode

I have been testing it manually using SQL Server Management Studio. It occassionally fails but it mainly works OK.

I am at a loss as to how I can debug an error that I cannot reproduce at will.

Any suggestions?

Is it the exact same query (including the where) that randomly fails or different where clauses that fail? Depending on your OE versions and your level of access to the actual OE server there are various logs to check/enable. — TheMadDBA
Yes. I get various errors when it fails. the most common is "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Failure getting record lock on a record from table PUB.RM-JOB.". i have tried adding SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED beforetheselect statement but that doesn't aways work either. — Steve Staple
Have you tried adding WITH (NOLOCK) on your query? I have found that controlling isolation level through multiple layers (DSN, Linked server, etc) to be problematic at best. — TheMadDBA

Jensd Jensd · Accepted Answer · 2015-08-05T10:28:02

I'm not that proficient in SqlClients and Progress myself but: the Progress Knowledgebase might give you a solution!

This entry for instance describes a similar error, even if the version mentioned might be older than the one you use? (Always post version when asking about OpenEdge - there's lots of older installations out there and Progress has evolved quite a bit during the last couple of years).

The Knowledgebase is honestly best searched using Google:

Search for instance: site:knowledgebase.progress.com MSDASQL and you'll get 48 results.

How to debug an intemittent error using Progress OpenEdge database

1 Answers