100 should not be equal 200 in any version.

try to display all values:

SELECT test_table1.test_id,test_view1.test_id , test_view1.some_test_value

FROM  test_table1

LEFT OUTER JOIN test_view1  ON test_table1.test_id  = test_view1.test_id

What if you run

SELECT * FROM test_view1

test_id     some_test_value

on that second server?

It's not the same.

You code: UPDATE ##tblAmounts1

my code UPDATE A

And all you WHERE conditions are applied to A, not to ##tblAmounts1.

You never filter in your script the rows to be updated. That's why it will always update entire table.

Use this:

UPDATE A

SET AgeBracket = L.AgeBracket

FROM tblSC L 

INNER JOIN ##tblAmounts1 A...

What is this:

SET @loop_counter = ISNULL(SELECT COUNT(*) FROM @item_table),0) ?

I would like to see a case when SELECT COUNT(*) returns NULL.

Problem is:

select @username = username from inserted

You take only last username from inserted and ignore all previouse ones. And you cannot really predict which one will be the last in every...

Set scheduler to run an SP every hour (every n minutes if you need) and within that SP check UTC time and run appropriate task depending on the time zone.

EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML

SELECT * FROM OPENXML (@iDoc, '/Commission/CopyData', 2)

            WITH (Comm_Rate real ) -- - Returns 10

 SELECT * FROM OPENXML(@iDoc,'/Commission/CopyData', 2)

            WITH (Res_Begin_Date datetime  ) --- returns empty

/*...

You are assignig error code returning by SP to @Result and than check @@error.

What would you expect?

@@error reports you if "EXEC" was failed, e.g. no such SP, wrong number of...

Select from subquery:

SELECT Lesson_1,Code_1

FROM ...

UNION

SELECT Lesson_2,Code_2

FROM ...

UNION

SELECT Lesson_3,Code_3

FROM ...

UNION

SELECT Lesson_4,Code_4

FROM ...

UNION

SELECT Lesson_5,Code_5

UNION

FROM ...

SELECT Lesson_6,Code_6

FROM ...

Same problem.

FOR XML EXPLICIT statemets inserts CR after 2033 character and I cannot find out any way to control it.

SELECT REPLENIS."PRODUCT", REPLENIS."BULKSLOT", 1, REPLENIS."PACKSLIP", REPLENIS."WAREHOUSE"

FROM REPLENIS 

INNER JOIN dbo.Nums on REPLENIS."QTY_REQD" <= dbo.Nums.Number

ORDER BY    REPLENIS.BulkSlot

dbo.Nums is a table with IDENTITY(1,1) int numbers in coumn Number.

Is it row-by-row update using cursor?

If not why lock each row, not the whole table?

Is it SP updating the table or query from application?

"Assuming that the application cannot be modified"...

WITH (un_cid varchar(50), tnum nvarchar(50), snum nvarchar(50), cid varchar(50), entityid varchar(50), ctype varchar(50),

  why  varchar(50),

  comp  varchar(50),

  oc  varchar(50),

  bs  varchar(50),

  ae  varchar(50),

  cp  varchar(50),

  trd_date datetime,

  set_date datetime,

  mat_date datetime,

  val_date   datetime,

  trader  varchar(50),

  famt  float(8),

  price  float(8),

  coupon  float(8),

  next_coupon datetime,

  last_coupon datetime,

  cpnfreq  int,

  cpnrate  float,

  cpntype  int,

  daycounttype smallint,

  exch_notion smallint,

  contract_spot float(8),

  base_cur varchar(50),

  year_basis int,

  buy_currency varchar(50),

  buy_currency varchar(50),

  buy_amount float(8),

  sell_currency varchar(50),

  sell_amount float(8),

  [timestamp] varchar(50))

If number of records = 1 then sum(rmstranamt) = rmstranamt.

So just use sum(rmstranamt).