I see, thank you ever so much everyone!

The example on MSDN had a number of contexts in a diagram which had me confused. I always thought parameters were not...

That is what I always understood, this is what has caused my confusion from MSDN:

"Each user that is currently executing the query has a data structure that holds the data...

Excellent, i thought I read read every bit of matter on the subject and I have to say it was a great read.

I am certainly interested to see the trade...

Might be worth a shot, have you tried square brackets around the server name when you try to connect via SSMS? I am sure I have had something similar...

Just to ask, is there a counter I can use in perfmon (as its an old box) to determine just how many users are connected in and if any connection...

Cheers, we had already implemented a load of code to cater for these closed connections and it has made a difference but we are still getting issues. Im not...

No the code underneath is optimsied and efficient enough, sproc takes a millisecond.

I still dont understand how without closing the connection, pooling is utilised. If a connection is opened...

I know there could well be no difference really but...

If the databases are not intended to be online again on the original server then wouldn't an attach/detach be better, only...

Cheers that makes sense.

I didnt understand why there was only one process id in the activity monitor when there was activity from two different users going on showing...

BUILTIN\ADMIN isnt required as a rule, what account is the SQL Server Service running under?

Detach and Attach is the best way for the user databases. For system, you may be able to do some of them (model perhaps) but not master for sure....

Cheers, just edited my post. To my knowledge I always thought running as local system was a bad idea, I dont know if that would cause the issues though...

Has it recently stopped working or is it a new install?

I always assumed running agent as local system was a bit of a no no.

Try:

SELECT _BODY, REPLACE('from a blogis easy', 'blogis', 'blog is') from aaa.dbo.aaa2

The replace needs to be in the SELECT before the FROM clause.

I have configured replication before and havent come across an partially invisible database!

It doesnt sound totally right to me, but it isnt permissions is it? I wonder if you...