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
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21618414