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:
Copyright and trademark information
IBM, the IBM logo and ibm.com are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at "Copyright and trademark information" at www.ibm.com/legal/copytrade.shtml.