1

Topic: There were errors in runtime of multistep operation of an OLE DB

Good afternoon.

delete from [192.168.1.99].Archive.dbo. RA
where FK_R in
(select FK_R
from [192.168.1.99].Archive.dbo. RA as ra
left join [192.168.1.99].Archive.dbo. R rr on ra. FK_R=rr.pk
where rr. RegistrNum is not null)

The given request on  the server [192.168.1.99], produces such error:

The supplier of an OLE DB "SQLNCLI10" for a linked server "192.168.1.99" returned the message "There were errors in runtime of multistep operation of an OLE DB. Whenever possible, check up values of all states of an OLE DB. Operation is not fulfilled.".
The message 7202, level 11, state 2, line 1
Could not find server ' SQLSERVER2 ' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Such error is produced on one  the server from 30.... On others such error is not present...
Help to understand, please...

2

Re: There were errors in runtime of multistep operation of an OLE DB

At what if

DELETE

to replace on

SELECT *

errors are not present

3

Re: There were errors in runtime of multistep operation of an OLE DB

r77;

wrote:

Could not find server ' SQLSERVER2 ' in sys.servers.

and it whence? If on DELETE works, it is possible at you the trigger works and there a curve link

4

Re: There were errors in runtime of multistep operation of an OLE DB

TaPaK;
Triggers are not present on the table

5

Re: There were errors in runtime of multistep operation of an OLE DB

r77;
On what?

6

Re: There were errors in runtime of multistep operation of an OLE DB

TaPaK;
On both and [192.168.1.99].Archive.dbo. RA and [192.168.1.99].Archive.dbo. R - triggers are not present

7

Re: There were errors in runtime of multistep operation of an OLE DB

r77;
So search where Could not find server ' SQLSERVER2 ' in sys.servers.

8

Re: There were errors in runtime of multistep operation of an OLE DB

TaPaK;
What do you mean?
Certainly at me  the server [192.168.1.99], instead of SQLSERVER2
SQLSERVER2 Is server name [192.168.1.99]

9

Re: There were errors in runtime of multistep operation of an OLE DB

TaPaK;
Once again.... If DELETE to replace on SELECT - that with links turns out everything is all right... Than DELETE it is not pleasant to a link? Whether and in a link a problem?

10

Re: There were errors in runtime of multistep operation of an OLE DB

r77;
DTC it is adjusted? The computer did not rename?

11

Re: There were errors in runtime of multistep operation of an OLE DB

Vladislav Kolosov;
I do not know it.... Can and renamed...
If to make

UPDATE

, i.e.

UPDATE [192.168.1.99].Archive.dbo. RA SET FlagDel=1
where FK_R in
(select FK_R
from [192.168.1.99].Archive.dbo. RA as ra
left join [192.168.1.99].Archive.dbo. R rr on ra. FK_R=rr.pk
where rr. RegistrNum is not null)

And then already

DELETE [192.168.1.99].Archive.dbo. RA WHERE FlagDel=1

That all again works also any errors... Than so DELETE it is not pleasant in a construction

...where FK_R in ()

?

12

Re: There were errors in runtime of multistep operation of an OLE DB

r77;
Can because of an enclosure...
delete ra where not exists (select * from r where...)?

13

Re: There were errors in runtime of multistep operation of an OLE DB

r77 wrote:

Vladislav Kolosov;
I do not know it.... Can and renamed...
If to make

UPDATE

, i.e.

UPDATE [192.168.1.99].Archive.dbo. RA SET FlagDel=1
where FK_R in
(select FK_R
from [192.168.1.99].Archive.dbo. RA as ra
left join [192.168.1.99].Archive.dbo. R rr on ra. FK_R=rr.pk
where rr. RegistrNum is not null)

And then already

DELETE [192.168.1.99].Archive.dbo. RA WHERE FlagDel=1

That all again works also any errors... Than so DELETE it is not pleasant in a construction

...where FK_R in ()

?

it is pleasant, but you and did not show it.
Look on basis of a mention of a line SERVER2
Somewhere to it there is a reversal.

14

Re: There were errors in runtime of multistep operation of an OLE DB

r77 wrote:

TaPaK;
Once again.... If DELETE to replace on SELECT - that with links turns out everything is all right... Than DELETE it is not pleasant to a link? Whether and in a link a problem?

Here . If you are assured that there are no triggers, search  for perversion CONSTRAINT ON DELETE CASCADE

15

Re: There were errors in runtime of multistep operation of an OLE DB

TaPaK;
And without insults in any way? Do not want to answer - nobody forces you!

16

Re: There were errors in runtime of multistep operation of an OLE DB

Rolg Hupin;
To look on basis - that you on these imply? To search where?

17

Re: There were errors in runtime of multistep operation of an OLE DB

Vladislav Kolosov;
You are probably right... I will try, thanks

18

Re: There were errors in runtime of multistep operation of an OLE DB

EXEC (' delete from Archive.dbo. RA
where FK_R in
(select FK_R
from Archive.dbo. RA as ra
left join Archive.dbo. R rr on ra. FK_R=rr.pk
where rr. RegistrNum is not null) ') AT [192.168.1.99]

19

Re: There were errors in runtime of multistep operation of an OLE DB

wrote:

EXEC (' delete from Archive.dbo. RA
where FK_R in
(select FK_R
from Archive.dbo. RA as ra
left join Archive.dbo. R rr on ra. FK_R=rr.pk
where rr. RegistrNum is not null) ') AT [192.168.1.99]

Thanks you big, the kind person! All works in such construction!

20

Re: There were errors in runtime of multistep operation of an OLE DB

wrote:

EXEC (' delete from Archive.dbo. RA
where FK_R in
(select FK_R
from Archive.dbo. RA as ra
left join Archive.dbo. R rr on ra. FK_R=rr.pk
where rr. RegistrNum is not null) ') AT [192.168.1.99]

And if the ip-address has to be substituted dynamic? For example, this piece at me enters into stored procedure which is fulfilled in the agent under the list of ip-addresses?

21

Re: There were errors in runtime of multistep operation of an OLE DB

To blind EXEC EXEC-a dynamic

22

Re: There were errors in runtime of multistep operation of an OLE DB

r77 wrote:

it is passed...
And if the ip-address has to be substituted dynamic? For example, this piece at me enters into stored procedure which is fulfilled in the agent under the list of ip-addresses?

Dynamics in dynamics

23

Re: There were errors in runtime of multistep operation of an OLE DB

alexeyvg wrote:

it is passed...
Dynamics in dynamics

Thanks, I will try... It was not necessary so earlier to turn exec in exec...

24

Re: There were errors in runtime of multistep operation of an OLE DB

r77 wrote:

it is passed...
Thanks, I will try... It was not necessary so earlier to turn exec in exec...

Basically it is possible so:

declare @executesql nvarchar (1000) = N ' [192.168.1.99].Archive.dbo.sp_executesql'
exec @executesql N'delete from Archive.dbo. RA
where FK_R in
(select FK_R
from Archive.dbo. RA as ra
left join Archive.dbo. R rr on ra. FK_R=rr.pk
where rr. RegistrNum is not null'

Only it is necessary to resolve at the linked-server RPC
Accordingly, the server can be changed in a name.

25

Re: There were errors in runtime of multistep operation of an OLE DB

In my opinion OLE the provider does not process requests like delete... from...
Something I remember - once faced it.