I have scripts for adding columns to tables which worked.
When I run scripts to alter views with the new column the script fails because the columns are not recognized
Msg 207, Level 16, State 1, Procedure UniqueTempDispositions, Line 76
Invalid column name 'servicerequestid'.
Msg 207, Level 16, State 1, Procedure UniqueTempDispositions, Line 47
Invalid column name 'servicerequestid'.
Msg 207, Level 16, State 1, Procedure MergeDispositions, Line 54
Invalid column name 'ServiceRequestID'.
Msg 207, Level 16, State 1, Procedure NonPIICachedDispositions, Line 18
Invalid column name 'ServiceRequestID'.
Any reason why? Am I missing something?
I've started and stopped the server, I've relogged in to no avail. ORIGINAL SCRIPT:
alter view
dbo.UniqueTempDispositions
as
SELECT QuotaGroup, Country, ServiceGroup, Language, ContactChannel, TrackingID, CONVERT(DATETIME, CaseClosedDate) AS casecloseddate, MSFTRep, CustEmail,
CustPhone, CustomerName, ProductFamily, ProductSubType, CONVERT(DATETIME, CandidateReceivedDate) AS candidatereceiveddate, CONVERT(INT, SurveyMode)
AS surveymode, CONVERT(DATETIME, SurveyWaveStartDate) AS surveywavestartdate, CONVERT(DATETIME, SurveyInvitationDate) AS surveyinvitationdate,
CONVERT(DATETIME, SurveyReminderDate) AS surveyreminderdate, CONVERT(DATETIME, SurveyCompleteDate) AS surveycompletedate, CONVERT(DATETIME,
OptOutDate) AS optoutdate, CONVERT(DATETIME, SurveyWaveEndDate) AS surveywaveenddate, CONVERT(INT, DispositionCode) AS dispositioncode, SurveyName,
SurveyVendor, COUNT(*) AS countofunique, BusinessUnitName, UploadId, MIN(LineNumber) AS LineNumber, BusinessUnitSubgroup, ServiceRequestID
FROM DispositionReporting.dbo.tempDispositions AS td
GROUP BY QuotaGroup, Country, ServiceGroup, Language, ContactChannel, TrackingID, CONVERT(DATETIME, CaseClosedDate), MSFTRep, CustEmail, CustPhone,
CustomerName, ProductFamily, ProductSubType, CONVERT(DATETIME, CandidateReceivedDate), CONVERT(INT, SurveyMode), CONVERT(DATETIME,
SurveyWaveStartDate), CONVERT(DATETIME, SurveyInvitationDate), CONVERT(DATETIME, SurveyReminderDate), CONVERT(DATETIME, SurveyCompleteDate),
CONVERT(DATETIME, OptOutDate), CONVERT(DATETIME, SurveyWaveEndDate), CONVERT(INT, DispositionCode), SurveyName, SurveyVendor, BusinessUnitName,
UploadId, BusinessUnitSubgroup, ServiceRequestID
go
I ended up dropping and re-adding the view which worked, just don't understand the other way around:
use DispositionReporting
go
drop view dbo.UniqueTempDispositions
go
create view
dbo.UniqueTempDispositions
as
SELECT QuotaGroup, Country, ServiceGroup, Language, ContactChannel, TrackingID, CONVERT(DATETIME, CaseClosedDate) AS casecloseddate, MSFTRep, CustEmail,
CustPhone, CustomerName, ProductFamily, ProductSubType, CONVERT(DATETIME, CandidateReceivedDate) AS candidatereceiveddate, CONVERT(INT, SurveyMode)
AS surveymode, CONVERT(DATETIME, SurveyWaveStartDate) AS surveywavestartdate, CONVERT(DATETIME, SurveyInvitationDate) AS surveyinvitationdate,
CONVERT(DATETIME, SurveyReminderDate) AS surveyreminderdate, CONVERT(DATETIME, SurveyCompleteDate) AS surveycompletedate, CONVERT(DATETIME,
OptOutDate) AS optoutdate, CONVERT(DATETIME, SurveyWaveEndDate) AS surveywaveenddate, CONVERT(INT, DispositionCode) AS dispositioncode, SurveyName,
SurveyVendor, COUNT(*) AS countofunique, BusinessUnitName, UploadId, MIN(LineNumber) AS LineNumber, BusinessUnitSubgroup, ServiceRequestID
FROM DispositionReporting.dbo.tempDispositions AS td
GROUP BY QuotaGroup, Country, ServiceGroup, Language, ContactChannel, TrackingID, CONVERT(DATETIME, CaseClosedDate), MSFTRep, CustEmail, CustPhone,
CustomerName, ProductFamily, ProductSubType, CONVERT(DATETIME, CandidateReceivedDate), CONVERT(INT, SurveyMode), CONVERT(DATETIME,
SurveyWaveStartDate), CONVERT(DATETIME, SurveyInvitationDate), CONVERT(DATETIME, SurveyReminderDate), CONVERT(DATETIME, SurveyCompleteDate),
CONVERT(DATETIME, OptOutDate), CONVERT(DATETIME, SurveyWaveEndDate), CONVERT(INT, DispositionCode), SurveyName, SurveyVendor, BusinessUnitName,
UploadId, BusinessUnitSubgroup, ServiceRequestID
go
exec sp_refreshview 'dbo.UniqueTempDispositions'
go