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.