I believe this will do it, but not sure how it will perform:

UPDATE M

SET Date_Rtn = rmt.CUS_DATE

FROM MAIN_TBL As M

INNER JOIN

-- Derived table

(

  SELECT ORD_NBR, CUS_DATE

  FROM LinkServer..DB.TableName AS L1

 ...

T-SQL Parsing happens first. Then execution.

If the column does not exist, the parser barfs on the ALTER TABLE statement at *parse* time, before any of the code has actually executed.

You...

>>where clearedate = 'Blank'

What datatype is the [clearedate] column ? If it's a datetime, you can't compare it to the text...

Is SP1 for SQL 2005 applied ?

You could use DatePart() and construct the string from each date element - would require the zero-padding logic.

An alternative is using format codes 10 (date as DD-MM-YY) and 8 (time as...

Do you have any clustered indexes on the tables to prevent fragmentation ?

Does the UPDATE statement join on the "Invoice" column ? If so, is that column indexed in either...

Create a queue table to hold the details of the email, and have the trigger insert records to this table, along with a timestamp of when the item was queued.

Then...

You didn't provide any DDL for table named "rptpxa2".

What are the datatypes of columns  rptpxa2.PNO and  rptpxa2.DCode ?

Service Pack 4 introduced this:

http://support.microsoft.com/default.aspx/kb/906344

Why purchase a SQL RDBMS with a query optimizer that does all this for you, if all you're going to do is try to force the joins yourself ?

Wrapping certain...

Let's try that again. Too much eggnog ...

My derived table was wrong, should be the MAX() not MIN() for most recent.

Also, as pointed out, I neglected the time portion of...

Join to a derived table that generates the most recent date:

SELECT     OeOrders.SourceID, OeOrders.OrderID, OeOrders.CategoryName, OeOrders.ProviderID, OeOrders.ProviderName, OeOrders.ProviderOrder,

                      OeOrders.Meal, OeOrders.OrderDateTime, OeOrders.OrderNumber, OeOrders.OrderedProcedure, OeOrders.OrderedProcedureName, OeOrders.Priority,

                      OeOrders.ProcedureNumber, OeOrders.Quantity, OeOrders.ServiceDateTime, OeOrders.Status, OeOrders.VisitID,...

>>I need to have SQL Mail workign for this to happen, don't I? 

Instead of relying on setting up the "Notification" tab, we use the "Advanced" tab under job steps.

For the...

Consider switching to this:

http://www.sqldev.net/xp/xpsmtp.htm

Replace Send Mail tasks with Exec SQL tasks that call the xp_smtp_sendmail stored proc. SQL Mail with Outlook in Sql 2000 is horrible. We finally threw...

Even better would be to use this:

http://www.sqldev.net/xp/xpsmtp.htm

We have switched over to this due to continual headaches with xp_sendmail and Outlook. Any DTS task that needs to send email now...