INSERT INTO EXEC() PROBLEM

Question

INSERT INTO EXEC() PROBLEM

Dumel

SSC Veteran

Points: 215
More actions
October 4, 2006 at 9:35 am

#113866

Hi!
Weird problem: I use a SQL server 2000 SP3 on windows 2000 Advanced Server. I'm trying to get some data from another server. So I created a linked server. The other server team gave me a Stored procedure to work with so I did the following code:
insert INTO MyTable
exec TheOtherServer.TheOtherDB.dbo.TheStoredProcedure
It worked perfectly well until they went throught a lease roll. After that, it seems that the job stopped running! Not emailing me or nothing... just stopped. When I tried to run the statement manually, it was hangging. I could not even kill the process... It would just hang. I finally got my hand on the content of the strored procedure which turn out to be a simple select. So I tried the following:
insert INTO MyTable
select MyID,Submitted,Reviewed
from TheOtherServer.TheOtherDB.dbo.TheTableName
order by MyID
And it worked! So I asked the guy on the orther server to create a view so I can use the view instead... just to make it "clean". So I tried that:
insert INTO MyTable
select MyID,Submitted,Reviewed
from TheOtherServer.TheOtherDB.dbo.TheViewName
order by MyID
But it started to HANG again!... What Am I missing? What's the difference?
PS: I could actually ran the "exec" statement and the "select view" directly in query analizer but if you put INSERT INTO in front of it, it start to hang!... So I'm sure that I did have the privileges....

Viewing 10 posts - 1 through 9 (of 9 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

When you "insert" , "update" or "delete" using a linked server you are starting a distributed transaction. For that to work you need to make sure that DTC is correctly configured, That the sp is coded with SET NOCOUNT ON, that you are effectively retrieving only what you need and that the credentials are being passed correctly to the remote server.

Cheers,

* Noel

Dumel SSC Veteran Points: 215 More actions · Answer 2

But... Why this works

insert INTO MyTable

select MyID,Submitted,Reviewed

from TheOtherServer.TheOtherDB.dbo.TheTableName

order by MyID

But not this:

insert INTO MyTable

select MyID,Submitted,Reviewed

from TheOtherServer.TheOtherDB.dbo.TheViewName

order by MyID

???

noeld SSC Guru Points: 96590 More actions · Answer 3

When using Views and a linked server, the metadata has to be passed around. If there is a problem with that you get hanged.

Does a simple select in the view works ?

select MyID,Submitted,Reviewed

from TheOtherServer.TheOtherDB.dbo.TheViewName

order by MyID

* Noel

Dumel SSC Veteran Points: 215 More actions · Answer 4

Yes, the three metods (Stored Proc, view and table) Work very well... but if you put a "INSERT INTO" in front of it, only the "table" works!

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Try INSERT INTO #Table

_____________
Code for TallyGenerator

Vernon Jimmerson-306463 Mr or Mrs. 500 Points: 561 More actions · Answer 6

I don't think this issue is directly caused by using a linked server. It really is annoying. The EXEC part below will run in about 20 seconds and return 20,000 rows. If I try to insert it into the table as shown below it takes about an hour to do the insert. I watch the insert through the profiler and there is no activity. I insert 20,000 dummy records into ##Trans while this insert was running. They went in right away. I moved the temp table creation and population into the dbo.usp_loan_transByTypeAndDate procedure and works fine. Takes about 30 seconds. Sounds like another bug!

INSERT INTO ##Trans(

loan_id

, loan_num

, client_id

, trans_type

, distrib_eff_date

, distrib_type

, distrib_amt

, RunningTotal

)

EXEC dbo.usp_loan_transByTypeAndDate

@loan_id = @loan_id

, @loan_num = @loan_num

, @client_id = @client_id

, @trans_type = @trans_type

, @distrib_type =@distrib_type

, @boolExcludeLP = @boolExcludeLP

, @as_of_trans_eff_date = @as_of_trans_eff_date

, @boolGroupOnTransType =@boolGroupOnTransType

, @boolUseProcessList = @boolUseProcessList

The same issue is occuring for an insert using a user defined table. The select works find but the insert hangs. It may complete in an hour or so. I'm not sure but it's definately a serious issue.

Cheryl Hanlon SSC Enthusiast Points: 151 More actions · Answer 7

although this is an old thread i thought i would bump it

I had the same problem over the past few days and the resolution was to go into admin tools\component services. Right click my computer, click on security configuration and the enable network dtc options were not selected.

I selected network dtc access, allow remote clients, allow remote admin, allow inbound and outbound and no authentication needed. This solved my problem.

Chris Mohrbacher-293594 Grasshopper Points: 10 More actions · Answer 8

That sure solved *my* problem, Cheryl. Glad you jumped in when you did.

How on earth did you figure that out?

Chris Mohrbacher

Sergiy SSC Guru Points: 110208 More actions · Answer 9

"DTC" means "Distributed Transaction Coordinator".

It must be on to perform distributed transactions.

Noel pointed on it in the very first reply.

_____________
Code for TallyGenerator