SQL 2005 calling remote stored proc never completes?????

[sanjdhiman]

Hi can I do this in SQL SERVER 2005

INSERT INTO LocalServer.LocalDatabase.dbo.#Temp
EXEC REMOTESERVER.RemoteDatabase.dbo.storedprocedure_test


The stored proc when ran like above never completes but if I just run the stored proc like below

EXEC REMOTESERVER.RemoteDatabase.dbo.storedprocedure_test

It takes 5 seconds to complete which is correct.

Why does it never complete?
Thanks for your help on this

Its a WEIRD one

Note:Cannot use OPEN QUERY as this stored procedure takes parameters.

 

[mrdenny]

First it should be executed like this.

INSERT INTO #Temp
EXEC REMOTESERVER.RemoteDatabase.dbo.storedprocedure_test

Temp tables are server wide, not database specific and shouldn't be qualified with server, database, or owner.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sanjdhiman]

Hi

I did try it that way first, INSERT INTO #temp exec stored proc.

But it still never finished.

If I just call the sp, it runs and runs under 5 seconds.

So I cant see why it would never complete?

Any ideas

Thanks

Sanj

 

[mrdenny]

Run SQL Profiler on both machines and see what it shows happening.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLSister]

have you created the temp table structure first?

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[sanjdhiman]

Yep Created #temp beforehand and then ran it and still it never finishes to run

Very strange

 

[gmmastros]

If you create a #Temp table in one stored procedure, and then call another stored procedure that also creates a #Temp table, then you will have problems. I don't know if this is your problem, but I suggest naming the #Temp table to something that is VERY UNLIKELY to be used in another stored procedure.

Ex...

[tt][blue]
Create Table #TempThisIsUniqueName(Field1 int, etc...)

INSERT INTO #TempThisIsUniqueName
EXEC REMOTESERVER.RemoteDatabase.dbo.storedprocedure_test
[/blue][/tt]

I don't know if this is the problem you are having, but is certainly something you should check. In any event, I wish you luck.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom