I've had some awful problems due to improper transaction handling. Things got lot better couple years ago when we fixed it all as described below.  You can get trickier & implement partial...

Gary, can you elaborate further why cursor variable is preferable to local cursor as Lars wrote?  Aren't both similarly limited in scope? 

Wrote this long time ago, could probably do better job now but we still use it (still managing over 100 6.5 servers).  Has a hard-coded hackish calculation of log-space used for...

First need to fix previous post para #5 "if exists" to "if not exists" ... that's pretty embarassing.

I have never explicitly tried to log SQL errors directly into a...

Nice article, I especially near the end where real world experience is discussed.  I personally would like to see real world comparison between nolock and readpast, but maybe that's another...

 1.  The name prefix xp_ is typically used for extended stored procs (not written in T-SQL), I would not use it here.

 2.  After AS, add "set nocount on" line

 3.  I...

I have similar purge/archive routines.  In dev environment I can run these as single large delete, and they run twice as fast as when cursoring thru and deleting 1 row...

I agree w/Andrew Murphy.  You should either eliminate all tables from GEOGRAPHY to EXTENDED_ACC_SET_TYPE--they're not contributing anything, given the outer joins; or else change these joins to inner joins so...

from http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops3.mspx

sysprotects is a "dynamically created psuedo-table" while syspermissions is a real table.  Perhaps it is THE real table underlying sysprotects?

Never used it in real life, but your link & BOL both explain this is to support scrollable client cursor in dblib apps.  I doubt you're using dblib for ASP/SQL app, I...

0.  At top, between AS and DECLARE, add line with "set nocount on"

1.  cursor declaration:  depending on your coding stds, definitely add either "FOR READ ONLY" (ANSI) or FAST_FORWARD (TSQL). ...

I have never wanted to do this before, but now curious.  Just made both a scalar and table-valued function that declares, opens, traverses, and deallcoates a simple cursor.  Both worked...

Problem is global cursors.  Can fix with TSQL cursor declare syntax instead of ANSI-92:

            declare c0 cursor local fast_forward for ...

Or you can change the DB option CURSOR_DEFAULT value to local to set this for...

The problem really might be incorrect syntax near the AND if the connections on your hosted site wind up using different ANSI connection options than when you ran all this...

I have accepted this recommendation due to the performance impact, and don't argue that. 

But I find that this has killed for me the utility of object-ownership in general.  Has anyone made good use of object...