Two SMS 2003 servers, one returns different data format for same query.

Problem
When doing a database query via SQL, one of our Microsoft SMS 2003 Servers (MILDRED) was returning an incorrect number of characters (8) for a field.  The field was supposed to return 56 characters.

As we were getting correct returns from the other SMS 2003 Server (GEORGE), we knew our T-SQL query was correct.

Was it the different SMS 2003 Service Pack levels? (MILDRED SP3, GEORGE SP2), or the different SQL Server versions? (MILDRED SQL 2005, GEORGE SQL 2000); causing the problem?

No.

Solution
Our SQL DBAs suggested that running a sp_refreshview would resolve the problem.  They were right, it did!

A sp_refreshview:
"Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends."

And the original cause?
My original guesses (SQL/SMS versions being the root cause) were wrong.

The only change we have made to MILDRED was to install Windows 2003 Server Service Pack 2, which should not have broken our SMS query.

So I don’t know.  But I do have a solution (sp_refreshview).

Bookmark and Share