openquery cursor conflict

[simon]

If I have connection setting on linked server for "no count" to ON,
than linked server insert statement doesn't work:

insert openquery(linkedServerName, 'select * from
lnkDb.dbo.target_table')
select 0, ''

It returns error message:
OLE DB provider "SQLNCLI10" for linked server "linkedServerName"
returned message "Cursor operation conflict".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "SQLNCLI10" for linked server "linkedServerName"
could not INSERT INTO table "[SQLNCLI10]".

If i remove tick for "no count", than insert works.
But I don't know what influence will have on other applications on
that server, so I would like to leave "no count" as it was.

Why is this error and is there any other way to go around this? And
interesting is that this error is only when inserting, everything else
work(update, delete or select).

br, Simon

[Erland Sommarskog]

simon (zupa...@gmail.com) writes:
> If I have connection setting on linked server for "no count" to ON,
> than linked server insert statement doesn't work:

I'm not sure that I understand this. There is no server option "no count";
nor do I recognize it as a connection-string attribute. Could you post
the sp_addlinkedserver call to create the linked server?

> If i remove tick for "no count", than insert works.
> But I don't know what influence will have on other applications on
> that server, so I would like to leave "no count" as it was.

It's not simple to understand what is going on. Running a trace against
the other server may reveal something, but that's far from certain.

Any particular reason you use OPENQUERY and not just four-part notation?

In any case, if you don't want to change the server definition, you could
create a second one for the same server.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[simon]

On Nov 11, 11:25 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:

If you open server properties and go to Connections, you have default
connection options:
- implicit transactions
- cursor close on commit
- ansi warnings
- ansi padding
- no count
- and so on....

If you have tick at "no count" than OPENQUERY INSERT won't work. No
matter how you set linked server, it won't never works, I tried all
combinations(I guess).
SELECT, UPDATE and DELETE statement works, only insert wont work. I
know I can do other way, but I would like to know why this won't
work :)

The reason I used openquery was to show the difference between pull
and push method with this example:
http://sqlblog.com/blogs/linchi_shea/archive/2010/12/01/linked-server-and-performance-impact-direction-matters.aspx

You can try this script and on the linked server add thick to "no
count" and you will see, what I mean.

Here is similar question:
http://www.sqlservercentral.com/Forums/Topic597106-145-1.aspx

Thank you,
Simon

[Erland Sommarskog]

simon (zupa...@gmail.com) writes:
> If you open server properties and go to Connections, you have default
> connection options:
> - implicit transactions
> - cursor close on commit
> - ansi warnings
> - ansi padding
> - no count
> - and so on....

So this is the configuration option "user options". Is this option set
on the server you run the INSERT command from, or the server you connect to?

> The reason I used openquery was to show the difference between pull
> and push method with this example:
> http://sqlblog.com/blogs/linchi_shea/archive/2010/12/01/linked-server-and-performance-impact-direction-matters.aspx
>

So Linchi's post tells you that this is a lot slower than running the
INSERT statement on the other server. And on top of that it is not
working. And the reason it is not working, is probably due what Linchi
describes in his post.

If you are only playing around, why not find a pair servers that you
configure anyway you like?