Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

OpenQuery error with Oracle

Posted on 2010-08-17
7
Medium Priority
?
605 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 1800 total points
ID: 33460184
double up the single-quotes
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 16

Assisted Solution

by:vdr1620
vdr1620 earned 200 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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 …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

885 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