Is this due to an implicit transaction that is not being committed ?

Have you considered compact edition ?

http://www.microsoft.com/sqlserver/2005/en/us/compact.aspx

Table variables have never been in -memory only objects, where this myth originates from im not exactly sure.

Heres a link for sql server 2000 http://support.microsoft.com/kb/305977

which states

Q4: Are table...

Maybe a binary file editor 🙂

Ive added the OrderCol columns simply to use in the order by clause.

I could of equally said

select 0 as rid,'ALL' as UserName, 0 as OrderCol

union all

select rid,UserName,1

from hc_users

where...

Here's what i would do

select 0 as rid,'ALL' as UserName, 0 as OrderCol

union all

select rid,UserName,1

from hc_users where locationid in (@locid)

ORDER BY 3,2

<semirant>

How about going medieval and draconian !

What really bugs me is when i ask for more info, ie a query plan DDL etc.

Why not have a 'trusted' user lock the...

Got to be worth a go 😉

Please see this link

http://www.sommarskog.se/arrays-in-sql-2005.html

2 , will leave you open to SQLInjection.

Please confirm your sqlserver version.

Could be a hundred other things. Need DDL and execution...

This query

SELECT deptid FROM Humans WHERE deptid=@DPID

Is returning more than one row, so you need to define which one. Use TOP 1 and an Order by clause.

Although Its superfluous...

Possibly the simplest way is to use DISTINCT.

IMO, this link is required reading

http://www.karaszi.com/SQLServer/info_datetime.asp

Please see this article

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/