INSERT INTO table EXEC - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

INSERT INTO table EXEC

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2006-08-10 : 13:20:53

I gave some code to a junior developer in a different division. They say that they can execute a sproc, and it runs in seconds...but when they try to insert the results to a holding table, the process hangs. Any ideas? Here is the code and her comments:


I am still having trouble running the code that Lana had 
sent over to me.  

TRUNCATE TABLE SprocResults

DECLARE @rc int, @rs int, @x int
INSERT INTO SprocResults (company_region, name, user_role, User_Type_Desc, user_id, ssoid)
EXEC @x = PAERSCBVD0014.db_profiles..usp_SEL_Metrics_Main  @rc OUTPUT, @rs OUTPUT 
SELECT @rc, @rs

If I comment out the INSERT statement, I get the result set 
(in a matter of seconds).  However, once I put the INSERT 
statement back in, it just keeps executing until I kill it.  
Any advice you can give on this matter is greatly appreciated.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-10 : 13:47:51

Are you calling a remote stored procedure via a linked server? If so, the slowness might have to do with it needing to initiate a distributed transaction. I don't know why it would be slower though. Have you tried calling a shell of a remote stored procedure, one that doesn't have the output parameters? Have you tried removing @x? I personally would start with a shell, then keep adding things in until you can determine where the slowness is. If it occurs on the shell, then I'd say it has to do with DTC.

Tara Kizer

X002548
Not Just a Number

15586 Posts

Posted - 2006-08-10 : 15:23:20

Removed the @x=

That didn't work

Removed the output variables

That didn't work

I'm going to ask her to restore my db on her server and try to make the call to see if I have the problem next

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources