Many thanks Drew for the suggestion and many thanks Steve - that was exactly what I needed.

You guys are stars!

Solved...

Needed

ALTER AUTHORIZATION ON DATABASE::[DB_Test1] TO [sa]

GO

Code here...

CREATE MESSAGE TYPE ReceivedOrders
AUTHORIZATION dbo
VALIDATION = NONE
GO

CREATE CONTRACT postmessages (ReceivedOrders SENT BY ANY)
GO

CREATE QUEUE OrderQueue WITH STATUS = ON, RETENTION = OFF
GO

CREATE SERVICE OrderService
AUTHORIZATION dbo 
ON...

• This reply was modified 2 years, 3 months ago by  Charlottecb.

Nice Jacob. thanks for the response.

That did it - seems very obvious now and I feel a bit foolish!

Many thanks for taking the time to respond.

• This reply was modified 4 years, 1 month ago by  Charlottecb.

Joe, chill out man - no one died, it's just a bit of code... It's not even a real table or data - I only created it to highlight my...

Drew, that's a great solution. Many thanks.

Perfect Phil. Many, many thanks for your help.

Phil, you are a legend!

One further small request for help... If I wanted to make it so that if the last occurence was not missed, the record was...

That's a good idea. Thanks

That's great guys - thanks for all your help on this.

Many many thanks. 😀

That works well Chris,

The only bit which doesn't work is the padding of the number to two digits with zero. I replaced your RIGHT('00' + rn, 2)

With

REPLICATE('0', 2 - LEN(rn))...

ChrisM@Work (12/22/2015)

---

Something along these lines should do the trick, however I'd recommend you don't do it. If the number you are storing with the date of the visit is of...

John Mitchell-245523 (12/22/2015)

---

So if the ExpectedDate changes, for example, from 2015-12-01 to 2016-01-11 then you could find yourselves renumbering the visits for two years - do I understand that correctly?...

Brilliant Sean - Many thanks for this. 😀