Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. And see here for current known issues.

New Member

CRS DB Write SQL Error log

Does anyone know where I can find the detail logs behind a SQL Error? Using CRS 4.0.5.

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Bronze

Re: CRS DB Write SQL Error log

hmmm, I think it could be the data in $TestWrite field

Can you change the $TestWrite to a literal value

INSERT INTO ivr_options ( date, ANI, options ) VALUES ('test','test','test')

if this works

forget the IPCC logs for a minute, and do a step by step REACTIVE debug in the CRS Editor

I want to know what is in $TestWrite

16 REPLIES
Bronze

Re: CRS DB Write SQL Error log

Give this a try

turn on the SS_DB tracing, You will need to click on expanded view ... see the screen shot attached

Restart the engine

and check the most recent log in the CRS Engine folder in Control Center

Our DB is Oracle and the familiar ORA- errors show in this file.

What Database are you working with?

Let me know how it goes

Blair

*Please rate all useful posts

New Member

Re: CRS DB Write SQL Error log

Thanks Blair, this is a call to a MS SQL 2000 database. I'm getting an incorrect syntax message when you try to use a variable in the SQL statement.

INSERT INTO ivr_options( date, ANI, options ) VALUES ($TestWrite,'test','test')

Am I just doing this wrong? Posting a doc with some of my findings.

Bronze

Re: CRS DB Write SQL Error log

What is the error message you are getting?

How is $TestWrite defined in CRS and what is the value that is trying to be inserted?

New Member

Re: CRS DB Write SQL Error log

it's defined as char. That seems to be the only type that will pass the test in the DB Write node. On the SQL 2000 side the date field is a char as well; I've tried a number of different types on that side with no success.

Bronze

Re: CRS DB Write SQL Error log

try defining it as a string,

what is the CRS value of the field, when running debug?

New Member

Re: CRS DB Write SQL Error log

String doesn't get me anywhere either. I'm going to upload portions of the debug.

Could this be an issue with SQL 2000 sp4?

Thanks for all your help.

Bronze

Re: CRS DB Write SQL Error log

Are you familiar with Reactive-Debug in the CRS editor? Run a Reactive Debug in the CRS Editor, call the app, and put a breakpoint exactly on the DBWrite step,push play, and get to the DBWrite, then go over to the variable pane on the left and see what the value of this field is.

To early to say its SQL 2000

Bronze

Re: CRS DB Write SQL Error log

Incorrect syntax near '('

put a space between your table name and the (

New Member

Re: CRS DB Write SQL Error log

It still fails:

Current SQL Statement:INSERT INTO ivr_options ( date, ANI, options ) VALUES ($TestWrite,'test','test')

Error:24893288: Oct 18 08:27:20.731 EDT %MIVR-SS_DB-7-UNK: Checkout Connection

24893289: Oct 18 08:27:20.731 EDT %MIVR-SS_DB-7-UNK: GetAvailableConnection

24893290: Oct 18 08:27:20.731 EDT %MIVR-SS_DB-7-UNK: Granted connection # 155

24893291: Oct 18 08:27:20.731 EDT %MIVR-SS_DB-7-UNK: Exception: java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '('. ExecuteUpdate Error Occurred

24893292: Oct 18 08:27:20.731 EDT %MIVR-SS_DB-3-EXECUTE_SQL_UPDATE_EXCEPTION:Exception occured while executing SQL Update: SQL State=null,SQL Error code=null,Connection #=null

24893293: Oct 18 08:27:20.731 EDT %MIVR-DB_STEPS-3-EXECUTE_SQL_UPDATE_EXCEPTION:Error during executing SQL Update: Task ID=33000126359,Step Name=DB Write,SQL State=37000,SQL Error Code=170

Bronze

Re: CRS DB Write SQL Error log

hmmm, I think it could be the data in $TestWrite field

Can you change the $TestWrite to a literal value

INSERT INTO ivr_options ( date, ANI, options ) VALUES ('test','test','test')

if this works

forget the IPCC logs for a minute, and do a step by step REACTIVE debug in the CRS Editor

I want to know what is in $TestWrite

New Member

Re: CRS DB Write SQL Error log

Got it! It's the the NULL value of the char type. Even if the database can accept null values, CRS seems to refuse to send a null. If I set a value, it writes just fine.

Bronze

Re: CRS DB Write SQL Error log

alright...

also the Char variable type is only 1 Character, a String should work fine if you need more letters/numbers

Also for SQL Server to receive a Null on an insert it must be put in a format for SQL Server to understand. Java's Null and Sql Servers Null I think are different, Oracle's Null and Java's Null representation are different.

Good luck

Blair

New Member

Re: CRS DB Write SQL Error log

Dear colleagues,

I am getting the same error in a DB REad step:

I am using a Linked Server definition to access an Oracle DB from the script.

This query works fine:

SELECT * FROM OPENQUERY( ProductionDB, 'select distinct hzp.attribute2 from AR.hz_contact_points tel, AR.hz_parties hzp where tel.phone_number = 946574753')

But when changing the last value for a string variable that is assigned the 946574753 value it fails:

SELECT * FROM OPENQUERY( ProductionDB, 'select distinct hzp.attribute2 from AR.hz_contact_points tel, AR.hz_parties hzp where tel.phone_number = $ANI ')

Error log:

224310: Jan 12 10:38:29.782 CET %MIVR-SS_DB-7-UNK:Error executing the query: Exception=java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '946574753'.

224311: Jan 12 10:38:29.782 CET %MIVR-SS_DB-7-EXCEPTION:java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '946574753'.

...

224338: Jan 12 10:38:29.782 CET %MIVR-SS_DB-4-EXECUTE_SQL_EXCEPTION:Exception occured while executing SQL statement: SQL State=37000,SQL Error code=170,Connection #=null

224339: Jan 12 10:38:29.782 CET %MIVR-DB_STEPS-3-EXECUTE_SQL_EXCEPTION:Error during executing SQL Update: Task ID=24000001427,Step Name=DB Read,SQL State=37000,SQL Error Code=170

I would be very grateful if you could tell me which is the cause of this error.

Best regards,

Amaia Lesta

Bronze

Re: CRS DB Write SQL Error log

I like your SQL, however I have found CRS likes pretty basic SQL, when I push it with PL/SQL or Selects from selects it may not work, instead try re-writing this SQL and SQL from a View and join $ANI with it.

New Member

Re: CRS DB Write SQL Error log

Yes, I assume that maybe a complex query can get an error.

But why a successful successful query gets an error when changing a fix value for a variable?

Best regards,

Amaia

New Member

Re: CRS DB Write SQL Error log

Hi,

Ir was a matter of query sintaxis. Now it works as this:

SELECT * FROM OPENQUERY( LaguntelDEV, 'select distinct hzp.attribute2 from AR.hz_contact_points tel, AR.hz_parties hzp where tel.phone_number = ''@ANI'' ')

Now the problem is in the GET step. I don't know how to create a System DSN resource that points to the linked server in order to define it as a Dtabase in the CRS adminsitration webpage. Any idea?

Thanks

Amaia

526
Views
4
Helpful
16
Replies