May 26, 2006 at 6:48 pm
I don't understand why it would differ between servers, but looking at the code, I notice that this field:
c.clientid AS CovastID,
is inserted into this column:
CovastID VARCHAR(36),
but joined using this predicate:
ON LEFT(c.clientid,8)+'-'+SUBSTRING(c.clientid,9,4)+'-'+SUBSTRING(c.clientid,13,4)
+'-'+SUBSTRING(c.clientid,17,4)+'-'+SUBSTRING(c.clientid,21,12)
= (f3.[ServiceInstance/ActivityID])
So the clientid is expected to be 36 chars for the purposes of inserting into the table, but to be 32 chars (i.e. missing the four hyphens) for the purposes of doing the join. It strikes me that manipulating strings like that is a good place to start looking for errors, and it would be consistent with your error msg if something was in the wrong format.
Even if that sn't the cause in this case, if you need to convert between formats, you should consider putting the code you use to handle the conversion in either direction into a pair of functions so that you can easily see what has been converted to what, and so that you can reuse the same code everywhere - maximising consistency by minimising redundancy.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
May 28, 2006 at 6:04 pm
It's really smart to replace 16-bytes uniqueidentifier value with 39 bytes varchar(36) value bringing all those problems with debugging complex queries, potential collation issues, etc.
_____________
Code for TallyGenerator
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply