Help In Setting Up Oracle Publisher To 64-bit Distributor
Aug 28, 2007
I have Oracle 64-bit drivers installed. I can run SQLPLUS and it works. I can create linked servers to oracle and that works also. I am having this error when I try to add oracle as a publisher. OraOLEDB.Oracle is already set to 'Allow InProces'.
Msg 21626, Level 16, State 1, Procedure sp_MSrepl_testconnection, Line 110
Unable to connect to Oracle database server 'DTMART' using the Oracle OLEDB provider OraOLEDB.Oracle. For addition information, see SQL Server Error 21626 in Troubleshooting Oracle Publishers in SQL Server Books Online.
Msg 21747, Level 16, State 1, Procedure sp_MSrepl_adddistpublisher, Line 463
Cannot establish a connection to the Oracle Publisher 'DTMART'. Verify connection information and ensure that you can connect to the Publisher through a tool like SQL*PLUS.
I AM TRYING TO SET UP A DISTRIBUTOR ON SQL SERVER 2005.
THE LAST STEP WHEN I AM TRING TO CONNECT I GET THE ERROR BELOW:
“ORACLE SERVER INSTANCE 'FOOBAR' CANNOT BE ENABLED AS A PUBLISHER BECAUSE OF THE FOLLOWING ERROR:
ADDITIONAL INFORMATION: UNABLE TO RUN SQL*PLUS. MAKE CERTAIN THAT A CURRENT VERSION OF THE ORACLE CLIENT CODE IS INSTALLED AT THE DISTRIBUTOR. FOR ADDITIONAL INFORMATION, SEE SQL SERVER ERROR 21617 IN TROUBLESHOOTING ORACLE PUBLISHERS IN SQL SERVER BOOKS ONLINE. (MICROSOFT SQL SERVER, ERROR: 21617)�
RESEARCHING THE ERROR IT STATES TO MAKE SURE SQLPLUS IS SET UP CORRECTLY IN THE PATH. THE PATH IS CORRECT. TESTING SQL PLUS IT WORKS; AND I CAN ALSO CONNECT TO THE ORACLE DB IN BUSINESS INTELLIGENCE.
I'm reading the MS online docs about replication. there's something not so clear until so far.
I have one central server and 7 client servers. Sometimes new data is entered at one client server. This should be replicated (when client comes online) to the Central Server (merge replication).
Also, the other way, once the central server gets new data (that was replicated from a client), it should be again replicated to all 6 other clients (from the central server).
now, who should i configure as the distributor, publisher and subscriber?
Hi - hoping some folks here can help me. I've got a SQL 2000, Service Pack 4 database that I need to publish, but I'm getting the following error on the exec sp_addpublication statement when I try to set up publication in Management Studio:
Msg 8526, Level 16, State 2, Procedure sp_addpublication, Line 802 Cannot go remote while the session is enlisted in a distributed transaction that has an active savepoint.
I also tried to set up the Publisher in Enterprise Studio, on the theory that the system sproc was not backwards compatible, and there I got this error:
Error 627: Cannot use SAVE TRANSACTION within a distributed transaction
There is one proc in the publisher's database that uses SAVE TRANSACTION, but it isn't currently being run and it isn't dependent on the table being published. Has anybody else encountered this? Any ideas about how to make this go away?
I am setting up  transaction replication in SQL Server 2012 with three individual servers for Distributor, Publisher, Subscriber, But I want to know how to set the folder for Snapshot agent like whether the folder should be a network shared folder or normal local folder.
I am investigating scenarios for our new replication strategy and we have 2 very powerfull machines. 1 however more powerful than the other.
The distributor will be pushing (merge) data to 40 replicas around the globe, the database is about 5GB in size and uses dynamic filtering to sort out who needs what data.
The publisher will only be working with the distributor it is not doing any other work.
I am guessing the distributor will have the majorty of the work to do in this case and should have the more powerful machine. Does anyone know if this is correct?
Can push and pull subscriptions be configured on the publisher/distributer server?
We have server A running SQL Server 2000 configured as Publisher/Distributor and also a Push subscription (transactional replication) to Server B configured as Subscriber.
The idea was to have Server B as backup server if Server A goes down. Users will continue to update data in server B till server A is up.
Once server A is up, I want to synchronise the up-to-minuit date from Server B to Server A in the form of Transactional replication configured as "Pull Subscription" from Server A.
I am having a problem now. I cannot drop my publisher and publication because i was uninstalled the remote distibutor before i disable al lthe publication. Now the remote distibutor no longer exist.
hi, Im I want to configure my sqlserver 2005 server as both subscriber and distributor for transactional replication purposes. I want to configure the sql server 2000 as the publisher.
When I try to configure the publisher in the sql 2000 server, Im not being able to establish the connection with the distributor 2005 sql server in any of the 2 possible cases. case1-Using windows authentication- message:need to use management studio or SMO case2-Using sql server authentication-message:authentication failed
Question: Is it possible at all to do this? or should I make the distributor to reside in a sql 2000 server?
For transactional replication, are there any issues and is it even possible to have 32-bit SQL 2000 publisher and SQL 2005 64-bit distributor and subscriber? Thanks
The subscriptions will be pulled from the distributor and pushed to the subscriber. Basically, all the work will be done by the distributor (in the hope of improving performance).
Has anyone had any experience of a similar architecture? All SQL servers are running with the latest service packs.
Hello All,The setting is SQL server 7, on Windows NT.If the distributor can not access the log on the publisher database ( butsubscribers are not deleted yet), will the full backup of the publisherdatabase truncate the transaction log? My guess is, it will not truncate thelog.Can any one confirm please?Thanks,Mokles
Are there any requirements that dictate the SQL Server version for the distribution agent for a SQL 2000 publisher with a transactional push subscription to a SQL 2005 subscriber?
I am not able to connect to Remote Distributor from Publisher using Administrative Link Password. I have configured the Distributor on 1 m/c and Publisher on another m/c. When i use the wizard to configure the publisher using remote distributor. I have also specified the same Admin Password link (distributor_admin) in the distributor m/c. Â I am getting the following message: Â TITLE: New Publication Wizard------------------------------ SQL Server could not connect to the Distributor using the specified password.
Connection to server [SANMENON] failed.OLE DB provider "SQLNCLI" for linked server "D956CF83-AE2E-4FC5-83DD-BE90D84A3950" returned message "Login timeout expired".OLE DB provider "SQLNCLI" for linked server "D956CF83-AE2E-4FC5-83DD-BE90D84A3950" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". (Microsoft SQL Server, Error: 21670)
I am trying to make an oracle publiching from sql server 2005 enterprise final release, i installed the oracle client 10.2 (10g) on the same server where sql server already installed, i made different connection to oracle database instance and it was ok.
from sql server : right click on publication -New oracle publication-Next-Add Oracle Publisher-Add button-Add Oracle Publisher-i entered server insttance test1 and their users and passwords--connect --->
the oracle publisher is displayed in the list of publisher but when press ok i got the following error :
An error occurred applying the changes to the Distributor.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476
SQL Server could not enable 'test1' as a Publisher. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The permissions associated with the administrator login for Oracle publisher 'test1' are not sufficient. Changed database context to 'master'. (Microsoft SQL Server, Error: 21684)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21684&LinkId=20476
Oracle server cannot be enabled as a Publisher because of the following error: Additional inlonnation: Unable to connect to Oracle database server 'abc€™ using the Microsoft OLEDB provider MsDAORA. For addition ilorrnation, see SQL Server Eiror 21627 in Troubleshooting Oracle Publishers in sQL Server Books 0nline. (Microsoft sQL Server, Error: 21627)
We are going away from a 2003 Windows Server OS with SQL Server 2008 R2 to a 2012 Windows Server with 2012 SQL Server. Both the distributor and subscriber resides on 2003 Windows Server and the focus will be to migrate those databases to the 2012 SQL Server.
 We would also like to avoid sending down a new snapshot due to logic in the replication process (major headache to clean up data).  what's the best approach in moving the distributor and subscriber databases without having to run a snapshot? Â
In theory and needs testing:
1. Work with business users to get downtime
2. Stop sql apply so no new changes are going to the Oracle publisher database
3. Remove subscription, publication and distributor from current replication
4. Oracle DBA to clear out replication related objects - fresh clean slate
5. Use log shipping to apply last t-log and restore db with Keep_Replication
6. Set up new distribution, publication (keep existing object unchanged), and subscriber (without initialization) Will this work?
I am receiving the following error message when trying to create a new Oracle Publisher.
Msg 21684, Level 16, State 1, Procedure sp_ORACheckAdminPrivileges, Line 136
The permissions associated with the administrator login for Oracle publisher 'NT02' are not sufficient.
We have added the following permissions manually (not through a role) in Oracle:
CREATE PUBLIC SYNONYM and DROP PUBLIC SYNONYM CREATE PROCEDURE CREATE SEQUENCE CREATE SESSION CREATE ANY TRIGGER. CREATE TABLE CREATE VIEW
And we have also tried the Program FilesMicrosoft SQL Server<InstanceName>MSSQLInstalloracleadmin.sql script.
In both cases we are receiving the error message above. The only way we seem to be able to get around the error is to grant the DBA role to the replication schema user, which doesn't seem right.
Has anybody run into this problem before? How did you get around the problem?
Also is there a way to view sp_ORACheckAdminPrivileges because it's doesn't seem to be viewable anywhere in the database? This would at least let us know what it's looking for from an Oracle standpoint.
I have a setup where I need to replicate the database which is actually subscribing from another database. The current setup is all in SQL Server 2000. I need to now setup a Distrbutor on a SQL server 2005 and publish the database using this distributor to another server on SQL server 2000.
Has anybody done this before. If yes what will I need to check. Can you please let me know :-
1) SQL Server 2000 which SP should be installed to support this enviroment.
2) SQL Server 2005 which SP should be installed to support this environment.
1 SERVERNAME SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
1 SERVERNAMEINSTANCE SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
IIS 5.0
SQL Server CE 2.0 (sp3a)
Merge replication previously working fine. The problem
After migrating MSDE from sp3a to sp4 (in order to fix CE replication bug) I can't sync to MSDE. I got the following error code: [29045]->[Initializing SQL Server Reconciler has failed.] so I've looked into SQL Server CE log file and I've found the following error message:
2006/02/07 19:44:23 Thread=9A8 RSCB=4 Command=SYNC Hr=00000000 The process could not connect to Distributor 'Distributor-name'. 20084
2006/02/07 19:44:23 Thread=9A8 RSCB=4 Command=SYNC Hr=00000000 Data source name not found and no default driver specified
I've already tried without success
Uninstalling all SQL Server 2000 (EE) instances (except for the "no instance") Unistalling and re-install MSDE (sp4) Re-registering sscerp20.dll. Rebooting server. Restarting IIS.
Does anyone know where I can get some good examples of how to set up a linked server to an Oracle data source in SQL 2000? I can't seem to get a link set up to my Oracle database. In particular I am wondering how the communication works between OLEDB to the Oracle Instance and how to trouble shoot issues (for example, would is the Oracle Listener what OLEDB is talking to?) Thanks!!
In Sql server if i have a varchar field of length 12, if i enter 12 chinese characters, it fails. In Oracle there is a setting nls_length_semantics.If you put char in nls_length_semantics , it takes 12 characters irrespective of english(one byte) or chinese(doublr byte).
I have a requirement to set up an Oracle linked server, Ive never done this before and in fact never touched Oracle so I dont really know what I am doing.
I have a wired situation..!I set up transactional replication on one of my development server (SQL2000 Dev Edition with sp4).It is running fine without any issues and all of a sudden, i noticed inmy repication monitor tab under Publisher where I usually see thepublication is empty now.I do see the snapshot agent, log reader and distribution agent under myagents inside the replication Monitor. But its usefull to see all 3agents in one window under publisher before. What happend? Is there anyway to get that inside that monitor? Has someone encountered thissitation before? Please advise....After that I tried to create a new set of replication on differentdatabase on the same server and i dont see those either underReplication Monitor - Publishers....All it says is (No Items)....I would appreciate any help to correct this issue... Thanks in advance..
After extensive testing I have found that bcp's run almost twice as fast on my dedicated Distributor than on either the Publisher or Subscriber. I was wondering if there is a way to make the bcp's spawned by creating a Snapshot run on the distributor and not the Publisher.
I was wondering if you could set up multiple distributors for one database?For example:Server A needs to publish data to server B so I set up server A's distributor as server B. Later Server A needs to publish data to server C so I want to set up the distributor for this replication to use server C.I don't think this is possible as you need to set up the distributor for the entire DB when its created not for each individual replication job.Any help is appreciated.Nick
I am having problem creating a distributor on my sever, when i do it thru the enterprise manager it defaults to another sever's path and when i put the severs path it dosen't find it. and when i do it thru query analzer this is what i get
Server: Msg 6, Level 16, State 1, Procedure sp_adddistributor, Line 147 Specified SQL server not found. Remote logins for remote server 'repl_distributor' have been dropped. Server dropped. Server added. Server network name set.
and for this it is going to another server sp_adddistributor @distributor = '158.72.80.48' ,@heartbeat_interval = 10 , @password = 'grants831' -- , @from_scripting = from_scripting
error message
Server: Msg 18482, Level 14, State 1, Procedure sp_adddistributor, Line 147 Could not connect to server 'GEMS' because 'vivian' is not defined as a remote server. Remote logins for remote server 'repl_distributor' have been dropped. Server dropped. Server added. Server network name set.
Completed SQL 2000 Transactional Replication from distributor/publisher to Subscriber. (with identity problem solved)
NOW wizard disallows set up subscriber to publish back to the Distributor/Publisher. "Distributor not known" message on Subscriber server when we open configure Publisher-Subscriber tool.
Any experience using this wizard to set this up would be appreciated.
For remote distributor (transactional replication,distribution database 50GB, 3 day retention), what is the most critical resource except disk ? Is it CPU intensive ? does it use lots of memory (i.e. would 4GB be enough) ?
