Grid with Openquery for MS SQL not support?

Applications Grid / Reports
#1

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.

#2

Grid with Openquery for MS SQL not support?

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.

#3

Use and look at “Join” commands … For example: http://www.w3schools.com/sql/sql_join.asp

#4

Thank you but same issue, as the problem should come from OPENQUERY, as I need to use 2 databases data for enquiry