Mike,
OK ... that *was* the problem (the binary sort order), so we're having the customer set up another instance of SQL Server without that sort order. That'll take care of the immediate problem for now (he's setting up our software on his laptop in order to demo it to potential customers) ... we just needed to get him up and running. But, you're correct that we should have done this differently, as you suggested, for better performance. That'll be the next thing we tackle, revamping our SP's ... good thing our app is still in beta, eh? =)
Thanks!!
~~Bonnie
>Is the server running with a case-sensitive sort order?
>
>Also, becareful with this pattern. Referencing temp tables outside of the stored procedure that created them can cause performance issues due to recompilations:
>
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_queryrecompilation.asp>
>If you're using SQL Server 2000 and want to isolate the funtionality that fills the temp table, you'd be better off calling a table-valued function:
>
>
> CREATE TABLE #temp1 (xyz char(1))
>
>
> INSERT INTO #temp1 (xyz)
> SELECT x FROM dbo.fn_myfunc(...)
>
> SELECT * FROM #temp1
>
>
>
>>We're having a problem with Stored Procedures that access Temp tables ... the problem occurs only on one of our customer's servers (using the exact same database, on a different one of their servers, there are no problems).
>>
>>We've traced the problem to something to do with the visibility or scope of Temp tables.
>>
>>Here's an example of what I mean:
>>
>>SP1 has this code:
>>
>> CREATE TABLE #temp1 (xyz char(1))
>>
>> EXEC SP2
>>
>> SELECT * FROM #temp1
>>
>>Now, SP2 contains code that uses #temp1 if it exists:
>>
>> IF OBJECT_ID('TempDb..#temp1) IS NOT NULL
>> INSERT INTO #temp1 (xyz) VALUE ('x')
>> ELSE
>> SELECT xyz FROM someothertable
>>
>>What ends up happening is that SP2 does not recognize that #temp1 has already been created and what we're trying to figure out is why. Something to do possibly with *where* Temp tables get created? Isn't it *always* in TempDb? If not, then where is the setting that dictates that?
>>
>>TIA,
>>~~Bonnie