Thanks, Bala. The Error that I'm getting is as follows:
Msg 195, Level 15, State 10, Procedure usp_GetPageDetails, Line 27
'object_schema_name' is not a recognized built-in function name.
Msg 156, Level 15, State 1, Procedure usp_GetPageDetails, Line 42
Incorrect syntax near the keyword 'as'.
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'usp_GetPageDetails', because it does not exist or you do not have permission.
The first error seems to infer that I don't have SP2 installed... which I do have installed.
Do you think that reapplying SP2 might fix this issue?