Unanswered: SQL Server 2008 Linked Server to Sybase 15.5
Cannot create an instance of OLE DB provider "ASEOLEDB" for linked server "[SERVER_NAME]"
The current setup:
Our primary database is on Sybase, I needed to extract hierarchical (nested) XML from multiple tables on Sybase. Although Sybase ASE 15.5 has `FOR XML` query capability it does now allow nested XML format. So we used the 'FOR XML', XML functionality in SQL Server (as we had a SQL Server 2008 database available which is used for a smaller system) to query Sybase tables through a Linked Server connection.
Everything is working perfectly when extracting data to XML format from Sybase via SQL Server. The issue is when the SQL Server machine is rebooted, the connection to Sybase can not be re-established automatically after the reboot. I get the following error from the executable that calls the SQL Server query: Cannot create an instance of OLE DB provider "ASEOLEDB" for linked server "[SERVER_NAME]".
Once I execute the following command on the SQL Server it re-establishes a connection and it then works again until the machine is rebooted again:
The obvious (and highly unsatisfying) workaround is to set up a SQL Agent job to run the sp_add_linkedsrvlogin command at startup.
I am assuming here that the login does not need to be dropped? It is highly unusual for SQL Server to lose any data like this due to a restart of the service. It is not like this is an in-flight transaction that should be rolled back.