I'll just give a shot of some short answers (the thread contained some pointers to deeper information)

1) It is 'costly' mainly because you're doing two things in one go -...

Before you go down that route, go here http://www.sommarskog.se/dynamic_sql.html and read (everything)

This (dynamic SQL) isn't as easy or innocent as it may seem at first glance. Be fully aware...

AFAIK, current availability is for MSDN subscribers only.

/Kenneth

Indeed...?

select charindex('|', '80339:dinesh badgujar|80339:dinesh badgujar')

-----------

22

(1 row(s) affected)

Doesn't look too deterministic to me...

/Kenneth

Ok... First, I recommend to rewrite this legacy syntax as ANSI instead.

Change

FROM   two..RM00101 RM1 ,two..RM00102  RM2

where  RM1.CUSTNMBR =* RM2.CUSTNMBR

to:

FROM   two..RM00101 RM1

RIGHT JOIN...

You have to specify the characters denoting each field and rows.

What are they in your file? These are the ones you should use. Also, is the file native and not...

Can you please post the definition for the view?

(my guess is that there is an outer join in there..?)

/Kenneth

Have you looked at CHARINDEX ?

/Kenneth

Can't quite read what your problem is - mailing or getting the filelist into the mail..

If it's the latter, just run the query from osql with -o pointing to a...

Basically, you want triggers to be as lean as possible, else they are in the way, and you're in great risk of getting trouble with the transaction, may create concurrency...

Cursors, while loops, temptables... They are all 'bad'

If you mean by your question "how do I think to avoid cursors", the answer is...

I wouldn't recommend sending email from within a trigger. It will bite you eventually.

It's better to have the trigger log the mails to be sent in a queue table, then...

Why make it harder than it needs to be?

-- 2003-03-11 / Kenneth Wilhelmsson

-- This is a sample matrix showing how to find out dates of month boundries

--...

The beta is now available for MSDN subscribers.

/Kenneth

If you're interested in the reasons as to why MS has implemented UDFs in a way that not allows nondeterministic functions to be included, Books On Line is a good...