IBM Support

InfoSphere Datastage job with MSOLEDB stage fails with "OLE DB provider error: A MERGE statement must be terminated by a semi-colon (;)"

Troubleshooting


Problem

InfoSphere Datastage job with MSOLEDB stage fails with "OLE DB provider error: A MERGE statement must be terminated by a semi-colon (;)" even though merge query is terminated by a semicolon at the output tab of Before SQL tab of MSOLEDB stage

Symptom

Cause

The merge query used in Before SQL tab of MSOLEDB Stage give the warning : "OLE DB provider error: A MERGE statement must be terminated by a semi-colon (;)"

Sample merge query looks like :

MERGE StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);

Environment

Microsoft MS SQL Server 2008 in Windows Environment

Diagnosing The Problem

The job log give the following warning message :

"OLE DB provider error: A MERGE statement must be terminated by a semi-colon (;)."

Resolving The Problem

Merge statement is a new feature of Microsoft SQL Server 2008.

To fix this problem:

  • Use latest Microsoft SQL Server 2008 Native Client version 10.1 and later that supports merge queries
  • Apply the patch JR43751

[{"Product":{"code":"SSZJPZ","label":"IBM InfoSphere Information Server"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"--","Platform":[{"code":"PF033","label":"Windows"}],"Version":"8.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21618414