Dear Readers,
In this article, we will understand the Oracle to SQL Server Migration Issues which I have faced during the migrationactivity.
We will look at the resolution of the below issues one by one.
Issue 1)A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
Resolution: – To resolve above issue we need to follow below steps and check few settings.
- We need to check whether SQL Server service is running, to check that service use below path.
Path: – Go to startèAll ProgramsèMicrosoft SQL Server 2012èConfiguration ToolsèSQL Server Configuration Manager
We can see from above output our SQL Server Instance is up and running, if it is not running then you need to start it manually.
- Now second thing we need to check whether Named Pipes and TCP/IP protocols are enabled.
Path: –Go to startèAll ProgramsèMicrosoft SQL Server 2012èConfiguration ToolsèSQL Server Configuration ManagerèSQL Server Network ConfigurationèProtocols for SQLEXPRESSNEW
We can see from above output both protocols are disabled. Right click on each protocols and click on enable option.
- Now the last thing we need to check whether connection allowed option is enable.
Path: –Connect to the SQL Server InstanceèRight Click on Instance and click on propertiesèClick on connectionsèCheck status of Remote server connectionsèit should be ticked marked.
- Now again check the SQL Server connection from SSMA tool.
SQL Server connection has been established successful.
Issue 2)you cannot connect to an older version (SQL Server 2012) of target SQL Server from a higher version SSMA project (Project type = SQL Server 2017). Try creating another SSMA project of project type which matches the version of target SQL Server you are connecting to OR connect to a target SQL Server equal to SQL Server 2017.
Resolution: – Before connecting to Oracle and SQL Server first we need to create new project for the first time, for connection there are few options which we need to give like Name, Location and Migrate To.
I was getting above issue because I choose SQL Server 2017 option, but I was installed SQL Server 2012 in my system. So you have to select that option which you have installed in your system.
Issue 3)Connection to Oracle failed. The ‘OraOLEDB.Oracle.1’ provider is not registered on the local machine.
Resolution: – I was getting above issue because I didn’t install the Oracleclient software and in that I was not selected Oracle OLE DB option.
To check the client installation, click on this link. After Installation you are able to resolve above issue.
Issue 4)The module “OraOLEDB12.dll was loaded but the call to DLLRegisterServer failed with error code 0x80070005
Without Admin Option: –
Resolution: –When I was getting this issue “Oracle failed. The ‘OraOLEDB.Oracle.1’ provider is not registered on the local machine”, at that time I ran below command to register OraOLEDB12.dll service but I was getting above issue.
Command: -regsvr32.exe OraOLEDB12.dll
So I checked again in cmd prompt and found that I was not running from admin option I execute the command again from admin option and then above issue got resolved.
With Admin Option: –
Issue 5) Connection to Oracle failed.ORA-12545: Connect failed because target host or object does not exist
Resolution: –To resolve above issue please use IP address instead of host name in tnsnames.ora file in both Client side and Oracle database end.
Oracle Client tnsnames.ora file: –
Oracle Database tnsnames.ora file output: –
Once you done the above changes then connection failed issue will get resolved.
Thank you for giving your valuable time to read the above information.
Follow us on
Website : www.ktexperts.com
Facebook Page : KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin
Loading...
