IBM Support What's New?

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

Technote (troubleshooting)


Problem(Abstract)

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

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

Document information

More support for: InfoSphere Information Server

Software version: 8.5

Operating system(s): Windows

Reference #: 1618414

Modified date: 2012-11-29