Post reply

Hint 'noexpand' on object is invalid

  • October 3, 2009 at 2:35 am

    #135545

    Dear All,

    We have a view which joins two tables and this view is not indexed view.

    This view is being used in one stored procedure but we are getting the following error

    System.Data.SqlClient::SqlConnection.OnError(){6} Hint 'noexpand' on object 'vwNG_SBGetUnreconciledRecords' is invalid. Hint 'noexpand' on object 'vwNG_SBGetUnreconciledRecords' is invalid. Hint 'noexpand' on object 'vwNG_SBGetUnreconciledRecords' is invalid. Hint 'noexpand' on object 'vwNG_SBGetUnreconciledRecords' is invalid. Hint 'noexpand' on object 'vwNG_SBGetUnreconciledRecords' is invalid.{6} at

    Your help is highly appreicated.

    Regards,

    Adil

  • October 3, 2009 at 1:27 pm

    #1061362

    The NOEXPAND hint is only valid for indexed views. Normal views have to be expanded because they don't actually stored any data, they're just saved select statements.

    Remove all of the NOEXPAND hints from the procedure in question.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 4, 2009 at 12:58 am

    #1061426

    Hi Gail,

    Many thanks for the reply. The issue got resolved. The index was somehow deleted from this view and this was causing the problem.

    Regards,

    Adil

  • December 4, 2010 at 11:02 pm

    #1258794

    Thank you Gail, solved my problem.

    Cheers

    Vaishali Husain

  • June 13, 2019 at 8:14 am

    #3653110

    Hello Team,

     

    COuld you please help me on this? I have the same issue and I need your assistance SQL script to remove.

     

    Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

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