CRS DB Write SQL Error log

[tgledbetter]

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

Thanks in advance.

[BCOLE2007]

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

[tgledbetter]

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.

[BCOLE2007]

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?

[tgledbetter]

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.

[BCOLE2007]

try defining it as a string,

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

[tgledbetter]

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.

[BCOLE2007]

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

[BCOLE2007]

Incorrect syntax near '('

put a space between your table name and the (

[tgledbetter]

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

[BCOLE2007 · Accepted solution]

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

[tgledbetter]

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.

[BCOLE2007]

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

[amaia.lesta]

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

[BCOLE2007]

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.