When the user launched the Business Portal website, and navigated to enter a purchase requisition for this new company, they could create the requisition header just fine. They clicked on the Add Item field to open the item selection window. Did a look up on the item numbr field, to display a list of the available items in GP…all fine so far. However, once they selected an item and clicked Select, they got the following message:
The multi-part identifier “T2.Null.[CMPTITNM]6” could no be bound. ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator. The multi-part identifier “T2.Null.[CMPITUOM]7” could no be bound. ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
The source of this issue was the database compatibility level. It was set to 90 (MSQL 2005) …and should have been 80 (MSSQL 2000).
To change compatibility levels, open MSSQL enterprise manager and log in. Select the database you want to change, click on Properties, and then click on Options. The database compatibility is displayed in a drop down list box. Simply select a new value and save.
Once done a new error message appeared at exactly the same spot:
‘Invalid Object Name ‘company database name’ ..IV_Item_Quantities.
This time, the issue was that there are a number of BP views that need to be created in each company database. These had not been created for this company. To find these, expand the company database in MSSQL Enterprise Manager and click on Views. If you get an Invalid Object Name Error, it will be followed by the actual object name that is causing the problem. Check if this is listed in Views.
Missing views can be created very easily. On whatever server BP is installed on, go to the following directory: C:\Program Files\Microsoft Business Solutions\Business Portal\Utilities\SQL\Company\GP_COMMON (Replace Microsoft Business Solutions with Dynamics for versions after V8). Here will be listed a number of MSSQL queries which are named as per the objects they create. Open the one you need, and copy its contents into a SQL query window on the MSSQL Server. Run the query against the company database to create the view. Once done, run the Grant.sql query to be sure, to be sure.
If you can’t locate the GP_COMMON folder at the path above, just do a search on the BP Server, they will be there somewhere depending on the original install path chosen.