Hi everybody,

I have two sql servers on different computers. One is SQL 2000 on the computer 1 and other is SQL 2005 on computer 2. On SQL 2000 we have our working database and we are planning to migrate to SQL 2005. On the computer 2 in the SQL 2005 environment I've added linked server using sp_addlinkedserver. Now in SQL 2005 I can access SQL 2000 data. And on SQL 2005 I have some testing database I'd like to work with.

The problem is that we are using Access as a Front End. When Access tries to call stored procedure that in turn contain calls to linked server tables _sometimes_ there is the following error:
"An existing connection was forcibly closed by remote host" or
OLE DB provider 'SQLNCLI' for linked server returned message "Communication link fail" or just "ODBC error".
We could resolve this problem going to SQL 2005 and opening this stored procedure (with the call to linked table) directly from the server. After that Access is working for some time. Then we receive our errors again.
Does anybody have any suggestions?
Thank_you.

Just had another problem with link server, but managed to successfully decide it.
Will describe it here, may be it'll help somebody.
So...
In the SQL server there is a view, that uses data from the linked server. Then in the stored procedure we use this view for updating some information in the database. The update statement tries to change data in the linked table. This update statement was producing an error. Instead of update statement I've just created a cursor and made changes in the cycle and this works.