Odd behaviour from sys.dependencies

  • (SQL Server 2008 R2 SP1)

    Hi all, I have an SSIS package that dynamically builds a set of SQL statements based on the dependencies in a set of procs. The SSIS package makes use of the dm function sys.dm_sql_referenced_entities to build up a distinct list of columns required from the source system. The code is run against every proc in the database into a working table, then a distinct list is used to pick up data from the source system.

    We've been testing this for weeks and weeks in UAT, the day we're going live, UAT has fallen over with a really odd issue :crazy:. Basically, very randomly, the values in the ImportColumn are presented with some characters returned with unexpected characters, only ever the first 4 digits, it can be different rows from the resultset and just to make it more complicated how often it returns is completely random.

    As the SSIS package iterates through each table, I've lifted one of the scripts it builds to explain, this is extracting dependencies from a proc called staging.uspInstrumentHolding.

    SELECT DISTINCT

    Ref.referenced_database_name AS ImportDatabase

    ,Ref.referenced_schema_name AS ImportViewSchemaName

    ,SUBSTRING(Ref.referenced_entity_name,

    (CHARINDEX('_', Ref.referenced_entity_name) + 1),

    LEN(Ref.referenced_entity_name)) AS TableName

    ,SUBSTRING(Ref.referenced_entity_name, 1,

    (CHARINDEX('_', Ref.referenced_entity_name, 1) - 1)) AS ConnectionName

    ,Ref.referenced_minor_name AS ImportColumn

    FROM

    sys.dm_sql_referenced_entities('Staging' + '.' + 'uspInstrumentHolding',

    'OBJECT') AS Ref

    WHERE

    1 = 1

    AND Ref.referenced_minor_name IS NOT NULL

    AND ref.referenced_schema_name = 'import'

    The values that return in a strange state are only from the ImportColumn. I can run the script 7-8 times accurately, then the results will appear like this:

    UNPOSTED

    ꀨ⃗가⃗RL

    ꀨ⃗가⃗AN

    PCCVRT

    䀨噠RE

    䀨噠RT

    䀨噠EF

    䀨啨UV

    䀨啨RT

    䀨䒰TION

    䀨䒰VT

    䀨䒰EF

    䀨䒰LT

    䀨䎰RL

    䀨䎰CTION

    䒰忰AL

    䀨亰UP

    䶸忰F

    ꀨ⃗ꗐ⃗TE

    ꀨ⃗ꗐ⃗T

    ꓘ⃗뿰⃗TL

    Next time I run them, they look like this:

    UNPOSTED

    UNSERL

    UNTRAN

    PCCVRT

    PCDIRE

    PCINRT

    PCIREF

    PCORUV

    PCSRRT

    PDACTION

    PDINVT

    PDIREF

    PDMULT

    PDSERL

    PDUACTION

    PDUVAL

    RPCOUP

    RPREF

    RBDATE

    RBRAT

    RBTITL

    As you can see, only SOME of the values have returned in this state (and which values are returned in this state changes), it is only ever the first 4 characters and how often it occurs changes.

    It's just so random, we're yet to identify a trend or reason for this, can anyone offer any suggestions?

    Thanks

    JJ

  • Hi JJ,

    I have two comments to your issue.

    1 - I had to look up this dm on books on line since I never used it. The sample from BOL works fine.

    USE AdventureWorks2012;

    GO

    SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name,

    referenced_minor_id, referenced_class_desc, is_caller_dependent, is_ambiguous, referenced_minor_name

    FROM sys.dm_sql_referenced_entities ('dbo.ufnGetContactInformation', 'OBJECT');

    GO

    If I take your code and replace the object name with the BOL sample, it does not work.

    -- This is due to the fact the code does not handle a name that does not have a underscore, second sub string calculation. Some defensive programming will make the solution portable.

    2 - Taking a quick look at your post, it looks like that field is in another collation sequence / code page.

    -- Check the code page on the database.

    3 - From BOL, the field is defined as the following.

    Column name when the referenced entity is a column; otherwise NULL. For example, referenced_minor_name is NULL in the row that lists the referenced entity itself.

    A referenced entity is a column when a column is identified by name in the referencing entity, or when the parent entity is used in a SELECT * statement.

    -- I would do a DBCC on the database to make sure there are not system issues with the database.

    Thanks again for your post, I always learn something from replying to them.

    Tell me how you make out.

    Sincerely

    John

    John Miner
    Crafty DBA
    www.craftydba.com

  • j.miner (4/4/2013)


    If I take your code and replace the object name with the BOL sample, it does not work.

    That's a fair comment, and one I should have explained. As a standard, all our "import" tables follow the notation of [source system]_[source table name] to allow for easy identification. In this case, I'm simply splitting info to two columns. So it works for me, it's not necessarily going to work for you, commenting out those two columns still causes the same issue.

    j.miner (4/4/2013)


    Taking a quick look at your post, it looks like that field is in another collation sequence / code page.

    Well that was my initial reaction, but one I couldn't explain because this code has been running on the same server for about 7 weeks. In addition, would that not mean it would return this characters EVERY time I ran it? This is what has baffled me here. Will do some digging on Server / Database / Column collation settings.

    j.miner (4/4/2013)


    I would do a DBCC on the database to make sure there are not system issues with the database.

    🙂 Great minds think alike! Did this straight away, no issues to report.

    The thing that has thrown me is the randomness, it happens once every 15-30 times and only then SOME columns and only then just the first 4 characters!

    One thing I did do today, is back the database up and restore it on a DEV server to run the same code and it had no issues. I am resorting to rebuilding all databases affected and rerunning the process from scratch EDITED TO ADD: I will however rename the existing database and retain it so at least I can recreate (and therefore continue to troubleshoot) the error.

    Will update on the collation settings...

  • Server Collation: Latin1_General_CI_AS

    Database Collation: Latin1_General_CI_AS

    I'm assuming you were after collation of the column that is returned in the result set?

  • Ok, done some investigation on column collation. I've cross referenced sys.columns on the object_id and column_id like so:

    SELECT DISTINCT

    Ref.referenced_database_name AS ImportDatabase

    ,Ref.referenced_schema_name AS ImportViewSchemaName

    ,SUBSTRING(Ref.referenced_entity_name,

    (CHARINDEX('_', Ref.referenced_entity_name) + 1),

    LEN(Ref.referenced_entity_name)) AS TableName

    ,SUBSTRING(Ref.referenced_entity_name, 1,

    (CHARINDEX('_', Ref.referenced_entity_name, 1) - 1)) AS ConnectionName

    ,Ref.referenced_minor_name AS ImportColumn

    ,collation_name

    FROM

    sys.dm_sql_referenced_entities('Staging' + '.' + 'uspInstrumentHolding',

    'OBJECT') AS Ref

    LEFT JOIN sys.columns sysc

    ON COLUMNPROPERTY(OBJECT_ID(Ref.referenced_schema_name + '.'

    + Ref.referenced_entity_name),

    Ref.referenced_minor_name, 'ColumnId') = sysc.column_id

    AND OBJECT_ID(Ref.referenced_schema_name + '.'

    + Ref.referenced_entity_name) = sysc.[object_id]

    WHERE

    1 = 1

    AND Ref.referenced_minor_name IS NOT NULL

    AND ref.referenced_schema_name = 'import'

    Which returns the following when all values are populated correctly:

    ImportColumncollation_name

    PCCVRTLatin1_General_CI_AS

    PCDIRELatin1_General_CI_AS

    PCINRTLatin1_General_CI_AS

    PCIREFNULL

    PCORUVNULL

    PCSRRTLatin1_General_CI_AS

    PDACTIONLatin1_General_CI_AS

    PDINVTNULL

    PDIREFNULL

    PDMULTNULL

    PDSERLNULL

    PDUACTIONNULL

    PDUVALNULL

    RPCOUPNULL

    RPREFNULL

    RBDATENULL

    RBRATNULL

    RBTITLLatin1_General_CI_AS

    UNAMOUNTNULL

    UNINSTREFNULL

    UNMULTNULL

    UNPOSTEDNULL

    UNSERLNULL

    UNTRANLatin1_General_CI_AS

    Obviously, when rogue characters returned, result set is different:

    ImportColumncollation_name

    ????RTNULL

    ????UVNULL

    ????EFNULL

    ????RENULL

    ????RTNULL

    PDACTIONLatin1_General_CI_AS

    PDINVTNULL

    PDIREFNULL

    PDMULTNULL

    PDSERLNULL

    PDUACTIONNULL

    PDUVALNULL

    RPCOUPNULL

    RPREFNULL

    ????TLNULL

    ????TNULL

    ????TENULL

    UNAMOUNTNULL

    UNINSTREFNULL

    UNMULTNULL

    UNPOSTEDNULL

    UNSERLNULL

    UNTRANLatin1_General_CI_AS

    But I've guessed what this has proved is despite some of the columns have a standard collation, they still present rogue characters.

    My assumption is where collation is NULL it will take database default, consequently, all columns should be consistent?

  • JJB@TGT (4/4/2013)


    Ok, done some investigation on column collation. I've cross referenced sys.columns on the object_id and column_id like so:

    SELECT DISTINCT

    Ref.referenced_database_name AS ImportDatabase

    ,Ref.referenced_schema_name AS ImportViewSchemaName

    ,SUBSTRING(Ref.referenced_entity_name,

    (CHARINDEX('_', Ref.referenced_entity_name) + 1),

    LEN(Ref.referenced_entity_name)) AS TableName

    ,SUBSTRING(Ref.referenced_entity_name, 1,

    (CHARINDEX('_', Ref.referenced_entity_name, 1) - 1)) AS ConnectionName

    ,Ref.referenced_minor_name AS ImportColumn

    ,collation_name

    FROM

    sys.dm_sql_referenced_entities('Staging' + '.' + 'uspInstrumentHolding',

    'OBJECT') AS Ref

    LEFT JOIN sys.columns sysc

    ON COLUMNPROPERTY(OBJECT_ID(Ref.referenced_schema_name + '.'

    + Ref.referenced_entity_name),

    Ref.referenced_minor_name, 'ColumnId') = sysc.column_id

    AND OBJECT_ID(Ref.referenced_schema_name + '.'

    + Ref.referenced_entity_name) = sysc.[object_id]

    WHERE

    1 = 1

    AND Ref.referenced_minor_name IS NOT NULL

    AND ref.referenced_schema_name = 'import'

    Which returns the following when all values are populated correctly:

    ImportColumncollation_name

    PCCVRTLatin1_General_CI_AS

    PCDIRELatin1_General_CI_AS

    PCINRTLatin1_General_CI_AS

    PCIREFNULL

    PCORUVNULL

    PCSRRTLatin1_General_CI_AS

    PDACTIONLatin1_General_CI_AS

    PDINVTNULL

    PDIREFNULL

    PDMULTNULL

    PDSERLNULL

    PDUACTIONNULL

    PDUVALNULL

    RPCOUPNULL

    RPREFNULL

    RBDATENULL

    RBRATNULL

    RBTITLLatin1_General_CI_AS

    UNAMOUNTNULL

    UNINSTREFNULL

    UNMULTNULL

    UNPOSTEDNULL

    UNSERLNULL

    UNTRANLatin1_General_CI_AS

    Obviously, when rogue characters returned, result set is different:

    ImportColumncollation_name

    ????RTNULL

    ????UVNULL

    ????EFNULL

    ????RENULL

    ????RTNULL

    PDACTIONLatin1_General_CI_AS

    PDINVTNULL

    PDIREFNULL

    PDMULTNULL

    PDSERLNULL

    PDUACTIONNULL

    PDUVALNULL

    RPCOUPNULL

    RPREFNULL

    ????TLNULL

    ????TNULL

    ????TENULL

    UNAMOUNTNULL

    UNINSTREFNULL

    UNMULTNULL

    UNPOSTEDNULL

    UNSERLNULL

    UNTRANLatin1_General_CI_AS

    But I've guessed what this has proved is despite some of the columns have a standard collation, they still present rogue characters.

    My assumption is where collation is NULL it will take database default, consequently, all columns should be consistent?

    What are the data types of the columns with NULL collations? It is my understanding the only character type columns will have a collation associated with them.

  • Lynn Pettis (4/4/2013)


    What are the data types of the columns with NULL collations? It is my understanding the only character type columns will have a collation associated with them.

    Yep, getting late, not thinking. The NULLS are indeed non-character columns. Either way, collation does not seem to be the culprit.

  • To throw something in here....what are the chances that this odd issue is associate with a lack of memory (and if so, how!!).

    The only reason I ask, is that I've just been troubleshooting another issue on this server - a set of failed backups from this evening, which was simply backing up of the system databases. It would appear as though SQL has been significantly struggling for memory. SQLBackup returned the following error:

    VDI error 1010: Failed to get the configuration from the server because the timeout interval has elapsed. SQL Backup required 6,291,456 bytes of free SQL Server memory, which was not available. VDI error: An abort request is preventing anything except termination actions.

    ......

    SQL error 3013: BACKUP DATABASE is terminating abnormally.

    SQL error 18210: BackupVirtualDeviceSet::Initialize: Request large buffers failure on backup device 'SQLBACKUP_9409B57E-8DC9-416A-B5E9-BDF77A16A98D'. Operating system error 0x8007000e(failed to retrieve text for this error. Reason: 15105).

    I've just restarted the instance in order to get these backups out before midnight and this issue has gone away. I'm a bit annoyed with myself here, I've messed up my troublshooting 🙂 I've fixed the problem without getting to bottom of it and I don't know WHAT has fixed it! Gah!

  • Cool beans, some type of memory issue.

    Do you have regular alerting and operators defined on the box so that you get error messages?

    We use SQL Sentry Monitor but I have simple alerts for errors 11-25 defined also.

    http://craftydba.com/?p=1099

    Last but not least, do not forget the out of space alerts. They can be found in the sp_Blitz script.

    :w00t:

    I wonder if this bug will return in a few hours/days?

    John Miner
    Crafty DBA
    www.craftydba.com

  • Not so cool beans, we've been running this on another server for further testing and we've now experienced same behaviour. Not convinced this is memory related if I'm honest. Bumping this again, has anyone seen such behaviour before? SQL returning alternative characters to what should be returned from both SSIS and SSMS??

    Just don't get this...

  • Have you checked the error logs to see if anything is going on elsewhere with SQL or Windows?

  • No, nothing untoward in logs...

  • The only way I have found to defeat this bug is to do an inner join to sys.columns, on referenced_minor_id directly, because it does not get garbled even when the referenced_minor_name does.

    SELECT Distinct

    refc.referenced_schema_name, refc.referenced_entity_name,

    col.column_id AS column_number,

    lower(isnull(col.name,'')) AS column_name,

    lower(refc.referenced_minor_name) AS bad_data

    FROM sys.dm_sql_referenced_entities

    (<schema>+'.'+<object_name>,'OBJECT') refc

    INNER JOIN sys.columns col

    ON ( col.[object_id] = refc.referenced_id )

    AND ( col.column_id = refc.referenced_minor_id )

    ORDER BY

    refc.referenced_entity_name,

    column_number

  • bitumenpit (3/6/2014)


    The only way I have found to defeat this bug is to do an inner join to sys.columns, on referenced_minor_id directly, because it does not get garbled even when the referenced_minor_name does.

    Are you suggesting you've seen the same behaviour??

  • Yes, constantly - each result is different, and therefore the lot of them are unreliable when executing a batch of them in a WHILE loop.

    Joining to sys.columns and directly using {col.column_id = refc.referenced_minor_id} is one way to be certain that you've got them all, as referenced_minor_id is not subject to mangling.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply