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

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

InfoSphere Information Server

Software version:

8.5

Operating system(s):

Windows

Reference #:

1618414

Modified date:

2012-11-29

Translate my page

Machine Translation

Content navigation