I found that if my SQL statement contains “openquery”, an error will popup. My SQL statement as below
SELECT
EMPLOYEE.EMPID,
SURNAME,
FIRSTNAME,
CHINAME,
CHRISTIANNAME,
SEX,
DOB,
LEFT(IDNO,LEN(IDNO)-3)+‘XXX’ as HKID,
ENROLLDATE,
LEAVEDATE,
ADDRESS,
employmentStatus
FROM
dbo.EMPLOYEE, OPENQUERY(MYSQL, ‘SELECT EMPID, employmentStatus FROM hkfrc_dev.employeesStatus, hkfrc_dev.employmentStatus where employeesStatus.employmentStatusID = employmentStatus.employmentStatusID’) mysql
WHERE
(CO = ‘FC’) and EMPLOYEE.EMPID = mysql.EMPID;
Because I need to get information from both databases, is there any way to solve the openquery problem, or any work around not to use openquery? Thank you.