1

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 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_Publication',
                   @article = N'TCUSTMER', 
                   @publisher = N'SNR', 
                   @source_owner = N'OGGATE', 
                   @source_object = N'TCUSTMER',
                   @type = N'logbased', 
                   @description = null, 
                   @creation_script = null, 
                   @pre_creation_cmd = N'drop', 
                   @schema_option = 0x0000000000000083, 
                   @use_default_datatypes = 1, 
                   @destination_table = N'TCUSTMER', 
                   @destination_owner = N'dbo', 
                   @vertical_partition = N'false', 
                   @ins_cmd = N'CALL sp_MSins_OGGATETCUSTMER', 
                   @del_cmd = N'CALL sp_MSdel_OGGATETCUSTMER', 
                   @upd_cmd = N'SCALL sp_MSupd_OGGATETCUSTMER'
GO
  • Please show the code. – RLF Sep 30 '14 at 15:59
  • @RLF: There's isn't any code, as I'm using the SSMS "Add Oracle Publisher" wizard rather than using T-SQL directly. That said, the error seems to occur when the wizard executes sp_addarticle, which is turn executes sp_ORAaddarticle, from which the code is hidden (because it's a system stored proc). This, in turn, executes a package deployed to the oracle database called "MYUSERNAME.HREPL" where the error is actually occurring. I know that's not much help, but we're sifting through that package now to see if can find the statement. – SqlRyan Sep 30 '14 at 16:19
  • Does the same thing happen if you create the Oracle publication using T-SQL? – Brandon Williams Oct 1 '14 at 21:09
  • @BrandonWilliams I updated the question to add my T-SQL code, generated by the publication wizard. All the pre-steps execute without issue, and it's the final step that actually adds the article to the Oracle publication that gives me the error. – SqlRyan Oct 2 '14 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.