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'

Here is the data flow

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.

That is curious, especially given that `d_date_key` doesn't exist in the supplied code. Any chance there's a trigger attached to the table? — billinkc, Jul 19 '17 at 17:49
Would you post the trigger definition? Click the Edit button and patch it into the question — billinkc, Jul 19 '17 at 17:54
I recreated the table without column description extended properties and the task works without error. So the question still stands, why does an OLE DB Command component care about extended properties? — Steve OShaughnessy, Jul 19 '17 at 17:56
UPDATE: I put the extended properties back in the d_date table schema. I then changed the task to write to a working table (working_d_date) with out the properties. This configuration fails with the same error this time from an Execute SQL Task component that is updating d_date by joining with the working table. — Steve OShaughnessy, Jul 19 '17 at 18:46
Agreed with your point, I just wanted to have enough info to try and repro it in my own environment. The OLE DB Command might have some property available to set in the advanced editor to address this but again, I'd have to go digging. The other approach that might be of interest is to avoid the OLE DB Command all together - it performs poorly (singleton operations). Instead, we generally favor staging the updates to a table and then performing a bulk update. See Incremental Load pattern http://www.sqlservercentral.com/articles/Stairway+Series/76390/ — billinkc, Jul 19 '17 at 18:49
This problem has now moved beyond SSIS components. I tried running the last update statement in SSMS and get the same error. Curiously, the UPDATE statement has 17 lines, but the error says its on line 37. I added the UPDATE statement and the error message to the original question. — Steve OShaughnessy, Jul 19 '17 at 18:50

score 1 · Answer 1 · answered Jul 25 '17 at 16:30

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 .

Why is OLE DB Command trying to update the schema?

1 Answers1