September 11, 2007 at 3:31 pm
Hi, all! Just recently I started getting a very strange error when querying a view in one of my databases. When I perform a "select *..." against the view, all data comes back as expected. I can also query most columns and get the expected results. But certain columns appear to return the data for another column when queried individually. For example:
"select * from DallasMobile" returns valid results
"select RelatedLookupRecordNumberfrom DallasMobile" returns results for the column before RelatedLookupRecordNumber
At first I thought the view might have become corrupted, but I just found another case that is very disturbing. When I run the following query, I still get the results for the wrong column:
"Select Distinct RelatedLookupRecordNumber
From (Select ActionItemId,
RelatedLookupRecordNumber
From DallasMobile) a"
I was under the impression that if you nest a query the only data available to the outer select should be the results of the inner query. But for some reason this still returns the data for the column before RelatedLookupRecordNumber in the full view!
Does anyone have any idea what might be causing this?
September 12, 2007 at 1:44 am
Did you try sp_refreshview?
Perhaps the structure of your underlying table(s) has changed...
Ola
September 12, 2007 at 1:53 am
You should post the view definition here...Don't you have SELECT * inside the view?
September 12, 2007 at 8:17 am
The view is automatically generated by third-party software based on a web form definition we create and is defined as follows:
CREATE view [dbo].[DallasMobile] (
SalesResult,RejectionStage0,RejectionStage1,RejectionStage2,RejectionNotes,
DoNotContactReason,BusinessName,ProbabilityIndex,AccountType,FollowUpDateIndex,
StreetNumber,ESIID,NoOutcomeReason,FollowUpDate,SwapDiscountPct,Notes,Priority,
Result,ProbabilityToClose,SwapDiscountValue,FollowUpTime,SICDescription,
StreetName,LastUpdateDate,ContactFirstName,ContactLastName,TerritoryID,City,
EstimatedUsagekWh,FollowupContactFirstName,PriceperkW,PriceperkWh,
FollowupContactLastName,REP,State,JobTitle,ContactTelephoneNumber,Zip,
ContractExpirationDate,FollowUpNotes,PerkWhAveragePrice,NumberofTimesContacted,
LastDateWithTXUE,ContactFaxNumber,RelatedLocations,RelatedLookupRecordNumber,
ActionItemId, RecordId, CreatedAt, CreatedBy, LastModifiedAt, LastModifiedBy,
CurrentStateName)
with SCHEMABINDING
as select F_5,F_6,F_7,F_8,F_9,F_10,F_11,F_1,F_2,F_47,F_17,F_32,F_25,
CONVERT (datetime, case len(F_26) when 0 then null else F_26 end, 121),
CONVERT (money, case len(F_40) when 0 then null else F_40 end),
F_22,F_23,F_45,F_24,CONVERT (money, case len(F_41) when 0 then null else F_41 end),
CONVERT (datetime, case len(F_27) when 0 then null else F_27 end, 121),
F_33,F_18,CONVERT (datetime, case len(F_3) when 0 then null else F_3 end, 121),
F_12,F_13,F_4,F_19,CONVERT (money, case len(F_34) when 0 then null else F_34 end),
F_28,CONVERT (money, case len(F_42) when 0 then null else F_42 end),
CONVERT (money, case len(F_43) when 0 then null else F_43 end),
F_29,F_35,F_20,F_14,F_15,F_21,
CONVERT (datetime, case len(F_36) when 0 then null else F_36 end, 121),F_30,
CONVERT (money, case len(F_44) when 0 then null else F_44 end),F_31,
CONVERT (datetime, case len(F_37) when 0 then null else F_37 end, 121),
F_16,F_38,F_39, a.actionid, a.actionnum, a.created, a.creatorid, a.lastmodified,
a.lastsubmitterid, lc.stagename
from dbo.ActionData_37 ad,
dbo.action a,
dbo.lifecycle lc
where a.status = 1 and
a.actionid = ad.actionid and
ad.submstatus = 100 and
a.stagefid = lc.fid and
a.stageid = lc.stageid
Since it is schema-bound, sp_refreshview will not help. This view and others like it have worked reliably in our environment in the past, but only recently did this one appear to have problems.
- Mike
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply