SQL Server 2000 problem with MII Version 12.1.8 Build(24) Reconnecting

[Former Member]

Hi,

I have a problem using XMII Version 12.1.8 Build(24) with com.microsoft.sqlserver.jdbc.SQLServerDriver JDBC driver with SQL 2000.

The problem we have is the machine where the SQL server is installed on reboot as per schedule once a week. When the server comes back online, XMII seems to get problem to reconnect correctly. Sometimes it reconnects perfectly, sometimes not.

When it fails, the only way to fix the problem is to uncheck the 'Enabled' checkbox, then save the data server, then check the 'Enabled' checkbox, then save again.

When the reconnect issue happens, uploading messages to SAP fail 100% of time, but downloading Control Recipes from SAP works 50% of time. For example, some PO's can be processed correctly, some others not, causing data losses due to poor data buffering(buffering doesn't cover connection time out). The failed PO's are marked as 'Sent' into CO53 SAP transaction but in fact, are lost in a hole causing data losses.

See the error we get atfer the server reboot:

Unable to get a connection from the pool

[EXCEPTION]

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host VANASR70, port 1433 has failed. Error: "connect timed out. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting... [see details]

Cannot create a connection to the database

[EXCEPTION]

com.sap.xmii.Illuminator.system.CommunicationException: Cannot create a connection to the database... [see details]

Error processing transaction com.sap.xmii.bls.engine.TransactionInstance@cee764f

[EXCEPTION]

com.sap.xmii.scheduler.CronException: PMU ERR VAN - Cannot Qry MSIDs.... [see details]

Unable to get a connection from the pool

[EXCEPTION]

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host VANASR70, port 1433 has failed. Error: "Connection refused. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting... [see details]

Unable to get a connection from the pool

[EXCEPTION]

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host VANASR70, port 1433 has failed. Error: "connect timed out. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting... [see details]

The only way we have thought to workaround this problem is to develop our own buffering method into XMII workbench puting all data received from SAP or SQL insert query into an Oracle temp database. We should probably be able to fix this by this way, but it reprensents many costs and efforts, so we try to avoid this for sure.

Please, if you can provide us a patches or hints about how to fix this, it could be very appreciated because loosing data is very anoying.

Thanks a lot!

Edited by: mrmans on May 19, 2011 5:48 PM

Edited by: Michael Appleby on May 19, 2011 6:10 PM

[Former Member]

I'm using com.microsoft.sqlserver.jdbc.SQLServerDriver to connect MSSQL 2000 server. Should I use com.inet.tds.TdsDriver instead?

Maybe it could be the problem...

[jamie_cawley]

Did you provide a validation query? Is the server using a static ip address? Post the data server config.

Jamie

Edited by: Jamie Cawley on May 19, 2011 12:52 PM

[Former Member]

The validation query is in place and the system works very well 99% of the time.

When the SQL server machine reboot each sunday morning, during the time the machine is rebooting, the data server lose the connection which is normal, but when the machine comes back alive(less then 10 minutes), the data server seems to stay in an 'in-between' state showing the connection status 'Running' but showing also error messages posted in the orginial post in Netweaver logs. So at first look the data server is up and running and reconnected to SQL server after the reboot, but in fact, following these errors after the SQL server machine has rebooted, some data are processed correctly from SAP (Control Recipes) and some not.

The problems looks like very similar to threadID 1596329.

Here is the data server configuration for all our 5 data servers:

Connector IDBC

ConnectorID

ConnectorType SQL

DatePrefix '

DateSuffix '

DaysRetention 7

Description VANASR70 INTERFACE with MS-SQL JDBC 3.0

Enabled T

InitCommand

InternalDateFormat yyyy-MM-dd HH:mm:ss

JDBCDriver com.microsoft.sqlserver.jdbc.SQLServerDriver

MaxRetryCount 50

Name PROF_INT_VAN

PoolMax 150

PoolSize 100

RetryInterval 60000

ServerPackage com.sap.xmii.Illuminator.connectors.IDBC

ServerURL jdbc:sqlserver://VANASR70:1433;databaseName=INTERFACE

Timeout 30

UseCount 256

UserName mii_user

ValidationQuery SELECT GETDATE()

WaitTime 60

[Former Member]

And yes the server uses a static adress, in fact, we use an aliasas which is VANASR70

[jamie_cawley]

It may be worth trying a different version driver, una2000, 2005 or 2008 depending on what one you are using now. Its possible that a change was made in the driver that MII is not responding correctly to. I would also suggest entering a CSS ticket.

Jamie

[Former Member]

We're using una2000 to connect to MSSQL 2000.

[jamie_cawley]

Your JDBCdriver path is actually utilizing the 2005 or 2008 driver. I would assume your netweaver instance is running sql 2005 or you uploaded the newer driver. The path to the una2000 driver would be

com.inet.tds.TdsDriver

with a server url format of

jdbc:inetdae:server:1433?database=database&sql7=true

Jamie