Problems with Insert into Temp Table

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 2005 Forums

Transact-SQL (2005)

Problems with Insert into Temp Table

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-11-02 : 12:06:39

The below code works fine and 47 records are returned in less than a second:

declare @JobNumber int
set @JobNumber = 6003047
exec [VGIWPW03-SQL3\EQUENTIALPROD].goLabor30.dbo.sp_Vecellio_ProductionInquiry @JobNumber

I need to insert the result into temp table and when I do like this I get error:

declare @JobNumber int
set @JobNumber = 6003047
If Object_ID('tempdb..#Equential_JobQtyTable') Is Not Null    Drop Table #Equential_JobQtyTable
CREATE TABLE	#Equential_JobQtyTable
		(  eq_cost_code nvarchar(15) unique,
		   eq_item_name char(15),
		   eq_sum_qty decimal(10,2)                                             
                                      )
insert into #Equential_JobQtyTable
exec [VGIWPW03-SQL3\EQUENTIALPROD].goLabor30.dbo.sp_Vecellio_ProductionInquiry @JobNumber 
select * from #Equential_JobQtyTable

Error:

Msg -2, Level 11, State 0, Line 0
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-02 : 12:37:39

run this by itself and see what happens

exec [VGIWPW03-SQL3\EQUENTIALPROD].goLabor30.dbo.sp_Vecellio_ProductionInquiry @JobNumber
it looks like it is taking a long time to execute that sp.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-11-02 : 13:04:17

The first part of my post is when I call the sp without having any "insert into...". Response time, less than a second.

MSDTC is configured on both sides and up and running:

Network DTC acceess = Yes, Allow Remote Client = Yes, Allow Remote Admin = Yes, Allow Inbound = Yes, Allow Outbound = Yes, Enable XA Trans = Yes

The problem is when I add the "Insert into..."

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-02 : 13:13:39

oops sorry noticed it just as I clicked the submit button. what datatype is being returned for eq_cost_code, on sp_Vecellio_ProductionInquiry what is the datatype of the first field? see if you can add an index on the temp table for eq_cost_code and see if you get better performance. kind of strange just for 47 records.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources