Well, I have no idea why, but it appears as though if I launch SSMS or Visual Studio as Administrator, it solves the problem! Go figure ...

Much cleaner. Thanks Lynn, didn't think of using an inequality clause on the join!

Sure, although really that's the same solution - doing an EXISTS or a JOIN will be roughly equivalent in performance.

What I was hoping for, was a version of this query...

Yeah I'll echo what Bill said. Basically, you can have a query which runs perfectly fine some times, and other times it will return an error - *even if* the...

You could start with something like this. It gives you back almost what you're looking for, it just leaves out the cases where the employee pay rate never changes :

CREATE...

Thanks for the suggestion, Gail.

I've scheduled the server-side trace. Now we see if it finds anything for me ...

Okay, my bad, now I see where the problem is. It seems like it's centered around the "WITH EXECUTE AS ___" part.

The problem is easy to identify - just change...

I'm still not sure what the problem is. If I create a new stored procedure which executes that one, everything works fine...

Does the NotificationID reference some sort of lookup table for the type of Notification (which I assume are your types of newsletters)?

If so, your query could be constructed as such:

INSERT...

Yet another version, that doesn't limit you to having only 5 of them. This one's basically an adaptation off Sean's version.

Note that you could theoretically hit a limit on the...

As an example (clearly not your exact query), would something like this not work?

CREATE PROCEDURE Test1

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET...

Nice solution =)

But, when dissecting it, I realise that what you've done is constructed a series of UPDATE statements for each property that needs updating and inserting.

So, taking inspiration from...

Indeed. That's the option I'm exploring now - instead of just two tables, I'm going with the original design of the four tables, but having each table have its own...

I fully agree with your points Sean, unfortunately, company constraints mean those are the names for the columns. Got no control over that.

Regarding the GUIDs part, again, don't really have...

Well, as I said, unless you use an ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE, the SET OFFLINE will wait until all open processes are finished executing before setting the...