Problem

Agent message code 4502. View or function ‘sys.dm_exec_sessions’ has more column names specified than columns defined.

Scenario

Our replication on our clustered database just started to fail. We were getting the following error:

Agent message code 4502. View or function ‘sys.dm_exec_sessions’ has more column names specified than columns defined.

Ok, this is a weird message. How can sys.dm_exec_sessions have more columns that specified?

We checked the definition:

EXEC sp_helptext 'sys.dm_exec_sessions'

And this is what we got:

CREATE VIEW sys.dm_exec_sessions AS

 SELECT *

 FROM OpenRowset(TABLE SYSSESSIONS)

Ok, we can see how this can be a problem – because of the star (*). Any column change can spell disaster. However, we weren’t very sure why this would cause any problems, because this is not any regular user view. It’s a built in SQL Server DMV.

So we checked out a few things:

SELECT 

 SERVERPROPERTY('ResourceVersion'),

 SERVERPROPERTY('ProductVersion'),

 SERVERPROPERTY('ProductLevel')    -- Service Pack

It turns out the installed Service Packs are not the same in all the nodes.

Resolution

Make sure all nodes in the cluster have the same service pack. Sometimes it’s easy to miss because you have to failover to install the SP.

Be Sociable, Share!

• 
• 

Related posts:

1. SQLXML : How to Alter Existing Column Schema (XSD) …
2. How to Disable GAC View …
3. SQLXML : How to Use SQL Server XML Function exist() …
4. SQL Server PowerShell : How to View your SSRS Reports (rdl) Using PowerShell and ReportViewer …
5. How to Search for Columns in SQL Server …