September 15, 2009 at 12:14 pm
This is complicated view that also contains a union. I checked that the fields all matched, but I am guessing I have a mismatch somewhere. I get different errors, but they follow the same pattern. My question is: How can I identify which record, or at least which column(s) is in error ?
select applicationID,applicationTaskID,
calledNumber,origCalledNumber,
contactType,destinationDN,
destinationID,destinationType,originatorDN,originatorID,originatorType,
ResourceID,SessionID,SessionSeqNum,StartTime,resourceGroupID,
extension,loginID,AGTCALL,AgtEndTime,AgtStartTime,
startHour,callGroup,AGENTNAME,DOW,WeekNum,CSQ,
VMEXT,callWrapUp,STARTDATE,
AgtCallCount,talkTime
from CONTACTCALLDETv1
where STARTDATE between 20090101 and 20090415
The error is
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Other' to a column of data type tinyint.
September 15, 2009 at 2:40 pm
September 15, 2009 at 3:30 pm
I'd guess that the problem is within the definition of your view rather than the STARTDATE column in the SELECT clause.
Reason: The values provided for STARTDATE exceed the tinyint data type as well, so this probably isn't the root cause.
You've mentioned that the view contains a union. It looks like your first SELECT statement returns tinyint values for a specific column whereas one of the following SELECT's in your UNION clause will have character values such as 'Other'.
Check your second SELECT statement from the view definition if there's a column containing the value 'Other'. If that's the case, find the corresponding column in the first SELECT statement. I bet it'll return all tinyint's. If that's the case, use CAST(column as char(?)/varchar(?)) in the first SELECT statement.
When you use UNION, the data type of the column is determined by the data type of the first SELECT statement.
September 15, 2009 at 4:02 pm
lmu92 (9/15/2009)
I'd guess that the problem is within the definition of your view rather than the STARTDATE column in the SELECT clause.Reason: The values provided for STARTDATE exceed the tinyint data type as well, so this probably isn't the root cause.
I stand corrected. Good catch sir.
If all else fails, you can always revert to the age old method of commenting all the fields out then adding them back in one at a time until you figure out which field is causing the issue.
September 15, 2009 at 4:17 pm
select * INTO Temp_ CONTACTCALLDETv1
from CONTACTCALLDETv1
where 1=0
Then check the new table columns, find the one of tinyint type and find which one of the sources of the data for this column is a varchar column.
_____________
Code for TallyGenerator
September 15, 2009 at 4:48 pm
Sergiy (9/15/2009)
select * INTO Temp_ CONTACTCALLDETv1
from CONTACTCALLDETv1
where 1=0
Then check the new table columns, find the one of tinyint type and find which one of the sources of the data for this column is a varchar column.
Nice one, Sergiy!!
Didn't know that it's possible to force SQL Server to ignore conversion issues and still providing column type definition.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply