Solved

OpenQuery error with Oracle

Posted on 2010-08-17
7
599 Views
Last Modified: 2012-05-10
I've got a 2008 SQL Server linked to Oracle 10 server and when writing straight queries with OpenQuery everything works fine.  However I need to create proc with parameters as I will have to perform several additional steps after I get this data.

I'm getting an error that makes no sense to me, so I need an extra set of eyes to help.  I've had the code print off the dynamic SQL and it runs just fine in Oracle, but when I wrap it in the OpenQuery It fails with an incorrect syntax error at a spot that just can't be the issue.  The error is in the WHERE clause.  Message is Incorrect syntax near 'INCIDENT'.

Anybody got an idea about what's going on?




SELECT * FROM OPENQUERY(MAXPROD,'

   SELECT  DISTINCT

     INC.TICKETID,

     INC.SEVERITY,

     INC.VENDOR,

     INC.VENDORTKTNO

   FROM 

     INCIDENT INC

     LEFT OUTER JOIN (

       SELECT DISTINCT

	TICKETID,	

	MIN(CHANGEDATE) AS CHANGEDATE 		 

       FROM TKSTATUS 

       WHERE 

	TKSTATUS.CLASS = 'INCIDENT'

       AND TKSTATUS.STATUS IN ('RESOLVED','CAN','CLOSED')

       GROUP BY TICKETID) STATUSCHANGEDT 

       ON INC.TICKETID = STATUSCHANGEDT.TICKETID         

     LEFT OUTER JOIN WOCHANGE WC

       ON INC.TICKETID = WC.ORGID 	   

   WHERE 

       INC.VENDOR IS NOT NULL	

   AND INC.CREATIONDATE 

       BETWEEN to_date'6/24/2010','mm/dd/yyyy') 

       AND to_date('7/15/2010','mm/dd/yyyy'))



Msg 102, Level 15, State 1, Line 13

Incorrect syntax near 'INCIDENT'.

Open in new window

0
Comment
Question by:BPeb
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 12

Expert Comment

by:mcv22
ID: 33460176
Quotes used for strings need to be escaped:

Use ''INCIDENT'' ... ''RESOLVED'' etc
0
 
LVL 12

Expert Comment

by:mcv22
ID: 33460182
Something like :


SELECT * FROM OPENQUERY(MAXPROD,'

   SELECT  DISTINCT

     INC.TICKETID,

     INC.SEVERITY,

     INC.VENDOR,

     INC.VENDORTKTNO

   FROM 

     INCIDENT INC

     LEFT OUTER JOIN (

       SELECT DISTINCT

	TICKETID,	

	MIN(CHANGEDATE) AS CHANGEDATE 		 

       FROM TKSTATUS 

       WHERE 

	TKSTATUS.CLASS = ''INCIDENT''

       AND TKSTATUS.STATUS IN (''RESOLVED'',''CAN'',''CLOSED'')

       GROUP BY TICKETID) STATUSCHANGEDT 

       ON INC.TICKETID = STATUSCHANGEDT.TICKETID         

     LEFT OUTER JOIN WOCHANGE WC

       ON INC.TICKETID = WC.ORGID 	   

   WHERE 

       INC.VENDOR IS NOT NULL	

   AND INC.CREATIONDATE 

       BETWEEN to_date(''6/24/2010'',''mm/dd/yyyy'') 

       AND to_date(''7/15/2010'',''mm/dd/yyyy'')')

Open in new window

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 450 total points
ID: 33460184
double up the single-quotes
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 16

Assisted Solution

by:vdr1620
vdr1620 earned 50 total points
ID: 33460185
your missing an open braces near BETWEEN

BETWEEN to_date('6/24/2010','mm/dd/yyyy')
0
 
LVL 5

Author Comment

by:BPeb
ID: 33460703
vdr1620 - I think you may have it. I'll take a look in the morning.  The code I included was the output of the dynamic SQL, so the others that commented about the double quotes missed that small point, but I thank you all for helping.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33461071
I admit didn't get that far - the error was complaining about "near INCIDENT", so I noticed all the single single-qoutes.  mcv22 didn't miss it though, he fixed the entire query.
0
 
LVL 5

Author Closing Comment

by:BPeb
ID: 33467330
The problem was that the OpenQuery treats the SQL as if it were dynamic and thus requires the double quotes for processing.  Thanks to all for your help.  BTW - The missing bracket turns out to be from editing the code to look right on EE.  
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

861 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

21 Experts available now in Live!

Get 1:1 Help Now