Oracle 10g R2 To Sql Server 2005 - Publisher Set Up Error
Jun 7, 2007
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 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)
version SQL Server 2005 SP2 table in SQL Server 2000 (80) compatibility level publication in SQL Server 2000 compatibility level - Distributor : SQL Server 2005 SP2 - Subscriber : SQL Server 2000 SP4 + hotfixes (version 8.00.2187)
There is only one article in our publication (a simple table with a GUID and a nvarchar(50) columns), and we have left the default resolver.
1 - The first synchronization of the subscription is successfully completed, and it is the same for the different updates/inserts/deletes or conflicts.
2 - Then we change the Resolver of our article by our own COM-Based Custom Resolver (developped in C# 2.0). This resolver only change the default behaviour: the subscriber always win (this is for a test, in the future we will have a complex business logic). All the synchronizations works fine and do what we want in the conflicts.
3 - We rollback the resolver to the default one... and here is the problem! The synchonizations stop to work correctly. For all of them we've got the same error:
It is no more possible to synchronize this subscription... Any remark will be helpfull cause we did not find anything on the net concerning this error.
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 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 upgraded a SQL 2000 installation to SQL 2005 this past weekend. Minimal issues overall, but one that did crop up relates to a linked server to an Oracle database. The linked server worked before the upgrade, and stopped working immediately after.
We're running on Windows 2003 x64, latest service pack. The linked server is set up using the MSDAORA provider. The error I'm getting post-upgrade is:
=====================
OLE DB provider "MSDAORA" for linked server "PROD" returned message "ORA-06413: Connection not open.".
Msg 7303, Level 16, State 1, Procedure RDM_GET_REP_LIST_SP, Line 12
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "PROD".
=====================
A tnsping from the server works fine. I've also tried recreating the linked server. Any help would be appreciated. Let me know what other information would be useful.
We have recently migrated from SQL 2000 (32bit) to SQL 2005 on Win2003 (both are 64bit). And we have Legacy DTS packages on SQL 2005, some packages are pulling records from Oracle 10g (32bit server). When I execute the DTS package I am getting following error.
Error = -2147467259 (80004005) Error string: Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed. Error source: Microsoft OLE DB Provider for Oracle
Did I need to install/update any MS OLE DB driver for Orace. Some blogs & forums suggesting to install Oracle 10g Client. If so Which version of Oracle client(32bit/64bit) I have to install?.
I'm trying to set up a transactional replication from SQl Server 2005 to SQl server 2000. The Distributor and the publisher are on our server and the subscription is supposed to be on a SQL 2000 server on a different location. Before upgrading to 2005 I didn't have problems - the replication from 2000 to 2000 was working perfectly.
After I succesfully created the distributor and the publication the first problem that I encountered was that when I tried to create the subscription it was giving me an error that I cannot use an IP to acces the server. I realized to fix that issue by creating an Alias in the SQL Server Configuration Manager for the server where I wanted to create the subscription (a push subscription).
Now when I try to add the subscriber it gives me another message "Execute permission denied on object 'sp_MS_replication_installed', database 'master', owner 'dbo'" - so I cannot create the subscriber.
The user that I use to create the subscriber on the 2000 server is dbo for the subscriber database but it doesn't have rights on the master database. Also I realized that on the 2000 server I still have the old subscription but I cannot delete it - for the same reason - no access on the master database. Before upgrading to 2005 I had the exact same rights on the 2000 server.
I have created a package where Iam transferring data from oracle to sql 2005 server and I am getting below error message.
[OLE DB Source [1]] Error: An OLE DB error has occurred. Error code: 0x80040E10. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E10 Description: "ORA-01008: not all variables bound ".
The query which I got from oracle team has the following note
---- NOTE: Replacement Variable is required. &3 is the offset you wish to run (0 to -11).
The query is having a & sign in where clause.Part of the q is displayed below
I've got a table adapter that connects using an oracle data connector. In the adapter, I'm using native oracle SQL such as:
select TO_DATE(SUBSTR(TO_CHAR(weird_oracle_field),0,12),'YYYYMMDDHH24MI') as dt_added from oracle_data_table
There's also a CASE statement in there with some other data transformations.
Anyway, I want to take the results of that Oracle query and put the dataset into a SQL Server Compact Edition database - within an application that I'm creating in Visual Studio 2005.
For whatever reason, I can't seem to do anything like that in 'bulk' and there aren't any data migration tools that work with anything other than "full" SQL Server versions. My client doesn't support SQL Server, but I can deploy my app with SQL CE. I need a 'local' copy of the database (for several reasons) and just can't seem to figure out how to make this work.
I'm really going nuts. I feel like I'm soooo close when I see the data I want in the table adapter - but I can't seem to actually *move* the data over!!
I have been looking for a way to replicate data from a DB2 system running on an AIX machine. I found some information related to doing this with SQL2000, but not with 2005. Can this be done - preferably without writing our own provider.
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?
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
I want to have transactional replication setup with SQL 2000 as a publisher and SQL Express 2005 as a subscriber? Where can I get some documentation around that?
Basically, the problem I am stuck with is that while registering the subscriber in SQL2000, SQL2000 cannot recognize my SQL Express 2005 instance as a subscriber. I have tried both SQL Ent. Mgr. and sp_addsubscription.
I was wondering if it is possible to setup replication between a 2005 SQL Publisher and a 2000 SQL Subscriber? Is there any special setup steps I need?
From the SSMS GUI it is possible to re-initialize one or all subscriptions to a merge publication. This is done at the publisher.
How can I achieve these operations programmatically?
In particular, how do I initialize a single merge subscription from the publisher?
I have looked at the documentation for sp_reinitmergepullsubscription but it says that this proc must be run at the subscriber - which isn't much use when subscribers are disconnected for the majority of the time
I have a large number of merge subscribers and want to reinitialize all except one
I have more than 8 GB Data in oracle and Everyday we have to check some data in oracle but it takes times due to lot of data. So what i was looking that data that we need can import in SQL Server database and do checking purpose. But I was looking some command that connect to oracle brings its data and import in SQL Server. For that we will fire SQL statement to oracle through asp.net and insert into SQL Server I will fire differencial data insert covery only. is there any way or idea to do that please advice me.
Anyone know where I can find some good resources to help us choose betweenSQL and Oracle ( Progress Openedge as well ) . Any comments on what youwould choose ?? We are creating a new Warehouse Management System which wilmanage our very large inventory.Anyway comments suggestions welcomeThanksPaul
I am trying to import an Oracle .dmp database into SQL Server 2005, what would you suggest as the fastest and easiest way to do this? DTS/SSIS doesn't have an instant solution and the script I tried to use gave me an error...
Msg 3241, Level 16, State 0, Line 1
The media family on device '<DATABASE>' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I would like a GUI based way to do this if at all possible. Thanks -Kyle
I'm relatively new to DTS and I was trying to import data from the oracle database to SQL server database based on some parameter from the destination table. i.e select * from SourceTable where SourceTable.Column1 > (select max(Column1) from DestinationTable) This tries to search DestinationTable from the source database(Oracle) How shoud I go to solve this problem? Any Hint is greatly appreciated....
OK new Windows 2003 Enterprise 64-bit with MS SQL Server 2005 64-bit.. Now I am migrating the DTS packages from our olld Windows 2000 Standard with MS SQL 2000 (all 32-bit). I am not moving the packages to SSIS yet just want to move the legacy DTS jobs over... However whenever I try and connect to the Oracle DB I got ORA-12154: TNS:could not resolve the connect identifier specified.
I have the Oracle 10g 32-bit & Oracle 10g 64-bit clients installed and I can run SQL+Plus and do everything just fine. It is just in MS SQL 2005 I can not. Even in SSIS I get the ORA-12154 error. I can create an ODBC and test it and it works just fine... Has anyone here ever fixed this? Some say it is b/c of the ('s around x86 in program files and I have done their suggestion in making a ProgramFilesx86 directory and copy the visual stupid & sql directories there... still nothing. I have one of my DBA's searching Oracle for any patches but thought I would ask and see.
Hi,I am trying to set up a Updatable Subscriptions for TransactionalReplication with SQL 2005 (queued updating).I have a stored procedure "SYS_spShellTest" which is replicated to thesubscriber viathe replication option "Execution of the stored procedure":-------------------------------------------------------------ALTER procedure [dbo].[SYS_spShellTest] @cmd varchar(1000)WITH EXECUTE AS ownerasBEGINEXEC @result = master..xp_cmdshell @cmd, NO_OUTPUTReturn (@result)END-------------------------------------------------------------1) If I call "exec SYS_spShellTest" on the publisher the call is alsoexecuted on the subscriber.2) If I call "exec SYS_spShellTest" on the subscriber the call is NOTexecuted on the publisher.How can I make 2) work so that it is also executed on the publisherplease?Thanks a lot,Patrick
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 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..
