Solved

OpenQuery error with Oracle

Posted on 2010-08-17
7
602 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
[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
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

751 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