Service Desk Practitioners Forum
cancel

Bug found in SP18 for MSSQL Server

Highlighted
David Edelman
Valued Contributor.

Bug found in SP18 for MSSQL Server

For those out you out there using SQL Server and looking to upgrade to 4.5 SP18, I wanted to let you know about a bug that I have found (and reported to HP - the problem will be documented as ITSM008485). I also wanted to propose a different workaround than the one suggested by the support engineer.

Here is the text of the call I submitted to HP:

---------------------------------------------
SD 4.5, SP 18
MSSQL 2000

The new database table(s) created when creating a new custom fields (e.g. ITSM_WOR_CFT001) are being created under the "servicedesk" schema (servicedesk is the username for both the Datastore user and the Repository user), instead of the "dbo" schema.

This causes a problem when using "(Re)generate database views for reporting...". The views are created, but when you try to access the view on the database, you get this error message:

Server: Msg 208, Level 16, State 1, Procedure v_workorder, Line 2
Invalid object name 'itsm_wor_cft001'.
Server: Msg 208, Level 16, State 1, Procedure v_workorder, Line 2
Invalid object name 'itsm_wor_cft001'.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'v_workorder' because of binding errors.

The view definition is not incorporating the proper schema assignment and therefore rendering the view unusable.

Ex: In v_workorder,

LEFT OUTER JOIN itsm_wor_cft001 T15 ON (T15.ITSM_WOR_CFT001_oid = T1.wor_oid))

should be

LEFT OUTER JOIN servicedesk.itsm_wor_cft001 T15 ON (T15.ITSM_WOR_CFT001_oid = T1.wor_oid))

-------------------------------------------

Here is their proposed workaround:
-------------------------------------------
1. shutdown SD Server.
2. execute the sql from SQL Query Analyzer
exec sp_changeobjectowner 'servicedesk.ITSM_WOR_CFT001', 'dbo'
(please use you db user and the table name)
3. clear the c:\Program Files\Hewlett-Packard\OpenView\service desk 4.5\server\cache directory.
4. start SD Server again, and SD client should connect to the server ok.
-------------------------------------------

My proposed workaround would be to alter the definition of the view once created to refer to the correct schema.

Just wanted to throw this out there for early warning and public consumption.

Thanks,
-David