Solved

syntax error returned from linked server

Posted on 2011-03-10
10
979 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

772 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