Solved

syntax error returned from linked server

Posted on 2011-03-10
10
1,000 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
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

 
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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

636 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