Thanks a lot for the replies - especially as they pretty much matched what I said off the top of my head at the time (my manager looked aghast at...

Hmmm.. very quiet here on this subject - only 18 views in 2 days and I think four or five of those are me.

Oh well, fortunately somebody answered this for...

While I have an appreciation for a limited budget, it is much more expensive to have to come up with these types of workarounds than it is to buy more...

That's true, if I drop the database and re-run that script the same values come back, if I drop the table different values are used. But those second values are...

Thanks wolfkillj that was a good suggestion which would have helped track the problem down. As it is the penny dropped about 40 minutes ago when we found a job...

This actually, as a smarter colleague demonstrated, was caused by a stored procedure call that for some reason needed to be recompiled and was too much for the optimizer. We...

Ok don't worry - just one of those weird desktop issues I guess. Re-imaged my workstation - lost nearly a day but all ok now.

Thanks for all the positive feedback so far, in particular raulggonzalez for covering off the NULL issue I'd overlooked.

For the less confident raulggonzalez's line needs to be inserted into the...

Sorry, management are happy to leave this one on a legacy 2005 instance and I can only pursue in my own time. I can post the stack dump though.

I did...

After the stack dump in the log a line appears:

During upgrade, database raised exception 211, severity 23, state 72, address 00000000004A1A24. Use the exception number to determine the cause.

The plot thickens. It turns out I am able to restore the backup to a different 2005 instance. On 2008 and 2008 R2 instances on separate machines this causes the...

DBCC CHECKCATALOG (MY_DB)

just gives me the typical "DBCC execution completed. If DBCC printed error messages, contact your system administrator."

I'm surprised nobody has mentioned that the transaction will never complete. Nobody else looking at the database will ever see any of the rows added!

Thanks, it is indeed a permissions issue. I've checked and found that the ability to compile stored procedures in the publisher db comes and goes with membership of the dbo...

Belated thanks Roy - why indeed? This is certainly the way to do it, might get rid of the time-consuming backup/restore to do it properly like this in the fullness...