Kirk,
There is a known issue there is a MS Knowledge base article on it. I don't have the number of the article but here is an excerpt:
*******************
SYMPTOMS
Attempting to issue a CREATE SQL VIEW, USE or REQUERY() command with a REMOTE VIEW, results in the following error message:
Connection CONNECTION_NAME is busy.
This behavior occurs under the following conditions:
-- Two or more Remote Views are created using the same SHARED connection.
-- The MAXRECORDS property of at least one of the VIEWS is set to a value greater than 0.
-- The FETCHSIZE property of at least one of the VIEWS is set to a value greater than 0.
-- The FETCHSIZE property value is less than or equal to the MAXRECORDS property value.
RESOLUTION
Here are the workarounds for this problem:
1.Do not include the SHARED clause in the CREATE SQL VIEW command used to create the remote view (Kevin says this is a dumb work around)
2.Create a separate connection for each of the views (Kevin says this is another dumb work around)
3.Use the default MAXRECORDS and FETCHSIZE properties (Kevin says You probably alreay are using the default)
4.Set the FETCHSIZE property of the view(s) to -1 (ALL) (This should work)
STATUS
The behavior is by design. (Can you believe they put this behaviour in on purpose?????)
*****************************************************
(Kevin says this is a dumb work around)
Use work around #4. Modify the view and on the menu, select Query and then select Advanced Options. Set the fetching to ALL (-1).
Kevin