warning from SQL server 2005 Upgrade Advisor

Question

warning from SQL server 2005 Upgrade Advisor

maozi

Old Hand

Points: 391
More actions
September 11, 2007 at 10:34 pm

#374158

the report from SQL Server Upgrade Advisor running against a user database,
when to fix: Before
Description: Object reference not set to an instance of an object. VIEWCHECKOPT.
However I checked. this object does not exist in this database.
Did anyone experience this problem? I have searched microsoft website and internet, couldn't find the answer.
Can someone help with this?

Thanks,
Sherry

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 1

I believe that the Description "should" tell you that there is a reference to the non-existant VIEWCHECKOPT object somewhere in the database, probably in a stored procedure.

It would have been easier if UI would tell you where it found the reference.

Andy

cmille19 SSCertifiable Points: 5970 More actions · Answer 2

I haven't seen this issue, however I've seen issues where theobject was renamed and the name does not match the object definition. This could be why you can't find the object. Here's a script to identify object swhere the object name and object definition do not match. Run from the database in question:

SELECT TABLE_NAME AS 'OBJ',LEFT(VIEW_DEFINITION,255) AS 'DEF'

FROM INFORMATION_SCHEMA.VIEWS

WHERE VIEW_DEFINITION NOT LIKE '%' + TABLE_NAME + '%'

UNION

SELECT ROUTINE_NAME, LEFT(ROUTINE_DEFINITION,255)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION NOT LIKE '%' + ROUTINE_NAME + '%'

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 3

If you are referring to a database object called VIEWCHECKOPT in a view, function, trigger, ... that does not exist anymore (you can have this easily), you can identify the object by

SELECT  OBJECT_NAME(id)
FROM    syscomments
WHERE   text LIKE '%VIEWCHECKOPT%'

Does the above give you any results?

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

maozi Old Hand Points: 391 More actions · Answer 4

maozi

Old Hand

Points: 391

September 13, 2007 at 5:28 pm

#733639

Andras,

results:

"(0 row(s) affected)"

Sherry

maozi Old Hand Points: 391 More actions · Answer 5

Andy,

All I got in "description" from UA is "Object reference not set to an instance of an object" as I posted before.

Sherry

maozi Old Hand Points: 391 More actions · Answer 6

Hi cmille 19,

excellent! there is a view which name does not match that in the definition. I will check with our sys admin who has been working on it.

Thanks a lot,

Sherry

maozi Old Hand Points: 391 More actions · Answer 7

Hi all,

after renaming the view back to match that in the definition, I ran UA again, this warning message disappeared.

Thanks for your help!

Sherry