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

Issue with DBWrite step - enters Connection Not available path

Hi Everyone,

                   I have been having an issue with a customer survey script where I write the results to a SQL database. The Version of the CRS is 4.0(5) Build 042 its IPCC Express premium and I'm trying to write to a SQL 2005 database. The issue is every time I get to the dbwrite step it fails and takes the Connection Not available path, I have a DBrelease step but its only under the successful path. There are no other database functions in this script as well I do not have another DBRead, DBwrite or DBRelease any where else in this script. I have reviewed the logs and I see that it registers connections but it still gives the error each time, the query itself is performing a SQL insert of all string values to a mssql table with all varchars.

Everyone's tags (5)
10 REPLIES
Super Bronze

Re: Issue with DBWrite step - enters Connection Not available pa

Hi


Have you got the correct SQL drivers installed?

Have you tried doing a read in a script to verify they and your DSN are working correctly?

Have you tried connecting using an alternative utility as the user that your UCCX user is using, and checking you can write to the DB using the SQL statement you have in your script?

Post up your MIVR log covering an attempt, that should contain some info about the failure.

Aaron

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!
Community Member

Re: Issue with DBWrite step - enters Connection Not available pa

With the SQL drivers I beleive so I have tried using a DBread and I can get results from running a test query. With the permissions I have used the same credentials as the DSN and performed SQL inserts. With the logs I've been having a hard time finding error or exception messages for it, the only entries I ever see in the logs are when it allocates the ports. Now I do have a DSN which we use for testing which the credentials are failing now could that be causing this problem?

188248: Sep 10 04:13:00.642 EDT %MIVR-SS_DB-7-UNK: Checkout Connection
188249: Sep 10 04:13:00.642 EDT %MIVR-SS_DB-7-UNK: GetAvailableConnection
188250: Sep 10 04:13:00.846 EDT %MIVR-SS_DB-7-UNK: Connection denied
188251: Sep 10 04:13:00.846 EDT %MIVR-SS_DB-7-UNK: createAndCheckoutConnection:  DSN:  QA
188252: Sep 10 04:13:00.846 EDT %MIVR-SS_DB-7-UNK:Adding connection to DSN: com.cisco.wf.subsystems.edbs.EDBSDataSource@1eae15f
188253: Sep 10 04:13:00.877 EDT %MIVR-SS_DB-7-UNK:Exception: java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'ipcc'. Reason: The account is disabled. DB Create Connection Failed.  SQL State: 37000 Error Code: 18470
188254: Sep 10 04:13:00.877 EDT %MIVR-SS_DB-3-DB_CREATE_CONNECTION_FAILED:Database create connection failed: SQL State=37000,SQL Error code=18470
188255: Sep 10 04:13:00.877 EDT %MIVR-SS_DB-7-UNK: Exception:  java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'ipcc'. Reason: The account is disabled.occurred adding connection to QA
188256: Sep 10 04:13:00.877 EDT %MIVR-SS_DB-3-CANNOT_CREATE_DATASOURCE_FOR_DSN:Cannot create Datasource for given DSN: DSN Name=QA
188257: Sep 10 04:13:00.877 EDT %MIVR-SS_DB-7-UNK:Error in checkout from DSN, QA
188258: Sep 10 04:13:00.877 EDT %MIVR-SS_DB-3-ERROR_IN_CHECKOUT_FROM_DSN:Error in checkout from DSN: Task ID=null,DSN Name=QA
188259: Sep 10 04:13:00.877 EDT %MIVR-SS_DB-7-UNK:checkConnectivity error: java.lang.NullPointerException
188260: Sep 10 04:13:00.877 EDT %MIVR-SS_DB-7-UNK:CheckConnectivity, bad connection for QA
188261: Sep 10 04:13:00.877 EDT %MIVR-SS_DB-3-BAD_CONNECTION:Bad connection for the given DSN: DSN Name=QA
188262: Sep 10 04:13:00.877 EDT %MIVR-SS_DB-7-UNK: Checkout Connection
188263: Sep 10 04:13:00.877 EDT %MIVR-SS_DB-7-UNK: GetAvailableConnection
188264: Sep 10 04:13:00.877 EDT %MIVR-SS_DB-7-UNK: Granted connection # 46
188265: Sep 10 04:13:00.877 EDT %MIVR-SS_DB-7-UNK:Database subsystem Polling SQLState: 37000. If the database subsystem is OUT_OF_SERVICE please ensure that Polling SQLState exists in the list of values for the property com.cisco.wf.subsystems.edbs.SQLState=|HY000|Polling_SQLState_value| in the file wfavvid\SubsystemEDBS.properties.
188266: Sep 10 04:13:00.877 EDT %MIVR-SS_DB-7-UNK:EDBSConnection.checkConnectionState() - Ok.
188267: Sep 10 04:13:00.877 EDT %MIVR-SS_DB-7-UNK: Checkin Connection # 46
188268: Sep 10 04:13:00.877 EDT %MIVR-SS_DB-7-UNK: Connection : 46 added to available list.
188269: Sep 10 04:13:00.877 EDT %MIVR-SS_DB-7-UNK: Checkout Connection

Super Bronze

Re: Issue with DBWrite step - enters Connection Not available pa

So QA is not the DSN you are using? Are you sure? It would seem a little coincidental if that error is logged each time you attempt the connection..

Aaron

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!
Community Member

Re: Issue with DBWrite step - enters Connection Not available pa

The QA is an old dev DSN we have been using. The weird thing is we have two DSN's CORE and Lamda. Core has been worknig for a while doing database dips to read customer balance info and so forth, the new one I am using Lamda I can use for DBreads and get a result but not for DBwrites. The odd thing is I see no error messages or exceptions for either of these connections. I'm wandering if by chance we are limitted to the number of Database Subsystem connections because Core used to be set for a maximum of 20 and I changed it to 15 and Lamda is set for a maximum of 10. The QA that keeps failing used to have a maximum of 10 which I changed to 1 in case this was using connections Lamda needed. Now I'm not sure if there is a specific option where I can limit or increase the maximum number of connections for the system and I think if this was the case I think I would see an error message about maximum number of connections.

Silver

Re: Issue with DBWrite step - enters Connection Not available pa

Hello Warren,

If there's another database connection configured but not working, the system will keep trying to establish the connection with the remote database. This is probably why you see the connection attempts over and over again. This will also make the database subsystem go into partial service. Please go to the control center and check the database subsystem status. This might also affect the connection to other databases and I have even see it slowing the response time of server. Please try removing any unused DB connection and make sure that the service goes to IN_SERVICE.

Hope that helps!

Pablo

Community Member

Re: Issue with DBWrite step - enters Connection Not available pa

Hi Pablo,

        I took your advice but its still failing, the service is no longer in partial service now though, after looking through the logs I found the following error message. This is a bit of progress though as I now know the system is reaching the SQL server and gettting a SQL error.

563335: Sep 14 13:54:22.517 EDT %MIVR-SS_DB-3-EXECUTE_SQL_UPDATE_EXCEPTION:Exception occured while executing SQL Update: SQL State=null,SQL Error code=null,Connection #=null
563336: Sep 14 13:54:22.517 EDT %MIVR-DB_STEPS-3-EXECUTE_SQL_UPDATE_EXCEPTION:Error during executing SQL Update: Task ID=98000004192,Step Name=DB Write,SQL State=37000,SQL Error Code=128
563337: Sep 14 13:54:22.517 EDT %MIVR-SS_DB-7-UNK:Database subsystem SQLState: 37000. If the database subsystem is OUT_OF_SERVICE please ensure that Polling SQLState exists in the list of values for the property com.cisco.wf.subsystems.edbs.SQLState=|HY000|Polling_SQLState_value| in the file wfavvid\SubsystemEDBS.properties.
563338: Sep 14 13:54:22.517 EDT %MIVR-SS_DB-7-UNK:EDBSConnection.checkCurrentConnection() - Ok.

P.S. This is the query I am using below - All the columns are set to varchar and all the values I am passing are strings

insert into survey (CID, SDate, SurveyQ1, SurveyQ2, SurveyQ3, SurveyQ4) VALUES ($callerid, $SDate,$SurveyQ1, $SurveyQ2, $SurveyQ3, $SurveyQ4)

Community Member

Re: Issue with DBWrite step - enters Connection Not available pa

Ok I have made some head way since last posting this. I am having this issue though. I hanve changed all the values to int in the mssql database since int is one of the primitive java data types that is supposed work without any problems with the MS SQL. I am also writing data to all the sql columns, so I'm really scratching my head on this now on what to do next.

635220: Sep 15 14:09:05.998 EDT %MIVR-SS_DB-7-UNK: Granted connection # 53
635221: Sep 15 14:09:05.998 EDT %MIVR-SS_DB-7-UNK:  Exception:  java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. ExecuteUpdate Error Occurred
635222: Sep 15 14:09:05.998 EDT %MIVR-SS_DB-3-EXECUTE_SQL_UPDATE_EXCEPTION:Exception occured while executing SQL Update: SQL State=null,SQL Error code=null,Connection #=null
635223: Sep 15 14:09:05.998 EDT %MIVR-SS_DB-7-UNK: RMI Server  alter Columns failed.  SQLException: java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. SQLState:  37000 SQLErrorCode:  1038 Connection #: 53
635224: Sep 15 14:09:05.998 EDT %MIVR-SS_DB-4-EXECUTE_SQL_UPDATE_EXCEPTION:Exception occured while executing SQL Update: SQL State=37000,SQL Error code=1038,Connection #=53
635225: Sep 15 14:09:05.998 EDT %MIVR-SS_DB-7-UNK:Database subsystem SQLState: 37000. If the database subsystem is OUT_OF_SERVICE please ensure that Polling SQLState exists in the list of values for the property com.cisco.wf.subsystems.edbs.SQLState=|HY000|Polling_SQLState_value| in the file wfavvid\SubsystemEDBS.properties.
635226: Sep 15 14:09:05.998 EDT %MIVR-SS_DB-7-UNK:EDBSConnection.checkCurrentConnection() - Ok.
635227: Sep 15 14:09:05.998 EDT %MIVR-SS_DB-7-UNK: Checkin Connection # 53
635228: Sep 15 14:09:05.998 EDT %MIVR-SS_DB-7-UNK: Connection : 53 added to available list.

Super Bronze

Re: Issue with DBWrite step - enters Connection Not available pa

Hi

Looks like a problem with your SQL syntax - maybe post up what you've set on the step?

635221: Sep 15 14:09:05.998 EDT %MIVR-SS_DB-7-UNK:  Exception:  java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. ExecuteUpdate Error Occurred

Aaron

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!
Community Member

Re: Issue with DBWrite step - enters Connection Not available pa

Hi Aaron this is my query

insert into tst_survey (CID, SDate, SurveyQ1, SurveyQ2, SurveyQ3, SurveyQ4) VALUES ($CID, $SDate, $Q1, $Q2, $Q3, $Q4)

Community Member

Re: Issue with DBWrite step - enters Connection Not available pa

Hi Everyone,

                      First of thank you all for your help I was able to get everything working and  I will go through what were my issues so in case anyone else has a similar issue they know what to look for.

Enviroment - IPCC Express 4.0(5) Premium

Issue - My DBWrite Statement kept failing

Cause: There were two reasons why my DBwrite kept failing:

1. An old DSN

   

    When I started this project they had an old DSN which they were using before for QA which was never removed but the account it was using for authentication had been removed. This caused the IPCC environment to keep trying to recreate the DSN, even though I had the other DSN setup correctly it never was able to create a pool for connections. This is why when it was failing and taking the  No connection path, but once I deleted the old DSN and refreshed the DSN I was using the pool came up and it registered multiple connections

2. My SQL Query

   

    The SQL query I was using was inserting caller input from a survey. There were multiple things that I missed to consider: (1) For my version of IPCC you have to write to all the fields in the database if you do not it will not write to the database and throw a sql exception in the log (2) The CSR is a java based app so all the data types are java as well only a few java data types convert well to MSSQL data types without an issue unless you have the java.sql class available e.g. a java.string does not convert properly to a MSSQL varchar but a java.int and an MSSQL int convert without an issue. The resolution I came up with to solve this was the following since I could only insert a few data types without issue to MSSQL I changed all my data types to int. So the callerid, date, and customer response were all saved as ints. I achieved this by using the int function int.valueof() to convert the string values to int values. For the date I used a date variable and used the getDate(), getMonth() and getYear() function for the int values of the current date, I also need to point out that with the year and month int you have to do some minor tweaks, the month is less by one of the current month and the year you need to add 1900 so it it correct.

P.S. The data types that I believe work without any issue to MSSQL from the IPCC Express CSR are ints, chars, bool and double. I would love for a Cisco Expert to confirm this or correct me on this because I did quite a bit of digging and I found very little on this topic.

2244
Views
12
Helpful
10
Replies
CreatePlease to create content