Debug of DB connection on call qualification

[cavagnaro]

Erm...in MSSQL did you enable TCP connection??

[joao.carlos]

Hi cavagnaro,

I could finally connect. I was using only the HOST/IP address in the server field, but i figured out that i had to specify the name of the MSSQL instance, because it is not default. Look the connection now:

+0001 Open DB: username = 'sa', database = 'RQVC', dbserver = 'GCE-MSSQL-RQ\MSSQLSERVER_GCE'
+0004 Could not connect to SQL Server using SQL Native Client driver. Attemting default MS SQL Server driver...
+0034 Connected to MSSQL with SQL Server driver.
+0036 MSG_DBOPENED status='DBM_SUCCESS' msg='Open DB success'

But now i'm receiving this other error in the URS log (0014 unknown server):

_I_I_006f021b1a06a898 [09:04] ASSIGN: sqlStatment(LOCAL) <- STRING: "Select RAMAL from CLIENTEMARCA where CPF='26523477134';"
_I_I_006f021b1a06a898 [09:04] ASSIGN: currentIndex(LOCAL) <- INTEGER: 55
_I_I_006f021b1a06a898 [09:04] ASSIGN: index(LOCAL) <- INTEGER: 0
_I_I_006f021b1a06a898 [09:04] ASSIGN: index(LOCAL) <- INTEGER: 7
_I_I_006f021b1a06a898 [09:04] ASSIGN: sqlStatementStart(LOCAL) <- STRING: "Select"
_I_I_006f021b1a06a898 [09:04] ASSIGN: sqlStatementStart(LOCAL) <- STRING: "SELECT"

15:43:06.245 Int 22000 #### - 006f021b1a06a898 - IxnRouting - Qualifying - VisualIvrGroup - AutoAttendant - DBLookUp - SqlStatment : Select RAMAL from CLIENTEMARCA where CPF='26523477134'; calltype : 2

15:43:06.246 Int 22000 #### - 006f021b1a06a898 - IxnRouting - Qualifying - VisualIvrGroup - AutoAttendant - DBLookUp - SqlStatementStart : SELECT.
15:43:06.246_I_I_006f021b1a06a898 [07:38] HERE IS XDATA
_I_E_006f021b1a06a898 [09:04] error in strategy: 0014 Unknown server
_I_I_006f021b1a06a898 [09:04] ASSIGN: DBResult(LOCAL) <- STRING: ""

15:43:06.246 Int 22000 #### - 006f021b1a06a898 - IxnRouting - Qualifying - VisualIvrGroup - AutoAttendant - DBLookUp - DB result : () error : ( Unknown server )
_I_I_006f021b1a06a898 [09:04] ASSIGN: DBErrorCode(LOCAL) <- STRING: "14"

15:43:06.246 Int 22000 #### - 006f021b1a06a898 - IxnRouting - Qualifying - VisualIvrGroup - AutoAttendant - DBLookUp - update of the DB_ERROR_CODE data
_I_I_006f021b1a06a898 [09:04] ASSIGN: outError(LOCAL) <- INTEGER: -3

15:43:06.246 Int 22000 #### - 006f021b1a06a898 - IxnRouting - Qualifying - VisualIvrGroup - AutoAttendant - DBLookUp - DB result bad script or unkown request error : 14
request to 65200(--) message RequestUpdateUserData
AttributeReferenceID 30234
AttributeUserData [32] 00 02 00 00..
'RAMAL' ''
'DB_ERROR_CODE' '14'
AttributeConnID 006f021b1a06a898
AttributeThisDN '09970299'
..sent to gce-rq:3100(fd=564)
15:43:06.246_T_I_006f021b1a06a898 [14:02] sending event 85 for virtual queue 09970299S



Attached the screen shots:

[joao.carlos]

Yes. TCP Connection is enabled.

The data from our Database are:

SQL
Server Name: GCE-MSSQL-RQ
Table Name: RQVC

ORACLE (with these information i'm able to connect with SQL plus)
Server NAME: srv1-ora.rqr.com.br
Service Name: rqrescm.rqr.com.br
Database Name: CM

[joao.carlos]

Hi !

I could sucessfully connect to both ORACLE and MSSQL servers now. The problem in ORACLE DAP was that in the server field we had to use:

HOST_IP:PORT/Service_Name

Although, the same problem is happening with ORACLE and MSSQL as posted before:

_I_I_006f021b1a06a898 [09:04] ASSIGN: sqlStatment(LOCAL) <- STRING: "Select RAMAL from CLIENTEMARCA where CPF='26523477134';"
_I_I_006f021b1a06a898 [09:04] ASSIGN: currentIndex(LOCAL) <- INTEGER: 55
_I_I_006f021b1a06a898 [09:04] ASSIGN: index(LOCAL) <- INTEGER: 0
_I_I_006f021b1a06a898 [09:04] ASSIGN: index(LOCAL) <- INTEGER: 7
_I_I_006f021b1a06a898 [09:04] ASSIGN: sqlStatementStart(LOCAL) <- STRING: "Select"
_I_I_006f021b1a06a898 [09:04] ASSIGN: sqlStatementStart(LOCAL) <- STRING: "SELECT"

15:43:06.245 Int 22000 #### - 006f021b1a06a898 - IxnRouting - Qualifying - VisualIvrGroup - AutoAttendant - DBLookUp - SqlStatment : Select RAMAL from CLIENTEMARCA where CPF='26523477134'; calltype : 2

15:43:06.246 Int 22000 #### - 006f021b1a06a898 - IxnRouting - Qualifying - VisualIvrGroup - AutoAttendant - DBLookUp - SqlStatementStart : SELECT.
15:43:06.246_I_I_006f021b1a06a898 [07:38] HERE IS XDATA
_I_E_006f021b1a06a898 [09:04] error in strategy: 0014 Unknown server
_I_I_006f021b1a06a898 [09:04] ASSIGN: DBResult(LOCAL) <- STRING: ""

15:43:06.246 Int 22000 #### - 006f021b1a06a898 - IxnRouting - Qualifying - VisualIvrGroup - AutoAttendant - DBLookUp - DB result : () error : ( Unknown server )
_I_I_006f021b1a06a898 [09:04] ASSIGN: DBErrorCode(LOCAL) <- STRING: "14"

15:43:06.246 Int 22000 #### - 006f021b1a06a898 - IxnRouting - Qualifying - VisualIvrGroup - AutoAttendant - DBLookUp - update of the DB_ERROR_CODE data
_I_I_006f021b1a06a898 [09:04] ASSIGN: outError(LOCAL) <- INTEGER: -3

15:43:06.246 Int 22000 #### - 006f021b1a06a898 - IxnRouting - Qualifying - VisualIvrGroup - AutoAttendant - DBLookUp - DB result bad script or unkown request error : 14
request to 65200(--) message RequestUpdateUserData
AttributeReferenceID 30234
AttributeUserData [32] 00 02 00 00..
'RAMAL' ''
'DB_ERROR_CODE' '14'
AttributeConnID 006f021b1a06a898
AttributeThisDN '09970299'
..sent to gce-rq:3100(fd=564)


Any thoughts?

Thanks in advance!

[cavagnaro]

URS logs aren't helpful here, please post DBServer ones

[joao.carlos]

Cavagnaro,

At this time, the only messages from VisualCCDBServer i get are:

+0001 Open DB: username = 'sa', database = 'RQVC', dbserver = 'GCE-MSSQL-RQ\MSSQLSERVER_GCE'
+0004 Could not connect to SQL Server using SQL Native Client driver. Attemting default MS SQL Server driver...
+0034 Connected to MSSQL with SQL Server driver.
+0036 MSG_DBOPENED status='DBM_SUCCESS' msg='Open DB success'


and

+0054 Connection to multiserver created, host 'gce-rq', port '65183', socket '240'
+0054 MSG_STARTDB status='Unknown (215)'
Oracle: id='215.1' req='11988' Protocols versions: dbclient='00010004' dbserver='00010004' dblib='00010004'
+0000 Database Access Point in TKVList:
+0000 Open DB: username = 'jcr', database = 'CM', dbserver = '10.1.1.171:1521/rqrescm.rqr.com.br'
+0297 MSG_DBOPENED status='DBM_SUCCESS' msg='Open DB success'

Is there any other thing i should look for?

[cavagnaro]

Well at this point if the DAP was fine and the DB object on IRD too you should be seeing the SQL commands in there...so there is something else broken in the middle
Does URS has connection to that DBServer??

[joao.carlos]

Well,

I opened Configuration Manager, and in the Connection tab os URS, they are there, as shown in the picture attached.
Googleing for the error, I found there would be 2 possibles problems for this error: 1. There is no connection in the tab Connections of URS and 2. Security problem that i have no idea where the security of URS/DBServer is configured.

It seems URS is rejecting to make the query because it does not recognize the DBServer or its DAP.

Funny that in other places, again, We created DAP and only it and everything was fine.

As posted before, i've used already the login SA to make sure there is no permission problem on the connection.

Any more ideas?

[cavagnaro]

Unknown server is when using a DAP not connected to URS...so are you sure you are using ERSDAP also on your strategy?

[joao.carlos]

No, I'm not. In fact, i don't know what ERSDAP is.

Look, i created a whole routing strategy only for testing. The user calls, is asked to type some digits by a VisualIVR collector. In the VisualIVR script, is asks for the digits, and then use it to select the segment that the call should route to and attach the number of the segment. The qualificators then compares the attached data to fixed strings and decides which segment to go.

Obviously, as the VisualIVR is unable to consult the DB, the call is being routed directly to the default segment.

I attached some screenshots if helps.