We configured linked server between two SQL server 2000 servers. The remote query either select or update through the linked server is using server-side cursor and causes major performance issues.
In SQL trace we can see lot of
sp_cursorfetch
sp_cursorclose
sp_cursor
system stored procedure and making round trip for each row.
The OLEDB driver which was used in linked server configuration is like other server setup and in this particular linked server the remote query is using server-side cursor.
The dependent application is also the same like other setup and not sure how this is getting generated.
please help me how to find, which was causing this server-side cursor..Any idea how to enable ODBC tracing.