Search code examples
sqlsql-serverviewscheduled-tasksjobs

Create job in sql server agent using view

I have a question If I can populate data to table by executing View in sql server agent using weekly job or is it only possible using stored procedure?

Job_Steps

Job_Failed

View1: 
ALTER VIEW [dbo].[Destinations_1415]

AS

select
sy.styr_student_id,'2014/15' 'Year',q.Surname
,q.Forename,q.Course,pit.prpi_title
,CASE WHEN q.Section = 'BATT' THEN 'BATS' ELSE q.Section collate Latin1_General_CI_AS END AS'Section'
,q.Funding_Type,q.Funding_Body,q.Partner
,c.GNIC_Description 'Outcome',c2.GNIC_Description 'Outcome_Specific'
,q.Completion_Status,sd.stud_mobile_telephone,sd.stud_home_telephone_no
,CASE WHEN sy.STYR_Age_end_Aug < 16 THEN '1416' WHEN sy.STYR_Age_end_Aug < 19 THEN '1618' ELSE '19+' END AgeBand
from sql10.ng.dbo.styrstudentyr sy
Inner join(
SELECT  [Partner Name] 'Partner',[Funding Body] 'Funding_Body'
,[Funding Line] 'Funding_Type', [LearnRefNumber] 'learnerrefnumber'
,ROW_NUMBER()OVER(Partition By LearnRefNumber, [funding line] Order By CompStatus) 'row_no'
,[FamilyName] 'Surname',[GivenNames] 'Forename',[Full Name]
,[ProvSpecDelMon_A] 'Course',[ProvSpecDelMon_B] 'Section',[LearnAimRef] 'Learning_Aim'
,[LearnAimRefTitle] 'Learning_Aim_Title',[FundModel] 'Fundmodel'
,[CompStatus] 'Completion_Status'
FROM [CollegeReporting].[dbo].[PFR_1415_tbl]
where [Funding Line] not like '%appre%'
union all
SELECT  [Partner Name] 'Partner',[Funding Body] 'Funding_Body'
,[Funding Line] 'Funding_Type',[LearnRefNumber] 'learnerrefnumber'
,ROW_NUMBER()OVER(Partition By LearnRefNumber, [funding line] Order By CompStatus) 'row_no'
,[FamilyName] 'Surname',[GivenNames] 'Forename',[Full Name]
,[ProvSpecDelMon_A] 'Course',[ProvSpecDelMon_B] 'Section',[LearnAimRef] 'Learning_Aim'
,[LearnAimRefTitle] 'Learning_Aim_Title',[FundModel] 'Fundmodel',[CompStatus] 'Completion_Status'
FROM [CollegeReporting].[dbo].[PFR_1415_tbl]
where [Funding Line]  like '%appre%'
and LearnAimRef = 'ZPROG001')q on q.learnerrefnumber collate SQL_Latin1_General_CP1_CI_AS = sy.styr_student_id collate SQL_Latin1_General_CP1_CI_AS and q.row_no = '1'
left join sql10.NG.dbo.STDPOutcome o on o.STDP_Student_ID = sy.styr_student_id and o.STDP_Course_Text = '2014'
left join sql10.ng.dbo.GNICodes c on c.GNIC_Code = STDP_Outcome_Type and c.GNIC_Type = 'OUT'
left join sql10.ng.dbo.GNICodes c2 on c2.GNIC_Code = STDP_Outcome_Code and STDP_Outcome_Type = c2.GNIC_Type
left join sql10.ng.dbo.studstudent sd on sd.stud_student_id = sy.styr_student_id
inner join sql10.ng.dbo.PRPIProvisionInstance pit on pit.prpi_code collate Latin1_General_CI_AS = q.Course collate Latin1_General_CI_AS and pit.prpi_instance = '141501'
where sy.styr_year = '2014'

View 2:

ALTER VIEW [dbo].[Destinations_1516]
AS
select
sy.styr_student_id,'2015/16' as 'Year'
,q.Surname,q.Forename,q.Course
,pit.prpi_title,q.Section collate Latin1_General_CI_AS 'Section',q.Funding_Type
,q.Funding_Body,q.Partner,c.GNIC_Description 'Outcome'
,c2.GNIC_Description 'Outcome_Specific',q.Completion_Status
,sd.stud_mobile_telephone,sd.stud_home_telephone_no
, CASE WHEN sy.STYR_Age_end_Aug < 16 THEN '1416' WHEN sy.STYR_Age_end_Aug < 19 THEN '1618' ELSE '19+' END AgeBand
from sql10.ng.dbo.styrstudentyr sy
Inner join(
SELECT  Partner 'Partner'
,[Funding Body] 'Funding_Body',[Funding Line] 'Funding_Type'
,[LearnRefNumber] 'learnerrefnumber'
,ROW_NUMBER()OVER(Partition By LearnRefNumber, [funding line] Order By CompStatus) 'row_no'
,[FamilyName] 'Surname',[GivenNames] 'Forename'
,[Full Name],[coursecode] 'Course'
,[section] 'Section',[LearnAimRef] 'Learning_Aim'
,[coursetitle] 'Learning_Aim_Title',[Fund Model] 'Fundmodel'
,[CompStatus] 'Completion_Status'
FROM [CollegeReporting].[dbo].[PFR_1516]
where [Funding Line] not like '%appre%'
union all
SELECT  Partner 'Partner',[Funding Body] 'Funding_Body',[Funding Line] 'Funding_Type'
,[LearnRefNumber] 'learnerrefnumber'
,ROW_NUMBER()OVER(Partition By LearnRefNumber, [funding line] Order By CompStatus) 'row_no'
,[FamilyName] 'Surname',[GivenNames] 'Forename'
,[Full Name],[coursecode] 'Course'
,[section] 'Section',[LearnAimRef] 'Learning_Aim'
,[coursetitle] 'Learning_Aim_Title',[Fund Model] 'Fundmodel'
,[CompStatus] 'Completion_Status'
FROM [CollegeReporting].[dbo].[PFR_1516]
where [Funding Line]  like '%appre%'
and LearnAimRef = 'ZPROG001')q on q.learnerrefnumber collate SQL_Latin1_General_CP1_CI_AS = sy.styr_student_id collate SQL_Latin1_General_CP1_CI_AS and q.row_no = '1'
left join sql10.NG.dbo.STDPOutcome o on o.STDP_Student_ID = sy.styr_student_id and o.STDP_Course_Text = '2015'
left join sql10.ng.dbo.GNICodes c on c.GNIC_Code = STDP_Outcome_Type and c.GNIC_Type = 'OUT'
left join sql10.ng.dbo.GNICodes c2 on c2.GNIC_Code = STDP_Outcome_Code and STDP_Outcome_Type = c2.GNIC_Type
left join sql10.ng.dbo.studstudent sd on sd.stud_student_id = sy.styr_student_id
inner join sql10.ng.dbo.PRPIProvisionInstance pit on pit.prpi_code collate Latin1_General_CI_AS = q.Course collate Latin1_General_CI_AS and pit.prpi_instance = '151601'
where sy.styr_year = '2015'

View 3 (Union of View 1 and View2 ): 

ALTER VIEW [dbo].[Destinations_1415_1516_Union]

AS

SELECT * FROM [dbo].[Destinations_1415] 

UNION

SELECT * FROM [dbo].[Destinations_1516]

Error:

The OLE DB provider "SQLNCLI10" for linked server "sql10" reported a change in schema version between compile time ("182244063332028") and run time ("182274129276679") for table ""ng"."dbo"."styrstudentyr"".

Please let me know.

Solution

  • http://www.sqlservercentral.com/Forums/Topic542765-145-1.aspx

    as mentioned - recreate views, recreate synonyms if you have any.

    Another thing - if you have select * in the view definition - you are guarantied to have issues with this view which will require to recreate this view periodically. For example when underlying view's or table's column list will change - no matter if a column was deleted, added, or changed it's place (column order). I can't remember right now a list of error messages - some of them are alike one you are getting. Something strange about compilation and object definitions. Some situations will not cause an exception but will give unexpected results.

    One more issue designed in your code - insert into without column list specified. You saved one or two minutes and did not write insert column order, did not write select column order but will have to waste much more time trying to identify why everything is going wrong.

    Performance question: why is there a UNION instead of UNION ALL? First view returns a constant column value for year similarly does second view and these constants are different - so why UNION? There are no rows able to be duplicates. Year column will always differ.

    1. Recreate 1st level views and synonyms
    2. Rewrite 2nd level views to avoid select * and unnecessary sort+distinct caused by union
    3. Rewrite insert with column list defined