Solved

OpenQuery error with Oracle

Posted on 2010-08-17
7
601 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
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.

 
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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PROPERCASE SCRIPT IN SQL 3 21
Sql query with where clause 2 45
SQL Server maintenance plan 8 53
SQL Server 2008 R2 service pack updates 5 42
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

679 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