I have two databases (DB_FD, DB_TS) each database on a different server, a windows service is accessing DB_FD only, the service must update, insert, delete records from both databases, DB_FD can access the DB_TS database through a linked server. For each table in the DB_TS database there exists a simple view (select * from <linked_server_name>.<owner_name>.<table_name>) in the DB_FD database.
Now when I need to insert new records into the DB_TS database, my windows service in doing the insert statement through the views found in the DB_FD database.
The problem is that, some times, when I select records from the view in the DB_FD database and compare them with the data exist in the base table of that view I discovered that they are not the same!!
Besides, when executing an insert SQL statement against the view, it returns an error for primary key violation, while executing the same statement against the base table goes successfully. To solve the problem temporarily I used to drop and recreate the views, then the problem disappears for a limited time and then comes back again.
I will be so grateful to any of you guys to tell me what the problem exactly is.