Why is OLE DB Command trying to update the schema?

Question

I have an SSIS data flow task that is taking lookup matched records and feeding them to an OLE DB Command component. When I run it I get an error that says:

Error: 0xC0202009 at Data Flow Task, OLE DB Command [28]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E14  Description: "Property cannot be added. Property 'MS_Description' already exists for 'dbo.d_date.d_date_key'.".

My SQL is:

UPDATE d_date 
SET company_year_period = ?, 
    company_quarter = ?,  
    company_year_quarter = ?, 
    company_quarter_year = ?, 
    company_first_day_of_week = ?, 
    company_last_day_of_week = ?,
    is_company_holiday = ?, 
    company_special_event = ?, 
    is_us_holiday = ?,
    us_special_event = ?, 
    is_canadian_holiday = ?, 
    canadian_special_event = ?, 
    is_mexican_holiday = ?, 
   mexican_special_event = ?
WHERE d_company_key = ?
AND calendar_date = ?

Why is this component/simple update messing with the extended properties?!?

To be complete here is the code I used when I created the table.

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key for the d_date table.  While it is a surrogate key it never the less has the form yyyymmdd.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'd_date', @level2type=N'COLUMN',@level2name=N'd_date_key'

CREATE TRIGGER d_date_update ON d_date
AFTER UPDATE
AS
    UPDATE d_date
    SET last_update = GETDATE()
    WHERE d_date_key IN (SELECT DISTINCT d_date_key FROM Inserted)
;

---

I have a table, d_date, with extended properties of MS_Description for each column. The table is populated with 2558 date records. I have a second, working table without the extended properties. It is also populated with some slightly different records. The following update fails:

UPDATE dd 
SET dd.company_year_period = wdd.company_year_period,
dd.company_quarter = wdd.company_quarter, 
dd.company_year_quarter = wdd.company_year_quarter, 
dd.company_quarter_year = wdd.company_quarter_year, 
dd.company_first_day_of_week = wdd.company_first_day_of_week, 
dd.company_last_day_of_week = wdd.company_last_day_of_week, 
dd.is_company_holiday = wdd.is_company_holiday, 
dd.company_special_event = wdd.company_special_event, 
dd.is_us_holiday = wdd.is_us_holiday, 
dd.us_special_event = wdd.us_special_event, 
dd.is_canadian_holiday = wdd.is_canadian_holiday, 
dd.canadian_special_event = wdd.canadian_special_event, 
dd.is_mexican_holiday = wdd.is_mexican_holiday, 
dd.mexican_special_event = wdd.mexican_special_event
FROM d_date AS dd
LEFT JOIN working_d_date AS wdd ON wdd.d_company_key = dd.d_company_key 
AND wdd.calendar_date = dd.calendar_date

(2558 row(s) affected)
Msg 15233, Level 16, State 1, Procedure sp_addextendedproperty, Line 37
Property cannot be added. Property 'MS_Description' already exists for 'dbo.d_date.d_date_key'.

---

FINAL COMMENT: I am running all this on SS2014 Enterprise which I have set up on my workstation using the trial license. I'm not a DBA so the odds are good that I don't have something set up correctly.

I moved everything over to our production SS2008 R2 server and I don't see any problems. It could be a problem between 2014 and 2008, but more likely some kind of setup problem with my 2014 installation. I'll revisit again when we formally install 2014.

Answer 1

Wild guess:

When you did Create Trigger you included the call to sp_addextendedproperty in the same batch..

CREATE TRIGGER d_date_update ON d_date
AFTER UPDATE
AS
    UPDATE d_date
    SET last_update = GETDATE()
    WHERE d_date_key IN (SELECT DISTINCT d_date_key FROM Inserted)


 ;
 --- THIS IS YOUR PROBLEM!!!! 
 --- This has been included in the trigger itself.
 EXEC sys.sp_addextendedproperty @name=N'MS_Description', 
   @value=N'Primary key for the d_date table.  While it is a surrogate key it never the less has the form yyyymmdd.' , 
       @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'd_date', @level2type=N'COLUMN',@level2name=N'd_date_key'

The trigger continues to the end of the batch. semicolon does not end the batch it only ends the statement. You must say GO to end the batch.

A bit more discussion: Scoping rules for SQL are not like C# at all. You might imagine that a trigger consists of a single statement, which can either be a block statement or a simple statement, which would be a natural assumption, but this is not the case.

After you say "CREATE TRIGGER", "CREATE PROCEDURE" or "CREATE FUNCTION", everything to the end of the batch is part of the trigger, proc or function .