Nope, the joins are correctly...

Nice bit of investigative work there, Tim. Who would have guessed it from the error message?

Cheers

ChrisM

I'm sorry Tim. Better luck tomorrow.

Aha! I think you want to do it this way round...

UPDATE i SET catalogue_number = a.nwCode

FROM [sage].cleaningnet.dbo.ImportTimStock i

INNER JOIN ProductsWaitingApproval a

ON a.sku COLLATE SQL_Latin1_General_CP1_CI_AI = i.product --COLLATE Latin1_General_CI_AS...

Nice work, Srdjan! And...good point as always, Matt.

It's not often that this technique actually speeds up a process; it's more useful as a tool for analysing and simplifying long, overcomplex...

Grrr!

Have you recorded the collation cast of a.sku and i.product?

Hi Tim

This should do it...

UPDATE i SET catalogue_number = a.sku

FROM [sage].cleaningnet.dbo.ImportTimStock i

INNER JOIN ProductsWaitingApproval a

ON a.sku = i.product COLLATE Latin1_General_CI_AS

INNER JOIN [tmCleaningNet].dbo.products b ...

Thanks for the prompt reply Tim. I've seen this error before doing the same i.e. updating a table on one server from another linked server, where the collation is different....

Try this, Tim. If the SELECT works ok, then comment the SELECT and uncomment the UPDATE.

--UPDATE i SET catalogue_number = a.sku COLLATE Latin1_General_CI_AS

SELECT i.ImportTimStock, a.sku COLLATE Latin1_General_CI_AS

FROM [sage].cleaningnet.dbo.ImportTimStock i

INNER JOIN...

eddy (3/3/2008)

---

Hi Guys,

I have managed to come to a solution in the following way,

Can any expert please advice me if this is the right way to do this,

SELECT ...

No, not yet, but playing with the code, it seems that this whole query could be "tamed" considerably...

You're welcome Ryan. If you're not already familiar with this type of structure - the inner SELECT - then read up on derived tables. The generally accepted term for the...

David Jackson (2/29/2008)

---

Yes Chris, that's exactly what I was chasing down. 🙂

I mean I knew you could do this without a correlated sub-query, but I was struggling...

The link relates specifically to Excel and is of little or no use in this context.

Hi David

I would use something like this 'cos it's quick...

SELECT X.h_id, MAX(X.l_id), X.ton

FROM #t1 X

INNER JOIN (SELECT h_id, MAX(ton) AS MAXton FROM #t1 GROUP BY h_id) dt

ON dt.h_id...