Skip to Content

SQL DataServers & MII Buffering

Hello All,

We're running MII 12.1.7 (Build 44), in order to update a series of MS-SQL 2000 located off-site with some data. All remote SQL servers are accessed using the com.inet.tds.TdsDriver (Una2000.jar) which, unfortunately, times out from time to time.

Since all dataservers have buffering features, I've set them up so that the buffers are active, but I'm not very sure when to expect the data buffering to run. I've tested the following situations:

1. SQL Data Server disabled. Running the MII transaction to push the data to the MS-SQL server throws the following error:

[ERROR][QRY] Server XXXXXXXX is currently disabled.

It's expected that the buffers won't hold anything at this point, because the data server through which the content is obtained is disabled.

2. SQL Data Server Enabled, Wrong credentials. Running the MII transaction to push the data to the MS-SQL server throws the following error: [ERROR][QRY]Pending requests found, current request will be retried at a later date.

It's expected and observed that the buffers will hold all data at this point. The data will be posted to the SQL server as soon as good credentials are set for the SQL user.

3. SQL Data server enabled, good credentials, firewall blocking 1433 port temporarily. Running the MII transaction to push the data to the MS-SQL server throws the following errorlogged in NW : [ERROR][QRY] java.sql.SQLException: [TDS Driver] Operation interrupted.

Since the SQL query temprate returned an error at runtime, I was expecting the buffers to hold the data and retry the query as per the buffering settings. However, I cannot see the data being held in the buffers, the SQL BLS block reporting (i.e. last error message) "Response timed out, request terminated." Subsequently, the application log has 3 related entries :

#2.#2010 11 16



Plain##Response timed out, request terminated#

#2.#2010 11 16 22:38:35:571#0-500#Error#/Applications/XMII/



Plain##Error processing command for template [PROJECT/SQL_TEMPLATE]#

#2.#2010 11 16 22:38:35:598#0-500#Error#/Applications/XMII/



Plain##Error in command: INSERT INTO ....

The same insert command works fine when the connection is not interrupted inbetween, not having any syntax errors as the third message in the logs might suggest.

Is the above the expected buffering behavior? No buffering when the connection drops when executing a SQL command? If yes, how do I avoid it? Should I program my own buffering for the cases when the connection drops in the middle of a SQL transaction (i.e. based on the "Response timed out, request terminated." message received at runtime)?



Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Nov 17, 2010 at 09:21 PM

    SQL 2000 - are you sure it's the driver?

    As you have found the buffering is largely communication failure based retries (pseudo - garanteed delivery).

    It would appear that a mid-stream drop doesn't fit this classification since the initial Query delivery to the database was already made. Your suggestion for checking the Resultset from the SQLQuery action block and the Success bit will be your best bet for additional error handling.

    If you think there is a possible bug or needed expansion of functionality then I would suggest logging a support ticket, referencing this thread and providing your steps to reproduce.



    Add comment
    10|10000 characters needed characters exceeded

    • We'll set up the MS-SQL 2005 driver ASAP and I start to look into a local cacheing mechanism, having a transaction re-run the cached insert statements at given intervals of time.

      You're right, 120 minutes is a long time, determining some other u2018No Pool availableu2019 situations.