Hint 'noexpand' on object is invalid

  • 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

  • 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
  • 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

  • Thank you Gail, solved my problem.

    Cheers

    Vaishali Husain

  • 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