I have a work assignment that consists of linking two databases on two different computers through SQL Server Management Studio. Basically, both databases have to transmit the same data to a centralized SCADA from some machines that have different tasks.
What I am trying to do is making this connection through Linked Servers. I managed to set up the OCBD server on the first machine with no problems at all. After that I started setting up the connection on the second machine so that they could communicate. I am getting an array of problems but the most common is the one at the bottom.
I have searched plenty of pages for a solution but everyone suggests solutions that have no affect on my project, so i was wondering id anyone knows what is wrong with my settings. If it's not my settings, then could it be some Firewall setting or something along the lines of that.
On the first machine I created an OCDB data source through the Administrative Tools from Windows. On the second machine, through my SQL Server Management Studio I tried creating a new Linked Server and applying the settings suggested here: https://www.sqlshack.com/how-to-create-and-configure-a-linked-server-in-sql-server-management-studio/
Even though I have followed it step by step I still get error: 'Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SERVERNAME".(Microsoft SQL Server, Error: 7399)'
I have already tried to change the settings of the provider and enable "allow inprocess" since one of the pages I consulted, suggested this might be the problem.
Thanks to everyone in advance.
What I have tried:
I have already looked for solutions and applied all the different settings suggested online. Still not working.