June 7, 2013 at 10:08 am
Hello,
I have setup a linked server between SQL Server 2008 R2 and MAS90 server and I am able to select data and see the list of mas90 tables. I am using a sql agent job to pull data from mas90 to sql server database through the linked server.
The issue here is whenever a new account is added to MAS90 database the sql agent job which tries to pull data from MAS90 to SQL Server database gets hung. So the data that I am trying to pull from mas90 to sql database wont appear in the sql server database. Everytime this happens if I restart the sql server services and restart the sql agent job then it works fine.
Experts I need your advice on how to fix this as this is happening quite frequently and I cannot afford to restart the sql server services everytime a new account is added. Does it have to do anything with the local DTC property settings on the SQL Server database server side? I have attached the current msdtc settings jpg file with this post.
Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 7, 2013 at 12:02 pm
Experts I need your valuable advice. I am confused if I am missing something or is it a MAS90 issue.
Thanks a ton in advance
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 9, 2013 at 7:55 am
MAS90 is a database engine on its own. Yes I have installed the OLEDB driver for it. True there are no issues related to provider/ driver because whenever i test the linked server connectivity it is successfull and in the linked server folder on SSMS I am able to see all the MAS90 database tables and select the data from a query.
I have setup a SQL Agent job which runs every hour and tries to select all the data from just 2 tables of mas90 and insert into a couple of sql server database tables and it uses the linked server. The job runs successfully most of the times.
But the issue is only when new entries are made into MAS90 database through our sales application. So when SQL Agent job runs it gets hung so no data is selected. This happens everytime data gets inserted to mas90 database. In this scenario if I stop the sql agent job and retry then the job fails. But if I restart SQL Server services and then restart the SQL Agent job then job completes successfully and data is copied to sql server database.
Interestingly I am seeing nothing in the eventviewer or the SQLServer error log or the eventvwr in the MAS server.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply