I can see where the OP is coming from.

A solution like FreshBooks would be an ideal example of what the OP wants.

Multiple clients sign up to the companies invoicing system,...

Answer

+1, learned something new with that, didn't even know that function existed.

Just check sysmail_faileditems on its own or sysmail_allitems to see if any recorded any errors.

How I have got around the problem in the past.

;with cte as

(select

convert(varchar,run_date) as rundate,

right('000000'+convert(varchar,run_time),6) as runtime

from msdb.dbo.sysjobhistory

)

select convert(datetime,rundate+' '+left(runtime,2)+':'+left(right(runtime,4),2)+':'+right(runtime,2))

from cte

Sorry I had PeriodID in mind when writing the script and that it was an auto-increment number with no gaps, you are correct that with gaps the query doesn't work...

So its down to cross database issues.

Are both DB's owned by the same account?

Log files are sequential, thus only one log file is used at a time.

The only time I would recommend adding a second LDF file is when the first is full...

Was there anything in the sysmail_event_log table detailing the failures?

Can you run SSMS as the proxy user and try running the select?

Are there no deny's at all on MSDB.dbo.sysjobs?

The proxy account inst part of any groups which have deny...

Check for an explicit deny right on the sysjobs table

Are you bothered about the AD group modifying (alter, drop, restore) any of the DB's on the instance?

If not give them dbcreator, this will allow them to create, alter, drop,...

Visual

Personally I would still make the change via config manager. Just for my own piece of mind knowing its been done that way.

Not saying it cant be done via...

heres my quick take on it

create table #test (testdata varchar(50))

insert into #test values

('Consumer|3860701|1442421'),

('something|12345|67890')

;with cte as

(select * from #test

cross apply dbo.DelimitedSplit8K(testdata,'|')

)

select t1.item, t2.item from cte t1

inner join cte t2

on t1.testdata =...