>>This field can contain a 0. 

What does a zero mean in situation 1 ? NULL date ? Some other constnt past or future-dated date ?

Declare @NumDate1 as numeric(7,0),

        @NumDate2 as...

>>The good news is the rowset returned isn't out of order any more.

I would be concerned about the vendor's application quality if rowset order has a dependency on query plan.

It...

>>for Reporting Services Report Builder which only allows tables and views.

Huh ? Since when did RS prevent use of stored procs ?

>>I'm looking for the trigger to update the ChangeUser, ChangeDate, or both. 

That changes things.

 UPDATE TRASH 

 SET ChangeDate = 

    CASE WHEN d.ChangeDate <> i.ChangeDate THEN i.ChangeDate

            ELSE GETDATE()

    END,

    CASE WHEN d.ChangeUser <> i.ChangeUser THEN...

Use 2 datetime variables, and structure it as:

Declare @StartDate As Datetime

Declare @EndDate As DateTime

Select @StartDate = cast(convert(varchar,getdate(),112) As DateTime)

Select @EndDate = DateAdd(d, 1, @StartDate)

SELECT

...

WHERE CreateDate >= @StartDate

AND     CreateDate < @EndDate

Which version of SQL Server ? SQL 2005 has ranking functions which can supply a row number.

In SQL 2000, you'd need to first select into a temporary table with an...

So, the UPDATE may already have set the ChangeDate and ChangeUser column values ? And you only want the trigger code to fire if this *didn't* happen, correct ?

In this...

Don't make me fire up my old BTrieve workstation ...

>>The application provides 2 variables:  @ChangeDate, @ChangeUser

The application is VB.Net. Those are T-SQL variables. How does the application "provide" them ? As parameters to a stored proc ? And if so, what...

Why limit it to a SQL 2000 solution ?

Create Table #t ( RefNo Varchar(20) not null)

insert #t

select 'ABC-0' union all

select 'ABC-1' union all

select 'XYZ-0' union all

select 'XYZ-1' union all

select 'XYZ-02'...

>> It's SQL Server Standard, so I don't know what good the /3GB switch would do

It wouldn't do any good. But you hadn't mentioned Standard Edition or total RAM,...

>>the new one Windows 2003 Server Standard.  The new server has the same RAM and # of processors (2),

How much RAM ?

Which edition of SQL Server on each server...

Use dynamic SQL, so that the variable's value is passed to the linked server:

Declare @sql varchar(4000)

Select @sql = 'SELECT * FROM [Link].[DB}.[Owner].[TableName] Where SomeColumn = ''' + LocalVariable + '''...

Use RaisError with a zero error code instead of PRINT.

Do you really need the loop and the continuous feedback ? Can't you just use WAITFOR ?

"inserted" is a virtual table containing the row or rows being inserted. You can't Update it. You need to update dbo.sdata and join it to inserted on the primary key...