CF query fails in CF but works in sqlserver

Report · Aug 08, 2016

I have the following cfquery wich fails in CF and the sql in the error code is exactly the same as listed here and will work fine when placed back in sqlserver..

code will work when this section is removed:

and t.EmployeeNumber in

(select std_user_id

from HED.dbo.EDD_CPR_EMP

where TERMINATION_DATE is null)

why it is not working with above code and how to make it work??

my code:

<html>
<head>
<title>Retrieving data from Data Base</title>
</head>

<body>

<cfset intellex_datasource = "Intelex">

   select   distinct
            c.CourseTypeName                   Type,
            substring(s.WorkGroupName,1,2)     Dept,
            t.EmployeeNumber                   Win2k,
            t.FirstNam                         FirstName,
            t.LastName                         LastName,
            a.CourseName                       Course,
            b.NextDate                         NextDate
    from trnTraining a,trnEmployeeTraining b, trnCourseType c, trnEmployeeWorkgroup r, trnWorkgroups s, sysEmployee t
    where b.PriorityId = 'Y'
    and a.CourseId = b.CourseId
    and a.CourseTypeId = c.CourseTypeId
    and c.CourseTypeName = 'SP'
and r.employeeID = b.employeeID
and r.employeeID = t.employeeID
and t.EmployeeNumber in
      (select std_user_id
        from HED.dbo.EDD_CPR_EMP
        where TERMINATION_DATE is null)
    and t.deleted is NULL
and r.workgroupID = s.workgroupid
and len(s.WorkGroupName) <= 2
    and b.NextDate >= getdate()
order by t.LastName, t.EmployeeNumber , a.CourseName
;

</cfquery>
<h4>Employee Data Based on Criteria from Form</h4>
<cfoutput query="get_data">

#Get_data.type#, 'Check it', #Get_data.win2k#<br>

</cfoutput>

</body>
</html>

Report · Aug 08, 2016

Your query assumes that some employeeNumbers are userIDs. So verify this by dumping the two lists and comparing by eye. Do some employeeNumbers indeed appear in the list of userIDs?

select EmployeeNumber
from sysEmployee

</cfquery>

select std_user_id
from HED.dbo.EDD_CPR_EMP
where TERMINATION_DATE is null

</cfquery>

Report · Aug 09, 2016

Thank you for the quick response.

a bit of additional information may help to resolve this problem. intellex is one database and HED is another and both on the same server. When I run the sql code in sql and using the intelex database , I get back the correct result set for both queries.

running the code you supplied result in error: - seems like CF is unable to handle a sql accessing two different databases?

Invalid object name 'HED.dbo.EDD_CPR_EMP'.

#1

top 5 EmployeeNumber

from sysEmployee

where EmployeeNumber is not null

result

CG053C

VH035B

CC625B

GD763C

YR725B

# 2

top 5 upper(std_user_id)

from HED.dbo.EDD_CPR_EMP

where TERMINATION_DATE is null

VI725B

GE035B

AU225B

YS035B

PL035B

Report · Aug 09, 2016

CF_newtome wrote:
- seems like CF is unable to handle a sql accessing two different databases?

CF can do it, but I'm guessing that the credentials you are using in the CF datasource is where the problem is.

Log into the SQL Server in SQL Server Management Studio using the same credentials used by the datasource and test your query.

Cheers

Eddie

Report · Aug 09, 2016

EddieLotter probably hit the nail on the head. If the two databases use different access credentials, then you won't be able to do this in a single query. Some (if not all) databases can also be set so that different _schemas_ on the same database can't cross-access data; it's a segmentation security protocol.

If Eddie is right, and the two databases use different credentials, one possible solution would be to manually create a query object, fill it with data from one database, then use a different query for the second database to fill in the missing data from the first query. This could be problematic, though, if the query (or queries) include one-to-many or many-to-one aggregation.

V/r,

^_^

Report · Aug 09, 2016

EddieLotter saved the day and pointed to root cause of the problem. fixed the datasource with correct information solved the problem. FIVE gold stars.

CF query fails in CF but works in sqlserver

1 Correct answer