Do you have an execution plan showing how the function is behaving?

That's great. I hadn't even tried it in 2005. Thanks for posting.

Total agreement.

OUTPUT parameters will do the trick, but they'll only work for single row inserts. If you need to do any batch work, you'll want to look into the OUTPUT clause...

Yes, I want direct control over what is run and how it is run within my system. Maintenance jobs are OK, but they can be problematic. I have a lot...

check the collation settings between each database, the connection settings (with focus on ANSI settings) and, check the service packs and hot fixes to ensure they're both the same.

You have to ask the last...

Is that 11 million on top of the 12 million? You could be looking at more and page splits and index reorgs on top of the stuff that Jack mentioned.

Something is shutting down your database. If it's not AUTO_CLOSE, it's something else. Code sure seems like the next best bet.

Is it possible the database is being closed by the app when it closes it's connection? That's possible.

You've got a scan going against 6 million rows and returning nothing. That's a problem.

I'd suggest checking the indexes on COI_AppDb. Also, the <> construct can cause problems with index...

I'm not sure. I do know that the construct where you're using 'SELECT... (SELECT..) + (SELECT...)' is a bit odd and not how I would have done it. As you...

I'm not sure...

JOINs are pretty much by definition going to be predicated on the PK, an FK or an alternate key that can function as linking mechanism for an FK. You can...

The second solution is better. The first is going to set up weird relationships. It'll be difficult to query against and hard to maintain. The second solution will make for...