Oledb For Oracle
Jan 24, 2007hi,
can i use the acquireconnection method to an oledb for oracle connection manager?
ADVERTISEMENT
Oracle Connection Fail With Microsoft OLEDB Provider For Oracle MSDAORA.1
Feb 22, 2006Hello,
On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.
I use one or the other according to my needs.
In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.
Almost everything works except Microsoft OLEDB provider for Oracle.
ssis packages on the test machine will return an error
Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.
Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".
I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.
Does anyone could point me to the right direction to solve this issue?
Thanks,
Philippe
Unable To Connect To Oracle Using Microsoft OLEDB Provider For Oracle
Aug 23, 2007Hi everybody,
I have designed a DTS package which will migrate a view from Sqlserver 2000 to Oracle.My package is using Microsoft OLEDB provider for Oracle driver for connecting to oracle.Im able to execute this package on the my system ie on the system where sqlclient is installed(Oracle client is also installed on my machine) .But when im doing it on the server im not able to do it.The Connection to Oracle Fails.
I wanted to know in order to connect to Oracle from the server,is it necessary that Oracle has to be installed on the server?.If yes, is it enough if i install oracle client on the server or Oracle Server version has to be installed on Server.
Please suggest me wht should i do know?
Thanks in advance
Regards
Arvind L
What Is Difference Between Odbc, Oledb, Sql And Oracle
Mar 6, 2006i notice that in toolbox panel, there are these kind of different dataadapter and dataset, what is difference between them, and under which situation we use which one?
View 2 Replies View RelatedConnecting To Oracle 8 Database Using OLEDB
Mar 18, 2008Help, I'm attempting to connect to an Oracle 8 database using the microsoft OLD DB for oracle driver.
It works fine connecting to a 9i database but when I attempt to connect to the 8 database I get ORA-12537 TNS Connection Closed.
I can connect to the database fine using sqlplus on the machine.
Any ideas would be appreciated.
Thanks
Stapsey
Oracle OleDb Provider As Source
Mar 16, 2007Guys,
I am having a nightmarish time getting an Oracle Connection Manager working as a source in my SSIS package.
The CM is called "OLTP_SOURCE". When I inspect the configuration and test connection, it succeeds, however when I go to run the package (both in debug mode and via DTEXECUI) I get the following error:
The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009
After this happens, if I go into an OLE DB Source within a DFT, I get the following:
No disconnected record set is available for the specified SQL statement.
Now, if I go back into the CM, enter the password and test, it succeeds. From this point, I will go to preview the data in the OLE DB Source, and it comes back fine. However, when I go to run the package, I get the same error time and time again:
The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009
The quick reader will suggest that the password is not being persisted. To this end, I have tried each of the following techniques to no avail:
1. Double, Triple and Quadruple check that the "save" password option in the CM is checked.
2. Hardcode the connection string in the dtsx XML-behind.
3. Enable Package Configurations and hardcode the connection string in the dstsconfig file.
4. Run the dtsx file using DTEXECUI, providing it with the configuration (that includes the hard-coded password).
5. Run the dtsx file using DTEXECUI, providing it the connection string in the Connection Managager override UI.
Can anyone help shed some light on what might be going on? So far, it is obvious that there has to be something that I am doing wrong because (syntax dialect differences aside) I can't imagine that Oracle sources should be this much of a headache.
Thanks,
Rick
SSIS OLEDB Issue With Oracle
Jan 29, 2008I have several SSIS packages that test out without problems on my dev box when connecting to an Oracle server, both from inside BIDS IDE and from the command line with DTexec. The problem happens when running these packages from the app server that will be used for production; the packages get through the validation, preparation and pre-execution phases but crash when starting the execution phase with this error:
" Code: 0xC0202009
Source: Import Level tables Level1 Source [1261]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E07 Description: "ORA-01861: literal does not match format string".
I've Googled this and it often refers to a date issue. I am not writing to Oracle but importing to SQL Server by using SSIS import tasks, and my question is why would this work without problems from my box but not on this app server? Both machines have the same version of Oracle client installed. My box has the client SQL server with BIDS and the app server only has the full version of SSIS without SQL Server installed. At this point I'm trying to determine if this is an Oracle issue or SSIS. Any ideas?
Microsoft OLEDB Provider Oracle
Nov 23, 2006Hi,
Previously i was using oracle8i with ssis.ssis was working fine.later i have upgraded 8i to oracle 9i.now when ever i try to establish a new connection using Microsoft OLEDB Provider for Oracle.i am getting the following error "Oracle error occurred, but error message could not be retrieved from Oracle" .but i am able to access the oracle 9i database thru pl/sql and toad. what could be the problem?
Thanks
Jegan.T
Could Not Resolve Service Name - Oracle 8 And MS OLEDB Provider
Jan 23, 2001Trying to used DTS from SQL Server 7 to import a table from Oracle 8 DB to SQL Server. SQL Server has Oracle 8 connectivity installed and a database instance setup. Connection using the instance works from Oracle Enterprise manager and all Oracle tools. The MS OLEDB provider for Oracle fails to connect : could not resolve service name. I have this working on a different server with the same setup (SQL Server 7 SP 2 + All Oracle 8.0.5 connectivity).
WHAT AM I MISSING?
Missing Oracle OLEDB Provider In SSIS
Sep 18, 2007Windows 2003(64bit) server, SQL 2005 Server(64bit), Oracle client 10G rel2 (64bit) is installed. But when I am going to create a datasource to the Oracle database the Oracle OLEDB Provider is missing, the only Providers avaliable is from Microsoft.
I have tried to install ODAC (64bit) with no result.
Anyone who have come across this problem and how do I do to resolve it?
BTW! Is there a way to try communication with the OLEDB provider from the commandprompt?
Handling Special Characters Using Oledb To Oracle
Jan 22, 2007hi,
i am using oledb to connect to oracle.
i want to know if there is a way to handle different character sets in this type of connection. for sql to sql, i have been using auto translate in the connection string.
what about for sql oledb to oracle? how can i make sure that the data from sql to oracle is transferred as is?
many thanks.
How To Use The OLEDB Command To Call A Oracle Function?
Dec 11, 2006HI,
I want to use the OLEDB command to call a oracle function, but i havnt found any materials about how to do that, my oracle function is as below:
CREATE OR REPLACE function GET_ZONEID_FROM_SYFZ(ycz varchar2,xc varchar2,strat_id varchar2)
return varchar2 IS
zone_id_result varchar2(10) ;
begin
PKG_DM_DQ.GET_ZONEID_FROM_SYFZ(ycz,xc,strat_id,zone_id_result);
return zone_id_result;
end;
In OLEDB command transformation component, i fill the sql command with "select GET_ZONEID_FROM_SYFZ(?,?,?) from dual", but i dont have it worked.
The error message is :provider can not derive parameter information and setparameterinfo has not been called.
Who have any idea about how to make it work?
Thanks ~~
CacheType In Lookup For Oracle OLEDB Connection
May 15, 2007Hi,
1. If I have millions of rows to be compared, then which cache type is prefereed for lookup, Partial or no caching?
2. If I have lookup connected to Oracle Oledb, cache type as partial and SqlCommandParam as following
select * from (SELECT ORDER_ID, OPER_KEY, STEP_KEY, SUM(OCCUR_COUNT) AS OCCUR_COUNT FROM SFWID_OPER_DESC_EXPLD GROUP BY ORDER_ID, OPER_KEY, STEP_KEY) refTable where refTable.ORDER_ID = ? and refTable.OPER_KEY = ? and refTable.STEP_KEY = ?
then it doesn't allow me to add the parameters from Advance tab of Lookup transformation edition, and raises following error
"Provider cannot derive parameter information and SetParametInfo has not been called"
what am I missing?
Oracle OLEDB Drivers Problem With Numbers
Jun 14, 2007Hi All:
I am using oracle oledb drivers to write to a oledb destination.
if i give decimal values to decimal fields in the source table, i get the same in destination. But if the input is integers, in some cases, the value in the destination is different from that of source
Source Target
50 50.00
100 0.000
111 111.000
600 0.0000
520 20.00
178 178
4546.50 4546.50
I have Sql server SP2 9.0.3042 installed on my machine. Please let me know if theres something i am missing out.
Thanks,
Vipul
Parameters For OLEDB Oracle Source Query
Jul 5, 2006Provider cannot derive parameter information and SetParameterInfo has not been called. (Microsoft OLE DB Provider for Oracle)
I am getting the above error while opening the parameter box at OLEDB source for Oracle using SQL command option at Data Access Mode?? Can you any one please help me in this regard and trouble shoot this problem..
OleDb Source Error - Connecting To Oracle 9i
Nov 14, 2006Hi,
I have my datasource in Oracle9i and destination is Sql2005. I am connecting to Oracle 9i through OLEDb provider and when I connect to my DataSource using OLE DB Data Source, I get the following error:
Warning at "guid code": Cannot retreive the column code page info from the oledb provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale Id will be used.
Could anyone please help me trouble shoot this problem.
Regards,
Sudhakara.T.P.
sudhakaratp@hotmail.com
OleDB Oracle Connection Error - PARTIALLY ANSWERED !!
Feb 28, 2008
Hi All:
I have a SSIS which have a main connection to a SQL Server 2005 DB, and also another connection to a Oracle database, and fetch data from the Oracle db.
I have the 2 connection managers, and everything runs fine when I test it in dev studio.
However, once I imported it to the DB, and run it on command line, I get the "Class not registered" error.
Log file as follows:
C:>dtexec /DTS "MSDB ST1INST101CRDA_D_GIM_CONN_TEST" /SERVER crpnycmsq34q /CONNECTION "CRD_CM";""Data Source=crpnycmsq34qST1INST101;Initial Catalog=CRD;Provider=SQLNCLI.1;Integrated Security=SSPI;"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 1:23:53 PM
Info: 2008-02-28 13:23:53.91
Code: 0x40016040
Source: CRDA_D_GIM_CONN_TEST
Description: The package is attempting to configure from SQL Server using the configuration string ""CRD_CM";"[dbo].[CMG_SSIS_CONFIG]";"GIM_SOURCE";".
End Info
Info: 2008-02-28 13:23:54.03
Code: 0x40016040
Source: CRDA_D_GIM_CONN_TEST
Description: The package is attempting to configure from SQL Server using the configuration string ""CRD_CM";"[dbo].[CMG_SSIS_CONFIG]";"GIM_USER";".
End Info
Info: 2008-02-28 13:23:54.05
Code: 0x40016040
Source: CRDA_D_GIM_CONN_TEST
Description: The package is attempting to configure from SQL Server using the configuration string ""CRD_CM";"[dbo].[CMG_SSIS_CONFIG]";"GIM_PWD";".
End Info
Error: 2008-02-28 13:23:54.55
Code: 0xC0202009
Source: CRDA_D_GIM_CONN_TEST Connection manager "GIM"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
End Error
Error: 2008-02-28 13:23:54.56
Code: 0xC00291EC
Source: Execute SQL Task Execute SQL Task
Description: Failed to acquire connection "GIM". Connection may not be configured correctly or you may not have the right permissions on this connection.
End Error
Warning: 2008-02-28 13:23:54.58
Code: 0x80019002
Source: CRDA_D_GIM_CONN_TEST
Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 1:23:53 PM
Finished: 1:23:54 PM
Elapsed: 1.547 seconds
I DID TRIED MY BEST to search before posting, and most of them are having problem with 32 bit and 64 bit executions.
However, in my case, both are 32 bit (as shown in the log file).
In the above test, I'm setting DoNotSaveSensitiveData, and then fetch it with package configuration.
To make sure the package configuration is not creating pblm for me, I also tried to hardcode everything in the package, and then encrypt the whole ssis with password, and run the package with the /DECRYPT option, so that everything is already intact.
But I still get the same error. Log file as follows.
C:>dtexec /DTS "MSDB ST1INST101CRDA_D_GIM_CONN_TEST" /SERVER crpnycmsq34q /De "painful" /CONNECTION "CRD_CM";""Data Source=crpnycmsq34qST1INST101;Initial Catalog=CRD;Provider=SQLNCLI.1;Integrated Security=SSPI;"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 2:24:25 PM
Error: 2008-02-28 14:24:27.11
Code: 0xC0202009
Source: CRDA_D_GIM_CONN_TEST Connection manager "GIM"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
End Error
Error: 2008-02-28 14:24:27.14
Code: 0xC00291EC
Source: Execute SQL Task Execute SQL Task
Description: Failed to acquire connection "GIM". Connection may not be configured correctly or you may not have the right permissions on this connection.
End Error
Warning: 2008-02-28 14:24:27.14
Code: 0x80019002
Source: CRDA_D_GIM_CONN_TEST
Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 2:24:25 PM
Finished: 2:24:27 PM
Elapsed: 1.422 seconds
Can anyone suggest what is missing ?? or what can be done ??
Any pointers are greatly appreciated.
Thanks,
- Henry.
Integration Services :: Oracle Provider For OleDB Is Missing?
Jul 14, 2015OS : Win7, 64bit
Software : Oracle Clients 32bit and 64 bit (11g), SSIS 2012
I have installed both 32bit and 64bit oracle ODACs during the clients installations, and then i have installed the ssis 2012.
When i open .udl file i can see the 'Oracle Provider for OLE DB' provider and connect to the oracle db.
When i try to open new Ole DB connection in the ssis the 'Oracle Provider for OLE DB' is not listed under the 'Native Ole DB' providers.
i tried to reinstall both ODACs of 32 and 64 bit of the oracle but still the provider is not listed under the ssis providers.why ?
Oracle Oledb Provider Not Registered In Local Machine
Jan 5, 2007Hi,
I am trying to establish a connection to an Oracle database using the following code in a script task:
Dim oOleDbConnection As OleDbConnection
Dim sConnString As String = _
"Provider=OraOLEDB.Oracle;" & _
"Data Source=DBxxx;" & _
"User ID=Userxxx;" & _
"Password=Passxxx"
oOleDbConnection = New OleDb.OleDbConnection(sConnString)
oOleDbConnection.Open()
When I execute the script task, I receive the following error:
The 'OraOLEDB.Oracle' provider is not registered on the local machine.
Am using the correct provider?
I do not know how to resolve the said error.
Here are some facts:
Oracle 8i is installed.
Tnsnames.ora is updated.
I have successfully connected to Oracle SQL *Plus to test the above credentials.
Please help.
Thanks.
Sql Command Oracle Oledb Connection - Parameter Issue
Jul 5, 2006I need to extract rows using date as parameter... where source contains millions of rows but few thousands per date.
I tried using SQL command in data access mode in OLE DB Source Editor but having problem with passing parameter...
Anyone has solution?
Integration Services :: Connect To Oracle Database Using OLEDB Manager
Nov 23, 2010Getting following error while trying to connect to ORACEL database using OLEDB manager.
Error at RADAR [Connection manager "RADAR_Updated"]: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR. The requested OLE DB provider OraOLEDB.Oracle.1 is not registered. Error code: 0x00000000.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
[code]....
SSIS OLEDB Data Source Query Parameters Connect To Oracle
Oct 26, 2007 Hi,
I'm trying to use query parameters with an Oracle OLEDB Source in a data
flow task and I'm having problems.
I've tried formatting the query each of the following ways...
--
select
frq_code,
frq_name,
update_frq,
uptime_frq
from frequency_bcs
where update_frq > ?
and update_frq <= ?
--
Parameters cannot be extracted from the SQL command. The provider might not
help to parse parameter information from the command. In that case, use the
"SQL command from variable" access mode, in which the entire SQL command is
stored in a variable.
Additional information
---> Provider cannot derive parameter information and SetParameterInfo has
not been called. (Microsoft OLE DB Provider for Oracle).
Parameterized Query For Microsoft OLEDB Provider For Oracle Using OLE DB Source In SSIS
Apr 26, 2007Hi,
Urgent Help required..........
Can anyone explain me steps how to parameterized query to send oracle.
If you know any other control which help to do this rather than OLEDB source.
Please let me know.
THanks
Integration Services :: Using Parameters With OleDB Command Provider For Oracle Into SSIS Package?
Dec 8, 2011I need to delete some records in a Oracle RDBMS based at a SQL Server's query. I'm using the follow structure SSIS's package:
View 4 Replies View RelatedProblem In Update Existing Records Using OLEDB Commmand(ORAcle DataBase) In SSIS Package
Sep 4, 2006Hi when i am using OLDB Command for Update Existing Records the Follwing ERror Code I am getting . Any one pls help me on this one.
1)[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Command 1" (9282)
failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method.
The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
2)
[OLE DB Command 1 [9282]] Error: An OLE DB error has occurred. Error code: 0x80040E10.
3)
[OLE DB Command 1 [9282]] Error: An OLE DB error has occurred. Error code: 0x80040E10.
Integration Services :: Calling Oracle Stored Procedure With Output Parameters In OleDB Command Task?
Nov 7, 2015I want to call "oracle" stored procedure with output parameter from SSIS ole db command task.
Actually I am able to successfully call the procedure but my Output value is not updating in the mapped column.
I used below PL/SQL query.
DECLARE
IS_VALID VARCHAR2(200);
BEGIN
IS_VALID(
PARAM1 => ?,
PARAM2 => ?,
IS_VALID => IS_VALID
);
? := IS_VALID;
END;
If I try to supply "OUTPUT" word I get error:
"ORA-06550: line 1, column 45:
PLS-00103: Encountered the symbol "OUTPUT" when expecting one of the following: . ( ) , * @ % & = - + < / >"
BEGIN
IS_VALID(
?,
?,
? OUTPUT
);
END;
how to receive output parameter value of oledb command while calling oracle stored procedures.
Urgent. Output Columns Are Not Appearing When I Use OLEDB Data Source With An Oracle Stored Procedure In Dataflow Task
Nov 12, 2007I am using execute sql task to run a stored procedure in oracle database which returns a resultset. This works. Now I need to send the ouput to a destination table in a sql database. Should I use for each loop to pick the resultset and insert it into the destination one by one (which I dont think is a great idea) or is there a better way to accomplish this task (in data flow task) ?
When I use dataflow task instead of execute sql task, the main issue is I am not able to see the output columns when I execute an oracle stored procedure, but when I see the preview I can see the resultset . But I can see the output columns for a sql server stored procedure.
Error When An OLEDB Source Points To An OLEDB Destination.
Oct 10, 2006Hi all,
I got an error when i do an OLE db Source pointing to an sql 2000 database and executing a sql query inside the OLE Source. The ole source will point to an OLE DB destination which is an sql 2005 database.
But i got the below error:
Error at Data Flow Task [OLE DB Destination [245]]: the column firstname cannot be processed because more than one code page (936 and 1252) are specified for it.
Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB destination" (245)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow TaSK: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
Issues Using Parameterised Reports Connecting To Oracle Using ODBC And Microsoft OLE DB Provider For Oracle
Sep 12, 2007I have an issue using parameterised reports connecting to Oracle using "ODBC" and "Microsoft OLE DB Provider for Oracle" using parameteried reports. The following error is generated "ORA-01008 not all variables bound (Microsoft OLE DB Provider for Oracle)" and a similiar one for ODBC. It works fine for simple reports. Do these 2 drivers have issues passing parameters for a remote Oracle query?
Thanks.
Output Column Has A Precision That Is Not Valid (loading From Oracle Using OraOLEDB.Oracle.1)
Apr 2, 2007Hi!
I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error:
TITLE: Microsoft Visual Studio
------------------------------
Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:
1) Union
I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"
Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.
2) SQL command from variable
The select basically looks like this:
"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"
Again, even if I cast all columns (like in the union), I still get the same error.
Any help would be greatly appreciated. Thanks!
Setting Up Oracle Linked Server : Need Help : Sql2005 Running On XP Linking In Oracle 10.2
Oct 26, 2006Is there any step by step help sites for setting up SQL 2005 linked (oracle 10) server?
I find MSDN articles but they referance winNT and 2000, I'm not getting very far and I'm not a DBA but need to get this working asap.
Data Access :: Accessing Oracle Tables From Server Via Oracle DBLINK?
May 8, 2015we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.
what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?
Oracle Publication Error:The Permissions Associated With The Administrator Login For Oracle Publisher 'test1' Are Not Sufficient
Jan 12, 2006Hi,
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 :
TITLE: Distributor Properties
------------------------------
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
------------------------------
ADDITIONAL INFORMATION:
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
------------------------------
BUTTONS:
OK
------------------------------
Any idea about this error ?
Thanks
Tarek Ghazali
SQL Server MVP.