Tag Archives: Management Studio

(IBRK-10): IB Routing referencing invalid handlers

Posted by on January 31, 2009 Comments Off on (IBRK-10): IB Routing referencing invalid handlers

Problem:

After running the SYSAUDIT report, the following errors appear under Integration Broker Audits:

(IBRK-10): IB Routing referencing invalid handlers:
Routing On Send Handler On Receive Handler
 - ------ --------------- ------------------
 ~GENERATED~17288 ACK
 1 Total Rows

Solution:

Log on to SQL Server Management Studio and run the following queries against fsdev:

select * from PSIBRTNGDEFN where ROUTINGDEFNNAME = '~GENERATED~17288'
select * from PSIBRTNGDEFN where ONRCVHDLRNAME = 'ACK'
select ONRCVHDLRNAME, count(*) from PSIBRTNGDEFN group by ONRCVHDLRNAME
select * from PSSRVHDLRRCV_VW

The outputs were:

ROUTINGDEFNNAME EFFDT VERSION EFF_STATUS SENDERNODENAME RECEIVERNODENAME RTNGTYPE IB_SYNCHNONBLOCK IB_OPERATIONNAME VERSIONNAME CONNOVERRIDE CONNGATEWAYID CONNID LOGMSGDTLFLG ONSNDHDLRNAME ONRCVHDLRNAME GENERATED IB_LOCALIDFLG LASTUPDDTTM LASTUPDOPRID OBJECTOWNERID DESCR DESCRLONG
 ------------------------------ ----------------------- ----------- ---------- ---------------------------------------------------------
 ~GENERATED~17288 2005-10-21 00:00:00.000 6 I ~~ANY~~ PSFT_EP R N CREATE_WORKLIST_ITEM V1 N 2 ACK Y 2 2008-12-30 11:38:38.000 VP1 PPT GENERATED NULL
(1 row(s) affected)
ROUTINGDEFNNAME EFFDT VERSION EFF_STATUS SENDERNODENAME RECEIVERNODENAME RTNGTYPE IB_SYNCHNONBLOCK IB_OPERATIONNAME VERSIONNAME CONNOVERRIDE CONNGATEWAYID CONNID LOGMSGDTLFLG ONSNDHDLRNAME ONRCVHDLRNAME GENERATED IB_LOCALIDFLG LASTUPDDTTM LASTUPDOPRID OBJECTOWNERID DESCR DESCRLONG
 ------------------------------ ----------------------- ----------- ---------- ---------------------------------------------------------
 ~GENERATED~17288 2005-10-21 00:00:00.000 6 I ~~ANY~~ PSFT_EP R N CREATE_WORKLIST_ITEM V1 N 2 ACK Y 2 2008-12-30 11:38:38.000 VP1 PPT GENERATED NULL
(1 row(s) affected)
ONRCVHDLRNAME COUNT(*)
 ------------------------------ -----------
 1500
 ACK 1
(2 row(s) affected)
IB_OPERATIONNAME ONRCVHDLRNAME DESCR
 ------------------------------ ------------------------------ ---------------------------
 CREATE_WORKLIST_ITEM OnAckForMarkedWorkedResp Create worklist entry
(1 row(s) affected)

On fsdmo, ran the following queries:

select * from PSIBRTNGDEFN where ROUTINGDEFNNAME = '~GENERATED~17288'
select * from PSIBRTNGDEFN where ONRCVHDLRNAME = 'ACK'
select ONRCVHDLRNAME, count(*) from PSIBRTNGDEFN group by ONRCVHDLRNAME
select * from PSSRVHDLRRCV_VW

The outputs were:

ROUTINGDEFNNAME EFFDT VERSION EFF_STATUS SENDERNODENAME RECEIVERNODENAME RTNGTYPE IB_SYNCHNONBLOCK IB_OPERATIONNAME VERSIONNAME CONNOVERRIDE CONNGATEWAYID CONNID LOGMSGDTLFLG ONSNDHDLRNAME ONRCVHDLRNAME GENERATED IB_LOCALIDFLG LASTUPDDTTM LASTUPDOPRID OBJECTOWNERID DESCR DESCRLONG
 ------------------------------ ----------------------- ----------- ---------- ---------------------------------------------------------
 ~GENERATED~17288 2005-10-21 00:00:00.000 1 A ~~ANY~~ PSFT_EP R N CREATE_WORKLIST_ITEM V1 N 2 ACK Y 2 2008-12-18 16:04:16.000 PPLSOFT PPT GENERATED NULL
(1 row(s) affected)
ROUTINGDEFNNAME EFFDT VERSION EFF_STATUS SENDERNODENAME RECEIVERNODENAME RTNGTYPE IB_SYNCHNONBLOCK IB_OPERATIONNAME VERSIONNAME CONNOVERRIDE CONNGATEWAYID CONNID LOGMSGDTLFLG ONSNDHDLRNAME ONRCVHDLRNAME GENERATED IB_LOCALIDFLG LASTUPDDTTM LASTUPDOPRID OBJECTOWNERID DESCR DESCRLONG
 ------------------------------ ----------------------- ----------- ---------- ---------------------------------------------------------
 CREATE_WORKLIST_ITEM 2005-10-21 00:00:00.000 1 I ~~ANY~~ PSFT_EP R N CREATE_WORKLIST_ITEM V1 N 2 ACK Y 2 2008-12-23 11:11:56.000 PPLSOFT Create worklist entry NULL
 ~GENERATED~17288 2005-10-21 00:00:00.000 1 A ~~ANY~~ PSFT_EP R N CREATE_WORKLIST_ITEM V1 N 2 ACK Y 2 2008-12-18 16:04:16.000 PPLSOFT PPT GENERATED NULL
(2 row(s) affected)
ONRCVHDLRNAME
 ------------------------------ -----------
 1513
 ACK 2
(2 row(s) affected)
IB_OPERATIONNAME ONRCVHDLRNAME DESCR
 ------------------------------ ------------------------------ ---------------------------
 CREATE_WORKLIST_ITEM ACK
 CREATE_WORKLIST_ITEM OnAckForMarkedWorkedResp Create worklist entry
 (2 row(s) affected)

Note that in PSSRVHDLRRCV_VW, there is a new row for ACK.

1. The simplest solution, however, is to delete the offending routing, ~GENERATED~17288.

2. In PIA, navigate to Integration Broker > Service Utilities > Service Administration. Select the Routings tab, and open the Delete section. Enter ~GENERATED~17288 into the Routing Name field and click Search. Check Select and then click Delete.

Under Results, the following message appears: Unable to delete. Routing belongs to a restricted service.

3. One way to resolve this is to change the restricted service to unrestricted. Navigate to Integration Broker > Configuration > Service Configuration. Click the Restricted Services tab, and search for PT_WORKLIST. Uncheck Restricted, and then Save.

4. Determine the field that is used as the lookup. Navigate to Integration Broker > Integration Setup > Routings. In the Find an Existing Value tab, in Routing Name, enter ~GENERATED~17288 and click Search.

5. Type Ctrl + J and note the Page name: IB_ROUTINGDEFN

6. On GL-WEB-APP, launch Application Designer and click Insert Definitions into Project. Choose Page and enter IB_ROUTINGDEFN. Choose IB_ROUTINGDEFN and click Close.

7. Double-click IB_ROUTINGDEFN. Right-click on the OnReceive Handler field and choose View Definition. On the ONRCVHDLRNAME field, right click and choose Record Field Properties. Click the Edits tab and note the Prompt Table: PSSRVHDLRRCV_VW.

8. In Management Studio, for the fsdev database, expand the Views navigator and locate PSSRVHDLRRCV_VW. Right click on PSSRVHDLRRCV_VW, choose Script View as, choose CREATE To, and click New Query. Note the command to create the view:

CREATE VIEW [dbo].[PSSRVHDLRRCV_VW] (IB_OPERATIONNAME, ONRCVHDLRNAME, DESCR) AS SELECT IB_OPERATIONNAME , HANDLERNAME , DESCR FROM PSOPRHDLR WHERE HANDLERID = 'RECV'

9. In PIA, navigate to Integration Broker > Integration Setup > Service Operations. In the Find Service Operation tab, in the Service Operation field, enter CREATE_WORKLIST_ITEM. Then click Search. Click CREATE_WORKLIST_ITEM to view it.

10. If the record is restricted, you will not be able to enter new Handlers. See step 3 above to resolve.

11. Click the Handler tab. Add a new Handler with the following information:
Name: ACK
Type: OnReceive
Implementation: Application Class
Status: Active

Click Details and add the following information:

Package Name: PT_WF_WORKLIST
Path: : [that is, only a colon must be entered]
Class ID: OnAckForMarkedWorkedResp
Method: OnAckReceive

12. Click Save.

(QUERY-27) Following Queries Use XLAT Fields Whose Values Do not Exist in PSXLATITEM Table

Posted by on January 21, 2009 No comments

(QUERY-27) Following Queries Use XLAT Fields Whose Values Do not Exist in PSXLATITEM Table

Problem:

After running SYSAUDIT on FSDEV, the following error appears:

(QUERY-27) Following Queries Use XLAT Fields Whose Values Do not Exist In PSXLATITEM Table:
Query Name Field Name
 ---------- ----------------------
 ARDISH01 DRAFT_TYPE
 1 Total Rows

Solution:

1. Determine if scriptsPTPATCH.DMS was run. Log onto SQL Server Management Studio and running the following command:

select * from PS_MAINTENANCE_LOG where UPDATE_ID = '768890';

2. Next, load Application Designer and copy the PATCH849 project from file.

It was already copied.

3. Check PS_QRYFIELD_VW to see the fields used in the ARDISH01 query:

select * from dbo.PS_QRYFIELD_VW where QRYNAME = 'ARDISH01'

4. Get list of ‘DRAFT_’ field names from the PSXLATITEM table:

select distinct FIELDNAME from PSXLATITEM where FIELDNAME like 'DRAFT_%'

There is no DRAFT_TYPE field in this table.

5. Get list of ‘DRAFT_’ field names from the PSXLATITEM table:

Checked SYSAUDIT for FSDMO. This error is not present. Apply MP5 to FSDEV to resolve issue.

Build and Alter Step While Applying MP5

Posted by on January 16, 2009 No comments

The Build and Alter step (or ‘chapter’) consists of two tasks:

1. Build and Alter Task_1
2. Build and Alter Task

Under the first task (Build and Alter Task_1), there are two steps:
a. Build Step
b. Run Recreate Tables Script

No action is required for the Build and Alter Task_1 steps. Read the documentation in the right pane, and then mark the steps as Complete.

Under the second task (Build and Alter Task), there are seven steps:
a. Build and Alter Step
b. Run Create Tables Script
c. Run Alter Tables Script
d. Run Create Triggers Script
e. Run Create Views Script
f. Additional Step 02
g. Run Create Indexes Script

The first step (Build and Alter Step) can be run automatically. The step completes with warnings, as there are some records have over 16 keys or some record definitions do not exist. The log file is:
E:/temp/softwareupdateFSCM900_MP5{3d899576-cc86-11dd-930f-9c077f4a98a5}/BuildAndAlter.log

The output scripts generated by the Build and Alter Step are located in E:tempsoftwareupdateFSCM900_MP5{3d899576-cc86-11dd-930f-9c077f4a98a5}

The second step, Run Create Tables Script is done by running the E:tempsoftwareupdateFSCM900_MP5{3d899576-cc86-11dd-930f-9c077f4a98a5} CreateTables.sql script in SQL Server Management Studio. The CreateTables.sql script executed successfully in 59 seconds.

The third step, Run Alter Tables Script is done by running the E:tempsoftwareupdateFSCM900_MP5{3d899576-cc86-11dd-930f-9c077f4a98a5} AlterTables.sql script in SQL Server Management Studio. The AlterTables.sql script executed successfully in 1 minute 30 seconds.

The fourth step, Run Create Triggers Script is done by running the E:tempsoftwareupdateFSCM900_MP5{3d899576-cc86-11dd-930f-9c077f4a98a5} CreateTriggers.sql script in SQL Server Management Studio. The CreateTriggers.sql script executed successfully in less than 1 second.

The fifth step, Run Create Views Script is done by running the E:tempsoftwareupdateFSCM900_MP5{3d899576-cc86-11dd-930f-9c077f4a98a5} CreateViews.sql script in SQL Server Management Studio. The CreateViews.sql script executed successfully in 26 seconds.

The sixth step, Additional Step 02 is done by logging into PIA and populating the KEYLIST field.

The seventh step, Run Create Indexes Script is done by running the E:tempsoftwareupdateFSCM900_MP5{3d899576-cc86-11dd-930f-9c077f4a98a5} CreateIndexes.sql script in SQL Server Management Studio. The CreateIndexes.sql script executed successfully in 10 seconds.

Note: When these steps were first run against the FSDMO instance, the Create Views step failed because the PTPATCH.DMS script and PATCH849 project were not run against the database.

Error has occurred while establishing a connection to the server

Posted by on January 3, 2009 No comments

When connecting to SQL Server database using SQL Server Management Studio, the following error appears:

TITLE: Microsoft SQL Server Management Studio
——————————

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476

——————————
BUTTONS:

OK
——————————

Solution:

The SQL Server instance service (MSSQLSERVER) is not started. Start the service using the Control Panel Services panel, or use SQL Server Configuration Manager:

SQL Server Configuration Manager (Local) > SQL Server 2005 Services > SQL Server (MSSQLSERVER)