Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Odd behaviour from sys.dependencies Expand / Collapse
Author
Message
Posted Thursday, April 4, 2013 3:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 3:02 AM
Points: 11, Visits: 41
(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 . 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
Post #1438702
Posted Thursday, April 4, 2013 12:55 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 9:13 AM
Points: 80, Visits: 344
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
Post #1438989
Posted Thursday, April 4, 2013 3:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 3:02 AM
Points: 11, Visits: 41
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...
Post #1439030
Posted Thursday, April 4, 2013 3:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 3:02 AM
Points: 11, Visits: 41
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?
Post #1439032
Posted Thursday, April 4, 2013 3:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 3:02 AM
Points: 11, Visits: 41
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:

ImportColumn	collation_name
PCCVRT Latin1_General_CI_AS
PCDIRE Latin1_General_CI_AS
PCINRT Latin1_General_CI_AS
PCIREF NULL
PCORUV NULL
PCSRRT Latin1_General_CI_AS
PDACTION Latin1_General_CI_AS
PDINVT NULL
PDIREF NULL
PDMULT NULL
PDSERL NULL
PDUACTION NULL
PDUVAL NULL
RPCOUP NULL
RPREF NULL
RBDATE NULL
RBRAT NULL
RBTITL Latin1_General_CI_AS
UNAMOUNT NULL
UNINSTREF NULL
UNMULT NULL
UNPOSTED NULL
UNSERL NULL
UNTRAN Latin1_General_CI_AS


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

ImportColumn	collation_name
䀨全RT NULL
䀨全UV NULL
䀨剠EF NULL
䀨剠RE NULL
䀨剠RT NULL
PDACTION Latin1_General_CI_AS
PDINVT NULL
PDIREF NULL
PDMULT NULL
PDSERL NULL
PDUACTION NULL
PDUVAL NULL
RPCOUP NULL
RPREF NULL
뻐䐮꾐䐮TL NULL
ꀨ䐮뿈䐮T NULL
ꀨ䐮뿈䐮TE NULL
UNAMOUNT NULL
UNINSTREF NULL
UNMULT NULL
UNPOSTED NULL
UNSERL NULL
UNTRAN Latin1_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?
Post #1439036
Posted Thursday, April 4, 2013 4:22 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:50 PM
Points: 23,003, Visits: 31,495
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:

ImportColumn	collation_name
PCCVRT Latin1_General_CI_AS
PCDIRE Latin1_General_CI_AS
PCINRT Latin1_General_CI_AS
PCIREF NULL
PCORUV NULL
PCSRRT Latin1_General_CI_AS
PDACTION Latin1_General_CI_AS
PDINVT NULL
PDIREF NULL
PDMULT NULL
PDSERL NULL
PDUACTION NULL
PDUVAL NULL
RPCOUP NULL
RPREF NULL
RBDATE NULL
RBRAT NULL
RBTITL Latin1_General_CI_AS
UNAMOUNT NULL
UNINSTREF NULL
UNMULT NULL
UNPOSTED NULL
UNSERL NULL
UNTRAN Latin1_General_CI_AS


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

ImportColumn	collation_name
䀨全RT NULL
䀨全UV NULL
䀨剠EF NULL
䀨剠RE NULL
䀨剠RT NULL
PDACTION Latin1_General_CI_AS
PDINVT NULL
PDIREF NULL
PDMULT NULL
PDSERL NULL
PDUACTION NULL
PDUVAL NULL
RPCOUP NULL
RPREF NULL
뻐䐮꾐䐮TL NULL
ꀨ䐮뿈䐮T NULL
ꀨ䐮뿈䐮TE NULL
UNAMOUNT NULL
UNINSTREF NULL
UNMULT NULL
UNPOSTED NULL
UNSERL NULL
UNTRAN Latin1_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

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1439045
Posted Thursday, April 4, 2013 4:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 3:02 AM
Points: 11, Visits: 41
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.
Post #1439052
Posted Thursday, April 4, 2013 4:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 3:02 AM
Points: 11, Visits: 41
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!
Post #1439055
Posted Thursday, April 4, 2013 6:45 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 9:13 AM
Points: 80, Visits: 344
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.



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


John Miner
Crafty DBA
www.craftydba.com
Post #1439065
Posted Friday, April 12, 2013 9:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 3:02 AM
Points: 11, Visits: 41
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...
Post #1441758
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse