to use openrowset or openquery, adhoc would need to be enabled; if that is not possible, then linked servers, or using a login that has access to both databases would...

I think linked servers would allow you to change authentication information, but you didn't want to do that...

The openrowset command lets you specify the connection information, you might roll both...

even worse, i believe the UPDATE() and COLUMNS_UPDATED() returns which columns were REFERENCED in the update statement, not which ones actually changed their value...since a lot of datalayers would send...

so correct me if i'm wrong, but you might want a sampling, like a random top 3 per group, right?

if that is the case, it's deceptively simple...a CTE and newid...

can you check the version of the instance you are connecting to? it might be that that instance is, say RTM, but your client tools are using SP4 or something,...

i haven't tried Azure yet, but why can't you create a permanent Tally table and use that?

Looks like the server name in @REMSServer has TWO slashes in it, the way C languages tend to escape the slashes.

can you PRINT @REMSServer and confirm the value is NYSQL004\NYSQL004...

doh rereading based on getoffmyfoot's comments, you are definitely looking for a running total kind of solution.

the pseudo code doesn't cut it for this one, i think. We'd need to see the actual statement you are executing, that is raising the error, so we can really...

Horrible example, Celko; all it proves is the row used to to update the parent table is not guaranteed unless you make sure the joins form a 1:1; That's SQL...

adding the same WHERE statement for the ORDERPK to each of the CTE's might speed things up also; you be filtering to jsut the rows for the order in...

you've got a few things killing you, without the actual execution plan , these are jsut guesses:

the WHERE statement on ORDERS is not SARG-able.

instead of doing WHERE DATEADD(dd, 0, DATEDIFF(dd,...

the beautiful part about the "proprietary" update format is the ease in which you can test to see which records are matching/ would be affected.

simply inserting "--SELECT *", and highlighting...

teh same escape method: two single quotes in a row:

For instance

Update table1

set title = 'Daddy''s Daycare'

something like this works...the important part is that two single quotes in a row is the escape sequence, so it can be contained within single quotes:

select * from yourtable where...