Linked Server Error Ora-00936
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up OLE DB provider “OraOLEDB.Oracle” for linked server “SWATDEVQUIP” returned message “ORA-00936: missing expression” up vote 0 down vote favorite SQL: DECLARE @Countries VARCHAR(MAX); -- Create comma-separated list of lookup values WITH Countries AS ( SELECT TOP 5 event_id FROM hitEvents ) SELECT @Countries = COALESCE(@Countries + ',', '') + '''''' + event_id + '''''' FROM Countries PRINT @Countries; DECLARE @TSQL1 nvarchar(4000); SET @TSQL1 = 'SELECT CAST(EVENTCREATIONTIME as VARCHAR(60)) AS EVENTCREATIONTIME, CAST(LOCALTIME as VARCHAR(60)) AS LOCALTIME, CONVERT(VARCHAR(32), GLOBALEVENTID, 2) AS GLOBALEVENTID FROM OPENQUERY(SWATDEVQUIP,''SELECT * FROM QUIPUSER.LOGWORTHYVIEW WHERE CONVERT(VARCHAR(32), GLOBALEVENTID, 2) IN(''''' + @Countries + ''''') '')' EXEC sp_executesql @TSQL1 I have the above sql server code. It seems fine. But when i execute this i get this error. OLE DB provider "OraOLEDB.Oracle" for linked server "SWATDEVQUIP" returned message "ORA-00936: missing expression". Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "SELECT * FROM QUIPUSER.LOGWORTHYVIEW WHERE CONVERT(VARCHAR(32), GLOBALEVENTID, 2) IN ('00003F1183951A2BCCD2ADE1B3F722C8')" for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "SWATDEVQUIP". i tried a lot but doesnt s
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Asked by: Linked server query to Oracle ORA-00936: missing expression SQL Server > Transact-SQL http://stackoverflow.com/questions/21558448/ole-db-provider-oraoledb-oracle-for-linked-server-swatdevquip-returned-messa Question 0 Sign in to vote I finally have my server linked, figured out my date issed with the decode statement and now I get a missing Expression error from Oracle. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d60c94d0-30ff-4250-9b33-8dae859afd88/linked-server-query-to-oracle-ora00936-missing-expression?forum=transactsql Here is the statement: SELECT *FROM OPENQUERY(PROD_ORACLE,'SELECT LEFT(CUST_ORDER_STUS_NME, 25) as CUST_ORDER_STUS_NME, LEFT(SRVC_ORDER_STUS_NME, 25) as SRVC_ORDER_STUS_NME, LEFT(Ser_ORD, 20) AS Ser_ORD, LEFT(CUST_ORDER_NME, 25) as CUST_ORDER_NME, LEFT(SRVC_ORDER_NME, 25) as SRVC_ORDER_NME, LEFT(CLS_ALLOWED_NBR, 2) as CLS_ALLOWED_NBR, LEFT(NOC_TO_NOC_NME, 3) as NOC_TO_NOC_NME, LEFT(CHARS_ID, 20) as CHARS_ID, LEFT(PRI_DNS_QTY, 5) as PRI_DNS_QTY, LEFT(SCNDY_DNS_QTY, 5) as SCNDY_DNS_QTY, LEFT(ORDER_TYPE_CD, 10) as ORDER_TYPE_CD, LEFT(ACTY_NME, 25) as ACTY_NME, replace(LEFT(CUST_A_NME, 50), ''|'', ''-'') as CUST_A_NME, LEFT(RLAT_CKT_ID, 8) as RLAT_CKT_ID, DECODE (BILL_CLR_DT, GREATEST(BILL_CLR_DT, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), BILL_CLR_DT, NULL), DECODE (CMPLT_DT, GREATEST(CMPLT_DT, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), CMPLT_DT, NULL), REPLACE(replace(LEFT(CMNT_TXT, 1000), char(10), '' ''), ''|'', ''-'') as CMNT_TXT, LEFT(CUST_H1_ID, 9) as CUST_H1_ID, LEFT(CUST_CMS_ID, 8) as CUST_CMS_ID, LEFT(Circuit_ID, 30) as Circuit_ID, DECODE (SO_CMPLT_DT, GREATEST(SO_CMPLT_DT, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), SO_CMPLT_DT, NULL), LEFT(DNS_ADMIN_NME, 30) AS DNS_ADMIN_NME, LEFT(DNS_ADMIN_PHN_NBR, 15) AS DNS_ADMIN_PHN_NBR, LEFT(DNS_ADMIN_EMAIL_ADDR,
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow http://dba.stackexchange.com/questions/78053/ora-00936-missing-expression-setting-up-oracle-publisher-in-sql-server the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best linked server answers are voted up and rise to the top “ORA-00936: missing expression” setting up Oracle Publisher in SQL Server up vote 1 down vote favorite I'm trying to set up an Oracle publisher in SQL Server so I can perform replication. I've gotten connected to Oracle, the drivers work great (I have other linked servers), I can select an article I want to publish, and then I can get to the very end linked server error of the "Add Oracle Publisher" wizard. However, when I click "Finish" and it attempts to actually add the article, I get the following error from the remote server: OLE DB provider "OraOLEDB.ORACLE" for linked server "MYLINKEDSERVER" returned message "ORA-00936: missing expression ORA-06512: at "MYUSERNAME.HREPL", line 1722 ORA-06512: at line 1". Error: 7215, Sev: 17, State: 1, Msg: Could not execute statement on remote server 'MYLINKEDSERVER'. I can't find this script anywhere (it seems like it's being generated by SQL Server and then executed) and I can't see what line 1722 actually contains, thought it's clear it contains a syntax error of some kind (at least from Oracle's perspective). Selecting a different table to publish gives me the exact same error on the exact same line. I can find a few posts online that reference this error (this is one of them), but they're all abandoned with no resolution. I'm hopeful that somebody has set up successful Oracle -> SQL Server replication and can shed some light on the error I'm getting as I set it up. Per a request in the comments, I scripted the steps and it fails on the final step of adding the actual article to the publication - this statement gives the error I listed above: use [distribution] exec sp_addarticle @publication = N'Orcl_NativeToSQL_Publi