We have configured Linked server using PI OLEDB provider. Stored procedures are written using pass-through queries and scheduled using SQL server agent jobs. Initially for a 20 days, scheduled procedures executed successfully and extracting data from PI. But after that, it started failing. All SPs scheduled to run at particular time intervals in jobs are failing. The error messages are like Cannot create an instance of OLE DB provider "<Provider Name>" for linked server "<Linked server Name>" and Cannot initialize the data source object of OLE DB provider "<Provider Name>" for linked server "<Linked server Name>".
The connection to PI server are successful. We are facing the error message for SPs running through scheduled jobs only.If we execute same SPs manually using exec <proc_name> command, it runs successfully. Also, if we write simple Pass through SQL queries to PI and execute them, they returns data. There is no connectivity issue between SQL server and PI server.
Can you please let me know the probable cause of error and how to resolve them.
Appreciate your help.