MII 12.1 - connection to MS SQL2005 database

[Former Member]

Hello,

according to #1109274, we deployed JDBC driver from kernel.

problem with data server definition:

What we can not find, is any documentation, how to configure "Data

Server" to connect to MS SQL 2005.

If we use defult values, Connection status is always "Stooped"

If we use values from SDN documentation for version 12.0 -> status is

Error !

Please,WHICH values must be under

1. JDBC driver

2. Server URL

to connect to internal MSSQL 2005 database ?

Thanks in advance

Kind regards

Thomas

[Former Member · Accepted solution]

Hello Thomas,

Please try to pass this "com.microsoft.sqlserver.jdbc.SQLServerDriver' in JDBCDriver and "jdbc:sqlserver://<dbserver>:<port>;databaseName=<dbname>" in Server URL field in Dataservers.

Have you also deployed sqljdbc.jar file in JDBC Drivers under System Management in MII.

Let me know if this solves the issue.

Regards,

Kumar

Edited by: Sudhir Kumar Shambana on May 5, 2010 10:17 AM

[Former Member · Accepted solution]

Hi Kumar,

thanks you for quick reply - but unfortunately it does not work

allready tried ...according

/people/abesh.bhattacharjee/blog/2008/01/23/xmii-120-connecting-to-a-microsoft-sql-server-2000

Kind regards

Thomas

[Former Member · Accepted solution]

Hi Thomas,

How about the status of JDBC Driver on NWA, have you checked this.

Log on to NWA Administration->Configuration management-> Infrastructure--->Application Resources and choose JDBC Drivers from the drop down of show ALL Resources, the status should be Green

Regards,

Kumar

[Former Member · Accepted solution]

Hi Kumar,

all green, except:

SAP/BC_ADM sap.com/tc_monitoringsysteminfo > Status red

thanks

Thomas

[Former Member · Accepted solution]

Thomas,

That sounds good, can you try login with the same user credentials onto SQL Server that you trying to pass in dataserver and check where you able to login.

Regards,

Kumar

[Former Member · Accepted solution]

Hi Kumar,

great - connected !

Nachricht: Created Connection pool [jdbc:sqlserver://cscappwin056.at.emea.csc.com:1433;database=CSC_PC]:

ConnectionObject[con=com.microsoft.sqlserver.jdbc.SQLServerConnection@4030fc78,inUse=false,useCount=0]

Datum: 2010-05-05

Zeit: 14:17:39:367

Kategorie: /Applications/XMII

now, one error in log:

No SR connection available, set up it and try again.

[EXCEPTION]

com.sap.esi.esp.lib.mm.config.exceptions.SRTechnicalExceptionNo SR connection available, set up it and try again.

at com.sap.engine.services.webservices.tools.wsil.SOACentralSRPublisherImpl.getPS(SOACentralSRPublisherImpl.java:2228)

at

Kind regards

Thomas

[Former Member · Accepted solution]

Hi Kumar,

thanks very much for your help to solve that problem !

I appreciate that.

Kind regards Thomas

[Former Member]

Thomas,

Below given are the things which you need to take care while trying to connect with MS SQL 2005.

1. Deploy sqljdbc.jar using SYSTEM MANAGEMENT --> JDBC Drivers Link

2. In Data servers, the details are as follows

JDBCDriver: com.microsoft.sqlserver.jdbc.SQLServerDriver

ServerURL: jdbc:sqlserver://<IPADDRESS or HOSTNAME>:1433;database=<DBNAME>

Hope this works.

Regards

Muzammil

[Former Member]

Also check for the SQL Server settings

1. Open the SQL Server Configuration Manager from the start menu.

2. Enable the TCP/IP connection

3. Make all the (three) dynamic port values "BLANK"

4. Set 1433 (default Port for SQL Server) for the all(three) TCP Port field.

This is the way i did, and it works fine for me.

Regards

Muzammil

[Former Member]

Hi Muzamill,

with these settings:

Connector IDBC

ConnectorID

ConnectorType SQL

DatePrefix '

DateSuffix '

DaysRetention 7

Description test

Enabled T

InitCommand

InternalDateFormat yyyy-MM-dd HH:mm:ss

JDBCDriver com.microsoft.sqlserver.jdbc.SQLServerDriver

MaxRetryCount 5

Name troe_2

PoolMax 100

PoolSize 1

RetryInterval 60000

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

ServerURL jdbc:sqlserver://cscappwin056.at.emea.csc.com:1433;database=CSC_PC

Timeout 15

UseCount 256

UserName sa

ValidationQuery SELECT GETDATE()

WaitTime 30

I get this:

Unable to fill connection pool for jdbc:sqlserver://cscappwin056.at.emea.csc.com:1433;database=CSC_PC

[EXCEPTION]

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'sa'.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)

at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(Unknown Source)

at com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(Unknown Source)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithoutFailover(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)

at com.sap.xmii.Illuminator.tools.database.ConnectionPool.getConnection(ConnectionPool.java:349)

++

Created Connection pool [jdbc:sqlserver://cscappwin056.at.emea.csc.com:1433;database=CSC_PC]:

Datum: 2010-05-05

Zeit: 12:11:13:413

Kategorie: /Applications/XMII

Standort: com.sap.xmii.Illuminator.tools.database.ConnectionPool

Applikation: sap.com/xapps_xmiiear

Thread: Thread[Managed_Application_Thread_15,5,Managed_Application_Thread]

Datenquelle: j2ee\cluster\server0\log\applications_00.log

Thx and kind regards

Thomas

[Former Member]

Hi Muzammil,

thank you very much for helping me to solve that problem, I appreciate that.

Kind regards Thomas