Error 8623

Hi,
After some changes in my database when I try to execute following statement:
delete from dbo.PrzychodH
i received this error:
Server: Msg 8623, Level 16, State 1, Line 1
Internal Query Processor Error:
The query processor could not produce a query plan.
Contact your primary support provider for more information.
When I dropped all changes this error does NOT disappeared.
Sql2000 SP4 on WinServ2003 SP1.
Is this SQL2k SP4 new bug?
MarekMarek,
Not sure what you're doing but might take a look at:
http://support.microsoft.com/search/default.aspx?spid=2852&query=not+produce+a+query+plan&catalog=LCID%3D1033&pwt=false&title=false&kt=ALL&mdt=0&comm=1&ast=1&ast=2&ast=3&mode=a
There are quite a few references to the error code in here.
Also, might perform a DBCC CHECKTABLE on przychodH just to rule that out as
a possible cause.
HTH
Jerry
"Marek" <marlie@.wp.pl> wrote in message
news:OcbJYr2wFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> After some changes in my database when I try to execute following
> statement:
>
> delete from dbo.PrzychodH
>
> i received this error:
>
> Server: Msg 8623, Level 16, State 1, Line 1
> Internal Query Processor Error:
> The query processor could not produce a query plan.
> Contact your primary support provider for more information.
>
> When I dropped all changes this error does NOT disappeared.
>
> Sql2000 SP4 on WinServ2003 SP1.
>
> Is this SQL2k SP4 new bug?
>
> Marek
>
>

error 8152 in sql update statement

hi all.
I am doing a simple sql update and getting below error:
Error Messages:
Server: Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
I verified that the values specified are within the column length limits of
the table. I tried to searche for troubleshooting guidelines and couldnt
find any.
Please assist. Thanks.
Please post your DDL plus the statement that is giving you trouble.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"KristinaY" <KristinaY@.discussions.microsoft.com> wrote in message
news:56B41165-C240-4111-9914-04672C4FA8BF@.microsoft.com...
hi all.
I am doing a simple sql update and getting below error:
Error Messages:
Server: Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
I verified that the values specified are within the column length limits of
the table. I tried to searche for troubleshooting guidelines and couldnt
find any.
Please assist. Thanks.

error 8152 in sql update statement

hi all.
I am doing a simple sql update and getting below error:
Error Messages:
Server: Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
I verified that the values specified are within the column length limits of
the table. I tried to searche for troubleshooting guidelines and couldnt
find any.
Please assist. Thanks.Please post your DDL plus the statement that is giving you trouble.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"KristinaY" <KristinaY@.discussions.microsoft.com> wrote in message
news:56B41165-C240-4111-9914-04672C4FA8BF@.microsoft.com...
hi all.
I am doing a simple sql update and getting below error:
Error Messages:
Server: Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
I verified that the values specified are within the column length limits of
the table. I tried to searche for troubleshooting guidelines and couldnt
find any.
Please assist. Thanks.sql

error 8152 in sql update statement

hi all.
I am doing a simple sql update and getting below error:
Error Messages:
Server: Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
I verified that the values specified are within the column length limits of
the table. I tried to searche for troubleshooting guidelines and couldnt
find any.
Please assist. Thanks.Please post your DDL plus the statement that is giving you trouble.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"KristinaY" <KristinaY@.discussions.microsoft.com> wrote in message
news:56B41165-C240-4111-9914-04672C4FA8BF@.microsoft.com...
hi all.
I am doing a simple sql update and getting below error:
Error Messages:
Server: Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
I verified that the values specified are within the column length limits of
the table. I tried to searche for troubleshooting guidelines and couldnt
find any.
Please assist. Thanks.

Error 8152

I'm trying to add a record to a table and am getting Error 8152:
String or binary data would be truncated.
Here is my statement written with VBA in an Access project.
Both datatypes are nvarchar
Private Sub cmdCreateJob_Click()
Dim strJcn As String
Dim strParcTag As String
Dim strSql As String
strJcn = 999999
strParcTag = Me.cboEquipID.Column(0)
strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
& " VALUES ('strJcn','strParcTag')"
DoCmd.RunSQL strSql
End Sub
Can anyone see what I'm doing wrong? Thanks.Can you post your DDL for table ESR?
Perayu
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:E21FB5F9-31A7-4FA4-89AD-49F37A3CA0E7@.microsoft.com...
> I'm trying to add a record to a table and am getting Error 8152:
> String or binary data would be truncated.
> Here is my statement written with VBA in an Access project.
> Both datatypes are nvarchar
> Private Sub cmdCreateJob_Click()
> Dim strJcn As String
> Dim strParcTag As String
> Dim strSql As String
> strJcn = 999999
> strParcTag = Me.cboEquipID.Column(0)
> strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
> & " VALUES ('strJcn','strParcTag')"
> DoCmd.RunSQL strSql
> End Sub
> Can anyone see what I'm doing wrong? Thanks.
>
>
>|||Check the ESR_JCN column length and strJcn string length
and ESR_PARCTAG column length and strParcTag string length
The data length must be longer than column length
Thanks & Rate the Postings.
-Ravi-
"AkAlan" wrote:

> I'm trying to add a record to a table and am getting Error 8152:
> String or binary data would be truncated.
> Here is my statement written with VBA in an Access project.
> Both datatypes are nvarchar
> Private Sub cmdCreateJob_Click()
> Dim strJcn As String
> Dim strParcTag As String
> Dim strSql As String
> strJcn = 999999
> strParcTag = Me.cboEquipID.Column(0)
> strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
> & " VALUES ('strJcn','strParcTag')"
> DoCmd.RunSQL strSql
> End Sub
> Can anyone see what I'm doing wrong? Thanks.
>
>
>|||It looks like this VB(?) code is wrong: " VALUES ('strJcn','strParcTag')".
You are inserting 'strJcn', not the value of strJcn, to ESR_JCN column. You
need to try something like " VALUES ('" + integerToString(strJcn) + "', '"
+ strParcTag+ "')". But I am not sure what the syntact is for concatinate
the single quots in VB.
Perayu
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:E21FB5F9-31A7-4FA4-89AD-49F37A3CA0E7@.microsoft.com...
> I'm trying to add a record to a table and am getting Error 8152:
> String or binary data would be truncated.
> Here is my statement written with VBA in an Access project.
> Both datatypes are nvarchar
> Private Sub cmdCreateJob_Click()
> Dim strJcn As String
> Dim strParcTag As String
> Dim strSql As String
> strJcn = 999999
> strParcTag = Me.cboEquipID.Column(0)
> strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
> & " VALUES ('strJcn','strParcTag')"
> DoCmd.RunSQL strSql
> End Sub
> Can anyone see what I'm doing wrong? Thanks.
>
>
>|||Ok I got everything to work up until I add the datOpenDate field. It only
puts 12:00 AM in the record when I put 1 mar 06 in the form. Here is what I
have so far:
I have tried formatting the datefield but no go. Thanks,
strJcn = "'" & NextJCN() & "'"
strParcTag = "'" & Me.cboEquipID.Column(0) & "'"
strPerfWc = "'" & Me.cboPWC & "'"
strRptby = "'" & Me.cboReportedBy.Column(0) & "'"
strDisc = "'" & Me.txtDisc & "'"
datOpenDate = “’” & Me.txtOpenDate & “’”
strSQL = "INSERT INTO ESR
(ESR_JCN,ESR_PARCTAG,ESR_DISC,ESR_RPTBY,
ESR_OPEN_DATE)" _
& " VALUES (" & strJcn & "," & strParcTag & "," & strDisc & "" _
& "," & strRptby & ", " & datOpenDate & " )"
DoCmd.RunSQL strSQL
"Perayu" wrote:

> It looks like this VB(?) code is wrong: " VALUES ('strJcn','strParcTag')"
.
> You are inserting 'strJcn', not the value of strJcn, to ESR_JCN column. Yo
u
> need to try something like " VALUES ('" + integerToString(strJcn) + "', '
"
> + strParcTag+ "')". But I am not sure what the syntact is for concatinate
> the single quots in VB.
> Perayu
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:E21FB5F9-31A7-4FA4-89AD-49F37A3CA0E7@.microsoft.com...
>
>|||It looks like you have typo here: datOpenDate = "'" & Me.txtOpenDate & "'".
"'" should be "'".
Perayu
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:2ABA6908-E32F-4C09-9BC0-BCF089F15C0F@.microsoft.com...
> Ok I got everything to work up until I add the datOpenDate field. It only
> puts 12:00 AM in the record when I put 1 mar 06 in the form. Here is what
> I
> have so far:
> I have tried formatting the datefield but no go. Thanks,
> strJcn = "'" & NextJCN() & "'"
> strParcTag = "'" & Me.cboEquipID.Column(0) & "'"
> strPerfWc = "'" & Me.cboPWC & "'"
> strRptby = "'" & Me.cboReportedBy.Column(0) & "'"
> strDisc = "'" & Me.txtDisc & "'"
> datOpenDate = "'" & Me.txtOpenDate & "'"
> strSQL = "INSERT INTO ESR
> (ESR_JCN,ESR_PARCTAG,ESR_DISC,ESR_RPTBY,
ESR_OPEN_DATE)" _
> & " VALUES (" & strJcn & "," & strParcTag & "," & strDisc & "" _
> & "," & strRptby & ", " & datOpenDate & " )"
> DoCmd.RunSQL strSQL
>
> "Perayu" wrote:
>

Error 8114 on big query - how to hone it down?

I have a big select into statement I'm running from QA (on the same machine
where the DB is). I've used it in the past, and it worked then. It's with
the NBI (National Bridge Inventory), trying to convert some data types, so
there are over 600,000 records with 120 or so fields each. It runs for
awhile, and I don't what what record or field produces the problem. How can
I get more info?
Every field in the source table is NVARCHAR.
The message after 5 minutes or so is:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric
TIA -
Mark
The statement:
select
identity(int,1,1) as ID,
Item1 as StateCode_1,
Item2 as HighwayDistrict_2,
Item3 as CountyCode_3,
Item4 as PlaceCode_4,
Item5a as InvenRouteRecordType_5a,
Item5b as InvenRouteSigningPrefix_5b,
Item5c as InvenRouteLevelService_5c,
item5d as InvenRouteNumber_5d,
Item5e as InvenRouteDirSuffix_5e,
Item6a as FeaturesIntersected_6a,
Item7 as FacilityCarried_7,
Item8 as StructureNumber_8,
Item9 as LocationNarrative_9,
-- 99.99 is a possible vert clearance (over 30 meters), don't care
case PATINDEX('%[^0-9.]%',Item10)
when 0 then cast(Item10 as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as InvenRouteMinVertClearance_10,
case PATINDEX('%[^0-9.]%',Item11)
when 0 then cast(Item11 as decimal(12,3))/1000
else cast(0 as decimal(12,3))
end as LrsKilometerPoint_11,
Item12 as IsOnBaseHighwayNetwork_12,
Item13a as LrsInvenRoute_13a,
Item13b as LrsSubroute_13b,
Item16 as Latitude_16,
Item17 as Longitude_17,
-- detours over 199 km are coded as 199
case PATINDEX('%[^0-9]%',Item19)
when 0 then cast(Item19 as int)
else cast(0 as int)
end as BypassDetourKilometers_19,
Item20 as TollCode_20,
Item21 as MaintRespCode_21,
Item22 as MaintRespOwnerCode_22,
Item26 as InvenRouteFunctionClass_26,
Item27 as YearBuilt_27,
case PATINDEX('%[^0-9]%',Item28a)
when 0 then cast(Item28a as int)
else cast(0 as int)
end as LanesOn_28a,
case PATINDEX('%[^0-9]%',Item28b)
when 0 then cast(Item28b as int)
else cast(0 as int)
end as LanesUnder_28b,
case PATINDEX('%[^0-9]%',Item29)
when 0 then cast(Item29 as int)
else cast(0 as int)
end as AvgDailyTraffic_29,
Item30 as AvgDailyTrafficYear_30,
Item31 as DesignLoadCode_31,
case PATINDEX('%[^0-9.]%',Item32)
when 0 then cast(Item32 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as ApproachRoadwayWidth_32,
Item33 as MedianExistOpenClosed_33,
case PATINDEX('%[^0-9]%',Item34)
when 0 then cast(Item34 as int)
else cast(0 as int)
end as RoadwayPierSkewDegrees_34,
Item35 as StructureIsFlared_35,
Item36a as TsRailings_36a,
Item36b as TsTransitions_36b,
Item36c as TsApprGuardrail_36c,
item36d as TsApprGuardrailEnds_36d,
Item37 as HistoricSigCode_37,
Item38 as NavControlCode_38,
case PATINDEX('%[^0-9.]%',Item39)
when 0 then cast(Item39 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as NavVertClearance_39,
case PATINDEX('%[^0-9.]%',Item40)
when 0 then cast(Item40 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as NavHorzClearance_40,
Item41 as OpenPostedClosedCode_41,
Item42a as ServiceTypeOnCode_42a,
item42b as ServiceTypeUnderCode_42b,
Item43a as StructureMaterialTypeCode_43a,
Item43b as StructureTypeCode_43b,
Item44a as ApproachMaterialTypeCode_44a,
Item44b as ApproachStructureTypeCode_44b,
case PATINDEX('%[^0-9]%',Item45)
when 0 then cast(Item45 as int)
else cast(0 as int)
end as MainSpans_45,
case PATINDEX('%[^0-9]%',Item46)
when 0 then cast(Item46 as int)
else cast(0 as int)
end as ApproachSpans_46,
-- 100 meters or greater coded as 999, leave as is
case PATINDEX('%[^0-9.]%',Item47)
when 0 then cast(Item47 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as InvenRouteHorzClearance_47,
case PATINDEX('%[^0-9.]%',Item48)
when 0 then cast(Item48 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as MaxSpanLength_48,
case PATINDEX('%[^0-9.]%',Item49)
when 0 then cast(Item49 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as StructureLength_49,
case PATINDEX('%[^0-9.]%',Item50a)
when 0 then cast(Item50a as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbSidewalkWidthLeft_50a,
case PATINDEX('%[^0-9.]%',Item50b)
when 0 then cast(Item50b as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbSidewalkWidthRight_50b,
case PATINDEX('%[^0-9.]%',Item51)
when 0 then cast(Item51 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbToCurbRoadwayWidth_51,
case PATINDEX('%[^0-9.]%',Item52)
when 0 then cast(Item52 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as DeckWidth_52,
-- Item53: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item53)
when 0 then cast(Item53 as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as VertClearanceOverRoadway_53,
Item54a as VertClearanceTypeUnderStructure_54a,
-- Item54b: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item54b)
when 0 then cast(Item54b as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as VertClearanceUnderStructure_54b,
Item55a as LatClearanceTypeUnderStructure_55a,
-- Item55b: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item55b)
when 0 then cast(Item55b as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as LatClearanceUnderStructure_55b,
-- Item56: 999 for "open", 998 for greater than 30 meters
case
when Item56 = '999' then cast(0 as decimal(7,1))
when PATINDEX('%[^0-9.]%',Item56) = 0 then cast(Item56 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as LatClearanceUnderOnLeft_56,
Item58 as DeckConditionCode_58,
Item59 as SuperstructureConditionCode_59,
Item60 as SubstructureConditionCode_60,
Item61 as ChannelConditionCode_61,
Item62 as CulvertConditionCode_62,
Item63 as OperationRatingMethodCode_63,
-- 999 coded for "live load is insignificant in structure capacity"
case PATINDEX('%[^0-9.]%',Item64)
when 0 then cast(Item64 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as OperatingRatingMetricTons_64,
Item65 as InvenRatingLoadMethodCode_65,
-- 999 coded for "live load is insignificant in structure capacity"
case PATINDEX('%[^0-9.]%',Item66)
when 0 then cast(Item66 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as InvenRatingMetricTons_66,
Item67 as StructureEvalCode_67,
Item68 as DeckGeoEvalCode_68,
Item69 as UnderclearanceEvalCode_69,
Item70 as BridgePostingCode_70,
Item71 as WaterwayAdequacyCode_71,
item772 as ApprRoadwayAlignmentCode_72,
Item75a as WorkTypeCode_75a,
Item75b as WorkerTypeCode_75b,
case PATINDEX('%[^0-9.]%',Item76)
when 0 then cast(Item76 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as ImprovementSpan_76,
Item90 as LastInspectionMonthYear_90,
Item91 as InspectionFrequencyMonths_91,
item92a as CfiFractureCriticalDetailsCode_92a,
Item92b as CfiUnderwaterInspectionCode_92b,
Item92c as CfiOtherSpecialInspectionCode_92c,
Item93a as CfiFractureCriticalDetailsMonthYear_93a,
Item93b as CfiUnderwaterInspectionMonthYear_93b,
item93c as CfiOtherSpecialInspectionMonthYear_93c,
case PATINDEX('%[^0-9]%',Item94)
when 0 then cast(Item94 as decimal)*1000
else cast(0 as decimal)
end as BridgeImprovementCost_94,
case PATINDEX('%[^0-9]%',Item95)
when 0 then cast(Item95 as decimal)*1000
else cast(0 as decimal)
end as RoadwayImprovementCost_95,
case PATINDEX('%[^0-9]%',Item96)
when 0 then cast(Item96 as decimal)*1000
else cast(0 as decimal)
end as TotalImprovementCost_96,
Item97 as ImprovementCostEstYear_97,
Item98a as BorderBrNeighborStateCode_98a,
-- 99 means no responsibility for the structure
case
when Item98b = '99' then cast(0 as int)
when PATINDEX('%[^0-9.]%',Item98b) = 0 then cast(Item98b as int)
else cast(0 as int)
end as BorderBrPctResponsibility_98b,
-- This one could be an NBI id, or a state structure number
Item99 as BorderBrNeighborsStructureId_99,
Item100 as StrahnetHighwayDesCode_100,
Item101 as ParallelStructureDesCode_101,
Item102 as InvenRouteTrafficDirCode_102,
Item103 as TemporaryStructure_103,
Item104 as IsOnNhs_104,
Item105 as FederalLandsHighwayCode_105,
Item106 as YearReconstructed_106,
Item107 as DeckTypeCode_107,
Item108a as DeckProtectSurfaceTypeCode_108a,
Item108b as DeckProtectMembraneTypeCode_108b,
Item108c as DeckProtectTypeCode_108c,
case PATINDEX('%[^0-9]%',Item109)
when 0 then cast(Item109 as int)
else cast(0 as int)
end as AvgDailyTruckTrafficPct_109,
Item110 as IsOnNatlTruckNetwork_110,
Item111 as PierOrAbutmentProtectionCode_111,
Item112 as NbisLengthYesNo_112,
Item113 as ScourCriticalCode_113,
case PATINDEX('%[^0-9]%',Item114)
when 0 then cast(Item114 as int)
else cast(0 as int)
end as FutureAvgDailyTraffic_114,
Item115 as FutureAvgDailyTrafficYear_115,
case PATINDEX('%[^0-9.]%',Item116)
when 0 then cast(Item116 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as NavMinVertClearanceLiftBrClosed_116,
cast(' ' as varchar(22)) as CountyName,
cast(' ' as varchar(52)) as PlaceName
into STEEL
from NBI
GO
Sometimes something about posting to usenet makes it work...
There are decimal points in the PATINDEX functions. I suppose if there is
one (or two) in a source field, then it will try to convert. Got rid of
those and it works.
Mark

> -- 99.99 is a possible vert clearance (over 30 meters), don't care
> case PATINDEX('%[^0-9.]%',Item10)
> when 0 then cast(Item10 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as InvenRouteMinVertClearance_10,
> case PATINDEX('%[^0-9.]%',Item11)
> when 0 then cast(Item11 as decimal(12,3))/1000
> else cast(0 as decimal(12,3))
> end as LrsKilometerPoint_11,
etc...
|||You can add ISNUMERIC to your CASE in order to identify other invalid data,
such as multiple decimal points and empty strings:
CASE WHEN
PATINDEX('%[^0-9.]%',Item10) = 0 AND ISNUMERIC(Item10) = 1
THEN CAST(Item10 AS decimal(7,2))/100
ELSE CAST(0 AS decimal(7,2)) END
If you need to identify rows with invalid data, specify the CASE statements
in a WHERE clause.
WHERE 'Invalid' =
CASE WHEN
PATINDEX('%[^0-9.]%',Item10) = 0 AND ISNUMERIC(Item10) = 1
THEN 'Valid'
ELSE 'invalid' END
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark G. Meyers" <mmeyers[at]hydromilling.com> wrote in message
news:ep5pRIqYEHA.1652@.TK2MSFTNGP09.phx.gbl...
> I have a big select into statement I'm running from QA (on the same
machine
> where the DB is). I've used it in the past, and it worked then. It's
with
> the NBI (National Bridge Inventory), trying to convert some data types, so
> there are over 600,000 records with 120 or so fields each. It runs for
> awhile, and I don't what what record or field produces the problem. How
can
> I get more info?
> Every field in the source table is NVARCHAR.
> The message after 5 minutes or so is:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type nvarchar to numeric
> TIA -
> Mark
> The statement:
> select
> identity(int,1,1) as ID,
> Item1 as StateCode_1,
> Item2 as HighwayDistrict_2,
> Item3 as CountyCode_3,
> Item4 as PlaceCode_4,
> Item5a as InvenRouteRecordType_5a,
> Item5b as InvenRouteSigningPrefix_5b,
> Item5c as InvenRouteLevelService_5c,
> item5d as InvenRouteNumber_5d,
> Item5e as InvenRouteDirSuffix_5e,
> Item6a as FeaturesIntersected_6a,
> Item7 as FacilityCarried_7,
> Item8 as StructureNumber_8,
> Item9 as LocationNarrative_9,
> -- 99.99 is a possible vert clearance (over 30 meters), don't care
> case PATINDEX('%[^0-9.]%',Item10)
> when 0 then cast(Item10 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as InvenRouteMinVertClearance_10,
> case PATINDEX('%[^0-9.]%',Item11)
> when 0 then cast(Item11 as decimal(12,3))/1000
> else cast(0 as decimal(12,3))
> end as LrsKilometerPoint_11,
> Item12 as IsOnBaseHighwayNetwork_12,
> Item13a as LrsInvenRoute_13a,
> Item13b as LrsSubroute_13b,
> Item16 as Latitude_16,
> Item17 as Longitude_17,
> -- detours over 199 km are coded as 199
> case PATINDEX('%[^0-9]%',Item19)
> when 0 then cast(Item19 as int)
> else cast(0 as int)
> end as BypassDetourKilometers_19,
> Item20 as TollCode_20,
> Item21 as MaintRespCode_21,
> Item22 as MaintRespOwnerCode_22,
> Item26 as InvenRouteFunctionClass_26,
> Item27 as YearBuilt_27,
> case PATINDEX('%[^0-9]%',Item28a)
> when 0 then cast(Item28a as int)
> else cast(0 as int)
> end as LanesOn_28a,
> case PATINDEX('%[^0-9]%',Item28b)
> when 0 then cast(Item28b as int)
> else cast(0 as int)
> end as LanesUnder_28b,
> case PATINDEX('%[^0-9]%',Item29)
> when 0 then cast(Item29 as int)
> else cast(0 as int)
> end as AvgDailyTraffic_29,
> Item30 as AvgDailyTrafficYear_30,
> Item31 as DesignLoadCode_31,
> case PATINDEX('%[^0-9.]%',Item32)
> when 0 then cast(Item32 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as ApproachRoadwayWidth_32,
> Item33 as MedianExistOpenClosed_33,
> case PATINDEX('%[^0-9]%',Item34)
> when 0 then cast(Item34 as int)
> else cast(0 as int)
> end as RoadwayPierSkewDegrees_34,
> Item35 as StructureIsFlared_35,
> Item36a as TsRailings_36a,
> Item36b as TsTransitions_36b,
> Item36c as TsApprGuardrail_36c,
> item36d as TsApprGuardrailEnds_36d,
> Item37 as HistoricSigCode_37,
> Item38 as NavControlCode_38,
> case PATINDEX('%[^0-9.]%',Item39)
> when 0 then cast(Item39 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as NavVertClearance_39,
> case PATINDEX('%[^0-9.]%',Item40)
> when 0 then cast(Item40 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as NavHorzClearance_40,
> Item41 as OpenPostedClosedCode_41,
> Item42a as ServiceTypeOnCode_42a,
> item42b as ServiceTypeUnderCode_42b,
> Item43a as StructureMaterialTypeCode_43a,
> Item43b as StructureTypeCode_43b,
> Item44a as ApproachMaterialTypeCode_44a,
> Item44b as ApproachStructureTypeCode_44b,
> case PATINDEX('%[^0-9]%',Item45)
> when 0 then cast(Item45 as int)
> else cast(0 as int)
> end as MainSpans_45,
> case PATINDEX('%[^0-9]%',Item46)
> when 0 then cast(Item46 as int)
> else cast(0 as int)
> end as ApproachSpans_46,
> -- 100 meters or greater coded as 999, leave as is
> case PATINDEX('%[^0-9.]%',Item47)
> when 0 then cast(Item47 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as InvenRouteHorzClearance_47,
> case PATINDEX('%[^0-9.]%',Item48)
> when 0 then cast(Item48 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as MaxSpanLength_48,
> case PATINDEX('%[^0-9.]%',Item49)
> when 0 then cast(Item49 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as StructureLength_49,
> case PATINDEX('%[^0-9.]%',Item50a)
> when 0 then cast(Item50a as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbSidewalkWidthLeft_50a,
> case PATINDEX('%[^0-9.]%',Item50b)
> when 0 then cast(Item50b as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbSidewalkWidthRight_50b,
> case PATINDEX('%[^0-9.]%',Item51)
> when 0 then cast(Item51 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbToCurbRoadwayWidth_51,
> case PATINDEX('%[^0-9.]%',Item52)
> when 0 then cast(Item52 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as DeckWidth_52,
> -- Item53: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item53)
> when 0 then cast(Item53 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as VertClearanceOverRoadway_53,
> Item54a as VertClearanceTypeUnderStructure_54a,
> -- Item54b: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item54b)
> when 0 then cast(Item54b as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as VertClearanceUnderStructure_54b,
> Item55a as LatClearanceTypeUnderStructure_55a,
> -- Item55b: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item55b)
> when 0 then cast(Item55b as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as LatClearanceUnderStructure_55b,
> -- Item56: 999 for "open", 998 for greater than 30 meters
> case
> when Item56 = '999' then cast(0 as decimal(7,1))
> when PATINDEX('%[^0-9.]%',Item56) = 0 then cast(Item56 as
decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as LatClearanceUnderOnLeft_56,
> Item58 as DeckConditionCode_58,
> Item59 as SuperstructureConditionCode_59,
> Item60 as SubstructureConditionCode_60,
> Item61 as ChannelConditionCode_61,
> Item62 as CulvertConditionCode_62,
> Item63 as OperationRatingMethodCode_63,
> -- 999 coded for "live load is insignificant in structure capacity"
> case PATINDEX('%[^0-9.]%',Item64)
> when 0 then cast(Item64 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as OperatingRatingMetricTons_64,
> Item65 as InvenRatingLoadMethodCode_65,
> -- 999 coded for "live load is insignificant in structure capacity"
> case PATINDEX('%[^0-9.]%',Item66)
> when 0 then cast(Item66 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as InvenRatingMetricTons_66,
> Item67 as StructureEvalCode_67,
> Item68 as DeckGeoEvalCode_68,
> Item69 as UnderclearanceEvalCode_69,
> Item70 as BridgePostingCode_70,
> Item71 as WaterwayAdequacyCode_71,
> item772 as ApprRoadwayAlignmentCode_72,
> Item75a as WorkTypeCode_75a,
> Item75b as WorkerTypeCode_75b,
> case PATINDEX('%[^0-9.]%',Item76)
> when 0 then cast(Item76 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as ImprovementSpan_76,
> Item90 as LastInspectionMonthYear_90,
> Item91 as InspectionFrequencyMonths_91,
> item92a as CfiFractureCriticalDetailsCode_92a,
> Item92b as CfiUnderwaterInspectionCode_92b,
> Item92c as CfiOtherSpecialInspectionCode_92c,
> Item93a as CfiFractureCriticalDetailsMonthYear_93a,
> Item93b as CfiUnderwaterInspectionMonthYear_93b,
> item93c as CfiOtherSpecialInspectionMonthYear_93c,
> case PATINDEX('%[^0-9]%',Item94)
> when 0 then cast(Item94 as decimal)*1000
> else cast(0 as decimal)
> end as BridgeImprovementCost_94,
> case PATINDEX('%[^0-9]%',Item95)
> when 0 then cast(Item95 as decimal)*1000
> else cast(0 as decimal)
> end as RoadwayImprovementCost_95,
> case PATINDEX('%[^0-9]%',Item96)
> when 0 then cast(Item96 as decimal)*1000
> else cast(0 as decimal)
> end as TotalImprovementCost_96,
> Item97 as ImprovementCostEstYear_97,
> Item98a as BorderBrNeighborStateCode_98a,
> -- 99 means no responsibility for the structure
> case
> when Item98b = '99' then cast(0 as int)
> when PATINDEX('%[^0-9.]%',Item98b) = 0 then cast(Item98b as int)
> else cast(0 as int)
> end as BorderBrPctResponsibility_98b,
> -- This one could be an NBI id, or a state structure number
> Item99 as BorderBrNeighborsStructureId_99,
> Item100 as StrahnetHighwayDesCode_100,
> Item101 as ParallelStructureDesCode_101,
> Item102 as InvenRouteTrafficDirCode_102,
> Item103 as TemporaryStructure_103,
> Item104 as IsOnNhs_104,
> Item105 as FederalLandsHighwayCode_105,
> Item106 as YearReconstructed_106,
> Item107 as DeckTypeCode_107,
> Item108a as DeckProtectSurfaceTypeCode_108a,
> Item108b as DeckProtectMembraneTypeCode_108b,
> Item108c as DeckProtectTypeCode_108c,
> case PATINDEX('%[^0-9]%',Item109)
> when 0 then cast(Item109 as int)
> else cast(0 as int)
> end as AvgDailyTruckTrafficPct_109,
> Item110 as IsOnNatlTruckNetwork_110,
> Item111 as PierOrAbutmentProtectionCode_111,
> Item112 as NbisLengthYesNo_112,
> Item113 as ScourCriticalCode_113,
> case PATINDEX('%[^0-9]%',Item114)
> when 0 then cast(Item114 as int)
> else cast(0 as int)
> end as FutureAvgDailyTraffic_114,
> Item115 as FutureAvgDailyTrafficYear_115,
> case PATINDEX('%[^0-9.]%',Item116)
> when 0 then cast(Item116 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as NavMinVertClearanceLiftBrClosed_116,
> cast(' ' as varchar(22)) as CountyName,
> cast(' ' as varchar(52)) as PlaceName
> into STEEL
> from NBI
> GO
>

Error 8114 on big query - how to hone it down?

I have a big select into statement I'm running from QA (on the same machine
where the DB is). I've used it in the past, and it worked then. It's with
the NBI (National Bridge Inventory), trying to convert some data types, so
there are over 600,000 records with 120 or so fields each. It runs for
awhile, and I don't what what record or field produces the problem. How can
I get more info?
Every field in the source table is NVARCHAR.
The message after 5 minutes or so is:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric
TIA -
Mark
The statement:
select
identity(int,1,1) as ID,
Item1 as StateCode_1,
Item2 as HighwayDistrict_2,
Item3 as CountyCode_3,
Item4 as PlaceCode_4,
Item5a as InvenRouteRecordType_5a,
Item5b as InvenRouteSigningPrefix_5b,
Item5c as InvenRouteLevelService_5c,
item5d as InvenRouteNumber_5d,
Item5e as InvenRouteDirSuffix_5e,
Item6a as FeaturesIntersected_6a,
Item7 as FacilityCarried_7,
Item8 as StructureNumber_8,
Item9 as LocationNarrative_9,
-- 99.99 is a possible vert clearance (over 30 meters), don't care
case PATINDEX('%[^0-9.]%',Item10)
when 0 then cast(Item10 as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as InvenRouteMinVertClearance_10,
case PATINDEX('%[^0-9.]%',Item11)
when 0 then cast(Item11 as decimal(12,3))/1000
else cast(0 as decimal(12,3))
end as LrsKilometerPoint_11,
Item12 as IsOnBaseHighwayNetwork_12,
Item13a as LrsInvenRoute_13a,
Item13b as LrsSubroute_13b,
Item16 as Latitude_16,
Item17 as Longitude_17,
-- detours over 199 km are coded as 199
case PATINDEX('%[^0-9]%',Item19)
when 0 then cast(Item19 as int)
else cast(0 as int)
end as BypassDetourKilometers_19,
Item20 as TollCode_20,
Item21 as MaintRespCode_21,
Item22 as MaintRespOwnerCode_22,
Item26 as InvenRouteFunctionClass_26,
Item27 as YearBuilt_27,
case PATINDEX('%[^0-9]%',Item28a)
when 0 then cast(Item28a as int)
else cast(0 as int)
end as LanesOn_28a,
case PATINDEX('%[^0-9]%',Item28b)
when 0 then cast(Item28b as int)
else cast(0 as int)
end as LanesUnder_28b,
case PATINDEX('%[^0-9]%',Item29)
when 0 then cast(Item29 as int)
else cast(0 as int)
end as AvgDailyTraffic_29,
Item30 as AvgDailyTrafficYear_30,
Item31 as DesignLoadCode_31,
case PATINDEX('%[^0-9.]%',Item32)
when 0 then cast(Item32 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as ApproachRoadwayWidth_32,
Item33 as MedianExistOpenClosed_33,
case PATINDEX('%[^0-9]%',Item34)
when 0 then cast(Item34 as int)
else cast(0 as int)
end as RoadwayPierSkewDegrees_34,
Item35 as StructureIsFlared_35,
Item36a as TsRailings_36a,
Item36b as TsTransitions_36b,
Item36c as TsApprGuardrail_36c,
item36d as TsApprGuardrailEnds_36d,
Item37 as HistoricSigCode_37,
Item38 as NavControlCode_38,
case PATINDEX('%[^0-9.]%',Item39)
when 0 then cast(Item39 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as NavVertClearance_39,
case PATINDEX('%[^0-9.]%',Item40)
when 0 then cast(Item40 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as NavHorzClearance_40,
Item41 as OpenPostedClosedCode_41,
Item42a as ServiceTypeOnCode_42a,
item42b as ServiceTypeUnderCode_42b,
Item43a as StructureMaterialTypeCode_43a,
Item43b as StructureTypeCode_43b,
Item44a as ApproachMaterialTypeCode_44a,
Item44b as ApproachStructureTypeCode_44b,
case PATINDEX('%[^0-9]%',Item45)
when 0 then cast(Item45 as int)
else cast(0 as int)
end as MainSpans_45,
case PATINDEX('%[^0-9]%',Item46)
when 0 then cast(Item46 as int)
else cast(0 as int)
end as ApproachSpans_46,
-- 100 meters or greater coded as 999, leave as is
case PATINDEX('%[^0-9.]%',Item47)
when 0 then cast(Item47 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as InvenRouteHorzClearance_47,
case PATINDEX('%[^0-9.]%',Item48)
when 0 then cast(Item48 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as MaxSpanLength_48,
case PATINDEX('%[^0-9.]%',Item49)
when 0 then cast(Item49 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as StructureLength_49,
case PATINDEX('%[^0-9.]%',Item50a)
when 0 then cast(Item50a as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbSidewalkWidthLeft_50a,
case PATINDEX('%[^0-9.]%',Item50b)
when 0 then cast(Item50b as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbSidewalkWidthRight_50b,
case PATINDEX('%[^0-9.]%',Item51)
when 0 then cast(Item51 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbToCurbRoadwayWidth_51,
case PATINDEX('%[^0-9.]%',Item52)
when 0 then cast(Item52 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as DeckWidth_52,
-- Item53: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item53)
when 0 then cast(Item53 as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as VertClearanceOverRoadway_53,
Item54a as VertClearanceTypeUnderStructure_54a,
-- Item54b: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item54b)
when 0 then cast(Item54b as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as VertClearanceUnderStructure_54b,
Item55a as LatClearanceTypeUnderStructure_55a,
-- Item55b: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item55b)
when 0 then cast(Item55b as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as LatClearanceUnderStructure_55b,
-- Item56: 999 for "open", 998 for greater than 30 meters
case
when Item56 = '999' then cast(0 as decimal(7,1))
when PATINDEX('%[^0-9.]%',Item56) = 0 then cast(Item56 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as LatClearanceUnderOnLeft_56,
Item58 as DeckConditionCode_58,
Item59 as SuperstructureConditionCode_59,
Item60 as SubstructureConditionCode_60,
Item61 as ChannelConditionCode_61,
Item62 as CulvertConditionCode_62,
Item63 as OperationRatingMethodCode_63,
-- 999 coded for "live load is insignificant in structure capacity"
case PATINDEX('%[^0-9.]%',Item64)
when 0 then cast(Item64 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as OperatingRatingMetricTons_64,
Item65 as InvenRatingLoadMethodCode_65,
-- 999 coded for "live load is insignificant in structure capacity"
case PATINDEX('%[^0-9.]%',Item66)
when 0 then cast(Item66 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as InvenRatingMetricTons_66,
Item67 as StructureEvalCode_67,
Item68 as DeckGeoEvalCode_68,
Item69 as UnderclearanceEvalCode_69,
Item70 as BridgePostingCode_70,
Item71 as WaterwayAdequacyCode_71,
item772 as ApprRoadwayAlignmentCode_72,
Item75a as WorkTypeCode_75a,
Item75b as WorkerTypeCode_75b,
case PATINDEX('%[^0-9.]%',Item76)
when 0 then cast(Item76 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as ImprovementSpan_76,
Item90 as LastInspectionMonthYear_90,
Item91 as InspectionFrequencyMonths_91,
item92a as CfiFractureCriticalDetailsCode_92a,
Item92b as CfiUnderwaterInspectionCode_92b,
Item92c as CfiOtherSpecialInspectionCode_92c,
Item93a as CfiFractureCriticalDetailsMonthYear_93a,
Item93b as CfiUnderwaterInspectionMonthYear_93b,
item93c as CfiOtherSpecialInspectionMonthYear_93c,
case PATINDEX('%[^0-9]%',Item94)
when 0 then cast(Item94 as decimal)*1000
else cast(0 as decimal)
end as BridgeImprovementCost_94,
case PATINDEX('%[^0-9]%',Item95)
when 0 then cast(Item95 as decimal)*1000
else cast(0 as decimal)
end as RoadwayImprovementCost_95,
case PATINDEX('%[^0-9]%',Item96)
when 0 then cast(Item96 as decimal)*1000
else cast(0 as decimal)
end as TotalImprovementCost_96,
Item97 as ImprovementCostEstYear_97,
Item98a as BorderBrNeighborStateCode_98a,
-- 99 means no responsibility for the structure
case
when Item98b = '99' then cast(0 as int)
when PATINDEX('%[^0-9.]%',Item98b) = 0 then cast(Item98b as int)
else cast(0 as int)
end as BorderBrPctResponsibility_98b,
-- This one could be an NBI id, or a state structure number
Item99 as BorderBrNeighborsStructureId_99,
Item100 as StrahnetHighwayDesCode_100,
Item101 as ParallelStructureDesCode_101,
Item102 as InvenRouteTrafficDirCode_102,
Item103 as TemporaryStructure_103,
Item104 as IsOnNhs_104,
Item105 as FederalLandsHighwayCode_105,
Item106 as YearReconstructed_106,
Item107 as DeckTypeCode_107,
Item108a as DeckProtectSurfaceTypeCode_108a,
Item108b as DeckProtectMembraneTypeCode_108b,
Item108c as DeckProtectTypeCode_108c,
case PATINDEX('%[^0-9]%',Item109)
when 0 then cast(Item109 as int)
else cast(0 as int)
end as AvgDailyTruckTrafficPct_109,
Item110 as IsOnNatlTruckNetwork_110,
Item111 as PierOrAbutmentProtectionCode_111,
Item112 as NbisLengthYesNo_112,
Item113 as ScourCriticalCode_113,
case PATINDEX('%[^0-9]%',Item114)
when 0 then cast(Item114 as int)
else cast(0 as int)
end as FutureAvgDailyTraffic_114,
Item115 as FutureAvgDailyTrafficYear_115,
case PATINDEX('%[^0-9.]%',Item116)
when 0 then cast(Item116 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as NavMinVertClearanceLiftBrClosed_116,
cast(' ' as varchar(22)) as CountyName,
cast(' ' as varchar(52)) as PlaceName
into STEEL
from NBI
GOSometimes something about posting to usenet makes it work...
There are decimal points in the PATINDEX functions. I suppose if there is
one (or two) in a source field, then it will try to convert. Got rid of
those and it works.
Mark
> -- 99.99 is a possible vert clearance (over 30 meters), don't care
> case PATINDEX('%[^0-9.]%',Item10)
> when 0 then cast(Item10 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as InvenRouteMinVertClearance_10,
> case PATINDEX('%[^0-9.]%',Item11)
> when 0 then cast(Item11 as decimal(12,3))/1000
> else cast(0 as decimal(12,3))
> end as LrsKilometerPoint_11,
etc...|||You can add ISNUMERIC to your CASE in order to identify other invalid data,
such as multiple decimal points and empty strings:
CASE WHEN
PATINDEX('%[^0-9.]%',Item10) = 0 AND ISNUMERIC(Item10) = 1
THEN CAST(Item10 AS decimal(7,2))/100
ELSE CAST(0 AS decimal(7,2)) END
If you need to identify rows with invalid data, specify the CASE statements
in a WHERE clause.
WHERE 'Invalid' = CASE WHEN
PATINDEX('%[^0-9.]%',Item10) = 0 AND ISNUMERIC(Item10) = 1
THEN 'Valid'
ELSE 'invalid' END
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark G. Meyers" <mmeyers[at]hydromilling.com> wrote in message
news:ep5pRIqYEHA.1652@.TK2MSFTNGP09.phx.gbl...
> I have a big select into statement I'm running from QA (on the same
machine
> where the DB is). I've used it in the past, and it worked then. It's
with
> the NBI (National Bridge Inventory), trying to convert some data types, so
> there are over 600,000 records with 120 or so fields each. It runs for
> awhile, and I don't what what record or field produces the problem. How
can
> I get more info?
> Every field in the source table is NVARCHAR.
> The message after 5 minutes or so is:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type nvarchar to numeric
> TIA -
> Mark
> The statement:
> select
> identity(int,1,1) as ID,
> Item1 as StateCode_1,
> Item2 as HighwayDistrict_2,
> Item3 as CountyCode_3,
> Item4 as PlaceCode_4,
> Item5a as InvenRouteRecordType_5a,
> Item5b as InvenRouteSigningPrefix_5b,
> Item5c as InvenRouteLevelService_5c,
> item5d as InvenRouteNumber_5d,
> Item5e as InvenRouteDirSuffix_5e,
> Item6a as FeaturesIntersected_6a,
> Item7 as FacilityCarried_7,
> Item8 as StructureNumber_8,
> Item9 as LocationNarrative_9,
> -- 99.99 is a possible vert clearance (over 30 meters), don't care
> case PATINDEX('%[^0-9.]%',Item10)
> when 0 then cast(Item10 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as InvenRouteMinVertClearance_10,
> case PATINDEX('%[^0-9.]%',Item11)
> when 0 then cast(Item11 as decimal(12,3))/1000
> else cast(0 as decimal(12,3))
> end as LrsKilometerPoint_11,
> Item12 as IsOnBaseHighwayNetwork_12,
> Item13a as LrsInvenRoute_13a,
> Item13b as LrsSubroute_13b,
> Item16 as Latitude_16,
> Item17 as Longitude_17,
> -- detours over 199 km are coded as 199
> case PATINDEX('%[^0-9]%',Item19)
> when 0 then cast(Item19 as int)
> else cast(0 as int)
> end as BypassDetourKilometers_19,
> Item20 as TollCode_20,
> Item21 as MaintRespCode_21,
> Item22 as MaintRespOwnerCode_22,
> Item26 as InvenRouteFunctionClass_26,
> Item27 as YearBuilt_27,
> case PATINDEX('%[^0-9]%',Item28a)
> when 0 then cast(Item28a as int)
> else cast(0 as int)
> end as LanesOn_28a,
> case PATINDEX('%[^0-9]%',Item28b)
> when 0 then cast(Item28b as int)
> else cast(0 as int)
> end as LanesUnder_28b,
> case PATINDEX('%[^0-9]%',Item29)
> when 0 then cast(Item29 as int)
> else cast(0 as int)
> end as AvgDailyTraffic_29,
> Item30 as AvgDailyTrafficYear_30,
> Item31 as DesignLoadCode_31,
> case PATINDEX('%[^0-9.]%',Item32)
> when 0 then cast(Item32 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as ApproachRoadwayWidth_32,
> Item33 as MedianExistOpenClosed_33,
> case PATINDEX('%[^0-9]%',Item34)
> when 0 then cast(Item34 as int)
> else cast(0 as int)
> end as RoadwayPierSkewDegrees_34,
> Item35 as StructureIsFlared_35,
> Item36a as TsRailings_36a,
> Item36b as TsTransitions_36b,
> Item36c as TsApprGuardrail_36c,
> item36d as TsApprGuardrailEnds_36d,
> Item37 as HistoricSigCode_37,
> Item38 as NavControlCode_38,
> case PATINDEX('%[^0-9.]%',Item39)
> when 0 then cast(Item39 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as NavVertClearance_39,
> case PATINDEX('%[^0-9.]%',Item40)
> when 0 then cast(Item40 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as NavHorzClearance_40,
> Item41 as OpenPostedClosedCode_41,
> Item42a as ServiceTypeOnCode_42a,
> item42b as ServiceTypeUnderCode_42b,
> Item43a as StructureMaterialTypeCode_43a,
> Item43b as StructureTypeCode_43b,
> Item44a as ApproachMaterialTypeCode_44a,
> Item44b as ApproachStructureTypeCode_44b,
> case PATINDEX('%[^0-9]%',Item45)
> when 0 then cast(Item45 as int)
> else cast(0 as int)
> end as MainSpans_45,
> case PATINDEX('%[^0-9]%',Item46)
> when 0 then cast(Item46 as int)
> else cast(0 as int)
> end as ApproachSpans_46,
> -- 100 meters or greater coded as 999, leave as is
> case PATINDEX('%[^0-9.]%',Item47)
> when 0 then cast(Item47 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as InvenRouteHorzClearance_47,
> case PATINDEX('%[^0-9.]%',Item48)
> when 0 then cast(Item48 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as MaxSpanLength_48,
> case PATINDEX('%[^0-9.]%',Item49)
> when 0 then cast(Item49 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as StructureLength_49,
> case PATINDEX('%[^0-9.]%',Item50a)
> when 0 then cast(Item50a as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbSidewalkWidthLeft_50a,
> case PATINDEX('%[^0-9.]%',Item50b)
> when 0 then cast(Item50b as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbSidewalkWidthRight_50b,
> case PATINDEX('%[^0-9.]%',Item51)
> when 0 then cast(Item51 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbToCurbRoadwayWidth_51,
> case PATINDEX('%[^0-9.]%',Item52)
> when 0 then cast(Item52 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as DeckWidth_52,
> -- Item53: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item53)
> when 0 then cast(Item53 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as VertClearanceOverRoadway_53,
> Item54a as VertClearanceTypeUnderStructure_54a,
> -- Item54b: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item54b)
> when 0 then cast(Item54b as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as VertClearanceUnderStructure_54b,
> Item55a as LatClearanceTypeUnderStructure_55a,
> -- Item55b: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item55b)
> when 0 then cast(Item55b as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as LatClearanceUnderStructure_55b,
> -- Item56: 999 for "open", 998 for greater than 30 meters
> case
> when Item56 = '999' then cast(0 as decimal(7,1))
> when PATINDEX('%[^0-9.]%',Item56) = 0 then cast(Item56 as
decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as LatClearanceUnderOnLeft_56,
> Item58 as DeckConditionCode_58,
> Item59 as SuperstructureConditionCode_59,
> Item60 as SubstructureConditionCode_60,
> Item61 as ChannelConditionCode_61,
> Item62 as CulvertConditionCode_62,
> Item63 as OperationRatingMethodCode_63,
> -- 999 coded for "live load is insignificant in structure capacity"
> case PATINDEX('%[^0-9.]%',Item64)
> when 0 then cast(Item64 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as OperatingRatingMetricTons_64,
> Item65 as InvenRatingLoadMethodCode_65,
> -- 999 coded for "live load is insignificant in structure capacity"
> case PATINDEX('%[^0-9.]%',Item66)
> when 0 then cast(Item66 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as InvenRatingMetricTons_66,
> Item67 as StructureEvalCode_67,
> Item68 as DeckGeoEvalCode_68,
> Item69 as UnderclearanceEvalCode_69,
> Item70 as BridgePostingCode_70,
> Item71 as WaterwayAdequacyCode_71,
> item772 as ApprRoadwayAlignmentCode_72,
> Item75a as WorkTypeCode_75a,
> Item75b as WorkerTypeCode_75b,
> case PATINDEX('%[^0-9.]%',Item76)
> when 0 then cast(Item76 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as ImprovementSpan_76,
> Item90 as LastInspectionMonthYear_90,
> Item91 as InspectionFrequencyMonths_91,
> item92a as CfiFractureCriticalDetailsCode_92a,
> Item92b as CfiUnderwaterInspectionCode_92b,
> Item92c as CfiOtherSpecialInspectionCode_92c,
> Item93a as CfiFractureCriticalDetailsMonthYear_93a,
> Item93b as CfiUnderwaterInspectionMonthYear_93b,
> item93c as CfiOtherSpecialInspectionMonthYear_93c,
> case PATINDEX('%[^0-9]%',Item94)
> when 0 then cast(Item94 as decimal)*1000
> else cast(0 as decimal)
> end as BridgeImprovementCost_94,
> case PATINDEX('%[^0-9]%',Item95)
> when 0 then cast(Item95 as decimal)*1000
> else cast(0 as decimal)
> end as RoadwayImprovementCost_95,
> case PATINDEX('%[^0-9]%',Item96)
> when 0 then cast(Item96 as decimal)*1000
> else cast(0 as decimal)
> end as TotalImprovementCost_96,
> Item97 as ImprovementCostEstYear_97,
> Item98a as BorderBrNeighborStateCode_98a,
> -- 99 means no responsibility for the structure
> case
> when Item98b = '99' then cast(0 as int)
> when PATINDEX('%[^0-9.]%',Item98b) = 0 then cast(Item98b as int)
> else cast(0 as int)
> end as BorderBrPctResponsibility_98b,
> -- This one could be an NBI id, or a state structure number
> Item99 as BorderBrNeighborsStructureId_99,
> Item100 as StrahnetHighwayDesCode_100,
> Item101 as ParallelStructureDesCode_101,
> Item102 as InvenRouteTrafficDirCode_102,
> Item103 as TemporaryStructure_103,
> Item104 as IsOnNhs_104,
> Item105 as FederalLandsHighwayCode_105,
> Item106 as YearReconstructed_106,
> Item107 as DeckTypeCode_107,
> Item108a as DeckProtectSurfaceTypeCode_108a,
> Item108b as DeckProtectMembraneTypeCode_108b,
> Item108c as DeckProtectTypeCode_108c,
> case PATINDEX('%[^0-9]%',Item109)
> when 0 then cast(Item109 as int)
> else cast(0 as int)
> end as AvgDailyTruckTrafficPct_109,
> Item110 as IsOnNatlTruckNetwork_110,
> Item111 as PierOrAbutmentProtectionCode_111,
> Item112 as NbisLengthYesNo_112,
> Item113 as ScourCriticalCode_113,
> case PATINDEX('%[^0-9]%',Item114)
> when 0 then cast(Item114 as int)
> else cast(0 as int)
> end as FutureAvgDailyTraffic_114,
> Item115 as FutureAvgDailyTrafficYear_115,
> case PATINDEX('%[^0-9.]%',Item116)
> when 0 then cast(Item116 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as NavMinVertClearanceLiftBrClosed_116,
> cast(' ' as varchar(22)) as CountyName,
> cast(' ' as varchar(52)) as PlaceName
> into STEEL
> from NBI
> GO
>

Error 8114 on big query - how to hone it down?

I have a big select into statement I'm running from QA (on the same machine
where the DB is). I've used it in the past, and it worked then. It's with
the NBI (National Bridge Inventory), trying to convert some data types, so
there are over 600,000 records with 120 or so fields each. It runs for
awhile, and I don't what what record or field produces the problem. How can
I get more info?
Every field in the source table is NVARCHAR.
The message after 5 minutes or so is:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric
TIA -
Mark
The statement:
select
identity(int,1,1) as ID,
Item1 as StateCode_1,
Item2 as HighwayDistrict_2,
Item3 as CountyCode_3,
Item4 as PlaceCode_4,
Item5a as InvenRouteRecordType_5a,
Item5b as InvenRouteSigningPrefix_5b,
Item5c as InvenRouteLevelService_5c,
item5d as InvenRouteNumber_5d,
Item5e as InvenRouteDirSuffix_5e,
Item6a as FeaturesIntersected_6a,
Item7 as FacilityCarried_7,
Item8 as StructureNumber_8,
Item9 as LocationNarrative_9,
-- 99.99 is a possible vert clearance (over 30 meters), don't care
case PATINDEX('%[^0-9.]%',Item10)
when 0 then cast(Item10 as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as InvenRouteMinVertClearance_10,
case PATINDEX('%[^0-9.]%',Item11)
when 0 then cast(Item11 as decimal(12,3))/1000
else cast(0 as decimal(12,3))
end as LrsKilometerPoint_11,
Item12 as IsOnBaseHighwayNetwork_12,
Item13a as LrsInvenRoute_13a,
Item13b as LrsSubroute_13b,
Item16 as Latitude_16,
Item17 as Longitude_17,
-- detours over 199 km are coded as 199
case PATINDEX('%[^0-9]%',Item19)
when 0 then cast(Item19 as int)
else cast(0 as int)
end as BypassDetourKilometers_19,
Item20 as TollCode_20,
Item21 as MaintRespCode_21,
Item22 as MaintRespOwnerCode_22,
Item26 as InvenRouteFunctionClass_26,
Item27 as YearBuilt_27,
case PATINDEX('%[^0-9]%',Item28a)
when 0 then cast(Item28a as int)
else cast(0 as int)
end as LanesOn_28a,
case PATINDEX('%[^0-9]%',Item28b)
when 0 then cast(Item28b as int)
else cast(0 as int)
end as LanesUnder_28b,
case PATINDEX('%[^0-9]%',Item29)
when 0 then cast(Item29 as int)
else cast(0 as int)
end as AvgDailyTraffic_29,
Item30 as AvgDailyTrafficYear_30,
Item31 as DesignLoadCode_31,
case PATINDEX('%[^0-9.]%',Item32)
when 0 then cast(Item32 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as ApproachRoadwayWidth_32,
Item33 as MedianExistOpenClosed_33,
case PATINDEX('%[^0-9]%',Item34)
when 0 then cast(Item34 as int)
else cast(0 as int)
end as RoadwayPierSkewDegrees_34,
Item35 as StructureIsFlared_35,
Item36a as TsRailings_36a,
Item36b as TsTransitions_36b,
Item36c as TsApprGuardrail_36c,
item36d as TsApprGuardrailEnds_36d,
Item37 as HistoricSigCode_37,
Item38 as NavControlCode_38,
case PATINDEX('%[^0-9.]%',Item39)
when 0 then cast(Item39 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as NavVertClearance_39,
case PATINDEX('%[^0-9.]%',Item40)
when 0 then cast(Item40 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as NavHorzClearance_40,
Item41 as OpenPostedClosedCode_41,
Item42a as ServiceTypeOnCode_42a,
item42b as ServiceTypeUnderCode_42b,
Item43a as StructureMaterialTypeCode_43a,
Item43b as StructureTypeCode_43b,
Item44a as ApproachMaterialTypeCode_44a,
Item44b as ApproachStructureTypeCode_44b,
case PATINDEX('%[^0-9]%',Item45)
when 0 then cast(Item45 as int)
else cast(0 as int)
end as MainSpans_45,
case PATINDEX('%[^0-9]%',Item46)
when 0 then cast(Item46 as int)
else cast(0 as int)
end as ApproachSpans_46,
-- 100 meters or greater coded as 999, leave as is
case PATINDEX('%[^0-9.]%',Item47)
when 0 then cast(Item47 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as InvenRouteHorzClearance_47,
case PATINDEX('%[^0-9.]%',Item48)
when 0 then cast(Item48 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as MaxSpanLength_48,
case PATINDEX('%[^0-9.]%',Item49)
when 0 then cast(Item49 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as StructureLength_49,
case PATINDEX('%[^0-9.]%',Item50a)
when 0 then cast(Item50a as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbSidewalkWidthLeft_50a,
case PATINDEX('%[^0-9.]%',Item50b)
when 0 then cast(Item50b as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbSidewalkWidthRight_50b,
case PATINDEX('%[^0-9.]%',Item51)
when 0 then cast(Item51 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbToCurbRoadwayWidth_51,
case PATINDEX('%[^0-9.]%',Item52)
when 0 then cast(Item52 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as DeckWidth_52,
-- Item53: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item53)
when 0 then cast(Item53 as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as VertClearanceOverRoadway_53,
Item54a as VertClearanceTypeUnderStructure_54a,
-- Item54b: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item54b)
when 0 then cast(Item54b as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as VertClearanceUnderStructure_54b,
Item55a as LatClearanceTypeUnderStructure_55a,
-- Item55b: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item55b)
when 0 then cast(Item55b as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as LatClearanceUnderStructure_55b,
-- Item56: 999 for "open", 998 for greater than 30 meters
case
when Item56 = '999' then cast(0 as decimal(7,1))
when PATINDEX('%[^0-9.]%',Item56) = 0 then cast(Item56 as decimal(7,1))/
10
else cast(0 as decimal(7,1))
end as LatClearanceUnderOnLeft_56,
Item58 as DeckConditionCode_58,
Item59 as SuperstructureConditionCode_59,
Item60 as SubstructureConditionCode_60,
Item61 as ChannelConditionCode_61,
Item62 as CulvertConditionCode_62,
Item63 as OperationRatingMethodCode_63,
-- 999 coded for "live load is insignificant in structure capacity"
case PATINDEX('%[^0-9.]%',Item64)
when 0 then cast(Item64 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as OperatingRatingMetricTons_64,
Item65 as InvenRatingLoadMethodCode_65,
-- 999 coded for "live load is insignificant in structure capacity"
case PATINDEX('%[^0-9.]%',Item66)
when 0 then cast(Item66 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as InvenRatingMetricTons_66,
Item67 as StructureEvalCode_67,
Item68 as DeckGeoEvalCode_68,
Item69 as UnderclearanceEvalCode_69,
Item70 as BridgePostingCode_70,
Item71 as WaterwayAdequacyCode_71,
item772 as ApprRoadwayAlignmentCode_72,
Item75a as WorkTypeCode_75a,
Item75b as WorkerTypeCode_75b,
case PATINDEX('%[^0-9.]%',Item76)
when 0 then cast(Item76 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as ImprovementSpan_76,
Item90 as LastInspectionMonthYear_90,
Item91 as InspectionFrequencyMonths_91,
item92a as CfiFractureCriticalDetailsCode_92a,
Item92b as CfiUnderwaterInspectionCode_92b,
Item92c as CfiOtherSpecialInspectionCode_92c,
Item93a as CfiFractureCriticalDetailsMonthYear_93a,
Item93b as CfiUnderwaterInspectionMonthYear_93b,
item93c as CfiOtherSpecialInspectionMonthYear_93c,
case PATINDEX('%[^0-9]%',Item94)
when 0 then cast(Item94 as decimal)*1000
else cast(0 as decimal)
end as BridgeImprovementCost_94,
case PATINDEX('%[^0-9]%',Item95)
when 0 then cast(Item95 as decimal)*1000
else cast(0 as decimal)
end as RoadwayImprovementCost_95,
case PATINDEX('%[^0-9]%',Item96)
when 0 then cast(Item96 as decimal)*1000
else cast(0 as decimal)
end as TotalImprovementCost_96,
Item97 as ImprovementCostEstYear_97,
Item98a as BorderBrNeighborStateCode_98a,
-- 99 means no responsibility for the structure
case
when Item98b = '99' then cast(0 as int)
when PATINDEX('%[^0-9.]%',Item98b) = 0 then cast(Item98b as int)
else cast(0 as int)
end as BorderBrPctResponsibility_98b,
-- This one could be an NBI id, or a state structure number
Item99 as BorderBrNeighborsStructureId_99,
Item100 as StrahnetHighwayDesCode_100,
Item101 as ParallelStructureDesCode_101,
Item102 as InvenRouteTrafficDirCode_102,
Item103 as TemporaryStructure_103,
Item104 as IsOnNhs_104,
Item105 as FederalLandsHighwayCode_105,
Item106 as YearReconstructed_106,
Item107 as DeckTypeCode_107,
Item108a as DeckProtectSurfaceTypeCode_108a,
Item108b as DeckProtectMembraneTypeCode_108b,
Item108c as DeckProtectTypeCode_108c,
case PATINDEX('%[^0-9]%',Item109)
when 0 then cast(Item109 as int)
else cast(0 as int)
end as AvgDailyTruckTrafficPct_109,
Item110 as IsOnNatlTruckNetwork_110,
Item111 as PierOrAbutmentProtectionCode_111,
Item112 as NbisLengthYesNo_112,
Item113 as ScourCriticalCode_113,
case PATINDEX('%[^0-9]%',Item114)
when 0 then cast(Item114 as int)
else cast(0 as int)
end as FutureAvgDailyTraffic_114,
Item115 as FutureAvgDailyTrafficYear_115,
case PATINDEX('%[^0-9.]%',Item116)
when 0 then cast(Item116 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as NavMinVertClearanceLiftBrClosed_116,
cast(' ' as varchar(22)) as CountyName,
cast(' ' as varchar(52)) as PlaceName
into STEEL
from NBI
GOSometimes something about posting to usenet makes it work...
There are decimal points in the PATINDEX functions. I suppose if there is
one (or two) in a source field, then it will try to convert. Got rid of
those and it works.
Mark

> -- 99.99 is a possible vert clearance (over 30 meters), don't care
> case PATINDEX('%[^0-9.]%',Item10)
> when 0 then cast(Item10 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as InvenRouteMinVertClearance_10,
> case PATINDEX('%[^0-9.]%',Item11)
> when 0 then cast(Item11 as decimal(12,3))/1000
> else cast(0 as decimal(12,3))
> end as LrsKilometerPoint_11,
etc...|||You can add ISNUMERIC to your CASE in order to identify other invalid data,
such as multiple decimal points and empty strings:
CASE WHEN
PATINDEX('%[^0-9.]%',Item10) = 0 AND ISNUMERIC(Item10) = 1
THEN CAST(Item10 AS decimal(7,2))/100
ELSE CAST(0 AS decimal(7,2)) END
If you need to identify rows with invalid data, specify the CASE statements
in a WHERE clause.
WHERE 'Invalid' =
CASE WHEN
PATINDEX('%[^0-9.]%',Item10) = 0 AND ISNUMERIC(Item10) = 1
THEN 'Valid'
ELSE 'invalid' END
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark G. Meyers" <mmeyers[at]hydromilling.com> wrote in message
news:ep5pRIqYEHA.1652@.TK2MSFTNGP09.phx.gbl...
> I have a big select into statement I'm running from QA (on the same
machine
> where the DB is). I've used it in the past, and it worked then. It's
with
> the NBI (National Bridge Inventory), trying to convert some data types, so
> there are over 600,000 records with 120 or so fields each. It runs for
> awhile, and I don't what what record or field produces the problem. How
can
> I get more info?
> Every field in the source table is NVARCHAR.
> The message after 5 minutes or so is:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type nvarchar to numeric
> TIA -
> Mark
> The statement:
> select
> identity(int,1,1) as ID,
> Item1 as StateCode_1,
> Item2 as HighwayDistrict_2,
> Item3 as CountyCode_3,
> Item4 as PlaceCode_4,
> Item5a as InvenRouteRecordType_5a,
> Item5b as InvenRouteSigningPrefix_5b,
> Item5c as InvenRouteLevelService_5c,
> item5d as InvenRouteNumber_5d,
> Item5e as InvenRouteDirSuffix_5e,
> Item6a as FeaturesIntersected_6a,
> Item7 as FacilityCarried_7,
> Item8 as StructureNumber_8,
> Item9 as LocationNarrative_9,
> -- 99.99 is a possible vert clearance (over 30 meters), don't care
> case PATINDEX('%[^0-9.]%',Item10)
> when 0 then cast(Item10 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as InvenRouteMinVertClearance_10,
> case PATINDEX('%[^0-9.]%',Item11)
> when 0 then cast(Item11 as decimal(12,3))/1000
> else cast(0 as decimal(12,3))
> end as LrsKilometerPoint_11,
> Item12 as IsOnBaseHighwayNetwork_12,
> Item13a as LrsInvenRoute_13a,
> Item13b as LrsSubroute_13b,
> Item16 as Latitude_16,
> Item17 as Longitude_17,
> -- detours over 199 km are coded as 199
> case PATINDEX('%[^0-9]%',Item19)
> when 0 then cast(Item19 as int)
> else cast(0 as int)
> end as BypassDetourKilometers_19,
> Item20 as TollCode_20,
> Item21 as MaintRespCode_21,
> Item22 as MaintRespOwnerCode_22,
> Item26 as InvenRouteFunctionClass_26,
> Item27 as YearBuilt_27,
> case PATINDEX('%[^0-9]%',Item28a)
> when 0 then cast(Item28a as int)
> else cast(0 as int)
> end as LanesOn_28a,
> case PATINDEX('%[^0-9]%',Item28b)
> when 0 then cast(Item28b as int)
> else cast(0 as int)
> end as LanesUnder_28b,
> case PATINDEX('%[^0-9]%',Item29)
> when 0 then cast(Item29 as int)
> else cast(0 as int)
> end as AvgDailyTraffic_29,
> Item30 as AvgDailyTrafficYear_30,
> Item31 as DesignLoadCode_31,
> case PATINDEX('%[^0-9.]%',Item32)
> when 0 then cast(Item32 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as ApproachRoadwayWidth_32,
> Item33 as MedianExistOpenClosed_33,
> case PATINDEX('%[^0-9]%',Item34)
> when 0 then cast(Item34 as int)
> else cast(0 as int)
> end as RoadwayPierSkewDegrees_34,
> Item35 as StructureIsFlared_35,
> Item36a as TsRailings_36a,
> Item36b as TsTransitions_36b,
> Item36c as TsApprGuardrail_36c,
> item36d as TsApprGuardrailEnds_36d,
> Item37 as HistoricSigCode_37,
> Item38 as NavControlCode_38,
> case PATINDEX('%[^0-9.]%',Item39)
> when 0 then cast(Item39 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as NavVertClearance_39,
> case PATINDEX('%[^0-9.]%',Item40)
> when 0 then cast(Item40 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as NavHorzClearance_40,
> Item41 as OpenPostedClosedCode_41,
> Item42a as ServiceTypeOnCode_42a,
> item42b as ServiceTypeUnderCode_42b,
> Item43a as StructureMaterialTypeCode_43a,
> Item43b as StructureTypeCode_43b,
> Item44a as ApproachMaterialTypeCode_44a,
> Item44b as ApproachStructureTypeCode_44b,
> case PATINDEX('%[^0-9]%',Item45)
> when 0 then cast(Item45 as int)
> else cast(0 as int)
> end as MainSpans_45,
> case PATINDEX('%[^0-9]%',Item46)
> when 0 then cast(Item46 as int)
> else cast(0 as int)
> end as ApproachSpans_46,
> -- 100 meters or greater coded as 999, leave as is
> case PATINDEX('%[^0-9.]%',Item47)
> when 0 then cast(Item47 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as InvenRouteHorzClearance_47,
> case PATINDEX('%[^0-9.]%',Item48)
> when 0 then cast(Item48 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as MaxSpanLength_48,
> case PATINDEX('%[^0-9.]%',Item49)
> when 0 then cast(Item49 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as StructureLength_49,
> case PATINDEX('%[^0-9.]%',Item50a)
> when 0 then cast(Item50a as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbSidewalkWidthLeft_50a,
> case PATINDEX('%[^0-9.]%',Item50b)
> when 0 then cast(Item50b as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbSidewalkWidthRight_50b,
> case PATINDEX('%[^0-9.]%',Item51)
> when 0 then cast(Item51 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbToCurbRoadwayWidth_51,
> case PATINDEX('%[^0-9.]%',Item52)
> when 0 then cast(Item52 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as DeckWidth_52,
> -- Item53: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item53)
> when 0 then cast(Item53 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as VertClearanceOverRoadway_53,
> Item54a as VertClearanceTypeUnderStructure_54a,
> -- Item54b: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item54b)
> when 0 then cast(Item54b as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as VertClearanceUnderStructure_54b,
> Item55a as LatClearanceTypeUnderStructure_55a,
> -- Item55b: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item55b)
> when 0 then cast(Item55b as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as LatClearanceUnderStructure_55b,
> -- Item56: 999 for "open", 998 for greater than 30 meters
> case
> when Item56 = '999' then cast(0 as decimal(7,1))
> when PATINDEX('%[^0-9.]%',Item56) = 0 then cast(Item56 as
decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as LatClearanceUnderOnLeft_56,
> Item58 as DeckConditionCode_58,
> Item59 as SuperstructureConditionCode_59,
> Item60 as SubstructureConditionCode_60,
> Item61 as ChannelConditionCode_61,
> Item62 as CulvertConditionCode_62,
> Item63 as OperationRatingMethodCode_63,
> -- 999 coded for "live load is insignificant in structure capacity"
> case PATINDEX('%[^0-9.]%',Item64)
> when 0 then cast(Item64 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as OperatingRatingMetricTons_64,
> Item65 as InvenRatingLoadMethodCode_65,
> -- 999 coded for "live load is insignificant in structure capacity"
> case PATINDEX('%[^0-9.]%',Item66)
> when 0 then cast(Item66 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as InvenRatingMetricTons_66,
> Item67 as StructureEvalCode_67,
> Item68 as DeckGeoEvalCode_68,
> Item69 as UnderclearanceEvalCode_69,
> Item70 as BridgePostingCode_70,
> Item71 as WaterwayAdequacyCode_71,
> item772 as ApprRoadwayAlignmentCode_72,
> Item75a as WorkTypeCode_75a,
> Item75b as WorkerTypeCode_75b,
> case PATINDEX('%[^0-9.]%',Item76)
> when 0 then cast(Item76 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as ImprovementSpan_76,
> Item90 as LastInspectionMonthYear_90,
> Item91 as InspectionFrequencyMonths_91,
> item92a as CfiFractureCriticalDetailsCode_92a,
> Item92b as CfiUnderwaterInspectionCode_92b,
> Item92c as CfiOtherSpecialInspectionCode_92c,
> Item93a as CfiFractureCriticalDetailsMonthYear_93a,
> Item93b as CfiUnderwaterInspectionMonthYear_93b,
> item93c as CfiOtherSpecialInspectionMonthYear_93c,
> case PATINDEX('%[^0-9]%',Item94)
> when 0 then cast(Item94 as decimal)*1000
> else cast(0 as decimal)
> end as BridgeImprovementCost_94,
> case PATINDEX('%[^0-9]%',Item95)
> when 0 then cast(Item95 as decimal)*1000
> else cast(0 as decimal)
> end as RoadwayImprovementCost_95,
> case PATINDEX('%[^0-9]%',Item96)
> when 0 then cast(Item96 as decimal)*1000
> else cast(0 as decimal)
> end as TotalImprovementCost_96,
> Item97 as ImprovementCostEstYear_97,
> Item98a as BorderBrNeighborStateCode_98a,
> -- 99 means no responsibility for the structure
> case
> when Item98b = '99' then cast(0 as int)
> when PATINDEX('%[^0-9.]%',Item98b) = 0 then cast(Item98b as int)
> else cast(0 as int)
> end as BorderBrPctResponsibility_98b,
> -- This one could be an NBI id, or a state structure number
> Item99 as BorderBrNeighborsStructureId_99,
> Item100 as StrahnetHighwayDesCode_100,
> Item101 as ParallelStructureDesCode_101,
> Item102 as InvenRouteTrafficDirCode_102,
> Item103 as TemporaryStructure_103,
> Item104 as IsOnNhs_104,
> Item105 as FederalLandsHighwayCode_105,
> Item106 as YearReconstructed_106,
> Item107 as DeckTypeCode_107,
> Item108a as DeckProtectSurfaceTypeCode_108a,
> Item108b as DeckProtectMembraneTypeCode_108b,
> Item108c as DeckProtectTypeCode_108c,
> case PATINDEX('%[^0-9]%',Item109)
> when 0 then cast(Item109 as int)
> else cast(0 as int)
> end as AvgDailyTruckTrafficPct_109,
> Item110 as IsOnNatlTruckNetwork_110,
> Item111 as PierOrAbutmentProtectionCode_111,
> Item112 as NbisLengthYesNo_112,
> Item113 as ScourCriticalCode_113,
> case PATINDEX('%[^0-9]%',Item114)
> when 0 then cast(Item114 as int)
> else cast(0 as int)
> end as FutureAvgDailyTraffic_114,
> Item115 as FutureAvgDailyTrafficYear_115,
> case PATINDEX('%[^0-9.]%',Item116)
> when 0 then cast(Item116 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as NavMinVertClearanceLiftBrClosed_116,
> cast(' ' as varchar(22)) as CountyName,
> cast(' ' as varchar(52)) as PlaceName
> into STEEL
> from NBI
> GO
>

Error 7139,image column

On Thurs, Apr 26 2001 8:52 pm, Stoney said:

> The SQL Server gives the following error msg When my app executes an update
> statement to set an image column:
> native error=7139, legth of text,ntext,or image data(70986) to be replicated
> exceeds configured maximum 65536.
I have this same error. Any suggestions?
OLE exception from "cqole":
SQLParamData: RETCODE=-1, State=37000, Native Error=7139
SQL statement="update devtask [...] where dbid = 34163920 and version =
11"
[Microsoft][ODBC SQL Server Driver][SQL Server]Length of text, ntext,
or
image data (73254) to be replicated exceeds configured maximum 65536.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
Win32::OLE(0.1702) error 0x80020009: "Exception occurred"
in METHOD/PROPERTYGET "Commit"
client: Windows 2003, OLE
server: Windows 2003, Sql Server 2000
issue the following sp_configure 'max text repl size',value where value is
the largest expected value of your image data. Issue a reconfigure with
override after issuing this command.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"topher67" <t0ph3r1967@.netscape.net> wrote in message
news:1162862580.901319.171170@.m7g2000cwm.googlegro ups.com...
> On Thurs, Apr 26 2001 8:52 pm, Stoney said:
>
> I have this same error. Any suggestions?
> OLE exception from "cqole":
> SQLParamData: RETCODE=-1, State=37000, Native Error=7139
> SQL statement="update devtask [...] where dbid = 34163920 and version =
> 11"
> [Microsoft][ODBC SQL Server Driver][SQL Server]Length of text, ntext,
> or
> image data (73254) to be replicated exceeds configured maximum 65536.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> Win32::OLE(0.1702) error 0x80020009: "Exception occurred"
> in METHOD/PROPERTYGET "Commit"
> client: Windows 2003, OLE
> server: Windows 2003, Sql Server 2000
>

error 547

I need to know the completely description of this error. It appears in
sysmessages table
so:
"%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict
occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls."
I need to know exactly what kind of values can have this parameters.
Can anybody help me? Please ...
It is very important for me and I didn't know where can I find this info
-enci-Try this.
use pubs
go
SELECT * FROM titles
UPDATE titles
SET pub_id = -1
WHERE title_id = 'BU1032'
It will give you the Error
Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with COLUMN FOREIGN KEY constraint
'FK__titles__pub_id__014935CB'. The conflict occurred in database 'pubs',
table 'publishers', column 'pub_id'.
The statement has been terminated.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Eniko Tegen" <EnikoTegen@.discussions.microsoft.com> wrote in message
news:EB3F91DF-0B9D-4CB3-A57A-2C005F6D65FF@.microsoft.com...
>I need to know the completely description of this error. It appears in
> sysmessages table
> so:
> "%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict
> occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls."
> I need to know exactly what kind of values can have this parameters.
> Can anybody help me? Please ...
> It is very important for me and I didn't know where can I find this info
>
> -enci-|||Thanks.
But it doesn't exist an exactly documentation for this error? I mean, I
can't try the all existing cases in which this error appears, cause I don't
know in which cases it appears. I know that it apears in minimum 2 cases:
foreign key-s and check constraint-s. But in every cases(for this 2), there
are diferrent messages for insert, update or delete.
-enci-
"Roji. P. Thomas" wrote:

> Try this.
> use pubs
> go
>
> SELECT * FROM titles
> UPDATE titles
> SET pub_id = -1
> WHERE title_id = 'BU1032'
> It will give you the Error
> Server: Msg 547, Level 16, State 1, Line 1
> UPDATE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK__titles__pub_id__014935CB'. The conflict occurred in database 'pubs',
> table 'publishers', column 'pub_id'.
> The statement has been terminated.
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Eniko Tegen" <EnikoTegen@.discussions.microsoft.com> wrote in message
> news:EB3F91DF-0B9D-4CB3-A57A-2C005F6D65FF@.microsoft.com...
>
>|||Thanks.
But it doesn't exist an exactly documentation for this error? I want to know
exactly how many cases are when this error apears to can make an exactly
translation for this error. I know that this message appears in minimum 2
cases: foreign keys and check constraint, but in every cases from this, it
appears diferent error messages for insert/update/delete operations.
I can't try the all cases if I don't know which are there. :-(
-enci-
"Roji. P. Thomas" wrote:

> Try this.
> use pubs
> go
>
> SELECT * FROM titles
> UPDATE titles
> SET pub_id = -1
> WHERE title_id = 'BU1032'
> It will give you the Error
> Server: Msg 547, Level 16, State 1, Line 1
> UPDATE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK__titles__pub_id__014935CB'. The conflict occurred in database 'pubs',
> table 'publishers', column 'pub_id'.
> The statement has been terminated.
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Eniko Tegen" <EnikoTegen@.discussions.microsoft.com> wrote in message
> news:EB3F91DF-0B9D-4CB3-A57A-2C005F6D65FF@.microsoft.com...
>
>

error 3520 on sql insert statement from a text file (only happens in 1 location)

I have a program that uses the Microsoft ODBC text driver Driver={Microsoft Text Driver (*.txt; *.csv)} this is used to make a connection to the database and then execute an sql insert to insert data from a flat file into an access database. The code works at hundreds of locations but for some reason it does not work at 1. I have ran an sql trace and found the error 3520 which would mean the data in the text file does not match the table that it is being inserted into but I have checked that and have tried using the same data on my computer and it works fine. I have also ran mdac compenent checker and there are no mismatched files. Any ideas on how to correct this or if anyone else has ever even seen this please help...Moving thread to Data Access Forum.