That's the question to the customer, not to this forum.

There is no point to discuss an implementation if it's not clear how customer wants to see it.

It only confirms that such a lame database design is not good enough for you.

What you need is a clusted index on [DW_PROD].[dbo].[SPF053_SALES] (dwyear, dwweek, dw_dow, dwstore)

Then your query will return results instantly.

But schedule index creation for weekend: on a table with 900 mil...

Check for triggers on the table.

IDENTITY property on ID column suggests otherwise.

Can you explain this sequence?

18271999 2010-08-03 07:49:00.000

18271988 2010-08-03 07:51:00.000

And what should happen after 08:05:00.000?

Should the next record be after 08:10:00.000?

Or should it be the first record of the next 5...

Answer on both questions - yes.

This should be more effective:

select main.sno, main.name, L.address

from main

inner join (

select sno, max(loca.address) address

...

You might also wish to consider using osql or isql for exporting files with headers.

Check BOL for details and syntax.

DISTINCT is not a silver bullet.

It's quite opposite.

I'd suggest not to use it in production code at all. Ever.

DISTINCT/GROUP BY is applied after all joins and WHERE clause.

If you have...

Can you post full text of procedure f_StockItemDepoQuantitySelect?

Or at least first 70 rows, where the error happens.

Remove trigger "using inserted". You don't need it.

Use "using deleted" only.

Put

SELECT * FROM inserted

SELECT * FROM deleted

into the trigger code.

Those table exist only while trigger is running and...

I believe both solutions are identical and work perfectly for the request.

Only issue I can see here is that Ken@Work did not bother to read and understand either of them.

And though you cannot declare a variable longer than 8000 characters in SQL2000 you still can declare a SP parameter with type "text" ot "ntext".

Then you can pass the whole...

Did you check the flow?

What's gonna happen if the first statement returns an error?

Your code will rollback transction and continue - then what?

This way it should work:

SET NOCOUNT ON;

DECLARE @ErrNo...