May 26, 2006 at 7:46 am
This more of a "How come question"
I have a select that runs against a test server and what will become the production server. One particular query on the test server runs fine but on the production server I get a
"Server: Msg 8169, Level 16, State 2, Line 71 Syntax error converting from a character string to uniqueidentifier."
in the following statement:
DECLARE @output TABLE ( MessageID VARCHAR(40),
ActivityID VARCHAR(40),
ReceiverID VARCHAR(30),
ControlNumber VARCHAR(15),
SetNumber VARCHAR(15),
DocNumber VARCHAR(15),
TrackedItem VARCHAR(15),
TrackedType VARCHAR(15),
CovastID VARCHAR(36),
SentTime DATETIME)
DECLARE @output10 TABLE (ReceiverID VARCHAR(30),
ControlNumber VARCHAR(15),
SetNumber VARCHAR(15),
DocNumber VARCHAR(15),
InvNumber VARCHAR(15),
CovastID VARCHAR(36),
SentTime DATETIME)
DECLARE @output14 TABLE (ReceiverID VARCHAR(30),
ControlNumber VARCHAR(15),
SetNumber VARCHAR(15),
DocNumber VARCHAR(15),
OrderNumber VARCHAR(15),
Ref_Number VARCHAR(15),
Status VARCHAR(15),
Stp_Number VARCHAR(15),
CovastID VARCHAR(36),
SentTime DATETIME)
DECLARE @startdate DATETIME,
@enddate DATETIME
SET @startdate = '05/01/06'
SET @enddate = '05/30/06'
INSERT INTO @output (MessageID, ActivityID, ReceiverID, ControlNumber, SetNumber, DocNumber, TrackedItem, TrackedType, CovastID, SentTime)
SELECT DISTINCT f1.[MessageInstance/InstanceID],
f3.[ServiceInstance/ActivityID],
c.rid AS ReceiverID,
c.icr AS ControlNumber,
c.mrn AS SetNumber,
c.msgout AS DocNumber,
CAST(m.vtValue AS VARCHAR(15)) AS TrackedItem,
mf.strFieldName AS TrackedType,
c.clientid AS CovastID,
c.tmte AS SentTime
FROM BizTalkDTADb.dbo.dta_MessageFieldValues m
INNER JOIN BizTalkDTADb.dbo.dta_MessageFields mf ON mf.nMessageFieldsId = m.nMessageFieldsId
LEFT JOIN dtav_FindMessageFacts f1 ON [MessageInstance/InstanceID] = m.uidMessageInstanceId
LEFT JOIN dtav_FindMessageFacts f2 ON f2.[ServiceInstance/InstanceID] = f1.[ServiceInstance/ActivityID]
LEFT JOIN dtav_FindMessageFacts f3 ON f3.[MessageInstance/InstanceID] = f2.[MessageInstance/InstanceID]
INNER JOIN CovastDB.dbo.audout c 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])
INNER JOIN CovastDB.dbo.v_messages_out cm ON cm.message_id = c.clientid
WHERE c.tmte BETWEEN @startdate AND @enddate
AND cm.status_text = 'SENT'
--select * from @output order by ControlNumber, SetNumber
--Insert first tracked item (InvNumber) into @output10 table
INSERT INTO @output10 (ReceiverID, ControlNumber, SetNumber, DocNumber, InvNumber, CovastID, SentTime)
SELECT o.ReceiverID, o.ControlNumber, o.SetNumber, o.DocNumber, o.TrackedItem, o.CovastID, o.SentTime
FROM @output o
WHERE o.TrackedType = 'InvNumber'
INSERT INTO @output14 (ReceiverID, ControlNumber, SetNumber, DocNumber, OrderNumber, CovastID, SentTime)
SELECT o.ReceiverID, o.ControlNumber, o.SetNumber, o.DocNumber, o.TrackedItem, o.CovastID, o.SentTime
FROM @output o
WHERE o.TrackedType = 'OrderNumber'
UPDATE @output14 SET Ref_Number = o.TrackedItem
FROM @output214 r, @output o
WHERE r.ControlNumber = o.ControlNumber
AND r.SetNumber = o.SetNumber
AND r.ReceiverID = o.ReceiverID
AND o.TrackedType = 'Ref_Number'
UPDATE @output214 SET Status = o.TrackedItem
FROM @output14 r, @output o
WHERE r.ControlNumber = o.ControlNumber
AND r.SetNumber = o.SetNumber
AND r.ReceiverID = o.ReceiverID
AND o.TrackedType = 'Status'
UPDATE @output14 SET Stp_Number = o.TrackedItem
FROM @output14 r, @output o
WHERE r.ControlNumber = o.ControlNumber
AND r.SetNumber = o.SetNumber
AND r.ReceiverID = o.ReceiverID
AND o.TrackedType = 'Stp_Number'
select * from @output10 order by DocNumber
select * from @output14 order by DocNumber
Anyone have an idea why the code would need changed between these servers to cast as varchar fo rthis to work?
Thanks
Scott Skeen
"If I don't have an answer I'll make one up for you on the spot"
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply