Highlighted

CF query fails in CF but works in sqlserver

Community Beginner ,
Aug 08, 2016

Copy link to clipboard

Copied

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>

<!--- Set Datasource(s) --->
<cfset intellex_datasource = "Intelex">

<CFQUERY NAME="get_data" datasource="#intellex_datasource#">

   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>

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

Views

239

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

CF query fails in CF but works in sqlserver

Community Beginner ,
Aug 08, 2016

Copy link to clipboard

Copied

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>

<!--- Set Datasource(s) --->
<cfset intellex_datasource = "Intelex">

<CFQUERY NAME="get_data" datasource="#intellex_datasource#">

   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>

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

Views

240

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Aug 08, 2016 0
Adobe Community Professional ,
Aug 08, 2016

Copy link to clipboard

Copied

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?

<cfquery name="get_empNumber" datasource="#intellex_datasource#">

  select EmployeeNumber
        from sysEmployee

</cfquery>

<cfquery name="get_userID" datasource="#intellex_datasource#">

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

</cfquery>

<cfdump var="#get_empNumber#">

<cfdump var="#get_userID#">

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 08, 2016 0
Community Beginner ,
Aug 09, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 09, 2016 0
Advocate ,
Aug 09, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 09, 2016 1
LEGEND ,
Aug 09, 2016

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 09, 2016 0
Community Beginner ,
Aug 09, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 09, 2016 1