>>>>I narrowed it down to the Routes table being the problem. Once I remove that from the SP it works properly!
>>>
>>>Sometimes even in T-SQL it makes sense to break complex query into 2 parts. In other words, you may try getting all info except Routes into temp table first and then add Routes table to the final select. Also, make sure that Routes table is indexed. Is it a wide table?
>>
>>hmmm, there may be more to it than that. I had started deleting tables from the code and trying to run it again and when I removed routes it worked, but I had removed a few others before. Now I am just removing routes and I'm still having the same problem. :(
>>
>>I will check the indexing, but the execution plan seemed to be happy with all the indexes.
>>
>>I do not know what you mean by "a wide table". It's only got two columns and less than 100 records.
>>
>>Can you give me an example of breaking this up into two, please? I've never used temp tables.
>

>if object_id("tepmdb..#Results',N'U') IS NOT NULL drop table #Results;
>
>  select ...
>  into #Results
>  from ....
>
>
>  select R.*, Rt....
>  from #Results R inner join dbo.Routes Rt on ...
>
>

Thanks, I've tried that but when I select this SP as the source for the tableadapter I do not get any columns. This is my full definition:

USE [IBC]
GO
/****** Object:  StoredProcedure [dbo].[InvoicesSelectByManifestNumberTest]    Script Date: 01/29/2013 10:31:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InvoicesSelectByManifestNumberTest2]
(
	@awb_manifestnumber int
)
AS
	SET NOCOUNT ON;
	
	if object_id('tepmdb..#Results',N'U') IS NOT NULL drop table #Results;
	
SELECT  cus_firstname
		,cus_lastname
		,cus_company
		,cus_addr1
		,cus_addr2
		,cus_number
		,cus_hold
		,cus_cashonly
		,inv_pk
		,inv_number
		,inv_date
		,inv_pieces
		,inv_weight
		,inv_goodsvalue
		,awb_number
		,awb_manifestdate
		,awb_manifestnumber
		,rte_name
		,are_name
		,cnt_code
	into #Results
FROM Customers 
		inner join Routes on cus_rtefk = rte_pk
		INNER JOIN Areas ON cus_arefk = are_pk
        INNER JOIN Countries ON cus_cntfk = cnt_pk 
        INNER JOIN Invoices ON cus_PK = Invoices.inv_cusfk 
        INNER JOIN AirWayBills ON inv_awbfk = awb_pk 
	WHERE ([awb_manifestnumber] = @awb_manifestnumber)

SELECT  cus_firstname
		,cus_lastname
		,cus_company
		,cus_addr1
		,cus_addr2
		,cus_number
		,cus_hold
		,cus_cashonly
		,inv_number
		,inv_date
		,inv_pieces
		,inv_weight
		,inv_goodsvalue
		,awb_number
		,awb_manifestdate
		,awb_manifestnumber
		,rte_name
		,are_name
		,cnt_code
		,ivd_amount 
		,cty_name 
	FROM #Temp
        INNER JOIN InvoiceDetails ON inv_pk = ivd_invfk 
        INNER JOIN ChargeTypes ON ivd_ctyfk = cty_pk
ORDER BY rte_name
		,are_name
		,cus_number
		,inv_number
		,cty_name
		
GO

Am I doing something wrong?