DB Lookup fails - changed columns

[Robert Boyajan]

We have been using DB Lookup with ICM for years. Currently on version 11. Recently we added columns to the SQL Database and removed them. We then added them back to SQL. We are also replicating the database to the B-side. The A-side is failing with the below error. The column order on A-side is different than the column order on the B-side when DB Lookup is accessing them. My understanding is that it does not matter. When I add the configuration the Database Lookup Explorer and look at the DBW log that is where we see the error. We also see it failing in the scripts.

All the values in the two databases are the same. They are all set to nvarchar. 

A-Side

09:36:59:489 ra-dbw Trace: Column 19: RouteValue, type=39, length=20

09:36:59:489 ra-dbw Trace: Column 26: PartnerRouting, type=39, length=100

09:36:59:489 ra-dbw Trace: Column 27: Language, type=39, length=100

B-Side

09:36:59:418 rb-dbw Trace: Column 19: RouteValue, type=39, length=20

09:36:59:418 rb-dbw Trace: Column 20: PartnerRouting, type=39, length=100

09:36:59:418 rb-dbw Trace: Column 21: Language, type=39, length=100

Here is the error in the DBW log on Side A

21:29:11:167 ra-dbw Trace: DBMessage(dbcoltype): Error: SQLSTATE=07009, Native error=0, msg='[Microsoft][SQL Server Native Client 11.0]Invalid Descriptor Index'

21:29:11:167 ra-dbw Trace: DBMessage(dbdata): Error: SQLSTATE=07009, Native error=0, msg='[Microsoft][SQL Server Native Client 11.0]Invalid Descriptor Index'

21:29:11:167 ra-dbw Trace: SQLConnection::SetupColumnData: Column data for column 26 Length = 0

21:29:11:167 ra-dbw Trace: Unable to convert type 563 to a char.

21:29:11:167 ra-dbw Trace: GetColumnData failed for column 26 of DialedNumber_Lookup in DBWLookupReply

Cisco recommended running rrtest and use the command exit_router to clear the cache on the rogger? That did not correct the error.

Is this an error with ICM DBLookup or SQL? The B-side is working correctly.

[david.macias]

Are the data types for that column the same for the both DBs?

david

[Robert Boyajan]

Yes they are. The Database is replicated and both sides have the same Data Types for all columns.

[david.macias]

Any chance there is an ES on one side and not the other. It doesn't make sense that it's working on one side and not the other. If you switch the register to point A->B and B->A, does the problem follow the DB?

david

[Robert Boyajan]

By Register, do you mean swapping Side A and Side B in the Database Lookup Explorer? If so then I will have to perform after hours. I have not tried that yet. Thanks

[david.macias]

Yes, that. See what happens. I think this will help at least nail down the culprit.

david

[Robert Boyajan]

Thank you. I will let you know what I find.

[geoff]

Simple Answer

NVARCHAR is not a supported data type.

Stop using it - change the table. Where did you get the idea that NVARCHAR is allowed? Check the documentation.

Now the facts are that it did work - it was never supported but it did work. Then Cisco tightened the screws and it stopped working around 10.5

Regards,

Geoff

[Robert Boyajan]

I changed the Data Type to varchar in the database and then added it to ICM DBLookup.

I get the below error. I still get the error that it cannot convert to char. Could Invalid Descriptor Index be causing the issue?  

14:39:54:287 ra-dbw Trace: SQLConnection::SetupColumnData: Column data for column 17 Length = 1
14:39:54:287 ra-dbw Trace: SQLConnection::SetupColumnData: Column data for column 18 Length = 10
14:39:54:287 ra-dbw Trace: SQLConnection::SetupColumnData: Column data for column 19 Length = 1
14:39:54:287 ra-dbw Trace: DBMessage(dbcoltype): Error: SQLSTATE=07009, Native error=0, msg='[Microsoft][SQL Server Native Client 11.0]Invalid Descriptor Index'
14:39:54:287 ra-dbw Trace: DBMessage(dbdata): Error: SQLSTATE=07009, Native error=0, msg='[Microsoft][SQL Server Native Client 11.0]Invalid Descriptor Index'
14:39:54:287 ra-dbw Trace: SQLConnection::SetupColumnData: Column data for column 26 Length = 1956715524
14:39:54:287 ra-dbw Trace: Unable to convert type 0 to a char.
14:39:54:287 ra-dbw Trace: GetColumnData failed for column 26 of DialedNumber_Lookup in DBWLookupReply

[geoff]

Great - good to see you changed the data type. SQL Server does seem to mind that change.

On the Router, run rttest and then call exit_dbw. This will drop the dbw process on both sides.

On the side you are on, go into the router log files directory and run dumplog dbw /last /o (looking at the trace since the restart). Study the file dbw.txt and see if it's OK. 

Normally changing types can mess you around unless you get it to start clean on each side, exit_dbw will do that.

Regards,

Geoff

[Robert Boyajan]

Hey Geoff...

Last night I started by looking at one column in SQL and making sure it was set to varchar. This is the field I am adding back into ICM. I then ran rttest and exit_dbw. I added the field back into ICM. I then ran dumplog dbw /hr 1. I could see the new field added back to ICM in the log. As soon as I placed a call to test, I still saw the same errors in dbw log. I then ran rttest and exit_dbw again. I still see the same error in the logs. I removed the field from ICM and everything is back to working without errors.

Should all the fields that are nvarchar be changed to varchar? Also can I just create a new table and link that to ICM? I could then rename the Database Lookup name. DialedNumber_Lookup to DialedNumber_LookupNew.

I am being cautious as to not break ICM DBLookup entirely.

Any suggestions would be appreciated.

[geoff]

---

@Robert Boyajan wrote: Should all the fields that are nvarchar be changed to varchar?

---

yes. Change all nchar to char and all nvarchar to varchar. Neither are supported. It's not a big deal.SQL Server will do it.

The reason people create columns with data type nchar is it pops up first in the list when you add a new column in Table Designer. But Cisco don't support the data type and you don't really need it - you just have strings.

Regards,

Geoff