Solved

syntax error returned from linked server

Posted on 2011-03-10
10
963 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
  • 6
  • 3
10 Comments
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 22

Expert Comment

by:Helena Marková
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 22

Expert Comment

by:Helena Marková
Comment Utility
Have you tried query directly on Oracle database ?
0
 

Author Comment

by:blossompark
Comment Utility
I only have linked server access to oracle db
0
 
LVL 22

Accepted Solution

by:
Helena Marková earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now