April 4, 2013 at 3:46 am
(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
April 4, 2013 at 12:55 pm
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
April 4, 2013 at 3:33 pm
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...
April 4, 2013 at 3:38 pm
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?
April 4, 2013 at 3:54 pm
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?
April 4, 2013 at 4:22 pm
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.
April 4, 2013 at 4:39 pm
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.
April 4, 2013 at 4:47 pm
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!
April 4, 2013 at 6:45 pm
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.
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
April 12, 2013 at 9:04 am
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...
April 12, 2013 at 9:26 am
Have you checked the error logs to see if anything is going on elsewhere with SQL or Windows?
April 13, 2013 at 12:46 am
No, nothing untoward in logs...
March 6, 2014 at 10:22 am
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
March 6, 2014 at 12:21 pm
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??
March 6, 2014 at 1:48 pm
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