SQL Server Performance Forum – Threads Archive

sp_cursoropen

A query that accesses a linked server is causing me issues. When I traced the query from the remote server (Server B), I can see that the connection is using a cursor to return results to the remote server (Server A) where the query is run from. The sp_cursorfetch statement is processing in batches of 100 records, and apparently is generating a round-trip (to Server A) for each completion. However, when I issue the same query from Server A to Server B using OPENQUERY – no cursor is used, and the results are returned immediately. I’ve researched everything I can imagine, but with no results … API Server Cursors link:http://msdn2.microsoft.com/en-us/library/aa172588(SQL.80).aspx It appears that this option is enabled/disabled at the provider level, but where? I’m using SQLOLEDB, with the following options configured at the server level (Allow Inprocess; Nested Queries).

---

You can find such options under the ProviderOptions button when you’re defining the LInked server details using Enterprise manager. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

I don’t think there is way to configure this property either in EM or SQLMS. I belive it is some thing to do with provider. Can you check server type in your linked server property. Is it default to sql server or other provider. If it is checked other provider what is the name of the provider?

---

Yes, as long as that provided is compatible with SQL server you can set it. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

]]>