CRS DB Write SQL Error log

Answered Question
Oct 15th, 2008

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

Thanks in advance.

I have this problem too.
0 votes
Correct Answer by BCOLE2007 about 8 years 1 month ago

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

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 4.5 (2 ratings)
Loading.
BCOLE2007 Wed, 10/15/2008 - 19:50

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 Thu, 10/16/2008 - 13:46

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 Fri, 10/17/2008 - 05:46

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 Fri, 10/17/2008 - 05:55

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 Fri, 10/17/2008 - 06:03

try defining it as a string,

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

BCOLE2007 Fri, 10/17/2008 - 09:51

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 Fri, 10/17/2008 - 10:10

Incorrect syntax near '('

put a space between your table name and the (

tgledbetter Sat, 10/18/2008 - 04:33

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

Correct Answer
BCOLE2007 Sat, 10/18/2008 - 17:08

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 Mon, 10/20/2008 - 11:56

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 Mon, 10/20/2008 - 12:12

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

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 Mon, 01/12/2009 - 06:09

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.

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 = [email protected]'' ')

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

Actions

This Discussion