belle’s sql musings

View or function ‘sys.dm_exec_sessions’ has more column names …

Posted in Issues/Troubleshooting by belle on November 5, 2008

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.

Advertisements
Tagged with:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: