Error 207: select

Showing posts with label select. Show all posts

Tuesday, March 27, 2012

Error 8650

In a normal select into from code, I encountered the intra-query parallelism
error. This code is being executed as a stored procedure in another code tha
t
executes about 5 different sprocs. My question where does one place a query
hint to rectify this error message?
Intra-query parallelism caused your server command (process ID #69) to
deadlock. Rerun the query without intra-query parallelism by using the query
hint option (maxdop 1).You can use the MAXDOP value of 1 as a query hint- see bol for proper use.
"learningSQL" wrote:

> In a normal select into from code, I encountered the intra-query paralleli
sm
> error. This code is being executed as a stored procedure in another code t
hat
> executes about 5 different sprocs. My question where does one place a quer
y
> hint to rectify this error message?
> Intra-query parallelism caused your server command (process ID #69) to
> deadlock. Rerun the query without intra-query parallelism by using the que
ry
> hint option (maxdop 1).

Error 8623

Hi,
When I execute an Insert form a select, SQL Server return the following
message
Server: Msg 8623, Level 16, State 1, Line 109
Internal Query Processor Error: The query processor could not produce a
query plan. Contact your primary support provider for more information.
I have search on technet and find the following article
(http://support.microsoft.com/defaul...kb;en-us;832412). It said to
put the FLAG 9136 on SQL Server.
I have search on Internet and don't find any information about this flag.
Procurei na internet e no encontrei informaes sobre este FLAG.
Does someone know any information about this error or about this flag?I found several KB's associated with that error such as this one:
http://support.microsoft.com/defaul...4&Product=sql2k
But it depends on what your doing. In any case I feel it's a bug and you
should contact MS PSS.
http://support.microsoft.com/defaul...id=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly
SQL Server MVP
"Thiago Campos Pereira" <tpereira@.attps.com.br> wrote in message
news:%23sH0wnkQEHA.2716@.tk2msftngp13.phx.gbl...
> Hi,
> When I execute an Insert form a select, SQL Server return the following
> message
> Server: Msg 8623, Level 16, State 1, Line 109
> Internal Query Processor Error: The query processor could not produce a
> query plan. Contact your primary support provider for more information.
>
> I have search on technet and find the following article
> (http://support.microsoft.com/defaul...kb;en-us;832412). It said
to
> put the FLAG 9136 on SQL Server.
> I have search on Internet and don't find any information about this flag.
> Procurei na internet e no encontrei informaes sobre este FLAG.
>
> Does someone know any information about this error or about this flag?
>

Error 8623

Hi,
When I execute an Insert form a select, SQL Server return the following
message
Server: Msg 8623, Level 16, State 1, Line 109
Internal Query Processor Error: The query processor could not produce a
query plan. Contact your primary support provider for more information.
I have search on TechNet and find the following article
(http://support.microsoft.com/default...b;en-us;832412). It said to
put the FLAG 9136 on SQL Server.
I have search on Internet and don't find any information about this flag.
Procurei na internet e no encontrei informaes sobre este FLAG.
Does someone know any information about this error or about this flag?
I found several KB's associated with that error such as this one:
http://support.microsoft.com/default...&Product=sql2k
But it depends on what your doing. In any case I feel it's a bug and you
should contact MS PSS.
http://support.microsoft.com/default...d=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly
SQL Server MVP
"Thiago Campos Pereira" <tpereira@.attps.com.br> wrote in message
news:%23sH0wnkQEHA.2716@.tk2msftngp13.phx.gbl...
> Hi,
> When I execute an Insert form a select, SQL Server return the following
> message
> Server: Msg 8623, Level 16, State 1, Line 109
> Internal Query Processor Error: The query processor could not produce a
> query plan. Contact your primary support provider for more information.
>
> I have search on TechNet and find the following article
> (http://support.microsoft.com/default...b;en-us;832412). It said
to
> put the FLAG 9136 on SQL Server.
> I have search on Internet and don't find any information about this flag.
> Procurei na internet e no encontrei informaes sobre este FLAG.
>
> Does someone know any information about this error or about this flag?
>

Error 8623

Hi,
When I execute an Insert form a select, SQL Server return the following
message
Server: Msg 8623, Level 16, State 1, Line 109
Internal Query Processor Error: The query processor could not produce a
query plan. Contact your primary support provider for more information.
I have search on TechNet and find the following article
(http://support.microsoft.com/default.aspx?scid=kb;en-us;832412). It said to
put the FLAG 9136 on SQL Server.
I have search on Internet and don't find any information about this flag.
Procurei na internet e não encontrei informações sobre este FLAG.
Does someone know any information about this error or about this flag?I found several KB's associated with that error such as this one:
http://support.microsoft.com/default.aspx?scid=kb;en-us;841404&Product=sql2k
But it depends on what your doing. In any case I feel it's a bug and you
should contact MS PSS.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
--
Andrew J. Kelly
SQL Server MVP
"Thiago Campos Pereira" <tpereira@.attps.com.br> wrote in message
news:%23sH0wnkQEHA.2716@.tk2msftngp13.phx.gbl...
> Hi,
> When I execute an Insert form a select, SQL Server return the following
> message
> Server: Msg 8623, Level 16, State 1, Line 109
> Internal Query Processor Error: The query processor could not produce a
> query plan. Contact your primary support provider for more information.
>
> I have search on TechNet and find the following article
> (http://support.microsoft.com/default.aspx?scid=kb;en-us;832412). It said
to
> put the FLAG 9136 on SQL Server.
> I have search on Internet and don't find any information about this flag.
> Procurei na internet e não encontrei informações sobre este FLAG.
>
> Does someone know any information about this error or about this flag?
>

Thursday, March 22, 2012

Error 8120 when selecting MAX(...)

Hi Folks,

I have a very strange problem with the MSSQL JDBC drivers (SP1).
I am calling executeQuery() on a very simple query:

SELECT MAX(fulltext_id) FROM contractor

I am getting an error 8120 (column is not contained in an aggregate-- see stack trace below). It thinks that 'id' is part of the column list, yet it is not.

Now, the strange thing is, when I run SQL Profiler, I see this line:

exec sp_cursoropen @.P1 output, N'SELECT MAX(fulltext_id) , * FROM contractor', @.P2 output, @.P3 output, @.P4 output

It seems that someone (the driver?) added a comma and a "*" after the MAX(fulltext_id) clause! What gives?

Thanks much,
Steve

===

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Column 'contractor.id' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest. openCursor(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.execute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQuery Internal(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQuery (Unknown Source)I don't know why the Java is throwing that * in there, but that is definitely what is causing the problem. That is not a valid SQL statement.

blindman|||Originally posted by blindman
I don't know why the Java is throwing that * in there, but that is definitely what is causing the problem. That is not a valid SQL statement.

blindman

Actually I found what was wrong. It appears I was opening the ResultSet as an updatable cursor. The SQL Server driver apparently tacks on a "*" to make the query select all rows (so any row in the query can be updated?)

I am still kinda confused, but opening the cursor as read-only fixed the problem... :?|||Originally posted by sehugg
Actually I found what was wrong. It appears I was opening the ResultSet as an updatable cursor. The SQL Server driver apparently tacks on a "*" to make the query select all rows (so any row in the query can be updated?)

I am still kinda confused, but opening the cursor as read-only fixed the problem... :?

Obviously a bug in the JDBC driver.

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
>

Wednesday, March 21, 2012

error '80004005' SQL7

Hello!
When I run this code:
[SELECT COUNT(DISTINCT UserLogin.companyid) AS
countCompanys, COUNT(UserLogin.id) AS countUsers,
UserLogin.AgentID, UserLogin1.OrderBy FROM UserLogin
INNER JOIN UserLogin UserLogin1 ON UserLogin.AgentID = UserLogin1.ID GROUP BY UserLogin.AgentID,
UserLogin1.OrderBy ORDER BY UserLogin1.OrderBy]
I get this message:
[Microsoft OLE DB Provider for ODBC Drivers
error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Process ID
7 attempting to unlock unowned resource PAG: 7:1:71555.]
I am using SQL7 with SP4.
Never had this problem before!
Thanks to any replyDid you try running DBCC CHECKDB and see if it points out any consistency or
other errors in your database?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"shimmy" <shimmy@.rampal.com> wrote in message
news:82b701c477ce$c7371260$a501280a@.phx.gbl...
> Hello!
> When I run this code:
> [SELECT COUNT(DISTINCT UserLogin.companyid) AS
> countCompanys, COUNT(UserLogin.id) AS countUsers,
> UserLogin.AgentID, UserLogin1.OrderBy FROM UserLogin
> INNER JOIN UserLogin UserLogin1 ON UserLogin.AgentID => UserLogin1.ID GROUP BY UserLogin.AgentID,
> UserLogin1.OrderBy ORDER BY UserLogin1.OrderBy]
> I get this message:
> [Microsoft OLE DB Provider for ODBC Drivers
> error '80004005'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Process ID
> 7 attempting to unlock unowned resource PAG: 7:1:71555.]
> I am using SQL7 with SP4.
> Never had this problem before!
> Thanks to any reply
>sql

Error 7410 when doing a SELECT from a Link table in xp_sendmail

Really would appreciate some advice on this problem. I have a cluster, CORPUSGA and a stand alone server CORPUSAPP22. CORPUSAPP22 has a linked server set up to CORPUSGA using 'sa' as the security context. Within a stored procedure I am running a SELECT to CORPUSGA.msdb.dbo.sysjobs.

When this is run from Query Analyzer it is fine and runs under the 'dbo' security context, but when is is run under xp_sendmail that uses the SQL Agent's security context (a domain account called
IntServiceGA) it fails with this message:

ODBC error 7410 (42000) Remote access not allowed for Windows NT user activated by SETUSER.

For the following command:

exec master.dbo.xp_sendmail
@.recipients = 'stelzner_eve@.emc.com;dabas_ravi@.emc.com;bergin_gi llian@.emc.com;lynch_eoin@.e
mc.com;sherman_ nancy@.emc.com',
@.message = @.email,
@.query = @.query_text,
@.no_header = 'true',
@.width=80,
@.subject = 'Job Failed on CORPUSGA ',
@.attach_results = 'true',
@.set_user = 'dbo'

Where @.query_text is something like "select * from CORPUSGA.msdb.dbo.sysjobs"

The IntServiceGA account is identical on both servers and is in the local administrators group on both servers and is in the system administrators role within SQL Server, has access to the database and sysjobs has granted permissions to IntServiceGA (SELECT, INSERT,UPDATE,DELETE).

When I run profiler against this, it tries to execute under IntServiceGA (an Windows authentication account) and then tries to SETUSER to sa . This is what it objects to the switching. I have tried to set up the IntServiceGA as the security context for the linked server too and this does not work, same error.

Any help would be great,
Thanks,
EveWhy do you need @.set_user if SQLAgent service account is already in sysadmin server role?|||I have another stored procedure that uses this and it works with the
@.set_user on this one. However, whether I leave it in or take out the
@.set_user parameter it still fails. Any ideas?

Thanks,
Eve|||Try to run your xp_sendmail after doing SETUSER 'DOMAIN_NAME\IntServiceGA' and see what you get.|||no go, using the SETUSER prior to the xp_sendmail still netted the
same results? Do you know what generates this message- NT or SQL?|||It's NT. It just proved that your SQLAgent account is lacking some rights. Was mail profile setup using SQLAgent service account?|||Yes, the profile was set up on both servers from the
corp\IntServiceGA account that is a local admin on
the both servers. Are there additional policies that need
to be put on for this account that are not in the default?

Error 7399 in Openquery. GetNextRow in Informix

When I execute a Select using Openquery against Informix, sometimes I receive the error below:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Informix][Informix ODBC Driver]Error in row.]
[OLE/DB provider returned message: sqlerrm(table_name)]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returned 0x80004005: ].

I noticed that the error is occuring when an user is Updating/Deleting or Inserting data in the table at the same time.

Environment:
Windows 2K
SQL Server 2000 with SP 3a
SDK Client 2.81 for Informix
ODBC 3.81 for Informix

Informix 7.3 running on a Unix machineHi,

Do you found any solution about this case? I have the same problem as you. Please send me information if you have some, this will be a big help for me.

Thank you and best regards,
ThinkMan|||Hi Thinkman,

Sorry, but I didnt find any solution for OUR problem.

Norivalsql

error 7391 in distributed transaction

I have a procedure running locally on my server A. This procedure does an
insert into a table after getting a return value from a select done on
another server say B . Server B is set up as linked server on server A. th
error i get shows as
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Has anyone seen this before? Is there any solution to this or any setting
that needs to be changed to get this working? any help will be greatly
appreciated.
TIA
Message posted via http://www.webservertalk.comFor distributed transaction you need to have a Primary key on updated table.
So pLease add primary key to resolve your issue. Please let me know if
doesn't work.
Regards,
Jagan Mohan
MCDBA
"ishaan99 via webservertalk.com" wrote:

> I have a procedure running locally on my server A. This procedure does an
> insert into a table after getting a return value from a select done on
> another server say B . Server B is set up as linked server on server A. th
> error i get shows as
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Has anyone seen this before? Is there any solution to this or any setting
> that needs to be changed to get this working? any help will be greatly
> appreciated.
> TIA
> --
> Message posted via http://www.webservertalk.com
>|||I've had this same error, mine only happened during times when transactions
were high on the system, I think there is a setting to increase the number o
f
concurrent distributed transactions, don't know what else you could do, I
know it has to do with MSDTC.
"ishaan99 via webservertalk.com" wrote:

> I have a procedure running locally on my server A. This procedure does an
> insert into a table after getting a return value from a select done on
> another server say B . Server B is set up as linked server on server A. th
> error i get shows as
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Has anyone seen this before? Is there any solution to this or any setting
> that needs to be changed to get this working? any help will be greatly
> appreciated.
> TIA
> --
> Message posted via http://www.webservertalk.com
>sql

Monday, March 19, 2012

Error 7105, how to fix it?

Hi
I am getting error 7105 Severity: 22 on one of my tables (about 1000
record), It seems 2 of the records has been corrupted. I can select other
records and edit the records as long as these 2 records are not part of
select query.
I have read microsoft support and they say that service pack2 should solve
this problem but my server already has service pack 3.
I can not choose these records to delete it, what is the solution, any idea?> I am getting error 7105 Severity: 22 on one of my tables (about 1000
> record), It seems 2 of the records has been corrupted. I can select other
> records and edit the records as long as these 2 records are not part of
> select query.
> I have read microsoft support and they say that service pack2 should solve
> this problem but my server already has service pack 3.
> I can not choose these records to delete it, what is the solution, any
idea?
What does DBCC CHECKDB report?
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Error 7105, how to fix it?

error 63 ??

Executing
insert into [NewDB]..DataTable select * from DataTable
sporadically returns
(EOleException): Could not find database ID 63. Database may not be
activated yet or may be in transition
Gory details:
We are copying the contents of about 10 tables each time to another
identical database.
It fails, or doesn't, seemingly randomly, on any of the tables.
Copy the insert into QueryAnalyzer and it runs perfectly.
It will sometimes fail even if we only copy one table.
It can copy the first 6 tables, say, and fail with this error on the seventh
table.
It tends to go in streaks, it will fail all day, on different tables at
different times,
then go away completely only to come back hours later.
We've never seen this error when run from QA.
Commenting out a table it is failing on, typically lets it run correctly the
next time
and then it fails the following time on a different table.
A pause or a sleep between the table copies has no effect.
Once it starts to fail on a table it typically continues to fail on that
table until it is commented out.
We are testing a tool that creates a mimic DB, copies data to it.
Since this is test first, the DB is created fresh every time and destroyed
afterwards.
These tests have been running fine for months and just started displaying
this error this w

.
--
TIA,
Brad.Brad,
Are you using DTS to copy the tables across? I wasn't sure what tool you
were using because you said "when we copy the query into Query Analyzer it
works fine." Does any of the target tables have a check constraint that use
s
a user defined function? Are you copying these across Linked servers?|||"Brad White" <bwhite at inebraska . com> wrote in message
news:O6Max7GbFHA.2996@.TK2MSFTNGP10.phx.gbl...
> Executing
> insert into [NewDB]..DataTable select * from DataTable
> sporadically returns
> (EOleException): Could not find database ID 63. Database may not be
> activated yet or may be in transition
>
Here is a typical table script for the new DB:
CREATE TABLE [DocTypeTestCriteria] (
[DocumentType] [smallint] NOT NULL ,
[TestOrder] [smallint] NOT NULL ,
[MICRType] [smallint] NOT NULL ,
[TestID] [smallint] NOT NULL ,
[TestValues] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RequireNext] [bit] NULL
) ON [PRIMARY]
I tried it with NULL instead of NOT NULL and it made no difference.
The main DB has referential integrity, etc. but the copy does not.
And here is the DB script: (path and DBname would be substituted before the
script is run)
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name =
N'%DatabaseName%')
DROP DATABASE [%DatabaseName%]
GO
CREATE DATABASE [%DatabaseName%] ON (NAME = N'%DatabaseName%_Data',
FILENAME = N'%ArchivePath%\%DatabaseName%.mdf') LOG ON (NAME =
N'%DatabaseName%_Log', FILENAME = N'%ArchivePath%\%DatabaseName%_log.ldf')
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
exec sp_dboption N'%DatabaseName%', N'autoclose', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'bulkcopy', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'trunc. log', N'true'
GO
exec sp_dboption N'%DatabaseName%', N'torn page detection', N'true'
GO
exec sp_dboption N'%DatabaseName%', N'read only', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'dbo use', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'single', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'autoshrink', N'true'
GO
exec sp_dboption N'%DatabaseName%', N'ANSI null default', N'true'
GO
exec sp_dboption N'%DatabaseName%', N'recursive triggers', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'ANSI nulls', N'true'
GO
exec sp_dboption N'%DatabaseName%', N'concat null yields null', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'cursor close on commit', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'default to local cursor', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'quoted identifier', N'true'
GO
exec sp_dboption N'%DatabaseName%', N'ANSI warnings', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'auto create statistics', N'true'
GO
exec sp_dboption N'%DatabaseName%', N'auto update statistics', N'true'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||"Cris_Benge" <CrisBenge@.discussions.microsoft.com> wrote in message
news:AE1DB944-B274-4B6E-A2D7-CCC2AB5F8950@.microsoft.com...
> Brad,
> Are you using DTS to copy the tables across?
No. Just the insert I specified.
Run from another executable through ADO.

> I wasn't sure what tool you
> were using because you said "when we copy the query into Query Analyzer it
> works fine."
I pull the actual query run from my log, copy it to QA and run it.

> Does any of the target tables have a check constraint that uses
> a user defined function?
No. Simple table with no constraints or refintegrity.

> Are you copying these across Linked servers?
Same server. Only different DB.
Could potentially be on a different partition, but right now, same drive.
Thanks for the response.
Brad.|||Stupid question - what service pack / SQL edition are you on? Can you show
me a sample of the offending INSERT statement?|||"Cris_Benge" <CrisBenge@.discussions.microsoft.com> wrote in message
news:600EF5C9-D0A9-4A89-B2E4-54A6490C68AA@.microsoft.com...
> Stupid question - what service pack / SQL edition are you on?
I don't see how that is a stupid question. 8:-)
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
MDAC version, 2.8

> Can you show me a sample of the offending INSERT statement?
The format is
insert into [NewDB]..DataTable select * from DataTable
so an actual example, copied directly from the log, is
insert into [CurrentStatementArchive]..DocumentTypes select * from
DocumentTypes
We have 61 databases, this process creates 2 new, and the error complains
about
not finding the DB with ID 63, which would probably be the correct ID.
--
Thanks for sticking with me on this,
Brad White

Friday, March 9, 2012

error 4606 Granted or Revoked privilege

I am recieving error 4606 Granted or revoked privilege SELECT is not compatiable with object.

Canot read the next data row for the dataset BEEP.

However when I use the same userid/pwsd in Crystal to access the same stored proceedure I have no problems accessing the data.

We have already tried re-granting the permissions to the userid to no avail.

Are there any other suggestions?

Phread

We have tried using the SA account also to no avail within SRS.

Both accounts work under Crystal.

Phread

Error 444 in UDF

Has anyone seen this error before?
(Am attempting to make my first UDF so is probably very basic.)
Error 444: Select statements within a function cannot return data to a client
This is the code:
CREATE FUNCTION udfActiveCaseSum
/******************************************************************************
** Eagle User Defined Function
** View Name: udfActiveCaseSum
** Description:
** Author:
** Date:
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -- -- ---
**
*******************************************************************************/
(
@.Other INT,
@.asofdate datetime,
@.CourtId int,
@.PersonID int
)
RETURNS INT
AS
BEGIN
SELECT distinct
ViewOrgPerson.PersonId,
ViewOrgPerson.FirstName,
ViewOrgPerson.LastName,
--ViewOrgPerson.RoleShort,
CASE TopRole.RoleCodeId WHEN 9 THEN 'MGMT' WHEN 8 THEN 'STAFF' ELSE 'XERROR' END as RoleShort,
Petition.CourtId,
Court.NameShort,
SUM(CASE WHEN Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('N') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('NA') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('N') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('NA') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('EP') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('EP') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('DV') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('ES') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('MD') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('NV') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('TP') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VA') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VD') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VS') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('XS') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('PH') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('DV') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('ES') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('MD') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('NV') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('TP') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VA') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VD') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VS') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('XS') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('PH') AND
Petition.DocketPrefix <> 'B' AND PetitionCode.PetitionShort NOT IN ('B') AND
Petition.DocketPrefix <> 'B' AND PetitionCode.PetitionShort NOT IN ('SR') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('SR') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('SR') AND
Petition.DocketPrefix <> 'V' AND PetitionCode.PetitionShort NOT IN ('V') AND
Petition.DocketPrefix <> 'S' AND PetitionCode.PetitionShort NOT IN ('<>S') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('D') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('DE') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('E') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('DE') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('EP') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('EP') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('MD') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('TP') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('TR') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VA') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VC') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VP') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('XP') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('PH') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('MD') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('TP') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('TR') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VA') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VC') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VP') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('XP') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('PH') AND
Petition.DocketPrefix <> 'K' AND PetitionCode.PetitionShort NOT IN ('K') AND
Petition.DocketPrefix <> 'K' AND PetitionCode.PetitionShort NOT IN ('KR') AND
Petition.DocketPrefix <> 'L' AND PetitionCode.PetitionShort NOT IN ('L')
THEN 1 ELSE 0 END) AS Other
FROM ViewOrgPerson with (noexpand)
INNER JOIN
Agent ON
ViewOrgPerson.OrgId = Agent.OrgId AND
ViewOrgPerson.PersonId = Agent.PersonId AND
-- ViewOrgPerson.RoleCodeId = Agent.RoleCodeId and
ViewOrgPerson.RoleShort IN ( 'LGS', 'LG') AND
ViewOrgPerson.CourtId = @.CourtId
inner join(
Select max (ViewOrgPerson.RoleCodeId) RoleCodeId , ViewOrgPerson.PersonId from
ViewOrgPerson WITH (NOEXPAND)
group by ViewOrgPerson.PersonId
)TopRole
on
TopRole.PersonId = Agent.PersonId
INNER JOIN
Petition ON Agent.PetitionId = Petition.PetitionId
INNER JOIN
Court ON Petition.CourtId = Court.CourtId and
Petition.CourtId = @.courtID
INNER JOIN
PetitionCode ON Petition.PetitionCodeId = PetitionCode.PetitionCodeId INNER JOIN
Appearance ON Petition.PetitionId = Appearance.PetitionId
and Appearance.EVENTDate >@.asofdate
--GROUP BY ViewOrgPerson.PersonId,
WHERE ViewOrgPerson.PersonId = @.PersonID
/**
ViewOrgPerson.FirstName,
ViewOrgPerson.LastName,
ViewOrgPerson.RoleShort,
TopRole.RoleCodeId,
Petition.CourtId,
Court.NameShort
**/
RETURN @.Other
ENDAndrew,
You have a SELECT statement which returns a resultset inside a scalar
function, can't have that. It would be like each invocation of SUBSTRING()
would return a table.
What type of function do you want to write? A scalar or a table function?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Andrew" <anonymous@.discussions.microsoft.com> wrote in message
news:8D4D2A43-0490-40CA-901B-B42DFEEA3BA8@.microsoft.com...
> Has anyone seen this error before?
> (Am attempting to make my first UDF so is probably very basic.)
> Error 444: Select statements within a function cannot return data to a
client
> This is the code:
> CREATE FUNCTION udfActiveCaseSum
>
/***************************************************************************
***
> ** Eagle User Defined Function
> ** View Name: udfActiveCaseSum
> ** Description:
> ** Author:
> ** Date:
>
****************************************************************************
***
> ** Change History
>
****************************************************************************
***
> ** Date: Author: Description:
> ** -- -- ---
> **
>
****************************************************************************
***/
> (
> @.Other INT,
> @.asofdate datetime,
> @.CourtId int,
> @.PersonID int
> )
> RETURNS INT
> AS
> BEGIN
> SELECT distinct
> ViewOrgPerson.PersonId,
> ViewOrgPerson.FirstName,
> ViewOrgPerson.LastName,
> --ViewOrgPerson.RoleShort,
> CASE TopRole.RoleCodeId WHEN 9 THEN 'MGMT' WHEN 8 THEN 'STAFF' ELSE
'XERROR' END as RoleShort,
> Petition.CourtId,
> Court.NameShort,
>
> SUM(CASE WHEN Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort
NOT IN ('N') AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('NA')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('N')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('NA')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('DV')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('ES')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('NV')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VD')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VS')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('XS')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('DV')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('ES')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('NV')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VD')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VS')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('XS')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'B' AND PetitionCode.PetitionShort NOT IN ('B')
AND
> Petition.DocketPrefix <> 'B' AND PetitionCode.PetitionShort NOT IN ('SR')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('SR')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('SR')
AND
> Petition.DocketPrefix <> 'V' AND PetitionCode.PetitionShort NOT IN ('V')
AND
> Petition.DocketPrefix <> 'S' AND PetitionCode.PetitionShort NOT IN ('<>S')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('D')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('DE')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('E')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('DE')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('TR')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VC')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('XP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('TR')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VC')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('XP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'K' AND PetitionCode.PetitionShort NOT IN ('K')
AND
> Petition.DocketPrefix <> 'K' AND PetitionCode.PetitionShort NOT IN ('KR')
AND
> Petition.DocketPrefix <> 'L' AND PetitionCode.PetitionShort NOT IN ('L')
>
> THEN 1 ELSE 0 END) AS Other
> FROM ViewOrgPerson with (noexpand)
> INNER JOIN
> Agent ON
> ViewOrgPerson.OrgId = Agent.OrgId AND
> ViewOrgPerson.PersonId = Agent.PersonId AND
> -- ViewOrgPerson.RoleCodeId = Agent.RoleCodeId and
> ViewOrgPerson.RoleShort IN ( 'LGS', 'LG') AND
> ViewOrgPerson.CourtId = @.CourtId
> inner join(
> Select max (ViewOrgPerson.RoleCodeId) RoleCodeId ,
ViewOrgPerson.PersonId from
> ViewOrgPerson WITH (NOEXPAND)
> group by ViewOrgPerson.PersonId
> )TopRole
> on
> TopRole.PersonId = Agent.PersonId
> INNER JOIN
> Petition ON Agent.PetitionId = Petition.PetitionId
> INNER JOIN
> Court ON Petition.CourtId = Court.CourtId and
> Petition.CourtId = @.courtID
> INNER JOIN
> PetitionCode ON Petition.PetitionCodeId =PetitionCode.PetitionCodeId INNER JOIN
> Appearance ON Petition.PetitionId =Appearance.PetitionId
> and Appearance.EVENTDate >@.asofdate
> --GROUP BY ViewOrgPerson.PersonId,
> WHERE ViewOrgPerson.PersonId = @.PersonID
> /**
> ViewOrgPerson.FirstName,
> ViewOrgPerson.LastName,
> ViewOrgPerson.RoleShort,
> TopRole.RoleCodeId,
> Petition.CourtId,
> Court.NameShort
> **/
> RETURN @.Other
> END
>
>
>|||Thanks for the reply! Am not clear on the difference between the two - Basically have a large query that produces a report. Need to add functionality to catch all not listed in an "Other" column - Am trying to use a UDF to keep all the "<> this" and "<> that" code out of the calling proc.|||Change your udf to a table valued function ie..
create func myfun (parameters)
returns table
as
return( select.... )
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Andrew" <anonymous@.discussions.microsoft.com> wrote in message
news:8D4D2A43-0490-40CA-901B-B42DFEEA3BA8@.microsoft.com...
> Has anyone seen this error before?
> (Am attempting to make my first UDF so is probably very basic.)
> Error 444: Select statements within a function cannot return data to a
client
> This is the code:
> CREATE FUNCTION udfActiveCaseSum
>
/***************************************************************************
***
> ** Eagle User Defined Function
> ** View Name: udfActiveCaseSum
> ** Description:
> ** Author:
> ** Date:
>
****************************************************************************
***
> ** Change History
>
****************************************************************************
***
> ** Date: Author: Description:
> ** -- -- ---
> **
>
****************************************************************************
***/
> (
> @.Other INT,
> @.asofdate datetime,
> @.CourtId int,
> @.PersonID int
> )
> RETURNS INT
> AS
> BEGIN
> SELECT distinct
> ViewOrgPerson.PersonId,
> ViewOrgPerson.FirstName,
> ViewOrgPerson.LastName,
> --ViewOrgPerson.RoleShort,
> CASE TopRole.RoleCodeId WHEN 9 THEN 'MGMT' WHEN 8 THEN 'STAFF' ELSE
'XERROR' END as RoleShort,
> Petition.CourtId,
> Court.NameShort,
>
> SUM(CASE WHEN Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort
NOT IN ('N') AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('NA')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('N')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('NA')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('DV')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('ES')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('NV')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VD')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VS')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('XS')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('DV')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('ES')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('NV')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VD')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VS')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('XS')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'B' AND PetitionCode.PetitionShort NOT IN ('B')
AND
> Petition.DocketPrefix <> 'B' AND PetitionCode.PetitionShort NOT IN ('SR')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('SR')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('SR')
AND
> Petition.DocketPrefix <> 'V' AND PetitionCode.PetitionShort NOT IN ('V')
AND
> Petition.DocketPrefix <> 'S' AND PetitionCode.PetitionShort NOT IN ('<>S')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('D')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('DE')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('E')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('DE')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('TR')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VC')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('XP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('TR')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VC')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('XP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'K' AND PetitionCode.PetitionShort NOT IN ('K')
AND
> Petition.DocketPrefix <> 'K' AND PetitionCode.PetitionShort NOT IN ('KR')
AND
> Petition.DocketPrefix <> 'L' AND PetitionCode.PetitionShort NOT IN ('L')
>
> THEN 1 ELSE 0 END) AS Other
> FROM ViewOrgPerson with (noexpand)
> INNER JOIN
> Agent ON
> ViewOrgPerson.OrgId = Agent.OrgId AND
> ViewOrgPerson.PersonId = Agent.PersonId AND
> -- ViewOrgPerson.RoleCodeId = Agent.RoleCodeId and
> ViewOrgPerson.RoleShort IN ( 'LGS', 'LG') AND
> ViewOrgPerson.CourtId = @.CourtId
> inner join(
> Select max (ViewOrgPerson.RoleCodeId) RoleCodeId ,
ViewOrgPerson.PersonId from
> ViewOrgPerson WITH (NOEXPAND)
> group by ViewOrgPerson.PersonId
> )TopRole
> on
> TopRole.PersonId = Agent.PersonId
> INNER JOIN
> Petition ON Agent.PetitionId = Petition.PetitionId
> INNER JOIN
> Court ON Petition.CourtId = Court.CourtId and
> Petition.CourtId = @.courtID
> INNER JOIN
> PetitionCode ON Petition.PetitionCodeId =PetitionCode.PetitionCodeId INNER JOIN
> Appearance ON Petition.PetitionId =Appearance.PetitionId
> and Appearance.EVENTDate >@.asofdate
> --GROUP BY ViewOrgPerson.PersonId,
> WHERE ViewOrgPerson.PersonId = @.PersonID
> /**
> ViewOrgPerson.FirstName,
> ViewOrgPerson.LastName,
> ViewOrgPerson.RoleShort,
> TopRole.RoleCodeId,
> Petition.CourtId,
> Court.NameShort
> **/
> RETURN @.Other
> END
>
>
>|||Is you want to return a table so you can use in a FROM clause of a SELECT
statement, then you want a table valued function. Check out Wayne's post.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Andrew" <anonymous@.discussions.microsoft.com> wrote in message
news:88954E4A-74AF-47AE-8F5C-D30CDD04B627@.microsoft.com...
> Thanks for the reply! Am not clear on the difference between the two -
Basically have a large query that produces a report. Need to add
functionality to catch all not listed in an "Other" column - Am trying to
use a UDF to keep all the "<> this" and "<> that" code out of the calling
proc.

Tuesday, March 27, 2012

Error 8650

Error 8623

Error 8623

Error 8623

Thursday, March 22, 2012

Error 8120 when selecting MAX(...)

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

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

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

Wednesday, March 21, 2012

error '80004005' SQL7

Error 7410 when doing a SELECT from a Link table in xp_sendmail

Error 7399 in Openquery. GetNextRow in Informix

error 7391 in distributed transaction

Monday, March 19, 2012

Error 7105, how to fix it?

Error 7105, how to fix it?

error 63 ??

Friday, March 9, 2012

error 4606 Granted or Revoked privilege

Error 444 in UDF

Error 207

Blog Archive

About Me