Solved

syntax error returned from linked server

Posted on 2011-03-10
10
988 Views
Last Modified: 2012-05-11
Hi,
      I'm firing the following query at an Oracle database via a linked server in SQL Server 2005

I'm getting the the error in the code snippet returned.

if i can draw your attention to  the following
OLE DB provider "MSDAORA" for linked server "REMEDY_PROD" returned message "ORA-00904: "HPD_HELP_DESK"."CLOSED_DATE": invalid identifier

The column CLOSED_DATE does exist in  the table but it looks like to me I might need to escape (HPD_HELP_DESK.CLOSED_DATE)

any help appreciated...thanks


SELECT * FROM OPENQUERY(REMEDY_PROD,'SELECT Incident_Number, NAME AS CI_NAME, generic_categorization_tier_1 as cause, RESOLUTION_METHOD,REQUEST_ID01 as RelatedItem, REQUEST_TYPE.REQUEST_TYPE as Related_Item_Type FROM ARADMIN.HPD_HELP_DESK LEFT JOIN ARADMIN.HPD_ASSOCIATIONS HPD_ASSOCIATIONS ON HPD_ASSOCIATIONS.REQUEST_ID02 = HPD_HELP_DESK.Incident_Number LEFT JOIN ARADMIN.BMC_CORE_BMC_BASEELEMENT ON HPD_ASSOCIATIONS.REQUEST_ID01 = BMC_CORE_BMC_BASEELEMENT.RECONCILIATIONIDENTITY LEFT JOIN ARADMIN.SRM_Request on SRM_Request.APPREQUESTID = HPD_HELP_DESK.Incident_Number  LEFT JOIN (SELECT DISTINCT FIELD_ENUM_VALUES.ENUMID,	FIELD_ENUM_VALUES.VALUE AS REQUEST_TYPE	FROM   ARADMIN.FIELD_ENUM_VALUES	WHERE  FIELD_ENUM_VALUES.FIELDID = 1000000211 AND (ARADMIN.fn_adjusted_date(HPD_HELP_DESK.CLOSED_DATE) BETWEEN timestamp ''2011-03-01 00:00:00'' AND timestamp ''2011-03-07 23:59:59'') AND FIELD_ENUM_VALUES.SCHEMAID = 772)	REQUEST_TYPE ON HPD_ASSOCIATIONS.REQUEST_TYPE01 = REQUEST_TYPE.ENUMID where ASSIGNED_SUPPORT_ORGANIZATION = ''Cognizant'' order by Incident_Number') 
OLE DB provider "MSDAORA" for linked server "REMEDY_PROD" returned message "ORA-00904: "HPD_HELP_DESK"."CLOSED_DATE": invalid identifier
".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT Incident_Number, NAME AS CI_NAME, generic_categorization_tier_1 as cause, RESOLUTION_METHOD,REQUEST_ID01 as RelatedItem, REQUEST_TYPE.REQUEST_TYPE as Related_Item_Type FROM ARADMIN.HPD_HELP_DESK LEFT JOIN ARADMIN.HPD_ASSOCIATIONS HPD_ASSOCIATIONS ON HPD_ASSOCIATIONS.REQUEST_ID02 = HPD_HELP_DESK.Incident_Number LEFT JOIN ARADMIN.BMC_CORE_BMC_BASEELEMENT ON HPD_ASSOCIATIONS.REQUEST_ID01 = BMC_CORE_BMC_BASEELEMENT.RECONCILIATIONIDENTITY LEFT JOIN ARADMIN.SRM_Request on SRM_Request.APPREQUESTID = HPD_HELP_DESK.Incident_Number  LEFT JOIN (SELECT DISTINCT FIELD_ENUM_VALUES.ENUMID,	FIELD_ENUM_VALUES.VALUE AS REQUEST_TYPE	FROM   ARADMIN.FIELD_ENUM_VALUES	WHERE  FIELD_ENUM_VALUES.FIELDID = 1000000211 AND (ARADMIN.fn_adjusted_date(HPD_HELP_DESK.CLOSED_DATE) BETWEEN timestamp '2011-03-01 00:00:00' AND timestamp '2011-03-07 23:59:59') AND FIELD_ENUM_VALUES.SCHEMAID = 772)	REQUEST_TYPE ON HPD_ASSOCIATIONS.REQUEST_TYPE01 = REQUEST_TYPE.ENUMID where ASSIGNED_SUPPORT_ORGANIZATION = 'Cognizant' order by Incident_Number" for execution against OLE DB provider "MSDAORA" for linked server "REMEDY_PROD".

Open in new window

0
Comment
Question by:blossompark
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35097642
not sure, but you may have to specify the schema also, so ARADMIN.HPD_HELP_DESK becomes ARADMIN.dbo.HPD_HELP_DESK
0
 

Author Comment

by:blossompark
ID: 35097719
Hi knightEknight,   unfortunately same error

SELECT * FROM OPENQUERY(REMEDY_PROD,'SELECT Incident_Number, NAME AS CI_NAME, generic_categorization_tier_1 as cause, RESOLUTION_METHOD,REQUEST_ID01 as RelatedItem, REQUEST_TYPE.REQUEST_TYPE as Related_Item_Type FROM ARADMIN.HPD_HELP_DESK LEFT JOIN ARADMIN.HPD_ASSOCIATIONS HPD_ASSOCIATIONS ON HPD_ASSOCIATIONS.REQUEST_ID02 = HPD_HELP_DESK.Incident_Number LEFT JOIN ARADMIN.BMC_CORE_BMC_BASEELEMENT ON HPD_ASSOCIATIONS.REQUEST_ID01 = BMC_CORE_BMC_BASEELEMENT.RECONCILIATIONIDENTITY LEFT JOIN ARADMIN.SRM_Request on SRM_Request.APPREQUESTID = HPD_HELP_DESK.Incident_Number  LEFT JOIN (SELECT DISTINCT FIELD_ENUM_VALUES.ENUMID,      FIELD_ENUM_VALUES.VALUE AS REQUEST_TYPE      FROM   ARADMIN.FIELD_ENUM_VALUES      WHERE  FIELD_ENUM_VALUES.FIELDID = 1000000211 AND (ARADMIN.fn_adjusted_date(ARADMIN.dbo.HPD_HELP_DESK.CLOSED_DATE) BETWEEN timestamp ''2011-03-01 00:00:00'' AND timestamp ''2011-03-07 23:59:59'') AND FIELD_ENUM_VALUES.SCHEMAID = 772)      REQUEST_TYPE ON HPD_ASSOCIATIONS.REQUEST_TYPE01 = REQUEST_TYPE.ENUMID where ASSIGNED_SUPPORT_ORGANIZATION = ''Cognizant'' order by Incident_Number')
OLE DB provider "MSDAORA" for linked server "REMEDY_PROD" returned message "ORA-00904: "ARADMIN"."DBO"."HPD_HELP_DESK"."CLOSED_DATE": invalid identifier
".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT Incident_Number, NAME AS CI_NAME, generic_categorization_tier_1 as cause, RESOLUTION_METHOD,REQUEST_ID01 as RelatedItem, REQUEST_TYPE.REQUEST_TYPE as Related_Item_Type FROM ARADMIN.HPD_HELP_DESK LEFT JOIN ARADMIN.HPD_ASSOCIATIONS HPD_ASSOCIATIONS ON HPD_ASSOCIATIONS.REQUEST_ID02 = HPD_HELP_DESK.Incident_Number LEFT JOIN ARADMIN.BMC_CORE_BMC_BASEELEMENT ON HPD_ASSOCIATIONS.REQUEST_ID01 = BMC_CORE_BMC_BASEELEMENT.RECONCILIATIONIDENTITY LEFT JOIN ARADMIN.SRM_Request on SRM_Request.APPREQUESTID = HPD_HELP_DESK.Incident_Number  LEFT JOIN (SELECT DISTINCT FIELD_ENUM_VALUES.ENUMID,      FIELD_ENUM_VALUES.VALUE AS REQUEST_TYPE      FROM   ARADMIN.FIELD_ENUM_VALUES      WHERE  FIELD_ENUM_VALUES.FIELDID = 1000000211 AND (ARADMIN.fn_adjusted_date(ARADMIN.dbo.HPD_HELP_DESK.CLOSED_DATE) BETWEEN timestamp '2011-03-01 00:00:00' AND timestamp '2011-03-07 23:59:59') AND FIELD_ENUM_VALUES.SCHEMAID = 772)      REQUEST_TYPE ON HPD_ASSOCIATIONS.REQUEST_TYPE01 = REQUEST_TYPE.ENUMID where ASSIGNED_SUPPORT_ORGANIZATION = 'Cognizant' order by Incident_Number" for execution against OLE DB provider "MSDAORA" for linked server "REMEDY_PROD".
0
 

Author Comment

by:blossompark
ID: 35097724
the error happens when i introduce
(ARADMIN.fn_adjusted_date(ARADMIN.HPD_HELP_DESK.CLOSED_DATE) BETWEEN timestamp ''" + CONVERT(varchar(19) , @start_date_range , 120) + "'' AND timestamp ''" + CONVERT(varchar(19) , @end_date_range , 120) + "''

to the code
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 22

Expert Comment

by:Helena Marková
ID: 35106189
Maybe the problem is caused by function ARADMIN.fn_adjusted_date. What happens if you use only:

(ARADMIN.HPD_HELP_DESK.CLOSED_DATE BETWEEN ... ?
0
 

Author Comment

by:blossompark
ID: 35106555
Hi Henka thanks for your response...
ran this:
SELECT * FROM OPENQUERY(REMEDY_PROD,'SELECT Incident_Number, NAME AS CI_NAME, generic_categorization_tier_1 as cause, RESOLUTION_METHOD,REQUEST_ID01 as RelatedItem, REQUEST_TYPE.REQUEST_TYPE as Related_Item_Type FROM ARADMIN.HPD_HELP_DESK LEFT JOIN ARADMIN.HPD_ASSOCIATIONS HPD_ASSOCIATIONS ON HPD_ASSOCIATIONS.REQUEST_ID02 = HPD_HELP_DESK.Incident_Number LEFT JOIN ARADMIN.BMC_CORE_BMC_BASEELEMENT ON HPD_ASSOCIATIONS.REQUEST_ID01 = BMC_CORE_BMC_BASEELEMENT.RECONCILIATIONIDENTITY LEFT JOIN ARADMIN.SRM_Request on SRM_Request.APPREQUESTID = HPD_HELP_DESK.Incident_Number  LEFT JOIN (SELECT DISTINCT FIELD_ENUM_VALUES.ENUMID,      FIELD_ENUM_VALUES.VALUE AS REQUEST_TYPE      FROM   ARADMIN.FIELD_ENUM_VALUES      WHERE  FIELD_ENUM_VALUES.FIELDID = 1000000211
AND (HPD_HELP_DESK.CLOSED_DATE
 BETWEEN timestamp ''2011-03-01 00:00:00'' AND timestamp ''2011-03-07 23:59:59'') AND FIELD_ENUM_VALUES.SCHEMAID = 772)      REQUEST_TYPE ON HPD_ASSOCIATIONS.REQUEST_TYPE01 = REQUEST_TYPE.ENUMID where ASSIGNED_SUPPORT_ORGANIZATION = ''Cognizant'' order by Incident_Number')

result:
OLE DB provider "MSDAORA" for linked server "REMEDY_PROD" returned message "ORA-00904: "HPD_HELP_DESK"."CLOSED_DATE": invalid identifier
".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT Incident_Number, NAME AS CI_NAME, generic_categorization_tier_1 as cause, RESOLUTION_METHOD,REQUEST_ID01 as RelatedItem, REQUEST_TYPE.REQUEST_TYPE as Related_Item_Type FROM ARADMIN.HPD_HELP_DESK LEFT JOIN ARADMIN.HPD_ASSOCIATIONS HPD_ASSOCIATIONS ON HPD_ASSOCIATIONS.REQUEST_ID02 = HPD_HELP_DESK.Incident_Number LEFT JOIN ARADMIN.BMC_CORE_BMC_BASEELEMENT ON HPD_ASSOCIATIONS.REQUEST_ID01 = BMC_CORE_BMC_BASEELEMENT.RECONCILIATIONIDENTITY LEFT JOIN ARADMIN.SRM_Request on SRM_Request.APPREQUESTID = HPD_HELP_DESK.Incident_Number  LEFT JOIN (SELECT DISTINCT FIELD_ENUM_VALUES.ENUMID,      FIELD_ENUM_VALUES.VALUE AS REQUEST_TYPE      FROM   ARADMIN.FIELD_ENUM_VALUES      WHERE  FIELD_ENUM_VALUES.FIELDID = 1000000211
AND (HPD_HELP_DESK.CLOSED_DATE
 BETWEEN timestamp '2011-03-01 00:00:00' AND timestamp '2011-03-07 23:59:59') AND FIELD_ENUM_VALUES.SCHEMAID = 772)      REQUEST_TYPE ON HPD_ASSOCIATIONS.REQUEST_TYPE01 = REQUEST_TYPE.ENUMID where ASSIGNED_SUPPORT_ORGANIZATION = 'Cognizant' order by Incident_Number" for execution against OLE DB provider "MSDAORA" for linked server "REMEDY_PROD".
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 35106595
Have you tried query directly on Oracle database ?
0
 

Author Comment

by:blossompark
ID: 35107084
I only have linked server access to oracle db
0
 
LVL 22

Accepted Solution

by:
Helena Marková earned 500 total points
ID: 35107339
I see that this
AND (ARADMIN.HPD_HELP_DESK.CLOSED_DATE
 BETWEEN timestamp '2011-03-01 00:00:00' AND timestamp '2011-03-07 23:59:59')
belongs to
(SELECT DISTINCT FIELD_ENUM_VALUES.ENUMID,FIELD_ENUM_VALUES.VALUE AS REQUEST_TYPE FROM ARADMIN.FIELD_ENUM_VALUES
WHERE  FIELD_ENUM_VALUES.FIELDID = 1000000211
AND (ARADMIN.HPD_HELP_DESK.CLOSED_DATE
 BETWEEN timestamp '2011-03-01 00:00:00' AND timestamp '2011-03-07 23:59:59') AND FIELD_ENUM_VALUES.SCHEMAID = 772)

Is it true, isn't ?

In such a case table ARADMIN.HPD_HELP_DESK
ought to be joined with ARADMIN.FIELD_ENUM_VALUES.

(SELECT DISTINCT FIELD_ENUM_VALUES.ENUMID,FIELD_ENUM_VALUES.VALUE AS REQUEST_TYPE FROM ARADMIN.FIELD_ENUM_VALUES<<join condition>>ARADMIN.HPD_HELP_DESK
...)

In other case remove it from part (SELECT DISTINCT .... AND FIELD_ENUM_VALUES.SCHEMAID = 772)
and put it to the right place.
0
 

Author Comment

by:blossompark
ID: 35309957
Hi Henka, firstly apologies  for "abandoning" this question.....after all your input it is bad manners of me and I apologize...
the problem was caused by the following code being in the wrong location
AND (ARADMIN.fn_adjusted_date(HPD_HELP_DESK.CLOSED_DATE) BETWEEN timestamp ''2011-03-01 00:00:00'' AND timestamp ''2011-03-07 23:59:59'')

moving this to from the "derived" where clause to the "external" where clause resolved the issue...once again thanks again for all your help

0
 

Author Closing Comment

by:blossompark
ID: 35309959
Hi Henka, firstly apologies  for "abandoning" this question.....after all your input it is bad manners of me and I apologize...
the problem was caused by the following code being in the wrong location
AND (ARADMIN.fn_adjusted_date(HPD_HELP_DESK.CLOSED_DATE) BETWEEN timestamp ''2011-03-01 00:00:00'' AND timestamp ''2011-03-07 23:59:59'')

moving this to from the "derived" where clause to the "external" where clause resolved the issue...once again thanks again for all your help
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question