I have a linked server set up to a proprietarty temporal DB through an OLEDB provider. From time to time and for various reasons the OLEDB provider crashes with "Error 7302 Could not create an instance of OLE DB Provider 'IhOLEDBProvider.iHistorian.1'. OLE DB Trace [Non Interface Error: CoCreate of DSO for IhOLEDBProvider.iHistorian.1 returned 0x80070008"
Up till now, to re-initilize the connection we have just restarted the SQL Server service. However, some automated processes are now feeding data into this server and more users and have started coming online so restarting for this issue has become a big deal.
Getting the OLEDB provider sorted out isn't an option at present so my question is:
Is there a way to re-initalise a failed linked server other that restarting the SQL Service?
I don't know the details of your app(s) that are hitting that linked database, but what if you can re-establish the linked server from the app itself, rather than SQL Server reacting to a failed linked server.
Thanks for the suggestion. I got it working late Friday by identifying the locked object from Enterprise Manager and using the Kill command to release it. The reason it's occurring seems to be VERY long running queries against the remote DB.