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
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
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