Ole Db Provider Msdaora For Linked Server Error
Contents |
19, 201022 Share 0 0 When setting up linked server to third-party Databases, it is recommended to run the third-party provider in out-of-process mode, because when the provider is run sql server 2012 linked server oracle in-process (within the same process as SQL Server), then any issues with the microsoft sql server error 7303 linked server provider can affect SQL Server process which could also result in crashing SQL server.
To how to cannot create an instance of ole db provider oraoledb.oracle for linked server error 7302 set a provider to run out-of-process, uncheck "Allow In Process" property of the provider: There are certain permissions that have to be set on MSDAINITIALIZE to be microsoft sql server error 7303 linked server oracle able to initialize the provider out-of-process and run linked server queries successfully locally and remotely. MSDAINITIALIZE is a COM class that is provided by OLE DB. This class can parse OLE DB connection strings and load/initialize the provider based on property values in the connection string. MSDAINITILIAZE is initiated by users connected to SQL Server. If windows authentication is usedSql Server Oracle Linked Server Query Syntax
to connect to SQL Server, then the provider is initialized under the logged in user account. If the logged in user is a SQL login, then provider is initialized under SQL Server service account. Based on the type of login used, permissions on MSDAINITIALIZE have to be provided accordingly.
When these permissions are not set for the logged in users, we get Access Denied errors as below: Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. Access denied. Msg 7301, Level 16, State 2, Line 1 Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)". Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) An exception has occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The OLEDB Provider "MSDAORA" for linked serversome cases it can be handy if you can query a database from one database server to the other and copy data and tables. Now the fun part is, we
Oracle Linked Server Equivalent
can use this on different databases as well, like Oracle, MySql e.g. how cannot initialize the data source object of ole db provider "oraoledb.oracle" for linked server cool is that! Let's set up a linked server to Oracle. If you don't have oracle available, you can download and sql server linked server oracle provider string install the Oracle Express edition, which we can get for free from the Oracle site. In some cases it can be handy if you can query a database from one database server to the other and copy https://blogs.msdn.microsoft.com/dataaccesstechnologies/2010/08/19/permissions-needed-to-set-up-linked-server-with-out-of-process-provider/ data and tables. Now the fun part is, we can use this on different databases as well, like Oracle, MySql e.g. how cool is that! Let's set up a linked server to Oracle. If you don't have oracle available, you can download and install the Oracle Express edition, which we can get for free from the Oracle site. After you have succesfully installed Oracle server, fire up MSSQL. First http://www.thebuttonfactory.nl/?p=1047 we are going to create a new database for this Linked Server Project, let's call it the ‘FunFactory' a great program from the '80. Insert some tables and rows and we're done! Now we open the Server Objects in the Object Explorer and right click ‘add new linked server': Now it's very important to fill in the right credentials: You need to connect Oracle on it's TNS name, for example, Oracle Express the default is XE, now press ‘script from', to see the script that will execute when you press ok. Ok, enought with the boring GUI, we are going to create another linked server, but this time we script it: DECLARE @vcLinkedServer VARCHAR(255)
DECLARE @vcLocalUser VARCHAR(255)
DECLARE @vcRemoteUser VARCHAR(255)
DECLARE @vcRemotePass VARCHAR(255)
DECLARE @vcRemoteTNS VARCHAR(255)
DECLARE @ncStatement NVARCHAR(MAX)
SET @vcLinkedServer = 'FunFactory'
SET @vcLocalUser = SYSTEM_USER
SET @vcRemoteTNS ='XE'
SET @vcRemoteUser = 'Fun'
SET @vcRemotePass = 'Factory'
/*
SET @ncStatement = 'USE MASTER
GRANT ALTER ANY LINKED SERVER TO ' + SYSTEM_USER + '
USE ' + DB_NAME()
EXEC sp_ExecuteSQL @ncStatement
*/
BEGIN TRY
EXEC sp_DropServer @vcLinkedServer, 'droplogins'
END TRY
BEGIN CATCH
EN
up Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 2005 » SQL Server http://www.sqlservercentral.com/Forums/Topic1635951-149-1.aspx 2005 General Discussion » OLE DB provider "MSDAORA" for linked server... OLE DB provider "MSDAORA" for linked server "OracleLinkedServer" returned message "Error while trying to retrieve text for error ORA-12154 ". Rate Topic Display Mode Topic Options Author Message ahamedsulthan5ahamedsulthan5 Posted Tuesday, November 18, 2014 5:55 AM Forum Newbie Group: General Forum Members Last Login: Tuesday, December 2, 2014 10:57 PM Points: 2, Visits: 23 HiWhen we linked server try to connect oracle database using linked server we face below issues Please help us how to resolve this issuesOLE DB provider "MSDAORA" for linked server "OracleLinkedServer" returned message "Error while trying to retrieve text for error ORA-12154".Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "OracleLinkedServer". Post #1635951 GilaMonsterGilaMonster Posted Tuesday, November 18, 2014 6:12 ole db provider AM SSC-Forever Group: General Forum Members Last Login: Today @ 4:15 AM Points: 45,432, Visits: 43,779 http://ora-12154.ora-code.com/ Gail ShawMicrosoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enterWe stand on the bridge and no one may pass Post #1635956 « Prev Topic | Next Topic » Permissions You cannot post new topics. You cannot post topic replies. You cannot post new polls. You cannot post replies to polls. You cannot edit your own topics. You cannot delete your own topics. You cannot edit other topics. You cannot delete other topics. You cannot edit your own posts. You cannot edit other posts. You cannot delete your own posts. You cannot delete other posts. You cannot post events. You cannot edit your own events. You cannot edit other events. You cannot delete your own events. You cannot delete other events. You cannot send private messages. You cannot send emails. You may read topics. You cannot rate topics. You cannot vote within polls. You cannot upload attachments. You may download attachments. You cannot post HTML code. You cannot edit HTML code. You ca