SP1 domain based attribute with change tracking enabled causes SYSERR245

SP1 domain based attribute with change tracking enabled causes SYSERR245 by furmangg

Closed

as Fixed

Type:	Bug
ID:	771245
Opened:	11/15/2012 7:27:45 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

After applying SQL2012 SP1, if you have a MDS model which contains a domain based attribute with change tracking enabled, when you try to load data via the staging table, you get the error:

SYSERR245|Conversion failed when converting the nvarchar value 'YourValueHere' to data type int.

Details (expand)

English

SQL Server 2012 - Enterprise Edition

Master Data Services

Windows Server 2008 R2 Enterprise (SP1)

US English

First unzip the attached. The deploy the .pkg file using the following command against a MDS 2012 SP1 instance:

"C:\Program Files\Microsoft SQL Server\110\Master Data Services\Configuration\MDSModelDeploy.exe" deploynew -package "C:\full path to\ChangeTrackingDbaBug.pkg" -model ChangeTrackingDbaBug

Then open the repro.sql script and run the script against the appropriate MasterDataServices database. You will get the error message mentioned above.

If you do a diff on stg.udp_ChangeTrackingDbaBugRepro_Leaf.11.0.2376.sql versus stg.udp_ChangeTrackingDbaBugRepro_Leaf.11.0.3000.sql you will see a new section of code with the comment:

-- Update change tracking mask.
-- This should be done before the values in EN table is updated.

If you look inside that loop, you'll see some code which reads:

AND COALESCE(NULLIF(stgl.' + quotename(@ChangedAttributeName) + N', en.' + quotename(@EntityAttributeName) + N'), NULLIF(en.' + quotename(@EntityAttributeName) + N', stgl.' + quotename(@ChangedAttributeName) + N')) IS NOT NULL;

When that string gets concatenated, the dynamic SQL reads:

AND COALESCE(NULLIF(stgl.[YesNo], en.[uda_150_5572]), NULLIF(en.[uda_150_5572], stgl.[YesNo])) IS NOT NULL;

Unfortunately, YesNo in the stage table is a nvarchar field, but the uda field is an int (since it's holding the integer ID of the YesNo domain). So that NULLIF function attempts to cast the nvarchar to an int, which fails.

see above

It shouldn't fail. Prior to SP1 it didn't fail.

X64

File Name	Submitted By	Submitted On	File Size
ChangeTrackingDbaBug.zip	furmangg	11/15/2012	24 KB

Posted by Microsoft on 1/30/2013 at 3:57 PM

Please see KB article
http://support.microsoft.com/kb/2790947
with link to download the update. Thank you for reporting or voting on the issue and your patience while we get the fix out.

Posted by Allxone on 12/4/2012 at 10:04 AM

I'm very disappointed by not having a temporary workaround. I had some troubles due to this bug and disabling Change Tracking is not an option for me. Looking at sp mdm.udpEntityStagingCreateLeafStoredProcedure I can see it is really a simple to solve bug, basically based on completely skipping the testing phase. Really strange it is included in a so called "highly tested service pack", probably MDS does not deserve so much attention by SQL Server testers.

Stefano

Posted by Microsoft on 11/27/2012 at 8:36 AM

Hi Greg,

Thanks for the feedback. We’re closing this issue as “Fixed” as we have identified the problem and will address it in the next post-SP1 cumulative update for SQL Server 2012.

Matthew

Posted by Sarang.Manohar on 4/8/2013 at 5:12 AM

Instead of changing the WHERE condition both the attributes may be converted to nvarchar to keep the overall condition intact.

Posted by furmangg on 11/15/2012 at 7:29 AM

Disable change tracking on all domain based attributes.