Insert into problem with linked server

Question

Insert into problem with linked server

Jesper-244176

SSCertifiable

Points: 7142
More actions
July 7, 2005 at 4:39 am

#66722

Hi.
I have a stored procedure on a remote server that performs a single select. When I execute the following in the query analyzer, everything works, and the result can be viewed.
exec linkedserver.dbname.dbo.procname
But when I create a table with columns matching that of the stored procedure select, and then execute the following
insert into tablename exec linkedserver.dbname.dbo.procname
the query analyzer hangs. I have tried to run it in a distributed query, but the result is the same.
Any suggestions?
Thanks in advance.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

I'm not sure I understand exactly what you want to do but exec stored procedure and insert into are 2 separate statements....

Your stored procedure only selects....can you modify it so that it selects the rows and inserts it into the table that you have created ?!

**ASCII stupid question, get a stupid ANSI !!!**

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 2

My first reply disappeared - maybe it will be back later...

It is possible to combine "insert into" and "exec" as follows (a simple example):

create table testtable (id int)

go

create proc testproc

as begin

select 1

end

go

insert into testtable exec testproc

select * from testtable /* result 1, i.e the output of testproc */

My problem is that when the stored procedure is accessed via linked server, the corresponding "insert into table exec linkedserver.dbname.dbo.procname" query never times out.

To answer your second question, I would like to avoid changing the stored procedure, since I am not responsible for the remote server.

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

you cannot combine the two....

one way of doing this would be to save the results of your stored procedure query in a file (could be .txt, excel etc...) and then import the results from the file into your table.

**ASCII stupid question, get a stupid ANSI !!!**

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 4

Did you run my example on your server? It runs on my server...

All I can say is that I have (mis)used the "insert into exec" feature (without the linked server) for years and that I am not the only one, so of course the two can be combined - at least on my servers

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

Here's one suggestion - Have you looked into Openrowset function to see if you could use that maybe ?!?!

**ASCII stupid question, get a stupid ANSI !!!**

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 6

That might work, I haven't tried it. I was hoping that I could use linked server since it is more elegant and probably also faster.

sushila SSC-Dedicated Points: 35293 More actions · Answer 7

Jesper - I'm curious - did you get this to work & if so could you please post the solution ?!

I was just wondering if openquery was not the right solution for this ?!

**ASCII stupid question, get a stupid ANSI !!!**

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 8

Are either of the servers Win 2003 OS? I've hit this same type of issue due to the default DTC settings on Win 2003.

The straight

exec linkedserver.dbname.dbo.procname

is a Remote Procedure Call that does not invoke a transaction, but the insert into a local table from a remote stored procedure does implicitly use a distributed transaction. Windows 2003 modified the security settings for these (see: http://support.microsoft.com/default.aspx?scid=kb;en-us;817064)

You (or a Windows admin) should navigate to the following:

Start / Program Files / Administrative Tools / Component Services (or follow the path in the KB article above)
Expand the Component Services node until you reach "My Computer"
Right-click on My Computer and select Properties
Go to the MSDTC tab and select "Security Configuration"

For Windows 2003 RTM (not SP1) , verify that "Network DTC Access" is selected. You may also need to select "Network Administration", "Network Transactions", and "Network Clients". For Win 2003 SP1, "Network DTC Access" must be selected. Under Transaction Manager Communication, I've selected "Allow Inbound" and "Allow Outbound" and the radio button "No Authentication Required". The latter was necessary for us because we have Win 2000 machines as well. You might want to check Microsoft Knowledge base for details of DTC security on Windows 2003 to verify these settings are reasonable for your environment.

Also verify that the DTC Logon Account has network privileges. The account NT AUTHORITY\NetworkService has priviliges; LocalSystem does not.

At this point, stop and start MSDTC. I think you also need to stop/start SQL Server service as well.

You may need to do the above on each Win 2003 server.

After that retry

insert into tablename exec linkedserver.dbname.dbo.procname

and see if it works. Let us know.

Hope this helps,

Scott Thornburg

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 9

Scott, Thanks for an excellent suggestion. Unfortunately both servers are Win 2000. But I have a feeling that we are getting closer...

Sushila, Openquery works... Unfortunately I cannot use it, since my local database must be on 65 and not 80 compatibility level (right click on database in enterprise manager, choose properties, options tab to set compatibility level). I could of course add a 80 compatibility level db and execute my query there (using openquery), but I am hoping for a nicer solution.

Thanks for the effort, guys. Other suggestions are more than welcome...

noeld SSC Guru Points: 96590 More actions · Answer 10

Do you have any firewall in between the servers?

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 11

what happens when you use something like:

@declare @stm = 'exec linkedservername.dbname.dbo.procname'

create table #T(fld1 int, fld2 ....)

insert into #T(fld1,fld2,...)

exec(@stm)

* Noel

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 12

Same problem with dynamic execution - and there is no firewall between the servers, I've been told (I have tested that port 135 is open).

s yil Newbie Points: 3 More actions · Answer 13

Hi,

I have the same problem ..

trying to call a SP from linked server ,

I want to insert select results into a local table ...

Getting the following error:

Server: Msg 7391, Level 16, State 1

The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]

OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

Does any one have a solution?

(would be grateful for a suggestion...)

Olga Turley Grasshopper Points: 12 More actions · Answer 14

Did you try to insert:

SET XACT_ABORT ON

just before you execute your INSERT INTO... EXEC statement?