Hint 'noexpand' on object is invalid

  • adil.muhamad

    SSC Veteran

    Points: 272

    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

  • Gail Shaw

    SSC Guru

    Points: 1004446

    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
  • adil.muhamad

    SSC Veteran

    Points: 272

    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

  • vaishalihusain-738828

    SSC Enthusiast

    Points: 110

    Thank you Gail, solved my problem.

    Cheers

    Vaishali Husain

  • hamza.elhadjali

    Newbie

    Points: 1

    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 5 (of 5 total)

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