RGR'us,

I would rewrite your TSQL like this:

if  @var_st_lev - @var_stt_amnt < 0 Then

    Set @var_NSL= @var_st_lev

else

    Set @var_NSL = @var_st_lev - @var_stt_amnt

Reason, each set is a single statement and...

Try this:

CREATE

FUNCTION dbo.NewStockLevel

(

@var_st_lev

int,

@var_stt_amnt

int

)

Is your database also set to compatibility mode 90 (2005)?  Can you set a test database to compatibility mode 80 (2000) on a SQL 2005 server and see what happens...

I'm curious, where in the original query did you have to add the hint to make it work as fast in 2005 as it was in 2000?

About the depreciated part of older style joins may be true, however, that doesn't mean you shouldn't start look at rewriting queries that use the old style format.  The ANSI...

Glad to help.  I could probably get the query using the CTE to work, but not having any data to test, I did my best.  It was looking at it...

Not sure, but have you checked to see if you have a failed drive on the raid 5 on the slower system?

Always good to look at what one writes, as I have another solution that may also work:

SELECT

    n.clientid,

    n.divid,

    n.contactid,

    n.rolecd,

    n.firstnm,

    n.lastnm

FROM

    csms.dbo.client_contact o

    RIGHT OUTER JOIN csms.csms_mts.tmp_client_contact n

       ...

My bad.  I think my previous post had a logic error (if someone wants to verify, I'd apreciate it).  I think this is what I was trying to come up...

Hate to burst your developers bubble, but they are going to have to rewrite this query eventually.  In its current format, it contains an old style inner join.  This has...

Greg,

You are right, so I made a change to mine, but I'm sure some of the other solutions may be just as good:

create table dbo.Test1 (refNo varchar(10))

insert into dbo.Test1 (refNo)...

If you are running SQL Server 2000, change the stored procedure to a scalar User-Defined function.

hth,

Lynn

Actually, what I think what is being looked for is this:

create table dbo.Test1 (refNo varchar(10))

insert into dbo.Test1 (refNo) values ('ABC-0')

insert into dbo.Test1 (refNo) values ('ABC-1')

insert into dbo.Test1 (refNo) values ('XYZ-0')

insert...

Looking at BOL, dbcc shrinkfile truncate_only, releases unused space in the log file to the operating system, shrinking the physical size of the transaction log.

You may want to look at...

Are you talking about the physical size of the log files, or the fact that inactive log entries are not truncated during the backup?  Full and Differential backups do not...