Insert the results of a stored procedure into a table

Question

Post reply

Insert the results of a stored procedure into a table

jma100

Mr or Mrs. 500

Points: 576
More actions
January 13, 2004 at 6:01 am

#152012

Hallo friends
I am using the following method to insert the result of stored procedure into a table
insert into <table name)
exec <stored procedure>
This is functioning without any problem. If the results are comming from a linked server, inserting is not working. The linked server is using inside the stored procedure.

Any ideas

Joseph

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

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

So, what kind of error do you get?

Can you exec without the insert?

Are you using the correct syntax?

exec server.dbname.owner.procname

I assume here that you are trying to do something like; INSERT mytable EXEC remoteserverProcedure.

Is that correct?

=;o)

/Kenneth

jma100 Mr or Mrs. 500 Points: 576 More actions · Answer 2

Procedure give results without insert within 1 sec.

But when I use this procedure with insert is not working.

there is no error messages. I have waited more than 30 minutes. and nothing happend.

Joseph

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Can you provide your exact syntax for both attempts?

Both when it succeeds and when it fails.

If it fails.. You say you wait - Does the query come back or is it still running after 30 minutes?

If the latter, then check for blocking on the remote server.

=;o)

/Kenneth

jma100 Mr or Mrs. 500 Points: 576 More actions · Answer 4

storedprocedure

Create procedure test_insert

as select title_id from mylinedserver.pubs.dbo.titles

exec test_insert is working

but

create table #tmp

(title_id varchar(15))

insert into #tmp

exec test_insert is not working

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

And you are still positive that you recieve no messages at all?

I must ask you if the query completes at all? If it does then you should recieve something in return.

Have you tried it with a permanent table instead? Or try to specify the column name in the insert.

Have no linked server I can try it on for the moment, sorry.

=;o)

/Kenneth

jerry6484 SSC Enthusiast Points: 120 More actions · Answer 6

Try setting SET NOCOUNT OFF in the stored procedure. I have had sp's return nothing because I did not set nocount to off.

sheepoo SSC Eights! Points: 959 More actions · Answer 7

This will only work with OPENQUERY. I spent a lot of time on it. Use OPENQUERY to get the results from your remote server

Hope that helps....

PW-201837 SSC-Insane Points: 20805 More actions · Answer 8

This thread is 14 months old ... chances are they have the answer by now

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Better late than never... It was the answer I was looking for...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)