Solved

syntax error returned from linked server

Posted on 2011-03-10
10
970 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
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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.

863 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

22 Experts available now in Live!

Get 1:1 Help Now