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 515: while creating a merge replication publication

I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
do you have a composite primary key?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chris McKenzie" <taganov@.charter.net> wrote in message news:e6fdwpkoEHA.1776@.TK2MSFTNGP14.phx.gbl...
I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
|||Since I'm not sure "what that is/ why I need it", I guess I'd have to say no. When I attempt to create the publication, I get the following messages:
SQL Server requires that all merge articles contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server will add a uniqueidentifier column to published tables that do not have one when the first snapshot is generated.
Adding a new column will:
Cause INSERT statements without column lists to fail
Increase the size of the table
Increase the time required to generate the first snapshot
SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables.
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblControl]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
[dbo].[tblReportParameters]
[dbo].[tblUsers]
AND
It is strongly recommendeds that all replicated IDENTITY columns use the NOT FOR REPLICATION option. When automatic identity range management is enabled for an article, SQL Server automatically adds the NOT FOR REPLICATION option to the IDENTITY column.
The following published tables, for which automatic identity range management has not been enabled, contain IDENTITY columns without the NOT FOR REPLICATION option:
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
SQL Server automatically adds what I need, right?
Thanks,
Chris McKenzie
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OGF4$2koEHA.1088@.TK2MSFTNGP09.phx.gbl...
do you have a composite primary key?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chris McKenzie" <taganov@.charter.net> wrote in message news:e6fdwpkoEHA.1776@.TK2MSFTNGP14.phx.gbl...
I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
|||There is a bug if you have a composite primary key which may generate the error message you are seeing.
The best way to check for this is to open up Enterprise Manager, connect to your publisher, expand your publication database and click on the tables folder. For each table you are replication right click on it and select design table. Look for icons to the left of columns which have a key on them. This is your primary key. If more than one column has the key icon on per table you have a composite primary key. You will have to talk to your developers or the vendor who created the database about changing the composite primary key.
You might also want to open a support incident with Microsoft on this on how to proceed.
Regarding the informational messages EM is throwing up.
Cause insert statements without column lists to fail.
If your application issues queries like this
insert into tablename1
select * from tablename2
you may get an application failure unless the GUID column (used to track changes in merge replication) is added to both tables. You will have to consult your developers or the vendor to confirm this is not happening. Or you can replicated every table using merge replication.
Regarding the changing size of the table - merge replication adds a GUID column of 16 bytes. This may cause very slight performance degradation on heavily utilized systems, and may make wide tables exceed the 8k maximum width of a table. Unless your tables are wide you should not have to worry about it.
Regarding the guid column - you should not have to worry about this as it almost always is not problematic. It will cause the snapshot creation time to increase especially on very large tables.
Regarding the identity column. As a good practice you should manually change your identity columns to not for replication. To make this change right click on your tables and select design table. Give focus to your identity columns and in the drop down box in the lower portion of the dialog change identity(YES) to Identity (NOT FOR REPLICATION).
HTH
"Chris McKenzie" <taganov@.charter.net> wrote in message news:ObSMlFloEHA.3900@.TK2MSFTNGP10.phx.gbl...
Since I'm not sure "what that is/ why I need it", I guess I'd have to say no. When I attempt to create the publication, I get the following messages:
SQL Server requires that all merge articles contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server will add a uniqueidentifier column to published tables that do not have one when the first snapshot is generated.
Adding a new column will:
Cause INSERT statements without column lists to fail
Increase the size of the table
Increase the time required to generate the first snapshot
SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables.
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblControl]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
[dbo].[tblReportParameters]
[dbo].[tblUsers]
AND
It is strongly recommendeds that all replicated IDENTITY columns use the NOT FOR REPLICATION option. When automatic identity range management is enabled for an article, SQL Server automatically adds the NOT FOR REPLICATION option to the IDENTITY column.
The following published tables, for which automatic identity range management has not been enabled, contain IDENTITY columns without the NOT FOR REPLICATION option:
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
SQL Server automatically adds what I need, right?
Thanks,
Chris McKenzie
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OGF4$2koEHA.1088@.TK2MSFTNGP09.phx.gbl...
do you have a composite primary key?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chris McKenzie" <taganov@.charter.net> wrote in message news:e6fdwpkoEHA.1776@.TK2MSFTNGP14.phx.gbl...
I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
|||HI Hilary,
I know what you mean by a composite primary key now. I don't use those normally, so I was a little thrown by terminology, lol. As far as manipulating the database goes, I have sole discretion over that.
I did as you suggested and manually changed my IDENTITY PRIMARY KEY columns to PRIMARY KEY IDENTITY NOT FOR REPLICATION.
Every table in the databas has one and only one PRIMARY KEY column now, and they are all created as IDENTITY NOT FOR REPLICATION. STill, when I try to create the new publication article, I get the same error.
Thanks for your help, and please let me know if you have any other ideas.
Chris
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:ePBmXploEHA.1308@.TK2MSFTNGP14.phx.gbl...
There is a bug if you have a composite primary key which may generate the error message you are seeing.
The best way to check for this is to open up Enterprise Manager, connect to your publisher, expand your publication database and click on the tables folder. For each table you are replication right click on it and select design table. Look for icons to the left of columns which have a key on them. This is your primary key. If more than one column has the key icon on per table you have a composite primary key. You will have to talk to your developers or the vendor who created the database about changing the composite primary key.
You might also want to open a support incident with Microsoft on this on how to proceed.
Regarding the informational messages EM is throwing up.
Cause insert statements without column lists to fail.
If your application issues queries like this
insert into tablename1
select * from tablename2
you may get an application failure unless the GUID column (used to track changes in merge replication) is added to both tables. You will have to consult your developers or the vendor to confirm this is not happening. Or you can replicated every table using merge replication.
Regarding the changing size of the table - merge replication adds a GUID column of 16 bytes. This may cause very slight performance degradation on heavily utilized systems, and may make wide tables exceed the 8k maximum width of a table. Unless your tables are wide you should not have to worry about it.
Regarding the guid column - you should not have to worry about this as it almost always is not problematic. It will cause the snapshot creation time to increase especially on very large tables.
Regarding the identity column. As a good practice you should manually change your identity columns to not for replication. To make this change right click on your tables and select design table. Give focus to your identity columns and in the drop down box in the lower portion of the dialog change identity(YES) to Identity (NOT FOR REPLICATION).
HTH
"Chris McKenzie" <taganov@.charter.net> wrote in message news:ObSMlFloEHA.3900@.TK2MSFTNGP10.phx.gbl...
Since I'm not sure "what that is/ why I need it", I guess I'd have to say no. When I attempt to create the publication, I get the following messages:
SQL Server requires that all merge articles contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server will add a uniqueidentifier column to published tables that do not have one when the first snapshot is generated.
Adding a new column will:
Cause INSERT statements without column lists to fail
Increase the size of the table
Increase the time required to generate the first snapshot
SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables.
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblControl]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
[dbo].[tblReportParameters]
[dbo].[tblUsers]
AND
It is strongly recommendeds that all replicated IDENTITY columns use the NOT FOR REPLICATION option. When automatic identity range management is enabled for an article, SQL Server automatically adds the NOT FOR REPLICATION option to the IDENTITY column.
The following published tables, for which automatic identity range management has not been enabled, contain IDENTITY columns without the NOT FOR REPLICATION option:
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
SQL Server automatically adds what I need, right?
Thanks,
Chris McKenzie
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OGF4$2koEHA.1088@.TK2MSFTNGP09.phx.gbl...
do you have a composite primary key?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chris McKenzie" <taganov@.charter.net> wrote in message news:e6fdwpkoEHA.1776@.TK2MSFTNGP14.phx.gbl...
I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
|||I am afraid I don't. I did a search on your problem and found some matches indicating it might be a bug.
Can you call PSS on this one?
"Chris McKenzie" <taganov@.charter.net> wrote in message news:%23dZ6$EmoEHA.3668@.TK2MSFTNGP15.phx.gbl...
HI Hilary,
I know what you mean by a composite primary key now. I don't use those normally, so I was a little thrown by terminology, lol. As far as manipulating the database goes, I have sole discretion over that.
I did as you suggested and manually changed my IDENTITY PRIMARY KEY columns to PRIMARY KEY IDENTITY NOT FOR REPLICATION.
Every table in the databas has one and only one PRIMARY KEY column now, and they are all created as IDENTITY NOT FOR REPLICATION. STill, when I try to create the new publication article, I get the same error.
Thanks for your help, and please let me know if you have any other ideas.
Chris
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:ePBmXploEHA.1308@.TK2MSFTNGP14.phx.gbl...
There is a bug if you have a composite primary key which may generate the error message you are seeing.
The best way to check for this is to open up Enterprise Manager, connect to your publisher, expand your publication database and click on the tables folder. For each table you are replication right click on it and select design table. Look for icons to the left of columns which have a key on them. This is your primary key. If more than one column has the key icon on per table you have a composite primary key. You will have to talk to your developers or the vendor who created the database about changing the composite primary key.
You might also want to open a support incident with Microsoft on this on how to proceed.
Regarding the informational messages EM is throwing up.
Cause insert statements without column lists to fail.
If your application issues queries like this
insert into tablename1
select * from tablename2
you may get an application failure unless the GUID column (used to track changes in merge replication) is added to both tables. You will have to consult your developers or the vendor to confirm this is not happening. Or you can replicated every table using merge replication.
Regarding the changing size of the table - merge replication adds a GUID column of 16 bytes. This may cause very slight performance degradation on heavily utilized systems, and may make wide tables exceed the 8k maximum width of a table. Unless your tables are wide you should not have to worry about it.
Regarding the guid column - you should not have to worry about this as it almost always is not problematic. It will cause the snapshot creation time to increase especially on very large tables.
Regarding the identity column. As a good practice you should manually change your identity columns to not for replication. To make this change right click on your tables and select design table. Give focus to your identity columns and in the drop down box in the lower portion of the dialog change identity(YES) to Identity (NOT FOR REPLICATION).
HTH
"Chris McKenzie" <taganov@.charter.net> wrote in message news:ObSMlFloEHA.3900@.TK2MSFTNGP10.phx.gbl...
Since I'm not sure "what that is/ why I need it", I guess I'd have to say no. When I attempt to create the publication, I get the following messages:
SQL Server requires that all merge articles contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server will add a uniqueidentifier column to published tables that do not have one when the first snapshot is generated.
Adding a new column will:
Cause INSERT statements without column lists to fail
Increase the size of the table
Increase the time required to generate the first snapshot
SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables.
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblControl]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
[dbo].[tblReportParameters]
[dbo].[tblUsers]
AND
It is strongly recommendeds that all replicated IDENTITY columns use the NOT FOR REPLICATION option. When automatic identity range management is enabled for an article, SQL Server automatically adds the NOT FOR REPLICATION option to the IDENTITY column.
The following published tables, for which automatic identity range management has not been enabled, contain IDENTITY columns without the NOT FOR REPLICATION option:
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
SQL Server automatically adds what I need, right?
Thanks,
Chris McKenzie
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OGF4$2koEHA.1088@.TK2MSFTNGP09.phx.gbl...
do you have a composite primary key?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chris McKenzie" <taganov@.charter.net> wrote in message news:e6fdwpkoEHA.1776@.TK2MSFTNGP14.phx.gbl...
I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris
|||I'm going to try reinstalling SQL Server and see if that takes care of it. I've tried the same operation on other installations and it works.
Chris
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:unrh$fmoEHA.3792@.TK2MSFTNGP11.phx.gbl...
I am afraid I don't. I did a search on your problem and found some matches indicating it might be a bug.
Can you call PSS on this one?
"Chris McKenzie" <taganov@.charter.net> wrote in message news:%23dZ6$EmoEHA.3668@.TK2MSFTNGP15.phx.gbl...
HI Hilary,
I know what you mean by a composite primary key now. I don't use those normally, so I was a little thrown by terminology, lol. As far as manipulating the database goes, I have sole discretion over that.
I did as you suggested and manually changed my IDENTITY PRIMARY KEY columns to PRIMARY KEY IDENTITY NOT FOR REPLICATION.
Every table in the databas has one and only one PRIMARY KEY column now, and they are all created as IDENTITY NOT FOR REPLICATION. STill, when I try to create the new publication article, I get the same error.
Thanks for your help, and please let me know if you have any other ideas.
Chris
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:ePBmXploEHA.1308@.TK2MSFTNGP14.phx.gbl...
There is a bug if you have a composite primary key which may generate the error message you are seeing.
The best way to check for this is to open up Enterprise Manager, connect to your publisher, expand your publication database and click on the tables folder. For each table you are replication right click on it and select design table. Look for icons to the left of columns which have a key on them. This is your primary key. If more than one column has the key icon on per table you have a composite primary key. You will have to talk to your developers or the vendor who created the database about changing the composite primary key.
You might also want to open a support incident with Microsoft on this on how to proceed.
Regarding the informational messages EM is throwing up.
Cause insert statements without column lists to fail.
If your application issues queries like this
insert into tablename1
select * from tablename2
you may get an application failure unless the GUID column (used to track changes in merge replication) is added to both tables. You will have to consult your developers or the vendor to confirm this is not happening. Or you can replicated every table using merge replication.
Regarding the changing size of the table - merge replication adds a GUID column of 16 bytes. This may cause very slight performance degradation on heavily utilized systems, and may make wide tables exceed the 8k maximum width of a table. Unless your tables are wide you should not have to worry about it.
Regarding the guid column - you should not have to worry about this as it almost always is not problematic. It will cause the snapshot creation time to increase especially on very large tables.
Regarding the identity column. As a good practice you should manually change your identity columns to not for replication. To make this change right click on your tables and select design table. Give focus to your identity columns and in the drop down box in the lower portion of the dialog change identity(YES) to Identity (NOT FOR REPLICATION).
HTH
"Chris McKenzie" <taganov@.charter.net> wrote in message news:ObSMlFloEHA.3900@.TK2MSFTNGP10.phx.gbl...
Since I'm not sure "what that is/ why I need it", I guess I'd have to say no. When I attempt to create the publication, I get the following messages:
SQL Server requires that all merge articles contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server will add a uniqueidentifier column to published tables that do not have one when the first snapshot is generated.
Adding a new column will:
Cause INSERT statements without column lists to fail
Increase the size of the table
Increase the time required to generate the first snapshot
SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables.
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblControl]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
[dbo].[tblReportParameters]
[dbo].[tblUsers]
AND
It is strongly recommendeds that all replicated IDENTITY columns use the NOT FOR REPLICATION option. When automatic identity range management is enabled for an article, SQL Server automatically adds the NOT FOR REPLICATION option to the IDENTITY column.
The following published tables, for which automatic identity range management has not been enabled, contain IDENTITY columns without the NOT FOR REPLICATION option:
[dbo].[tblActivity]
[dbo].[tblAssetPayable]
[dbo].[tblClass]
[dbo].[tblGLJournal]
[dbo].[tblItemClasses]
[dbo].[tblItemMaster]
[dbo].[tblItemVendors]
[dbo].[tblPicture]
SQL Server automatically adds what I need, right?
Thanks,
Chris McKenzie
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OGF4$2koEHA.1088@.TK2MSFTNGP09.phx.gbl...
do you have a composite primary key?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chris McKenzie" <taganov@.charter.net> wrote in message news:e6fdwpkoEHA.1776@.TK2MSFTNGP14.phx.gbl...
I've been trying to work through the example listed at http://www.databasejournal.com/featu...le.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:
SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'
Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.
Any ideas?
Chris

Error 42d during Fail Over SQL 2000 Enterprise in a Server 2003 Ent. Cluster

Hi All,
I've been struggling with this issue for a few days now.
We have 2node Server 2003 Entprise cluster with a fresh install of
MSSQL 2000 SP3a. Everything works great until I attempt to simluate a
node failure. Node 2 starts every service fine with the exception of
the 3 SQL services:
SQL Server
SQL Server Agent
SQL Server Fulltext
When I check the event log I see the following twice:
Event ID : 17052 [sqsrvres] StartResourceService: StartService
(MSSQLSERVER) failed. Error: 42d
Event ID : 17052 [sqsrvres] OnlineThread: ResUtilsStartResourceService
failed (status 42d)
Event ID : 17052 [sqsrvres] OnlineThread: Error 42d bringing resource
online.
Now I've read prior posts and have found that the 42d when converted
from HEX means that it's a logon failure. However I'm using the
Administrator account, have re-entered the username and password 3+
times and have checked all the domain security policies against this
account. I've even added Administrator to the "logon as service" and
three other priv's that Microsoft recommends.
I have rebooted the server numerous times now after each change and I
am still experiencing the same problem. What could I be missing?
I appreciate your time in this matter.
~Chris
Christopher,
Are you using the local "administrator" account to startup the services? If
so you should create a special domain account to startup your sql server
servcies on both servers and add this account to the Administrators local
group or to any other group with the restricted privileges you want.
Try from the working node, on the Enterprise manager to change the startup
account to the one which is currently working, doing from the EM should
change the account on all the nodes on the cluster.
Hope this helps.
Regards.
FR
|||On May 16, 5:26 pm, Frivas <Fri...@.discussions.microsoft.com> wrote:
> Christopher,
> Are you using the local "administrator" account to startup the services? If
> so you should create a special domain account to startup your sql server
> servcies on both servers and add this account to the Administrators local
> group or to any other group with the restricted privileges you want.
> Try from the working node, on the Enterprise manager to change the startup
> account to the one which is currently working, doing from the EM should
> change the account on all the nodes on the cluster.
> Hope this helps.
> Regards.
> FR
Greetings Frivas,
Thanks for your response. I am using the "Domain Administrator"
account. The software vendor the cluster is assembled for required
that we use that account unfortunately. However I will give your
recommendation a try and let you know my findings.
Thanks again.
~Chris
|||Look for the location of the SQL agent log file and make sure it is a
clustered resource in the SQL group. There is a bug in the
install/configure routines that allows the log file to be placed in an
incorrect cluster location.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<christophercnewton@.gmail.com> wrote in message
news:1179350437.951320.310920@.p77g2000hsh.googlegr oups.com...
> Hi All,
> I've been struggling with this issue for a few days now.
> We have 2node Server 2003 Entprise cluster with a fresh install of
> MSSQL 2000 SP3a. Everything works great until I attempt to simluate a
> node failure. Node 2 starts every service fine with the exception of
> the 3 SQL services:
> SQL Server
> SQL Server Agent
> SQL Server Fulltext
> When I check the event log I see the following twice:
> Event ID : 17052 [sqsrvres] StartResourceService: StartService
> (MSSQLSERVER) failed. Error: 42d
> Event ID : 17052 [sqsrvres] OnlineThread: ResUtilsStartResourceService
> failed (status 42d)
> Event ID : 17052 [sqsrvres] OnlineThread: Error 42d bringing resource
> online.
> Now I've read prior posts and have found that the 42d when converted
> from HEX means that it's a logon failure. However I'm using the
> Administrator account, have re-entered the username and password 3+
> times and have checked all the domain security policies against this
> account. I've even added Administrator to the "logon as service" and
> three other priv's that Microsoft recommends.
> I have rebooted the server numerous times now after each change and I
> am still experiencing the same problem. What could I be missing?
> I appreciate your time in this matter.
> ~Chris
>
|||Chris you should know that there is no technical reason to a domain admin
account. Usually people want a domain account so that they can run things
against an other machine. If you do create a service account and make is
local admin on the system and other systems that is needs to talk to you will
be fine. We run all our servers with service accounts that are just normal
users on the domain and the network, in some special cases that account in
local admin.
Good Luck
John Vandervliet
"christophercnewton@.gmail.com" wrote:

> On May 16, 5:26 pm, Frivas <Fri...@.discussions.microsoft.com> wrote:
>
> Greetings Frivas,
> Thanks for your response. I am using the "Domain Administrator"
> account. The software vendor the cluster is assembled for required
> that we use that account unfortunately. However I will give your
> recommendation a try and let you know my findings.
> Thanks again.
> ~Chris
>
|||Also make sure that if you have removed the BUILTIN\Administrators group as
a valid login that you add back the service account that operates the
Cluster service.
It does not need any special rights, but it will need to log into the
server.
On both cluster nodes, make sure that the SQL Server service account is a
domain account and has the following User Rights assignments:
Act as part of the OS
Bypass Traverse Checking
Lock Pages in Memory
Log on as a Batch Job
Log on as a Service
Replace a Process Level Token
http://support.microsoft.com/kb/283811
Anthony Thomas

"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:utHVUXImHHA.4752@.TK2MSFTNGP04.phx.gbl...
> Look for the location of the SQL agent log file and make sure it is a
> clustered resource in the SQL group. There is a bug in the
> install/configure routines that allows the log file to be placed in an
> incorrect cluster location.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> <christophercnewton@.gmail.com> wrote in message
> news:1179350437.951320.310920@.p77g2000hsh.googlegr oups.com...
>

error 40. Get the error locally, but not remotely!

I've got a windows 2003 server running sql server 2005. When I run a web site
on a desktop machine, there is no problem connecting to the database.
But, when I run the same web site with the same connection string on the
2003 server, I get the following error:
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server)
Obviously remote connections and tcp are enabled. My guess is that there is
something stopping IIS from connecting to the databse. The firewall is
disabled (not that it should interfere with local activities anyway).
I've been struggling with this one for a couple days. Any help would be
greatly appreciated.
The problem seemed to have fixed itself after I upgraded to Service Pack 1
for SQL 2005. I don't know if that was the problem.
"Trevor Murphy" wrote:

> I've got a windows 2003 server running sql server 2005. When I run a web site
> on a desktop machine, there is no problem connecting to the database.
> But, when I run the same web site with the same connection string on the
> 2003 server, I get the following error:
> An error has occurred while establishing a connection to the server. When
> connecting to SQL Server 2005, this failure may be caused by the fact that
> under the default settings SQL Server does not allow remote connections.
> (provider: Named Pipes Provider, error: 40 - Could not open a connection to
> SQL Server)
> Obviously remote connections and tcp are enabled. My guess is that there is
> something stopping IIS from connecting to the databse. The firewall is
> disabled (not that it should interfere with local activities anyway).
> I've been struggling with this one for a couple days. Any help would be
> greatly appreciated.

Error 3036 during Backup

I encounter this error during backup from a log shipping secondary server. I've set up log shipping between a production server and a secondary server, all work fine. Now I want to use the secondary server or the warm standby server to do complete databas
e backup every night, since I want to off load the backup from the production server, however, I get this error message for the DB backup job:
"
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft][ODBC SQL Server Driver][SQL Server]Database 'aspnetforums' is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire load sequence is comple
ted.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.
"
My settings are:
the restore job runs from 10:15am till 8:45 am next day, then the DB backup job starts at 9:10am. So the two jobs are not overlapping each other. However, even if I manually run the back up job at times after 9:10am, it fails with the same error message.
I am wondering if there is any extra setting I need to set to make it work.
Thank you for any help
You cannot backup a database that is in a non recovered state such as one
that you are log shipping too. Even if you recovered it and backed it up and
then restored it with no recovery, you would break the log chain
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Yi" <s_anyi@.yahoo.com> wrote in message
news:9C44B865-DF9C-4C0F-9814-95EA3EC1E1F7@.microsoft.com...
> I encounter this error during backup from a log shipping secondary server.
I've set up log shipping between a production server and a secondary server,
all work fine. Now I want to use the secondary server or the warm standby
server to do complete database backup every night, since I want to off load
the backup from the production server, however, I get this error message for
the DB backup job:
> "
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft][ODBC
SQL Server Driver][SQL Server]Database 'aspnetforums' is in warm-standby
state (set by executing RESTORE WITH STANDBY) and cannot be backed up until
the entire load sequence is completed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
terminating abnormally.
> "
> My settings are:
> the restore job runs from 10:15am till 8:45 am next day, then the DB
backup job starts at 9:10am. So the two jobs are not overlapping each other.
However, even if I manually run the back up job at times after 9:10am, it
fails with the same error message. I am wondering if there is any extra
setting I need to set to make it work.
> Thank you for any help
>
>
>

Error 21007 Cannot add remote distributor

Hello,
I've been trying to set up an msde database for replication. I've fixed
some problems but I can't get past thisone. I've read every topic about
this error but nothing worked.
I've set up msde with 'setup SAPWD="***"', I didn't set the instance
name or any other parameters.
Thanks.
Thijs
http://www.imm.be/
I've solved the problem.
I registered the server as 192.168... and not as 'servername' in the
enterprise manager.