>>>>>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?

You're selecting from #temp but created #Results table. I am wondering why you don't get an error. Also, please add aliases for all columns.