1

Topic: Insertion of the data from the coherent server

There is a task in which it is necessary to interpose into the given table the data from the coherent server.
For unification it decided to fulfill insertion procedure on the initial server, the request should look approximately so

INSERT INTO dbo. SourceTable
EXEC [linkedServer]. [dbName].Scheme. [StoreProcedure]

Where  StoreProcedure returns 1 data set
Request in a format

INSERT INTO dbo. SourceTable
SELECT * from [linkedServer]. [dbName].Scheme. Table

Works without problems, and about usage  error Distributed transaction falls out.
Can explain why so happens. And so to get rid of an error.

2

Re: Insertion of the data from the coherent server

Exec () at server

3

Re: Insertion of the data from the coherent server

The critic;
It does not approach me, I plan to wrap a call  in EXEC ("), and dynamic to set a name of the coherent server as in advance I do not know with what it will be necessary to obtain the data, as far as I know AT SERVER it is impossible to turn in dynamic SQL

4

Re: Insertion of the data from the coherent server

lex452 wrote:

works without problems, and about usage  error Distributed transaction falls out.

1. Also what it for an error?
2. Simply procedure call without an insertion in  works?

5

Re: Insertion of the data from the coherent server

The grandfather;
Yes, simply call . And into the error account if I knew that it means, on a forum wrote. As I understood that distributed transactions are forbidden. Here to me it is not clear, why procedure can be fulfilled, but it is impossible to interpose the data into the table

6

Re: Insertion of the data from the coherent server

lex452 wrote:

Here to me it is not clear, why procedure can be fulfilled, but it is impossible to interpose the data into the table

Because insert it is always fulfilled in transaction.
Adjust DTC.

7

Re: Insertion of the data from the coherent server

invm wrote:

Adjust DTC.

Not always there is such possibility.
Can eat other candidate solutions of the task.
It is necessary to implement an insertion of the data from the coherent servers in one general destination table (TargetTable).
It is now implemented so that there is a connection to coherent and the data therefrom is brought in TargetTable, that is at the moment of performance there is an insertion of the data on the coherent server, but it as comparative tests showed a slow method.
Here also it would be desirable to alter so that procedure was fulfilled on the server with TargetTable.
But there is a row of restrictions:
Sources dataful have a different format, that is for everyone it is necessary to write individual request about sampling;
Therefore also it would be desirable to wrap it in stored procedure on the server a source and to address to them through EXEC (") c server name substitution

8

Re: Insertion of the data from the coherent server

lex452 wrote:

about usage  error Distributed transaction falls out.
Can explain why so happens. And so to get rid of an error.

Install for linked server parameter remote proc transaction promotion in FALSE

9

Re: Insertion of the data from the coherent server

lex452 wrote:

And into the error account if I knew that it means, on a forum wrote.

matter is not in knowledge, it is logical  here the full text of an error, instead of to retell the trimmed version that all played .

lex452 wrote:

It is necessary to implement an insertion of the data from the coherent servers in one general destination table (TargetTable).
Sources dataful have a different format

and why do not use tool specially intended for it - SSIS?

10

Re: Insertion of the data from the coherent server

The grandfather;
Ssis I basically too planned to try to pick up, but they are more difficult for supporting and changing, than stored procedures

11

Re: Insertion of the data from the coherent server

lex452 wrote:

There is a task in which it is necessary to interpose into the given table the data from the coherent server.
For unification it decided to fulfill insertion procedure on the initial server, the request should look approximately so

INSERT INTO dbo. SourceTable
EXEC [linkedServer]. [dbName].Scheme. [StoreProcedure]

Where  StoreProcedure returns 1 data set
Request in a format

INSERT INTO dbo. SourceTable
SELECT * from [linkedServer]. [dbName].Scheme. Table

Works without problems, and about usage  error Distributed transaction falls out.
Can explain why so happens. And so to get rid of an error.

And so?

declare @sourceTable table...
INSERT INTO @sourceTable
EXEC [linkedServer]. [dbName].Scheme. [StoreProcedure]

12

Re: Insertion of the data from the coherent server

lex452;
Bypass DTC through

INSERT INTO... SELECT * FROM OPENQUERY (... ' EXEC ')

13

Re: Insertion of the data from the coherent server

TaPaK wrote:

lex452;
Bypass DTC through

INSERT INTO... SELECT * FROM OPENQUERY (... ' EXEC ')

Openquery parametrization how much I remember does not support

14

Re: Insertion of the data from the coherent server

Rolg Hupin;
And than your sentence differs from that that I wrote in the very first question?

15

Re: Insertion of the data from the coherent server

lex452 wrote:

it is passed...
Openquery parametrization how much I remember

does not support
What?

16

Re: Insertion of the data from the coherent server

invm wrote:

it is passed...
Because insert it is always fulfilled in transaction.
Adjust DTC.

No, the error arises at EXEC. Insert does not initiate distributed transaction, but EXEC - yes.
If between servers a firewall adjustment DTC demands discovery of enough big range of ports, on what administrators not always go.
Adjust application so that procedure on a remote server added the data in the local table for this server, and then take away the data by means of Insert.

17

Re: Insertion of the data from the coherent server

Vladislav Kolosov;
And at what here ??

18

Re: Insertion of the data from the coherent server

alexeyvg wrote:

it is passed...
Install for linked server parameter remote proc transaction promotion in FALSE

Here it helped

19

Re: Insertion of the data from the coherent server

TaPaK wrote:

it is passed...
What?

it is impossible for a variable to set connection server name

20

Re: Insertion of the data from the coherent server

lex452 wrote:

it is passed...
It is impossible for a variable to set connection server name

dynamic sql? All to envelop in sp_executesql

21

Re: Insertion of the data from the coherent server

TaPaK wrote:

lex452;
Bypass DTC through

INSERT INTO... SELECT * FROM OPENQUERY (... ' EXEC ')

Thanks, visors.

22

Re: Insertion of the data from the coherent server

TaPaK wrote:

Vladislav Kolosov;
And at what here ??

For operation DTC which is used by distributed transaction, are necessary TCP ports, their interserver firewall/proxy can close.

23

Re: Insertion of the data from the coherent server

lex452;
Try to envelop objects of a linked server in synonyms.

CREATE SYNONYM FOR [linkedServer]. [dbName].Scheme. [StoreProcedure]

Requests with OPENQUERY it is difficult , and with synonyms you can essentially reduce traffic volume on a network as the filter on the data works.

24

Re: Insertion of the data from the coherent server

Cristiano_Rivaldo wrote:

lex452;
Try to envelop objects of a linked server in synonyms.

CREATE SYNONYM FOR [linkedServer]. [dbName].Scheme. [StoreProcedure]

Requests with OPENQUERY it is difficult , and with synonyms you can essentially reduce traffic volume on a network as the filter on the data works.

And  relieves from DTC? And what  on the data

wrote:

For operation DTC which is used by distributed transaction, are necessary TCP ports, their interserver firewall/proxy can close.

Here you are a little right, for the HARDWARE was not resulted by the full text of an error but in view of that it at all did not adjust DTC for the server it is very strong by

25

Re: Insertion of the data from the coherent server

TaPaK;
Wanted to tell that in most cases synonyms , than OPENQUERY. It is possible to specify filters in the unit where without  in dynamic sql - .